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.
Recent Comments