Oracle EBS: 修改item主檔存檔耗時過久問題

這狀況只有特定的organization反應, 找到的解決方式是:

  begin
       ad_ctx_ddl.sync_index('APPLSYS.FND_LOBS_CTX');
  end;

查看資料筆數:

  CTXSYS.CTX_PENDING
  CTXSYS.DR$WAITING

CTXSYS.CTX_PENDING原先超過百萬筆, 以每週約兩萬筆的數量減少. 有時系統負載重時, 會先把程式停掉, 筆數又會再增加.

CTXSYS.DR$WAITING則在剛執行時降為0, 後來會再慢慢增加.

至於存檔的速度是否真有變快, 也只有請使用者多觀察.

 

問題原因: 可執行以下兩段SQL, 查看CTX_PENDING和DR$WAITING是否有大量pending/waiting index

 

  select pnd_index_owner, pnd_index_name, count (*) pnd_count
    from ctxsys.ctx_pending
group by pnd_index_owner, pnd_index_name
order by pnd_count desc;
 

  select wtg_index_owner, wtg_index_name, count (*) wtg_count
    from (select                                    /*+ ORDERED USE_NL(I P) */
                u.name wtg_index_owner,
                 idx_name wtg_index_name,
                 ixp_name wtg_partition_name,
                 wtg_rowid
            from ctxsys.dr$waiting,
                 ctxsys.dr$index i,
                 ctxsys.dr$index_partition p,
                 sys.user$ u
           where     idx_owner# = u.user#
                 and idx_id = ixp_idx_id
                 and wtg_pid = ixp_id
                 and wtg_pid != 0
                 and wtg_cid = idx_id
          union all
          select                                      /*+ ORDERED USE_NL(I) */
                u.name wtg_index_owner,
                 idx_name wtg_index_name,
                 null wtg_partition_name,
                 wtg_rowid
            from ctxsys.dr$waiting, ctxsys.dr$index i, sys.user$ u
           where idx_owner# = u.user# and wtg_pid = 0 and wtg_cid = idx_id) ctx_waiting
group by wtg_index_owner, wtg_index_name
order by wtg_count desc;

 

 

Reference:

1.请教：在新建物料和修改物料时非常慢是什么原因
http://www.itpub.net/thread-1440660-1-1.html

2.Bad performance for item attribute update
http://rex-study.blogspot.tw/2012/12/bad-performance-for-item-attribute.html

3.Organization Item Form INVIDITM. fmb Performance Issue When Updating On DELETE FROM DR$WAITING (Doc ID 1165366.1)
https://support.oracle.com/

4.There Are Millions Of Records In CTXSYS.DR$PENDING And CTXSYS.DR$WAITING Tables (Doc ID 382809.1)
https://support.oracle.com/