Describe how to show Oracle execution plan & optimizer hints.
0.prerequisite
$ sqlplus / as sysdba
sql>GRANT PLUSTRACE TO SCOTT;
sql>CONNECT scott/tiger;
sql>set autotrace traceonly;
(
1 set autotrace off; -- default setting
2 set autotrace on explain; -- show execution plan only
3 set autotrace on statistics; -- show statistics information only
4 set autotrace on; -- include both 2 and 3
5 set autotrace traceonly; -- like 4,but does not show execution result
)
sql>set timing on;
sql>set line 120;
Support table has 200K data rows.
It has unique constraint on fieldSEQ_NUMNOT NULL NUMBER(8) --UK_TAB
Its key contains 10 fields:
KEY_USER_ID NOT NULL CHAR(3)
KEY_CLASS NOT NULL CHAR(1)
KEY_ACCOUNT NOT NULL CHAR(4)
KEY_PROJECT NOT NULL CHAR(4)
...
Case 1: sql> select * from TAB;
200000 rows selected.
Elapsed: 00:00:37.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2031662960
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200K| 53M| 6642 (1)| 00:01:20 |
| 1 | TABLE ACCESS FULL| TAB | 200K| 53M| 6642 (1)| 00:01:20 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
36981 consistent gets
0 physical reads
0 redo size
60072986 bytes sent via sql*Net to client
147187 bytes received via sql*Net from client
13335 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200000 rows processed
Case 2:sql> select * from TAB ORDER BY SEQ_NUM;
200000 rows selected.
Elapsed: 00:00:35.27
Execution Plan
----------------------------------------------------------
Plan hash value: 1156615789
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200K| 53M| 8407 (1)| 00:01:41 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 200K| 53M| 8407 (1)| 00:01:41 |
| 2 | INDEX FULL SCAN | UK_TAB | 200K| | 380 (2)| 00:00:05 |
-------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
35741 consistent gets
0 physical reads
0 redo size
60072986 bytes sent via sql*Net to client
147187 bytes received via sql*Net from client
13335 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200000 rows processed
Case 3: sql> select * from TAB where SEQ_NUM between 100000 and 150000;
50001 rows selected.
Elapsed: 00:00:08.95
Execution Plan
----------------------------------------------------------
Plan hash value: 814933757
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50002 | 13M| 2105 (1)| 00:00:26 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 50002 | 13M| 2105 (1)| 00:00:26 |
|* 2 | INDEX RANGE SCAN | UK_TAB | 50002 | | 97 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SEQ_NUM">=100000 AND "SEQ_NUM"<=150000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8093 consistent gets
0 physical reads
0 redo size
37187 bytes received via sql*Net from client
3335 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50001 rows processed
Case 4: sql> select * from TAB where SEQ_NUM between 100000 and 150000 ORDER BY SEQ_NUM;
50001 rows selected.
Elapsed: 00:00:08.81
Execution Plan
----------------------------------------------------------
Plan hash value: 814933757
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50002 | 13M| 2105 (1)| 00:00:26 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 50002 | 13M| 2105 (1)| 00:00:26 |
|* 2 | INDEX RANGE SCAN | UK_TAB | 50002 | | 97 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SEQ_NUM">=100000 AND "SEQ_NUM"<=150000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8093 consistent gets
0 physical reads
0 redo size
37187 bytes received via sql*Net from client
3335 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50001 rows processed
Case 5: sql> select /*+ index_asc(TAB PK_TAB) */ * from TAB where SEQ_NUM between 100000 and 150000;
50001 rows selected.
Elapsed: 00:00:08.68
Execution Plan
----------------------------------------------------------
Plan hash value: 387596271
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50002 | 13M| 9444 (1)| 00:01:54 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TAB | 50002 | 13M| 9444 (1)| 00:01:54 |
| 2 | INDEX FULL SCAN | PK_TAB | 200K| | 1415 (1)| 00:00:17 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SEQ_NUM">=100000 AND "SEQ_NUM"<=150000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18096 consistent gets
0 physical reads
0 redo size
3765097 bytes sent via sql*Net to client
37187 bytes received via sql*Net from client
3335 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50001 rows processed
Case 6: sql> select /*+ index_asc(TAB UK_TAB) */ * from TAB where SEQ_NUM between 100000 and 150000;
50001 rows selected.
Elapsed: 00:00:08.80
Execution Plan
----------------------------------------------------------
Plan hash value: 814933757
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50002 | 13M| 2105 (1)| 00:00:26 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 50002 | 13M| 2105 (1)| 00:00:26 |
|* 2 | INDEX RANGE SCAN | UK_TAB | 50002 | | 97 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SEQ_NUM">=100000 AND "SEQ_NUM"<=150000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8093 consistent gets
0 physical reads
0 redo size
37187 bytes received via sql*Net from client
3335 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50001 rows processed
Case 7: sql> select /*+ FULL(TAB) CACHE(TAB) */ * from TAB where SEQ_NUM between 100000 and 150000;
50001 rows selected.
Elapsed: 00:00:08.16
Execution Plan
----------------------------------------------------------
Plan hash value: 2031662960
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50002 | 13M| 6628 (1)| 00:01:20 |
|* 1 | TABLE ACCESS FULL| TAB | 50002 | 13M| 6628 (1)| 00:01:20 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SEQ_NUM">=100000 AND "SEQ_NUM"<=150000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26321 consistent gets
0 physical reads
0 redo size
3765097 bytes sent via sql*Net to client
37187 bytes received via sql*Net from client
3335 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50001 rows processed
Appendix 1:
Jonathan Lewis's Rules for Hinting:
1. Don’t
2. If you must use hints,then assume you’ve used them incorrectly.
3. On every patch or upgrade to Oracle,assume every piece of hinted sql is going to do the wrong thing … because of (2) above. You’ve been lucky so far,but the patch/upgrade lets you discover your mistake.
Appendix 2:
The Oracle Optimizer Explain the Explain Plan
