Oracle Execution Plan & Optimizer Hints

来源：互联网发布：乐视max2 root软件编辑：程序博客网时间：2024/06/05 00:43

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 field SEQ_NUM NOT 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

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 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

0 0