5 Replies Latest reply: Jan 9, 2011 11:28 AM by ben23 RSS

    how to tune queries that have best performance with rule hint

    Insaponata-Oracle
      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
          Charles Hooper
          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-Oracle
            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
              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
                Charles Hooper
                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
                  ben23
                  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.