Oracle Execution Plan & Optimizer Hints
|
Describe how to show Oracle execution plan & optimizer hints.
0.prerequisite
$ sqlplus / as sysdba SQL>@/YOUR_ORACLE_HOME/sqlplus/admin/plustrce.sql 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 15024885 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 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 15024885 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 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 15024885 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 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: (https://jonathanlewis.wordpress.com/2008/05/02/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. 4. Every time you apply some DDL to an object that appears in a piece of hinted SQL assume that the hinted SQL is going to do the wrong thing … because of (2) above. You’ve been lucky so far,but the structural change lets you discover your mistake.
Appendix 2: The Oracle Optimizer Explain the Explain Plan http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |