Query Executes in Seconds with the RULE Hint and Several Minutes Otherwise – What Would You Do?

January 10, 2011

An interesting hypothetical question was posed on the OTN forums yesterday.  Consider this situation.  You are using Oracle Database 11.2.0.2 and you find a query that is executing much slower than expected, typically requiring 10 to 15 minutes to execute.  You add a RULE hint to the query and find that the query completes in just a couple of seconds.  How would you do to address this issue?

—-

To help your thought process, consider the following test case that I included in the OTN thread.  The table definition:

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 (your execution time may be a bit longer).

—-

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 (nearly twice as fast as 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?
• How might the test script results change if we were to replace the number 50 in the script with either the number 20 or the number 10?
• How might the test script results change if someone had altered the OPTIMIZER_INDEX_COST_ADJ parameter?

—-

The hypothetical question was essentially very simple.  How would you guide Oracle’s optimizer to find the optimal execution path?

—-

Edit January 10, 2011: Note that the phrase “How would you do to address this issue” in the initial paragraph is intentionally left undefined, and left for your interpretation.  “This issue” could very well have multiple intended meanings, depending on how you read the paragraph.