This discussion is archived
5 Replies Latest reply: Jan 9, 2011 9:28 AM by 602378 RSS

how to tune queries that have best performance with rule hint

Insaponata Newbie
Currently Being Moderated
Hi,
please consider this scenario: in a 11g database, I have a query that performs in dozens of minutes without any hint. If I add a rule hint the query performs in some seconds. So is possible to access data in some seconds and not in dozens of minutes - eg: there is a best execution plan. In this case what action can I perform to let Oracle know the existence of a best execution plan (or to let Oracle choose the right execution plan)?

IMPORTANT: I have not a case, I'm just planning in case of the occurrence of this situation
  • 1. Re: how to tune queries that have best performance with rule hint
    CharlesHooper Expert
    Currently Being Moderated
    Insaponata wrote:
    Hi,
    please consider this scenario: in a 11g database, I have a query that performs in dozens of minutes without any hint. If I add a rule hint the query performs in some seconds. So is possible to access data in some seconds and not in dozens of minutes - eg: there is a best execution plan. In this case what action can I perform to let Oracle know the existence of a best execution plan (or to let Oracle choose the right execution plan)?

    IMPORTANT: I have not a case, I'm just planning in case of the occurrence of this situation
    I would advise not even starting down that path with a RULE hint in Oracle Database 10.1 and above (the RULE hint is deprecated in Oracle Database 10.1 and above). Take a look at the comments in this blog article to see why you should not attempt to tune with a RULE hint:
    http://hoopercharles.wordpress.com/2010/12/06/optimizer-costing-3-what-is-wrong-with-this-quote/

    You can certainly guide the optimizer's decisions with multiple hints. You can also verify that the optimizer's estimated cardinality for each operation is reasonably close to the actual number of rows returned by the operation - make certain that the available statistics are reasonably accurate (hints may be used to adjust the cardinality estimates if the object statistics are correct). Histograms may either help or hurt the optimizer in finding the optimal execution plan for a specific set of predicates in the WHERE clause. And the list goes on... specific to each SQL statement and optimizer environment, such as someone setting the OPTIMIZER_INDEX_COST_ADJ parameter to a silly value rather than confirming that the system (CPU) statistics are accurate. (Side note: hints should not be a first step in developing a final solution.)

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 2. Re: how to tune queries that have best performance with rule hint
    Insaponata Newbie
    Currently Being Moderated
    Hi Charles,
    yes, I think that the best solution is to avoid the use of hints and the best would be that Oracle CBO choses the best plan automatically. I can do that only with histograms or are there other ways?
  • 3. Re: how to tune queries that have best performance with rule hint
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Insaponata wrote:
    Hi,
    please consider this scenario: in a 11g database, I have a query that performs in dozens of minutes without any hint. If I add a rule hint the query performs in some seconds. So is possible to access data in some seconds and not in dozens of minutes - eg: there is a best execution plan. In this case what action can I perform to let Oracle know the existence of a best execution plan (or to let Oracle choose the right execution plan)?

    IMPORTANT: I have not a case, I'm just planning in case of the occurrence of this situation
    It depends whether this is just a single query, or a "class" of queries which are very similar to each other, or a complete random collection of lots of queries.

    If it's a single query and it's not immediately obvious why things are going wrong, then you might decide that the most cost-effective solution was to run the query with the rule hint, then capture an SQL Baseline for the query but store the baseline against a copy of the query text that did not include the hint.

    If there is a class of similar queries, it's probably more sensible to work out what they have in common that makes the CBO choose a bad plan - it might, for example, be something like a misleading clustering factor on an index, or a 'dependent predicates' issue - and see if you can manipulate the statistics for the critical objects to tell the optimizer the truth about the data.

    If there are lots of apparently unrelated queries then you may have to go back to square one with your stats collection strategy and work out what you need to do to get the most truthful stats in the least expensive way - with the intention of fixing up the few problems that will probably remain.

    Regards
    Jonathan Lewis
  • 4. Re: how to tune queries that have best performance with rule hint
    CharlesHooper Expert
    Currently Being Moderated
    Insaponata wrote:
    Hi Charles,
    yes, I think that the best solution is to avoid the use of hints and the best would be that Oracle CBO choses the best plan automatically. I can do that only with histograms or are there other ways?
    I think that Jonathan Lewis has answered your question more completely than I did. However, please note that I also mentioned silly parameter values, such as OPTIMIZER_INDEX_COST_ADJ set to low values, and the accuracy of object statistics. There are certain features that the RULE based optimizer cannot use (function based indexes, descending indexes, etc.), but there is also the potential for an illusion of performance improvement that you need to watch out for. Consider the following test table:
    CREATE TABLE T5 (
      C1 NUMBER,
      C2 NUMBER,
      C3 NUMBER,
      C4 NUMBER,
      C5 NUMBER,
      C6 NUMBER,
      C7 NUMBER,
      C8 NUMBER,
      C9 VARCHAR2(50),
      C10 VARCHAR2(50));
     
    INSERT INTO
      T5
    SELECT
      ROWNUM,
      ROWNUM,
      MOD(ROWNUM,50),
      MOD(ROWNUM,40),
      MOD(ROWNUM,100),
      ROUND(ROWNUM/1000),
      ROUND(ROWNUM/950),
      ROUND(ROWNUM/600),
      RPAD(CHR(65 + MOD(ROWNUM-1,26)),50,CHR(65 + MOD(ROWNUM-1,26))),
      RPAD(CHR(65 + MOD(ROWNUM-1,26)),50,CHR(65 + MOD(ROWNUM-1,26)))
    FROM
      DUAL
    CONNECT BY
      LEVEL<=1000000;
     
    COMMIT;
     
    CREATE INDEX IND_T5_C10_F ON T5(LOWER(C10));  
     
    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T5',CASCADE=>TRUE)
    Now, let's put together a test case script that uses the above table:
    ALTER SYSTEM FLUSH BUFFER_CACHE;
    ALTER SYSTEM FLUSH BUFFER_CACHE;
     
    SET TIMING ON
    SET AUTOTRACE TRACEONLY STATISTICS EXPLAIN
    SET ARRAYSIZE 1000
     
    SELECT  
      *  
    FROM  
      T5  
    WHERE  
      LOWER(C10)=LPAD('a',50,'a');  
      
    SELECT /*+ RULE */
      *  
    FROM  
      T5  
    WHERE  
      LOWER(C10)=LPAD('a',50,'a');  
     
    SELECT /*+ __FAST=TRUE */
      *  
    FROM  
      T5  
    WHERE  
      LOWER(C10)=LPAD('a',50,'a');
     
    SELECT /*+ INDEX(T5 IND_T5_C10_F) */
      *  
    FROM  
      T5  
    WHERE  
      LOWER(C10)=LPAD('a',50,'a');
    In the above, we have an unhinted query, the same query with a RULE hint, the same query with the __FAST=TRUE hint, and the same query with an index hint. Which query will execute the fastest, and why? Let's execute the test case script to find out (note that your results could be very different from my results):
    SQL> SELECT
      2    *
      3  FROM
      4    T5
      5  WHERE
      6    LOWER(C10)=LPAD('a',50,'a');
     
    38462 rows selected.
     
    Elapsed: 00:00:00.57
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2002323537
     
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      | 38462 |  6911K|  5247   (1)| 00:01:03 |
    |*  1 |  TABLE ACCESS FULL| T5   | 38462 |  6911K|  5247   (1)| 00:01:03 |
    --------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(LOWER("C10")='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
                  aaaaaa')
     
    Statistics
    ----------------------------------------------------------
             99  recursive calls
              0  db block gets
          19441  consistent gets
          19295  physical reads
              0  redo size
        1178619  bytes sent via SQL*Net to client
            937  bytes received via SQL*Net from client
             40  SQL*Net roundtrips to/from client
             10  sorts (memory)
              0  sorts (disk)
          38462  rows processed
    The unhinted version completed in 0.57 seconds, and used a full table scan.
    SQL> SELECT /*+ RULE */
      2    *
      3  FROM
      4    T5
      5  WHERE
      6    LOWER(C10)=LPAD('a',50,'a');
     
    38462 rows selected.
     
    Elapsed: 00:00:00.31
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2002323537
     
    ----------------------------------
    | Id  | Operation         | Name |
    ----------------------------------
    |   0 | SELECT STATEMENT  |      |
    |*  1 |  TABLE ACCESS FULL| T5   |
    ----------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(LOWER("C10")='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
                  aaaaaa')
     
    Note
    -----
       - rule based optimizer used (consider using cbo)
     
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
          19327  consistent gets
              0  physical reads
              0  redo size
        1178619  bytes sent via SQL*Net to client
            937  bytes received via SQL*Net from client
             40  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          38462  rows processed
    The version that was hinted to use the rule based optimizer completed in 0.31 seconds (faster than the unhinted version), and also used a full table scan.
    SQL> SELECT /*+ __FAST=TRUE */
      2    *
      3  FROM
      4    T5
      5  WHERE
      6    LOWER(C10)=LPAD('a',50,'a');
     
    38462 rows selected.
     
    Elapsed: 00:00:00.28
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2002323537
     
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      | 38462 |  6911K|  5247   (1)| 00:01:03 |
    |*  1 |  TABLE ACCESS FULL| T5   | 38462 |  6911K|  5247   (1)| 00:01:03 |
    --------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(LOWER("C10")='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
                  aaaaaa')
     
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
          19326  consistent gets
              0  physical reads
              0  redo size
        1178619  bytes sent via SQL*Net to client
            937  bytes received via SQL*Net from client
             40  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          38462  rows processed
    The version hinted with __FAST=TRUE (a completely fake hint) completed in 0.28 seconds (0.03 seconds faster than the rule hinted version) and also used a full table scan.
    SQL> SELECT /*+ INDEX(T5 IND_T5_C10_F) */
      2    *
      3  FROM
      4    T5
      5  WHERE
      6    LOWER(C10)=LPAD('a',50,'a');
     
    38462 rows selected.
     
    Elapsed: 00:00:00.17
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1769636183
     
    --------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |              | 38462 |  6911K| 20458   (1)| 00:04:06 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T5           | 38462 |  6911K| 20458   (1)| 00:04:06 |
    |*  2 |   INDEX RANGE SCAN          | IND_T5_C10_F | 40009 |       |   350   (0)| 00:00:05 |
    --------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access(LOWER("C10")='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')
     
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
          19576  consistent gets
            352  physical reads
              0  redo size
        1178628  bytes sent via SQL*Net to client
            937  bytes received via SQL*Net from client
             40  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          38462  rows processed
    The index hinted version of the query completed in 0.17 seconds, which is about twice as fast as the query with the RULE hint and the unhinted version.

    With this knowledge, certain questions might come to mind:
    * Why was the first query slower than the third query?
    * Why did the optimizer not automatically select the index when that access path was faster? What do I need to check to see why the index was not selected?
    * Why didn't the RULE based optimizer select the index access path?

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.

    Edited by: Charles Hooper on Jan 9, 2011 10:58 AM
  • 5. Re: how to tune queries that have best performance with rule hint
    602378 Newbie
    Currently Being Moderated
    IMPORTANT: I have not a case, I'm just planning in case of the occurrence of this situation
    Sounds like you came from a background where sticking in the RULE hint worked wonders, and that was certainly the case in earlier versions of the CBO.

    If you are looking to use the RULE hint as a magic wand to solve your problems in later versions, you should get a refund for the wand.

    Spend a bit of time getting to know the CBO instead. You don't have to be an expert, but you do need to know that basics.

Legend

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