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 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

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

| 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