Oracle Database Discussions

Hi all,

My db version is 10.2.0.4 and application version is 11.5.10.2. A form screen is taking long time to move to next screen, so we have trace for the session and generated a tkprof sorted by exeela... Here is the most elapse time query

INSERT INTO GMI_TRAN_TMP ( SESSION_ID,ITEM_ID,LINE_ID,DOC_ID,LOT_NO,SUBLOT_NO,
  LOT_ID,LOT_CREATED,EXPIRE_DATE,QC_GRADE,WHSE_CODE,LOCATION,LOCT_ONHAND,
  LOCT_ONHAND2,COMMIT_QTY,COMMIT_QTY2,TRANS_ID,ID_COUNT,ALLOC_QTY,ALLOC_QTY2,
  LINE_DETAIL_ID,REASON_CODE,VENDOR_LOT_NO )  SELECT :b1,T.ITEM_ID,-1,-1,
  L.LOT_NO,L.SUBLOT_NO,T.LOT_ID,L.LOT_CREATED,L.EXPIRE_DATE,L.QC_GRADE,
  T.WHSE_CODE,T.LOCATION,0,0,SUM(T.TRANS_QTY),SUM(NVL(T.TRANS_QTY2,0)),0,0,0,
  0,0, NULL ,L.VENDOR_LOT_NO   FROM IC_LOTS_MST L,IC_TRAN_PND T,IC_ITEM_MST I,
  IC_WHSE_MST W  WHERE L.ITEM_ID = :b2  AND T.WHSE_CODE = :b3  AND
  T.WHSE_CODE = W.WHSE_CODE  AND L.ITEM_ID = T.ITEM_ID  AND L.INACTIVE_IND =
  0  AND T.LOT_ID = L.LOT_ID  AND T.DELETE_MARK = 0  AND T.COMPLETED_IND = 0
  AND T.TRANS_QTY < 0  AND (T.LOT_ID > 0  OR (I.LOT_CTL = 0  AND I.LOCT_CTL *
  W.LOCT_CTL  > 0  AND T.LOCATION != :b4 )) AND L.ITEM_ID = I.ITEM_ID  GROUP
  BY :b1,T.ITEM_ID,L.LOT_NO,L.SUBLOT_NO,T.LOT_ID,L.LOT_CREATED,L.EXPIRE_DATE,
  L.QC_GRADE,T.WHSE_CODE,T.LOCATION,L.VENDOR_LOT_NO


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.09          6        173          0           0
Execute      2      0.59       5.44        346      95441         17          12
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.59       5.53        352      95614         17          12

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173  (APPS)

Rows     Row Source Operation
-------  ---------------------------------------------------
     12  HASH GROUP BY (cr=95438 pr=346 pw=0 time=5413001 us)
     12   NESTED LOOPS  (cr=95438 pr=346 pw=0 time=20130 us)
     12    NESTED LOOPS  (cr=95398 pr=346 pw=0 time=19854 us)
      2     NESTED LOOPS  (cr=18 pr=0 pw=0 time=144 us)
2      NESTED LOOPS  (cr=12 pr=0 pw=0 time=108 us)
      2       TABLE ACCESS BY INDEX ROWID IC_WHSE_MST (cr=4 pr=0 pw=0 time=50 us)
      2        INDEX UNIQUE SCAN IC_WHSE_MST_PK (cr=2 pr=0 pw=0 time=30 us)(object id 421505)
      2       TABLE ACCESS BY INDEX ROWID IC_ITEM_MST_B (cr=8 pr=0 pw=0 time=49 us)
      2        INDEX UNIQUE SCAN IC_ITEM_MST_B_PK (cr=6 pr=0 pw=0 time=22 us)(object id 421326)
      2      INDEX UNIQUE SCAN IC_ITEM_MST_TL_PK (cr=6 pr=0 pw=0 time=26 us)(object id 421333)
     12     TABLE ACCESS BY INDEX ROWID IC_TRAN_PND (cr=95380 pr=346 pw=0 time=19718 us)
  96020      INDEX RANGE SCAN IC_TRAN_PNDI6 (cr=420 pr=0 pw=0 time=422 us)(object id 166920)
     12    TABLE ACCESS BY INDEX ROWID IC_LOTS_MST (cr=40 pr=0 pw=0 time=273 us)
     12     INDEX UNIQUE SCAN IC_LOTS_MST_PK (cr=28 pr=0 pw=0 time=131 us)(object id 421375)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
     12   HASH (GROUP BY)
     12    NESTED LOOPS
     12     NESTED LOOPS
      2      NESTED LOOPS
      2       NESTED LOOPS
      2        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                   'IC_WHSE_MST' (TABLE)
      2         INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                    'IC_WHSE_MST_PK' (INDEX (UNIQUE))
      2        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                   'IC_ITEM_MST_B' (TABLE)
      2         INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                    'IC_ITEM_MST_B_PK' (INDEX (UNIQUE))
      2       INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                  'IC_ITEM_MST_TL_PK' (INDEX (UNIQUE))
     12      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                 'IC_TRAN_PND' (TABLE)
  96020       INDEX   MODE: ANALYZED (RANGE SCAN) OF 'IC_TRAN_PNDI6'
                  (INDEX)
     12     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                'IC_LOTS_MST' (TABLE)
     12      INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'IC_LOTS_MST_PK'
                 (INDEX (UNIQUE))
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       5        0.00          0.00
  SQL*Net message from client                     5        0.00          0.00
  gc current block 2-way                        736        0.00          0.23
  db file sequential read                       346        0.06          4.62
  gc cr grant 2-way                             172        0.00          0.04
********************************************************************************

I could see a lot of fetch on the query and in explain plan could see this

96020       INDEX   MODE: ANALYZED (RANGE SCAN) OF 'IC_TRAN_PNDI6'

what does it indicate on this index? 96020 rows fetched?... How do i tune this query..?!!

thanks,
baskar.l