Tuning SQL Statement using DBMS

For AWR (Automatic Workload Repository) Report (generated by running $ORACLE_HOME/rdbms/admin/awrrpt.sql), it's convenient to have SQL IDs listed for tuning any particular sql statement.

DISPLAY_AWR Function of DBMS_XPLAN Package
Its syntax is as follows:

DBMS_XPLAN.DISPLAY_AWR(
   sql_id                             IN VARCHAR2,
   plan_hash_value    IN NUMBER       DEFAULT NULL,
   db_id                              IN  NUMBER      DEFAULT NULL,
   format                           IN VARCHAR2 DEFAULT TYPICAL);

To display the execution plans associated with the SQL ID '3pw0jftkr7u42';

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('3pw0jftkr7u42'));

Displaying a Plan Table Using DBMS_XPLAN.DISPLAY
DISPLAY Function of DBMS_XPLAN Package

Its syntax is as follows:
DBMS_XPLAN.DISPLAY(
   table_name       IN VARCHAR2 DEFAULT 'PLAN_TABLE',
   statement_id    IN VARCHAR2 DEFAULT NULL,
   format                  IN VARCHAR2 DEFAULT 'TYPICAL',
   filter_preds       IN VARCHAR2 DEFAULT NULL);

For example:

SQL> EXPLAIN PLAN FOR SELECT * FROM MY_DEPT WHERE DEPTNO = 10;

SQL> SET LINESIZE 120 PAGESIZE 500
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 917032719

------------------------------------------------------------------------------------------
| Id | Operation                   | Name       | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    47 |     1   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| MY_DEPT    |     1 |    47 |     1   (0)| 00:00:01 |
|* 2 |   INDEX UNIQUE SCAN         | PK_MY_DEPT |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------

2 - access("DEPTNO"=10)

已選取 14 個資料列.

DISPLAY_CURSOR Function of DBMS_XPLAN Package :
This table function displays the explain plan of any cursor loaded in the cursor cache. In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).

Its syntax is as follows:

DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id                    IN VARCHAR2 DEFAULT NULL,
   child_number IN NUMBER        DEFAULT NULL,
   format                 IN VARCHAR2 DEFAULT 'TYPICAL');

Examples:
To display the execution plan of the last SQL statement executed by the current session:
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR);

To display the execution plan of all children associated with the SQL ID '3pw0jftkr7u42':

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('3pw0jftkr7u42'));

To display runtime statistics for the cursor included in the preceding statement:
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('3pw0jftkr7u42', NULL, 'ALLSTATS LAST');

To display "Advanced" explain plan for the cursor:
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('3pw0jftkr7u42', NULL, 'ADVANCED');