SQL Tuning II DBMS_XPLAN, V$SQL_PLAN

SQL Tuning II

=============================
Contents
=============================
V$SQL_PLAN and other tables
DBMS_XPLAN

=============================
V$SQL_PLAN and other tables
=============================
V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL 

V$SQL_PLAN
V$SQL_PLAN contains the execution plan for every statement stored in the shared SQL area. 
Its definition is similar to the PLAN_TABLE.

V$SQL_PLAN_STATISTICS
The V$SQL_PLAN_STATISTICS view provides the actual execution statistics. 
All statistics, except the number of output rows, are cumulative. 

The statistics in V$SQL_PLAN_STATISTICS are available for cursors that have been compiled with the STATISTICS_LEVEL initialization parameter set to ALL.


V$SQL_PLAN_STATISTICS_ALL
This view combines both V$SQL_PLAN and V$SQL_PLAN_STATISTICS information.


=============================
DBMS_XPLAN Package
=============================
DBMS_XPLAN Package is used to  format and display execution plan.
DBMS_XPLAN Package got following functions:

DISPLAY - From plan table.

DISPLAY_AWR - For a stored SQL statement in the AWR.

DISPLAY_CURSOR - For execution plan of any loaded cursor.

DISPLAY_SQL_PLAN_BASELINE - For execution plans for the SQL statement identified by SQL handle.

DISPLAY_SQLSET - For execution plan of statements stored in a SQL tuning set.

=============================

DBMS_XPLAN Full Syntax
=============================

Full Syntax:

DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id        IN  VARCHAR2  DEFAULT  NULL,
   child_number  IN  NUMBER    DEFAULT  NULL, 

   format        IN  VARCHAR2  DEFAULT  'TYPICAL');

sql_id - is DBA_HIST_SQL_PLAN.sql_id

plan_hash_value - If omitted, the function will return ALL

                   stored execution plans for a given sql_id.

db_id  - is V$DATABASE.db_id

To find the correct sql_id:
SELECT sql_id, plan_hash_value
 FROM DBA_HIST_SQL_PLAN,
      DBA_HIST_SQLTEXT
WHERE DBA_HIST_SQLTEXT.sql_text LIKE '%KUKU%';

format - is optional.
       - typical - All data except parallel info.
       - basic - Less data than typical.
       - serial - Same as typical including parallel info.
       - all - Most detailed.


=============================
1. DISPLAY - From plan table.
=============================

EXPLAIN PLAN FOR
SELECT * 
  FROM emp e, dept d
 WHERE e.deptno = d.deptno;

SET LINESIZE 130
SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

=============================
2. DISPLAY_CURSOR - For execution plan of any loaded cursor.
=============================

To display the execution plan of the last executed statement for that session.
SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

To display the execution plan of specific statement, use V$SQL.sql_id:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));

where:
 'gwp663cqh5qbf' is the sql_id
0 is the child_number

To get the sql_id and child_number:
SELECT sql_id, child_number
FROM v$sql 
WHERE sql_text LIKE '%KUKU%';


=============================
3, DISPLAY_AWR Function.
=============================
This table function displays the contents of an execution plan stored in the AWR.

The full syntax is below. 
Only sql_id is mandatory.

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);
   
Usage Example:
SET LINESIZE 120

SET PAGESIZE 1000

This will show "historic" explain plan from AWR collected data.
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));


This will show up to date explain plan
SELECT sql_id, child_number 

  FROM V$SQL 

WHERE sql_id = 'fw4tmpkt79r4r';

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('fw4tmpkt79r4r',5,'OUTLINE'));

=============================
Reference
=============================

Explain Plan Reference

DBMS_XPLAN Reference