This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Feb 15, 2013 10:44 AM by ji li RSS

why is my query using the wrong index?

ji li Pro
Currently Being Moderated
Hi SQL Tuning experts....

I'm running Oracle 11.2.0.2 EE on Solaris 10

I've just checked my top SQL statements in OEM and drilling down on the top query of the list, I decided to run SQL Advisor on the query.
Low and behold, it came back with a profile that gives me a 99.9% improvement.
When I compared the original plan to the new plan the only difference is that the SQL Advisor is using a different index.
Now, the dumb DBA question: why? why is Oracle picking the wrong index?

original plan
SELECT STATEMENT
  COUNT STOPKEY
    VIEW
      SORT ORDER BY STOPKEY          
        TABLE ACCESS BY INDEX ROWID     ARADMIN.T2179          TABLE
          INDEX RANGE SCAN          ARADMIN.T2179_C3     INDEX
new plan...
SELECT STATEMENT
  COUNT STOPKEY
    VIEW
      SORT ORDER BY STOPKEY          
        TABLE ACCESS BY INDEX ROWID     ARADMIN.T2179               TABLE
          INDEX RANGE SCAN          ARADMIN.T2179_C536870923     INDEX
The stats:
table has approx. 3.3Mil rows
table segment is approx. 15G (5G of it is reclaimable space, but probably not related)
table has fresh stats and indexes have been recently rebuilt
indexes in this discussion are both b-tree

the C3 column is number(15) with 2836897 distinct rows
the C536870923 columns is VARCHAR2(255) with 1080533 distinct values


Here is the actual query:
     SELECT *
FROM (
SELECT T2179.C1,C536871040,C536871037,C536870944,C3,C536870918,C536870919,
C536870924,C536871048,C536871049,C536871050,C536870926,C536870925,
C536870916,C4,C536871275,C536871095,C536870913
FROM T2179
WHERE ((T2179.C536870923 = :"SYS_B_0") AND (T2179.C3 > :"SYS_B_1")) ORDER BY C1 DESC )
WHERE ROWNUM <= :"SYS_B_2"
Obviously this is a good example of why we should use profiles. :-)

Edited by: ji li on Feb 14, 2013 1:32 PM

Sorry... I should have included the plan output
I'm guessing this has something to do with the cardinality of the data in the columns.
  • 1. Re: why is my query using the wrong index?
    Iordan Iotzov Expert
    Currently Being Moderated
    Are C536870923 or C3 columns skewed? If yes, do they have histograms defined on them?

    Since you are using bind variables, Oracle generates the execution plan based on values of the bind variable during the first execution – “bind variable peeking”. That plan is not always suitable for different set of bind variables. In 11g, adaptive cursor sharing is supposed to alleviate that.

    An execution plan with estimated/actual rowcounts would really help.

    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 2. Re: why is my query using the wrong index?
    ji li Pro
    Currently Being Moderated
    Sorry, but the explain plan provided in OEM by looking at the before and after implementing the profile does not include rows.
    I just updated the original posting with the actual rows, datatypes and cardinality (counting distinct rows).
    Please look back at the original posting.

    And yes, this is also what I'm thinking this is related to, but why would Oracle make the wrong index choice?

    How do I confirm histograms on the specific columns of the table?

    I selected count from dba_tab_histograms where column_name = ..., table_name = ..., etc for both columns and one columns has 255 histograms, and the other has 250.
    What does that mean to me?
  • 3. Re: why is my query using the wrong index?
    Iordan Iotzov Expert
    Currently Being Moderated
    You most likely have histograms. You can also look at HISTOGRAM column in DBA_TAB_COLUMNS.

    Histograms and binds do not work well together without help -
    http://jonathanlewis.wordpress.com/2009/05/06/philosophy-1/
    http://hoopercharles.wordpress.com/2011/01/29/histograms-and-bind-variables-but-why/

    Since you probably cannot change the content of the table, you can consider getting rid of the binds. Removing the binds can cause more parsing, since every SQL would be hard parsed every time.

    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 4. Re: why is my query using the wrong index?
    ji li Pro
    Currently Being Moderated
    I was kind of afraid of that answer. I've recently posted another discussion on this exact same subject.
    So, do you recommend I get rid of the histograms and set shared_cursors to EXACT?

    11gR2  cursor_sharing=force  and  adaptive cursor sharing

    In our case, we have shared_cursors = FORCE, which is recommended on most web sites I've reviewed for 11gR2, especially with histograms in place.
    The bind variables you see were created by ACS and with shared_cursors = FORCE.
    This is because our COTS application uses a lot of literals instead of bind variables.

    We originally had shared_cursors set to EXACT which would create a new hard parse for almost every query.

    There is one thing I read here that seems to relate to our situation however:

    http://aychin.wordpress.com/2011/04/04/adaptive-cursor-sharing-and-spm/

    In cases when we will use CURSOR_SHARING parameter to replace literals with binds and share cursor between different sessions, in 11gR2 we need to set it to FORCE to effectively use ACS.


    It seems like at this point, we just need to stay on top of the "top queries" and generate profiles whenever we can.
    Occasionally, however, when I try to implement a recommended profile, I get an error.
    I've just submitted the error to MOS and hope they might tell me why we get the errors.

    Database Error
         
    There was a problem creating the SQL Profile ORA-13786: missing SQL text of statement object "1" for tuning task "TASK_61341" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16442 ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 31 ORA-06512: at "SYS.DBMS_SQLTUNE", line 7544 ORA-06512: at line 1
  • 5. Re: why is my query using the wrong index?
    Dom Brooks Guru
    Currently Being Moderated
    decided to run SQL Advisor on the query.
    Low and behold, it came back with a profile that gives me a 99.9% improvement.
    The SQL Tuning Advisor frequently overestimates its gains.
    Obviously this is a good example of why we should use profiles.
    Why?
    why is Oracle picking the wrong index?
    Difficult to answer without the requisite information.
    See [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request

    It could be histograms, it could just be about default bind selectivity.
    Supply actual values and the optimizer can estimate where they fit in the overall data distribution.
    Supply binds and the optimizer will use default selectivities or, depending on circumstance, it will peek at the binds, which may provide values unsuitable for general shareable sql.

    More information required.
  • 6. Re: why is my query using the wrong index?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    There is one thing I read here that seems to relate to our situation however:

    http://aychin.wordpress.com/2011/04/04/adaptive-cursor-sharing-and-spm/

    In cases when we will use CURSOR_SHARING parameter to replace literals with binds and share cursor between different sessions, in 11gR2 we need to set it to FORCE to effectively use ACS.
    Interesting. I am not yet convinced that I would want to use CURSOR_SHARING=FORCE.

    Note : CURSOR_SHARING=SIMILAR has been deprecated in 11g.


    Hemant K Chitale
  • 7. Re: why is my query using the wrong index?
    moreajays Pro
    Currently Being Moderated
    Hi,

    It looks remedy application , which version 7.0/7.6.. cursor_sharing is FORCE recommended by application.
    It would be better if you can get complete explain plan taken from sqlplus.
    Indexed column names /position & STOPKEY (due to ROWNUM) could be responsible for Index changes along with data
    Which index is performing better w.r.t response time as per advisory or manual execution ?

    Thanks,
    Ajay More
    http://www.moreajays.com
  • 8. Re: why is my query using the wrong index?
    ji li Pro
    Currently Being Moderated
    Thank you Sir. You are right on it exactly.

    We are running Remedy 7.6.04 SP1 201104191058.

    Good call. Obviously you are familiar with this and most likly the issues I'm facing.

    Yes, I've set cursor_sharing to FORCE, and as per the guidelines in the documents I referenced above, it says that using cursor_sharing=FORCE is best when we have histograms in place. That is exactly how I have it set up. Thank you for that confirmation.

    Okay, I'll get an explain plan of the query and post it.

    Thanks again.
  • 9. Re: why is my query using the wrong index?
    ji li Pro
    Currently Being Moderated
    Hi Dom, in response to your comments:
    The SQL Tuning Advisor frequently overestimates its gains.
    True, but it is still relative. 99.9% improvement is still a good improvement unless you have no faith at all in the SQL Tuning Advisor.
    Obviously this is a good example of why we should use profiles.
    Why?
    Not sure how to answer this. It has proven over and over that these types of findings improve performance.
    Difficult to answer without the requisite information.
    See How to post a sql tuning request
    Sorry. I tried to give as much relevant information as I could without getting ridiculous and running an rda, providing a copy of the database and everything else.
    I will post a true explain plan shortly.
    It could be histograms, it could just be about default bind selectivity.
    Supply actual values and the optimizer can estimate where they fit in the overall data distribution.
    Supply binds and the optimizer will use default selectivities or, depending on circumstance, it will peek at the binds, which may provide values unsuitable for general shareable sql.
    yes, true. That's what I'm thinking as well.
  • 10. Re: why is my query using the wrong index?
    rahulras Explorer
    Currently Being Moderated
    One more question. How do you collect stats? do you use the default job/settings provided by Oracle for stats collection?
    In my view, Oracle collects the stats on very small sample.
    I had one highly skewed column with 173 distinct values. The histogram Oracle created was frequency histogram (where you expect same number of buckets as number of distinct values). However, my frequency histogram had only 77 buckets.
    So, when there is a bind value outside those 77 values (which are in histogram), Oracle applies some algorithm and makes a decision weather to use certain index or not.
    See the thread - How AUTO stats job decides on histograms and sample size?

    I would be really interested to see if your histograms have same pattern as mentioned in this thread.
    I had this issue of Oracle using wrong index on odd days and queries taking very long time than normal.
  • 11. Re: why is my query using the wrong index?
    ji li Pro
    Currently Being Moderated
    Here is the explain plan where using index 'C3'.
    SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------
    Plan hash value: 2768254526
    
    -------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |          |     1 |  2315 |     6  (17)| 00:00:01 |
    |*  1 |  COUNT STOPKEY                 |          |       |       |            |          |
    |   2 |   VIEW                         |          |     1 |  2315 |     6  (17)| 00:00:01 |
    |*  3 |    SORT ORDER BY STOPKEY       |          |     1 |   226 |     6  (17)| 00:00:01 |
    |*  4 |     TABLE ACCESS BY INDEX ROWID| T2179    |     1 |   226 |     5   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | T2179_C3 |     1 |       |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM<=1)
       3 - filter(ROWNUM<=1)
       4 - filter(TO_NUMBER("T2179"."C536870923")=300885690)
       5 - access(SYS_OP_DESCEND("C3")<HEXTORAW('3AF1C2A1ABC9FF') )
           filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("C3"))>1360938353)
    
    21 rows selected.
    I'm still trying to get it to use the T2179_C536870923 index, but I can't seem to get it to use it even though I use a hint to use it.
    Also, I would think that Oracle would use the profile automatically since I created it an implemented it already.
    Oh well.... I'm apparently doing something wrong.
  • 12. Re: why is my query using the wrong index?
    ji li Pro
    Currently Being Moderated
    That's very interesting. I did not use skewonly, but rather AUTO since this is default in 11GR2 and should determine the need to generate histograms based on monitored usage. I confirmed I have monitoring on for all tables.

    Here is the gather stats job I used last time I ran it on this table.
    Actually, it is just a part of a dynamic sql output I generate to run stats on all tables in this schema.
    exec dbms_stats.gather_table_stats('ARADMIN','T2179',cascade=>TRUE, estimate_percent=>dbms_stats.auto_sample_size, granularity=>'ALL', method_opt=>'for all columns size auto', degree=>8);
    I also confirmed I have histograms on the indexes columns:
    COLUMN_NAME                       NUM_DISTINCT       NUM_NULLS     NUM_BUCKETS     SAMPLE_SIZE HISTOGRAM
    ------------------------------ --------------- --------------- --------------- --------------- ---------------
    C3                                     3147776               0             254           14825 HEIGHT BALANCED
    C536870923                             1157760          175584             254           14075 HEIGHT BALANCED
    BTW: Thank you for the link to your posting. I like the query to get the above results.
  • 13. Re: why is my query using the wrong index?
    Dom Brooks Guru
    Currently Being Moderated
    99.9% improvement is still a good improvement unless you have no faith at all in the SQL Tuning Advisor.
    Not sure how to answer this. It has proven over and over that these types of findings improve performance.
    Surely it depends on what the problem is?

    You have brute force bind variables, peeking and histograms.

    If it's a question of just bind variable peeking, and what's good for one execution not being good for another set of binds, then a sql profile is just as likely to have the same issues as it feeds back a set of specific cardinalities from a specific execution into a profile.

    And apart from anything else, the SQL Tuning Advisor usually fails to take into account differences in physical IO which can often affect allegeed gains of 99.9%.

    We've yet to confirm which feature or combinations of features are responsible for the problem because of a continued reluctance in both threads to post the required information, information which is far from
    getting ridiculous
    We can speculate and speculate but it's all a bit fruitless and hypothetical.

    The information required has been already provided in the linked to tuning threads.
  • 14. Re: why is my query using the wrong index?
    ji li Pro
    Currently Being Moderated
    Dom.... I've already posted most everything. Here are the relevant init params and misc stuff you are asking for.
    NAME                                 TYPE                             VALUE
    ------------------------------------ -------------------------------- -------------------
    object_cache_optimal_size            integer                          102400
    optimizer_capture_sql_plan_baselines boolean                          FALSE
    optimizer_dynamic_sampling           integer                          2
    optimizer_features_enable            string                           11.2.0.2
    optimizer_index_caching              integer                          0
    optimizer_index_cost_adj             integer                          100
    optimizer_mode                       string                           ALL_ROWS
    optimizer_secure_view_merging        boolean                          TRUE
    optimizer_use_invisible_indexes      boolean                          FALSE
    optimizer_use_pending_statistics     boolean                          FALSE
    optimizer_use_sql_plan_baselines     boolean                          TRUE
    plsql_optimize_level                 integer                          2
    db_file_multiblock_read_count        integer                          128
    db_block_size                        integer                          8192
    cursor_sharing                       string                           FORCE
    
    SNAME                          PNAME                                    PVAL1 PVAL2
    ------------------------------ ------------------------------ --------------- --------------------
    SYSSTATS_INFO                  STATUS                                         COMPLETED
    SYSSTATS_INFO                  DSTART                                         12-04-2012 12:28
    SYSSTATS_INFO                  DSTOP                                          12-04-2012 15:28
    SYSSTATS_INFO                  FLAGS                                        0
    SYSSTATS_MAIN                  CPUSPEEDNW                     2992.2746781116
    SYSSTATS_MAIN                  IOSEEKTIM                                   10
    SYSSTATS_MAIN                  IOTFRSPEED                                4096
    SYSSTATS_MAIN                  SREADTIM                             48027.271
    SYSSTATS_MAIN                  MREADTIM                             37217.102
    SYSSTATS_MAIN                  CPUSPEED                                  2978
    SYSSTATS_MAIN                  MBRC                                         0
    SYSSTATS_MAIN                  MAXTHR                               400782336
    SYSSTATS_MAIN                  SLAVETHR                               1403904
    
    13 rows selected.
    
    QL> l
      1  SELECT * FROM (
      2    SELECT
      3    T2179.C1,
      4    C536871040,
      5    C536871037,
      6    C536870944,
      7    C3,
      8    C536870918,
      9    C536870919,
     10    C536870924,
     11    C536871048,
     12    C536871049,
     13    C536871050,
     14    C536870926,
     15    C536870925,
     16    C536870916,
     17    C4,
     18    C536871275,
     19    C536871095,
     20    C536870913
     21    FROM aradmin.T2179
     22    WHERE (
     23     (C536870923 = '300885690')
     24    AND
     25     (C3 > '1360938353')
     26          )
     27    ORDER BY C1 DESC )
     28* WHERE ROWNUM <= 1
    
    no rows selected
    
    Elapsed: 00:00:00.01
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2768254526
    
    -------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |          |     1 |  2315 |     6  (17)| 00:00:01 |
    |*  1 |  COUNT STOPKEY                 |          |       |       |            |          |
    |   2 |   VIEW                         |          |     1 |  2315 |     6  (17)| 00:00:01 |
    |*  3 |    SORT ORDER BY STOPKEY       |          |     1 |   226 |     6  (17)| 00:00:01 |
    |*  4 |     TABLE ACCESS BY INDEX ROWID| T2179    |     1 |   226 |     5   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | T2179_C3 |     1 |       |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM<=1)
       3 - filter(ROWNUM<=1)
       4 - filter("C536870923"='300885690')
       5 - access(SYS_OP_DESCEND("C3")<HEXTORAW('3AF1C2A1ABC9FF') )
           filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("C3"))>1360938353)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           2615  consistent gets
              0  physical reads
              0  redo size
           1538  bytes sent via SQL*Net to client
            513  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              0  rows processed
1 2 Previous Next

Legend

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