This discussion is archived
1 2 3 Previous Next 32 Replies Latest reply: Jan 7, 2010 8:25 PM by PavanKumar Go to original post RSS
  • 15. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    hi pavan,

    Thanks for your suggesions...Read from this http://oracle-online-help.blogspot.com/2006/11/clustering-factor_28.html that rebuilding index wil not help..as we have rebuilt the entire table...as today morning i have rebuilded all index of the table and gathered stats, yet the clustering factor remain the same..
    SQL> select count(*) from IC_TRAN_PND
      2  ;
    
      COUNT(*)
    ----------
      35161944
    will wait for jonanthan review..

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

    As I suggested to sub divided the current index for better performance or re-order it.
    Just go through the link of Richard foote and comments of Hemant and Tom on particular example which they referred to

    http://richardfoote.wordpress.com/2008/02/15/clustering-factor-a-consideration-in-index-lead-column-decision-sweet-thing/

    Re- ordering of table might not be necessary.

    - Pavan Kumar N
    Oracle 9i/10g - OCP
    http://oracleinternals.blogspot.com/
  • 17. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    hi pavan,

    Thanks for the note..Good demo and explanation...Right now in test instance using expdp and impdp am rebuilding the table...Once it completes i will check the clustering factor.. And after that i will re-order the columns in the table...because as now from the definition of the index the columns are not ordered according to definition of the table...here is my table definition
    SQL> desc IC_TRAN_PND;
     Name                                                                                                  Null?    Type
     ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
     TRANS_ID                                                                                              NOT NULL NUMBER(10)
     ITEM_ID                                                                                               NOT NULL NUMBER(10)
     LINE_ID                                                                                               NOT NULL NUMBER(10)
     CO_CODE                                                                                               NOT NULL VARCHAR2(4)
     ORGN_CODE                                                                                             NOT NULL VARCHAR2(4)
     WHSE_CODE                                                                                             NOT NULL VARCHAR2(4)
     LOT_ID                                                                                                NOT NULL NUMBER(10)
     LOCATION                                                                                              NOT NULL VARCHAR2(16)
     DOC_ID                                                                                                NOT NULL NUMBER(10)
     DOC_TYPE                                                                                              NOT NULL VARCHAR2(4)
     DOC_LINE                                                                                              NOT NULL NUMBER(10)
     LINE_TYPE                                                                                             NOT NULL NUMBER(5)
     REASON_CODE                                                                                                    VARCHAR2(4)
     CREATION_DATE                                                                                         NOT NULL DATE
     TRANS_DATE                                                                                            NOT NULL DATE
     TRANS_QTY                                                                                             NOT NULL NUMBER
     TRANS_QTY2                                                                                                     NUMBER
     QC_GRADE                                                                                                       VARCHAR2(4)
     LOT_STATUS                                                                                                     VARCHAR2(4)
     TRANS_STAT                                                                                                     VARCHAR2(4)
     TRANS_UM                                                                                              NOT NULL VARCHAR2(4)
     TRANS_UM2                                                                                                      VARCHAR2(4)
     OP_CODE                                                                                               NOT NULL NUMBER(15)
     COMPLETED_IND                                                                                         NOT NULL NUMBER(5)
     STAGED_IND                                                                                            NOT NULL NUMBER(5)
     GL_POSTED_IND                                                                                         NOT NULL NUMBER(10)
     EVENT_ID                                                                                              NOT NULL NUMBER(10)
     DELETE_MARK                                                                                           NOT NULL NUMBER(5)
     TEXT_CODE                                                                                                      NUMBER(10)
     LAST_UPDATE_DATE                                                                                      NOT NULL DATE
     CREATED_BY                                                                                            NOT NULL NUMBER(15)
     LAST_UPDATED_BY                                                                                       NOT NULL NUMBER(15)
     LAST_UPDATE_LOGIN                                                                                              NUMBER(15)
     PROGRAM_APPLICATION_ID                                                                                         NUMBER(15)
     PROGRAM_ID                                                                                                     NUMBER(15)
     PROGRAM_UPDATE_DATE                                                                                            DATE
     REQUEST_ID                                                                                                     NUMBER(15)
     REVERSE_ID                                                                                                     NUMBER(10)
     PICK_SLIP_NUMBER                                                                                               NUMBER
     MVT_STAT_STATUS                                                                                                VARCHAR2(30)
     MOVEMENT_ID                                                                                                    NUMBER
     LINE_DETAIL_ID                                                                                                 NUMBER
     INVOICED_FLAG                                                                                                  VARCHAR2(1)
     INTORDER_POSTED_IND                                                                                            NUMBER(5)
     LOT_COSTED_IND                                                                                                 NUMBER(5)
     
    and index  description is
    
    CREATE INDEX "GMI"."IC_TRAN_PNDI6" ON "GMI"."IC_TRAN_PND" ("ITEM_ID", "COMPLETED_IND", "DELETE_MARK", "DOC_TYPE", "WHSE_CODE")
    thanks,
    baskar.l
  • 18. Re: Number of Executions of a query - from tkprof
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Merely rebuilding the table and index does not necessarily improve the clustering factor.
    CLUSTERING_FACTOR is improved if the table's rows are ordered in the same manner as the index definition (OR the index columns are specified in the manner the table's rows are ordered).


    However, I am not sure if we have enough information that CLUSTERING_FACTOR is the issue here.
    Particularly when you have multiple indexes, improving the CLUSTERING_FACTOR for one index may mean degrading this for another index(es). Also, highly skewed data can cause Oracle to wrongly use the index.
    (+eg where 80% of the data has the same key values and the data is re-ordered, the Clustering_Factor would be fantastic and Oracle would use the Index. This index would be the wrong one to use for those 80% rows but Oracle may likely use it if the SQL is using binds and bind variable peeking has caused Oracle to peek at other values earlier !. Histograms can then help Oracle decide about the 80% rows and not use the index for these rows).+

    Hemant K Chitale
    http://hemantoracledba.blogspot.com
  • 19. Re: Number of Executions of a query - from tkprof
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    From your 'explain plan' we can see that there is an arithmetic error, and that there's some sort of stats problem.

    Arithmetic: Oracle's estimate of the number of rowids fetched from the critical index is SIX for each call to that step of the plan - but in your case the actual number of rows is tens of thousands (96,000 rows from two calls to that specific operation). This suggests that there may be a problem with histograms - but might simply be the standard problem of dependent vs. independent predicates. (But postpone that issue for the moment).


    Stats problem: In the last two lines of the plan you have an "index unique scan" of a primary key which is prediccted to returns one rowid (line 14), but when you go to the table the prediction is six rows (line 13). These two lines are not consistent. (But postpone that issue for the moment).

    The actual run figure show that your index predicates identify 96,000 rowids in two calls to the range scan - and that you end up with 12 rows from the table after visiting all those 96,000 rows. The access predicates for the index line couldn't be much better - but you don't use the doc_type from the index, which is a clue that this index is not the one you're supposed to be using for this query because it's not the "perfect" design.

    Moreover, you have a messy filter predicate in line 11 that does most of the data elimination - and it's not necessarily going to be easy to get enough information into an index to avoid those table visits; which is a clue that perhaps you are supposed to visit the tables in a different order.

    The next thing to do is look at the index that ARE available - let's start with just that table, in case the join is the one expected by the original programmer:
    break on index_name skip 1
    
    select  index_name, column_name
    from    user_indexes
    where   table_name = 'whatever it was'
    order by
            index_name, column_position
    ;
    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 20. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    hi jonathan,

    My db version is 10.2.0.4 i dont find the column_name column in user_indexes
    SQL> select  index_name, column_name
      2  from    user_indexes
      3  where   table_name = 'IC_TRAN_PND'
      4  order by index_name;
    select  index_name, column_name
                        *
    ERROR at line 1:
    ORA-00904: "COLUMN_NAME": invalid identifier
    thanks,
    baskar.l
  • 21. Re: Number of Executions of a query - from tkprof
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    Check with "USER_IND_COLUMNS" - might be Jonathan referring to that.


    - Pavan Kumar N
    Oracle 9i/10g - OCP
    http://oracleinternals.blogspot.com/
  • 22. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    Thanks pavan..i got it..
    SQL> select INDEX_NAME,COLUMN_NAME from user_ind_columns
      2  where table_name='IC_TRAN_PND'
      3  order by INDEX_NAME,COLUMN_POSITION;
    
    no rows selected
    thanks,
    baskar.l
  • 23. Re: Number of Executions of a query - from tkprof
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    One of the output of your previous post says the below things

    CREATE INDEX "GMI"."IC_TRAN_PNDI6" ON "GMI"."IC_TRAN_PND"

    but, it results in "no rows selected" - I request to check correct the tables and schema which you are referring to.

    - Pavan Kumar N
    Oracle 9i/10g - OCP
    http://oracleinternals.blogspot.com/
  • 24. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    pavan,

    Still the index is there..but i can see those info from user_ind_columns
    SQL> @obj
    Enter value for 1: IC_TRAN_PND
    old   3: where object_name like upper('&1%')
    new   3: where object_name like upper('IC_TRAN_PND%')
    
    OWNER      OBJECT_NAME                    OBJECT_TYPE         CREATED   LAST_DDL
    ---------- ------------------------------ ------------------- --------- ------------------
    GMI        IC_TRAN_PND                    TABLE               14-MAY-00 06-jan-10 07:48:27
    GMI        IC_TRAN_PNDI1                  INDEX               14-MAY-00 06-jan-10 07:48:26
    GMI        IC_TRAN_PNDI3                  INDEX               14-MAY-00 06-jan-10 07:48:26
    GMI        IC_TRAN_PNDI4                  INDEX               14-MAY-00 06-jan-10 07:48:26
    APPS       IC_TRAN_PND                    SYNONYM             14-MAY-00 02-feb-09 11:40:32
    APPS       IC_TRAN_PND_VW2                VIEW                14-MAY-00 02-feb-09 12:18:14
    APPS       IC_TRAN_PND_VW3                VIEW                14-MAY-00 02-feb-09 12:18:19
    GMI        IC_TRAN_PNDI6                  INDEX               30-MAY-02 06-jan-10 07:48:26
    APPS       IC_TRAN_PND_OM_VW1             VIEW                30-MAY-02 02-feb-09 12:20:01
    GMI        IC_TRAN_PNDI5                  INDEX               25-JUN-03 06-jan-10 07:48:26
    GMI        IC_TRAN_PNDI7                  INDEX               01-NOV-05 06-jan-10 07:48:26
    GMI        IC_TRAN_PND_PK                 INDEX               21-JAN-06 06-jan-10 07:48:26
    PUBLIC     IC_TRAN_PND                    SYNONYM             16-MAY-06 02-feb-09 12:22:15
    PUBLIC     IC_TRAN_PND_VW2                SYNONYM             16-MAY-06 02-feb-09 12:27:06
    PUBLIC     IC_TRAN_PND_VW3                SYNONYM             16-MAY-06 02-feb-09 12:27:06
    PUBLIC     IC_TRAN_PND_OM_VW1             SYNONYM             16-MAY-06 02-feb-09 12:27:13
    GMI        IC_TRAN_PND2                   INDEX               22-JUN-06 06-jan-10 07:48:26
    GMI        IC_TRAN_PND_N1                 INDEX               30-AUG-06 06-jan-10 07:48:26
    APPS       IC_TRAN_PND_BCK28JUN           TABLE               28-JUN-06 28-jun-06 18:36:37
    GMI        IC_TRAN_PND21                  INDEX               16-DEC-06 06-jan-10 07:48:26
    GMI        IC_TRAN_PND22                  INDEX               19-JAN-07 06-jan-10 07:48:26
    GMI        IC_TRAN_PND23                  INDEX               22-MAR-07 06-jan-10 07:48:27
    GMI        IC_TRAN_PNDI2                  INDEX               28-MAY-08 06-jan-10 07:48:26
    
    23 rows selected.
    
    SQL> L
      1  select owner, object_name, object_type, created, to_char(last_ddl_time,'dd-mon-rr hh24:mi:ss') last_ddl
      2  from dba_objects
      3* where object_name like upper('&1%')
    thanks,
    baskar.l
  • 25. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    Sorry i cant see those info in user_ind_columns...

    thanks,
    baskar.l
  • 26. Re: Number of Executions of a query - from tkprof
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    baskar.l wrote:
    Sorry i cant see those info in user_ind_columns...
    It was supposed to be user_ind_columns, rather than user_indexes - but I was assuming you were going to connect as the application owner. Check dba_ind_columns if you're logged in as a DBA. Just in case there are two schemas owning a table of the same name, make sure you check the table_owner as well.

    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 27. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    hi
    SQL> select INDEX_NAME,COLUMN_NAME from user_ind_columns
      2  where table_name='IC_TRAN_PND'
      3  order by INDEX_NAME,COLUMN_POSITION;
     
    no rows selected
    thanks,
    baskar.l
  • 28. Re: Number of Executions of a query - from tkprof
    603349 Explorer
    Currently Being Moderated
    Either you have to be the object owner (GMI) or use dba_ind_columns.

    --
    Regards,
    Greg Rahn
    http://structureddata.org
  • 29. Re: Number of Executions of a query - from tkprof
    baskar.l Pro
    Currently Being Moderated
    hi greg,

    Thanks for the info..
    SQL> select INDEX_NAME,COLUMN_NAME from dba_ind_columns
      2  where table_name='IC_TRAN_PND'
      3  order by INDEX_NAME,COLUMN_POSITION;
    
    INDEX_NAME                     COLUMN_NAME
    ------------------------------ ------------------------------
    IC_TRAN_PND2                   LOT_ID
    IC_TRAN_PND2                   DELETE_MARK
    IC_TRAN_PND2                   DOC_TYPE
    IC_TRAN_PND2                   COMPLETED_IND
    IC_TRAN_PND21                  DOC_TYPE
    IC_TRAN_PND21                  LOT_STATUS
    IC_TRAN_PND21                  DELETE_MARK
    IC_TRAN_PND21                  COMPLETED_IND
    IC_TRAN_PND22                  DOC_TYPE
    IC_TRAN_PND22                  LOT_ID
    IC_TRAN_PND22                  ITEM_ID
    IC_TRAN_PND23                  ORGN_CODE
    IC_TRAN_PNDI1                  ITEM_ID
    IC_TRAN_PNDI1                  LOT_ID
    IC_TRAN_PNDI1                  WHSE_CODE
    IC_TRAN_PNDI1                  LOCATION
    IC_TRAN_PNDI2                  DOC_TYPE
    IC_TRAN_PNDI2                  DOC_ID
    IC_TRAN_PNDI2                  LINE_ID
    IC_TRAN_PNDI3                  LINE_ID
    IC_TRAN_PNDI4                  ITEM_ID
    IC_TRAN_PNDI4                  COMPLETED_IND
    IC_TRAN_PNDI4                  DOC_TYPE
    IC_TRAN_PNDI4                  WHSE_CODE
    IC_TRAN_PNDI5                  TRANS_DATE
    IC_TRAN_PNDI6                  ITEM_ID
    IC_TRAN_PNDI6                  COMPLETED_IND
    IC_TRAN_PNDI6                  DELETE_MARK
    IC_TRAN_PNDI6                  DOC_TYPE
    IC_TRAN_PNDI6                  WHSE_CODE
    IC_TRAN_PNDI7                  WHSE_CODE
    IC_TRAN_PNDI7                  LOCATION
    IC_TRAN_PNDI7                  COMPLETED_IND
    IC_TRAN_PNDI7                  DELETE_MARK
    IC_TRAN_PND_N1                 LINE_DETAIL_ID
    IC_TRAN_PND_PK                 TRANS_ID
    
    36 rows selected.
    thanks,
    baskar.l

Legend

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