Very Slow Response Time For Item Search In iProcurement :

Very Slow Response Time For Item Search In iProcurement / 
How to Recuce the Size of the ICX.DR$ICX_CAT_ITEMSCTXDESC_HDRS$I Table 

 CAUSE

The index ICX_CAT_ITEMSCTXDESC_HDRS, which is used for iProcurement item search has slow performance poor performance.

This may be due to excess data in CTXSYS.DR$PENDING and CTXSYS.DR$WAITING or related causes:

select u.username, i.idx_name

from dr$index i, dba_users u

where u.user_id=i.idx_owner#

and idx_id in (select pnd_cid from dr$pending);

select u.username, i.idx_name

from dr$index i, dba_users u

where u.user_id=i.idx_owner#

and idx_id in (select wtg_cid from dr$waiting);

SOLUTION

*** DO THIS IN A TEST INSTANCE FIRST ***

Login to the apps user

Sqlplus apps/apps

1. Synchronize the index used for item search:

  EXEC ad_ctx_ddl.sync_index(IDX_NAME => 'ICX.ICX_CAT_ITEMSCTXDESC_HDRS');

2. Optimize the index:

EXECUTE ad_ctx_ddl.optimize_index(IDX_NAME =>

'ICX.ICX_CAT_ITEMSCTXDESC_HDRS', optlevel => 'FULL');

3. Retest the issue and confirm improved item search performance.

IF THE PERFORMANCE IS STILL SLOW

4.  Recreate the index as follows:

4.1 Run this query and take note of the results

   SELECT * FROM all_objects WHERE object_name='ICX_CAT_ITEMSCTXDESC_HDRS';

4.2 If an index is shown from action 4.1 query above (it should be shown unless the index is already dropped), then drop the existing index as follows:

   exec ICX_CAT_INTERMEDIA_INDEX_PVT.drop_index;

4.3 Ensure no record (no index) is retrieved by following sql, before proceeding to the next step.  This ensures the drop_index command has completed.

   SELECT * FROM all_objects WHERE object_name='ICX_CAT_ITEMSCTXDESC_HDRS';

4.4 Run the following script to create the index:

   exec ICX_CAT_INTERMEDIA_INDEX_PVT.create_index;

5.  Retest again, and confirm the item search performance is improved.

*** DO THIS IN A TEST INSTANCE FIRST ***

(Doc ID 1292406.1 & 1553418.1)