To Create Plan Table
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT ALL ON sys.plan_table TO public;

SQL> EXPLAIN PLAN FOR
2 select * from rman.titi;

Explained.

How do I display and read the execution plans for a SQL statement

EXPLAIN PLAN command
V$SQL_PLAN
DBMS_XPLAN package

from EXPLAIN PLAN command
from V$SQL_PLAN
from Automatic Workload Repository (AWR)
from SQL Tuning Set (STS)
from SQL Plan Baseline (SPM

EXPLAIN PLAN




The arguments are for DBMS_XPLAN.DISPLAY are:


Plan table name (default 'PLAN_TABLE') or specified table name 
Statement_id (default NULL) or statement id has been specified
Format (default 'TYPICAL', 'BASIC','ALL') 




1

EXPLAIN PLAN FOR


SELECT * FROM   emp e, dept d

WHERE  e.deptno = d.deptno AND e.ename  = 'BLAKE';

2

select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));

or

SELECT PLAN_TABLE_OUTPUT 

FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', null,'BASIC'));

or

SELECT PLAN_TABLE_OUTPUT 

FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', null,'TYPICAL'));

or

SELECT PLAN_TABLE_OUTPUT 

FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', null,'ALL'));

or

$ORACLE_HOME/rdbms/admin/dbmsxpln.sql




DBMS_XPLAN.DISPLAY_CURSOR



The arguments used by DBMS_XPLAN.DISPLAY_CURSOR are:



SQL ID (default NULL, which means the last SQL statement executed in this session)
Child number (default 0)
Format (default 'TYPICAL''BASIC','ALL', 'ADVANCED')





note: you must provied sql_id




Directly:
select plan_table_output from

table(dbms_xplan.display_cursor('&sql_id',null,'basic'));




Indirectly:



select plan_table_output

from v$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'basic')) t



where s.sql_text like 'select PROD_CATEGORY%';





Displaying an execution plan corresponding to a SQL Plan Baseline

1

alter session set optimizer_capture_sql_plan_baselines=true;

2




SELECT * FROM   emp e, dept d


WHERE  e.deptno = d.deptno AND e.ename  = 'BLAKE';

3 to check in baseline (it's possible to found more than one) 

select SQL_HANDLE, PLAN_NAME, ACCEPTED 

from dba_sql_plan_baselines where sql_text like 'SELECT * FROM emp e, dept d%';

4-a

Directly:

select t.* from



table(dbms_xplan.display_sql_plan_baseline('&SQL_HANDLE',format => 'basic')) t



4-b

Indirectly:

select t.* from (select distinct sql_handle 

from dba_sql_plan_baselines 

where sql_text like 'SELECT * FROM emp e, dept d%') pb, 



table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle,null,'basic')) t;



Basic: The plan includes the operation, options, and the object name (table, index, MV, etc)



Typical: It includes the information shown in BASIC plus additional optimizer-related internal information such as cost, size, cardinality, etc. These information are shown for every operation in the plan and represents what the optimizer thinks is the operation cost, the number of rows produced, etc. It also shows the predicates evaluation by the operation. There are two types of predicates: ACCESS and FILTER. The ACCESS predicates for an index are used to fetch the relevant blocks because they apply to the search columns. The FILTER predicates are evaluated after the blocks have been fetched.



All: It includes the information shown in TYPICAL plus the lists of expressions (columns) produced by every operation, the hint alias and query block names where the operation belongs. The last two pieces of information can be used as arguments to add hints to the statement.

Explain with set statement id
EXPLAIN PLAN SET STATEMENT_ID='plan_titi' for
select * from rman.titi;

Explained.

SET LINESIZE 130
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','plan_titi','BASIC'));
Plan hash value: 1230735768

----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TITI |
----------------------------------

8 rows selected.

Advance
SET LINESIZE 130
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));

SQL_ID 1svu7vvskhaa2, child number 0
-------------------------------------
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','plan_titi','BASIC
'))

Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29 (100)| |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$F5BB74E1 / KOKBF$0@SEL$2

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$F5BB74E1" "KOKBF$0"@"SEL$2")
END_OUTLINE_DATA
*/

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - VALUE(A0)[300]

41 rows selected.

SQL>

GATHER_PLAN_STATISTICS Hint

SELECT /*+ GATHER_PLAN_STATISTICS */ * from rman.titi;
marta
jimena
nancy

SET LINESIZE 130
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

SQL_ID d26gbgzg7sp82, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ * from rman.titi

Plan hash value: 1230735768

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 8 | 6 |
| 1 | TABLE ACCESS FULL| TITI | 1 | 3 | 3 |00:00:00.01 | 8 | 6 |
---------------------------------------------------------------------------------------------

13 rows selected.

Specifying Different Tables for EXPLAIN PLAN
EXPLAIN PLAN
INTO my_plan_table
FOR

SELECT last_name FROM employees;

Specifying Different Tables for EXPLAIN PLAN and set statement id

EXPLAIN PLAN
SET STATEMENT_ID = 'st1'
INTO my_plan_table
FOR

SELECT last_name FROM employees;

SELECT cardinality "Rows",
lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0
AND statement_id = 'st1'

ORDER BY id;

To display Plan Table
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

SELECT PLAN_TABLE_OUTPUT

FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'st1','BASIC'));

SELECT PLAN_TABLE_OUTPUT

FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));

SELECT PLAN_TABLE_OUTPUT

FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'st1','ALL'));

To query our plan_table
1#
EXPLAIN PLAN
SET STATEMENT_ID = 'st1'
select * from emp;

2#
SELECT cardinality "Rows",
lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0
AND statement_id = 'st1'

ORDER BY id;