This discussion is archived
1 2 3 Previous Next 32 Replies Latest reply: Jan 7, 2010 8:25 PM by PavanKumar RSS

Number of Executions of a query - from tkprof

baskar.l Pro
Currently Being Moderated
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
  • 1. Re: Number of Executions of a query - from tkprof
    PavanKumar Guru
    Currently Being Moderated
    Hi,
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Query Cached out.

    Can you provide the explain separately of Select Query
    Object Id - 166920 - Can provide the details of Object and indexes on this.

    - Pavan Kumar N
    Oracle 9i/10g - OCP
    http://oracleinternals.blogspot.com/
  • 2. Re: Number of Executions of a query - from tkprof
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    baskar.l wrote:
    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)
    The significance of this pair of lines is that Oracle has picked up 96,020 rowids from the index range scan, and visited the table that many times, but thrown away all but 12 rows. This suggests that that this is the wrong index for the query, or possibly there is no good index.

    Check the definitions of all the indexes on that table, and the predicates used on that table (explain plan with dbms_xplan to see the actual use of predicates) to see if there is a more appropriate index. If there is, then you need to work out why Oracle isn't using it (a common problem is a clustering_factor that is not representative of the truth).

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan
    
    Edited by: Jonathan Lewis on Jan 5, 2010 5:04 PM
    Correcting error ('we' for '12')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 3. Re: Number of Executions of a query - from tkprof
    PavanKumar Guru
    Currently Being Moderated
    Hi Jonathan,
    a common problem is a clustering_factor that is not representative of the truth
    Can you guide on the above statement - I think histograms might be width balanced due to that - it might be facing issue and composite index columns on wrong column. I am analyzing that might be Issue.

    One more doubt - is there any chance of dynamic sampling takes place on the above scenario

    - Pavan Kumar N
    Oracle 9i/10g - OCP
    http://oracleinternals.blogspot.com/
  • 4. Re: Number of Executions of a query - from tkprof
    690963 Newbie
    Currently Being Moderated
    Hi Jonathan,

    Was reading your reply to this post and 2 questions
    The significance of this pair of lines is that Oracle has picked up 96,020 rowids from the index range scan, and visited the table that many times, but thrown away all but we rows. This suggests that that this is the wrong index for the query, or possibly there is no good index.
    1."but thrown away all but we rows".....out of 96,020 rowids how many does it return to the next part
    2. why is this a wrong index only becuause it pickup up lot many rows...?

    Just trying to understand the analysis of the tkprof ....
  • 5. Re: Number of Executions of a query - from tkprof
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Pavan Kumar wrote:
    Hi Jonathan,
    a common problem is a clustering_factor that is not representative of the truth
    Trying to solve a problem remotely like this is largely a question of small steps to avoid going in the wrong direction. Since the obvious problem is that the index that has been used is not appropriate I just want to check if there is an index that looks as if it should be more appropriate. If there is then there are several reasons why Oracle should haev chosen the wrong one and the next step would be to determine why it has made the wrong choice. Your thoughts about cardinality and histogram effects certainly could turn out to be the right answer.

    If the OP comes back with a comment that some other index is OBVIOUSLY the correct one, then the clustering_factor as a ciculprint is both (a) common, and (b) simple to check - so I was just taking a short-cut in the dialogue by mentioning it up-front.

    >
    One more doubt - is there any chance of dynamic sampling takes place on the above scenario
    Do you mean "would dynamic sampling help" ?
    There's no way to tell. It depends partly on the reason for the bad choice of index, partly on the variability of the complete data set, and partly on the variability of the query result set.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 6. Re: Number of Executions of a query - from tkprof
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    njafri wrote:


    1."but thrown away all but we rows".....out of 96,020 rowids how many does it return to the next part
    That should have been "all but 12 rows" (I've corrected it in the original - I was touch typing and missed ;) ) - the tkprof output is telling you that it supplied 96,020 rowids to the next step - which is the table access step. The table access step therefore visits 96,020 rows in the table but returns only 12 rows to the step after.
    2. why is this a wrong index only becuause it pickup up lot many rows...?
    Correct (though perhaps "unsuitable" is a better word than "wrong". It may be the best index that is available for the job).

    For more insight into reading execution plans and tkprof output, the best source of information at present is [+*Christian Antogninis*+|http://antognini.ch/top/] book Trouble-shooting Oracle Performance.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 7. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    hi jonathan and all,

    Thanks for your replies..i do see a index which got created a month back..and might probably that might be causing the issue...
    From the tkprof 96020       INDEX   MODE: ANALYZED (RANGE SCAN) OF 'IC_TRAN_PNDI6'
    
    SQL> @obj
    Enter value for 1: IC_TRAN_PNDI6
    old   3: where object_name like upper('&1%')
    new   3: where object_name like upper('IC_TRAN_PNDI6%')
    
    OWNER      OBJECT_NAME                    OBJECT_TYPE         CREATED   LAST_DDL
    ---------- ------------------------------ ------------------- --------- ------------------
    GMI        IC_TRAN_PNDI6                  INDEX               30-MAY-02 13-nov-09 07:47:29
    
    1 row selected.
    
    and its definition
    
    SQL> select dbms_metadata.get_ddl('INDEX','IC_TRAN_PNDI6','GMI') from dual;
    
      CREATE INDEX "GMI"."IC_TRAN_PNDI6" ON "GMI"."IC_TRAN_PND" ("ITEM_ID", "COMPLET
    ED_IND", "DELETE_MARK", "DOC_TYPE", "WHSE_CODE")
      PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "APPS_TS_TX_IDX"
    thanks,
    baskar.l
  • 8. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    To add on the above..the xplan for the query
    SQL> explain plan for INSERT INTO GMI_TRAN_TMP ( SESSION_ID,ITEM_ID,LINE_ID,DOC_ID,LOT_NO,SUBLOT_NO,
      2    LOT_ID,LOT_CREATED,EXPIRE_DATE,QC_GRADE,WHSE_CODE,LOCATION,LOCT_ONHAND,
      3    LOCT_ONHAND2,COMMIT_QTY,COMMIT_QTY2,TRANS_ID,ID_COUNT,ALLOC_QTY,ALLOC_QTY2,
      4    LINE_DETAIL_ID,REASON_CODE,VENDOR_LOT_NO )  SELECT :b1,T.ITEM_ID,-1,-1,
      5    L.LOT_NO,L.SUBLOT_NO,T.LOT_ID,L.LOT_CREATED,L.EXPIRE_DATE,L.QC_GRADE,
      6    T.WHSE_CODE,T.LOCATION,0,0,SUM(T.TRANS_QTY),SUM(NVL(T.TRANS_QTY2,0)),0,0,0,
      7    0,0, NULL ,L.VENDOR_LOT_NO   FROM IC_LOTS_MST L,IC_TRAN_PND T,IC_ITEM_MST I,
      8    IC_WHSE_MST W  WHERE L.ITEM_ID = :b2  AND T.WHSE_CODE = :b3  AND
      9    T.WHSE_CODE = W.WHSE_CODE  AND L.ITEM_ID = T.ITEM_ID  AND L.INACTIVE_IND =
     10    0  AND T.LOT_ID = L.LOT_ID  AND T.DELETE_MARK = 0  AND T.COMPLETED_IND = 0
     11    AND T.TRANS_QTY < 0  AND (T.LOT_ID > 0  OR (I.LOT_CTL = 0  AND I.LOCT_CTL *
     12    W.LOCT_CTL  > 0  AND T.LOCATION != :b4 )) AND L.ITEM_ID = I.ITEM_ID  GROUP
     13    BY :b1,T.ITEM_ID,L.LOT_NO,L.SUBLOT_NO,T.LOT_ID,L.LOT_CREATED,L.EXPIRE_DATE,
     14    L.QC_GRADE,T.WHSE_CODE,T.LOCATION,L.VENDOR_LOT_NO
     15  ;
    
    Explained.
    
    SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
    Plan hash value: 3649219943
    
    ------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                 |                   |     7 |   847 |    18   (6)| 00:00:01 |
    |   1 |  HASH GROUP BY                   |                   |     7 |   847 |    18   (6)| 00:00:01 |
    |   2 |   NESTED LOOPS                   |                   |     7 |   847 |    17   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS                  |                   |     1 |    58 |    15   (0)| 00:00:01 |
    |   4 |     NESTED LOOPS                 |                   |     1 |    28 |     4   (0)| 00:00:01 |
    |   5 |      NESTED LOOPS                |                   |     1 |    19 |     3   (0)| 00:00:01 |
    |   6 |       TABLE ACCESS BY INDEX ROWID| IC_WHSE_MST       |     1 |     7 |     1   (0)| 00:00:01 |
    |*  7 |        INDEX UNIQUE SCAN         | IC_WHSE_MST_PK    |     1 |       |     0   (0)| 00:00:01 |
    |   8 |       TABLE ACCESS BY INDEX ROWID| IC_ITEM_MST_B     |     1 |    12 |     2   (0)| 00:00:01 |
    |*  9 |        INDEX UNIQUE SCAN         | IC_ITEM_MST_B_PK  |     1 |       |     1   (0)| 00:00:01 |
    |* 10 |      INDEX UNIQUE SCAN           | IC_ITEM_MST_TL_PK |     1 |     9 |     1   (0)| 00:00:01 |
    |* 11 |     TABLE ACCESS BY INDEX ROWID  | IC_TRAN_PND       |     1 |    30 |    11   (0)| 00:00:01 |
    |* 12 |      INDEX RANGE SCAN            | IC_TRAN_PNDI6     |     6 |       |     5   (0)| 00:00:01 |
    |* 13 |    TABLE ACCESS BY INDEX ROWID   | IC_LOTS_MST       |     6 |   378 |     2   (0)| 00:00:01 |
    |* 14 |     INDEX UNIQUE SCAN            | IC_LOTS_MST_PK    |     1 |       |     1   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       7 - access("W"."WHSE_CODE"=:B3)
       9 - access("B"."ITEM_ID"=TO_NUMBER(:B2))
      10 - access("T"."ITEM_ID"=TO_NUMBER(:B2) AND "T"."LANGUAGE"=USERENV('LANG'))
      11 - filter("T"."TRANS_QTY"<0 AND ("T"."LOT_ID">0 OR "B"."LOT_CTL"=0 AND
                  "B"."LOCT_CTL"*"W"."LOCT_CTL">0 AND "T"."LOCATION"<>:B4))
      12 - access("T"."ITEM_ID"=TO_NUMBER(:B2) AND "T"."COMPLETED_IND"=0 AND "T"."DELETE_MARK"=0
                  AND "T"."WHSE_CODE"=:B3)
           filter("T"."WHSE_CODE"=:B3)
      13 - filter("L"."INACTIVE_IND"=0)
      14 - access("L"."ITEM_ID"=TO_NUMBER(:B2) AND "T"."LOT_ID"="L"."LOT_ID")
    
    35 rows selected.
    thanks,
    baskar.l
  • 9. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    hi,

    Also got the clustering factor...
    SQL> select clustering_factor from dba_indexes where index_name='IC_TRAN_PNDI6';
    
    CLUSTERING_FACTOR
    -----------------
             28194454
    thanks,
    baskar.l
  • 10. Re: Number of Executions of a query - from tkprof
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    Your index :-

    "IC_TRAN_PND"
    ("ITEM_ID", "COMPLETED_IND", "DELETE_MARK", "DOC_TYPE","WHSE_CODE")

    try to remove the composite index and go for sub divided
    Problem with the above index -can you get the histograms of the above columns

    1 - WHSE_CODE
    2 - COMPLETED_IND,DELETE_MARK,DOC_TYPE
    3 - ITEM_ID -does item id is used any where


    try to format the query for better understanding

    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


    You index might not be getting used for below conditions
    " I.LOCT_CTL * W.LOCT_CTL"

    Can you check the number of blocks
    select blocks from user_segments where segment_name='IC_TRAN_PNDI6';


    - Pavan Kumar N
    Oracle 9i/10g - OCP
    http://oracleinternals.blogspot.com/
  • 11. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    hi pavan,
    SQL> select clustering_factor from dba_indexes where index_name='IC_TRAN_PNDI6';
    
    CLUSTERING_FACTOR
    -----------------
             26635000
    
    1 row selected.
    
    SQL> select blocks from dba_segments where segment_name='IC_TRAN_PNDI6';
    
        BLOCKS
    ----------
        161744
    thanks,
    baskar.l
  • 12. Re: Number of Executions of a query - from tkprof
    sb92075 Guru
    Currently Being Moderated
    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 
  • 13. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    Pavan,

    That is the standard index from oracle itself on base table...

    thanks,
    baskar.l
  • 14. Re: Number of Executions of a query - from tkprof
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    As index on the columns in not efficient and the data distribution of the data is not good - its scattered all the way. index has to jump 26635000 times to give you the full data had you performed full table scan using the index. How many number of rows are exists in table. Better you make the appropriate index on the specified columns as I said in previous post - based on Selectivity and presently you need to re-organize or rebuild the index.

    Let's jonathan post the views.


    - Pavan Kumar N
    Oracle 9i/10g - OCP
    http://oracleinternals.blogspot.com/
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points