Get The Execution Plan

DBMS_XPLAN
· DISPLAY:  Get execution Plan from PLAN_TABLE (by  default)
· DISPLAY_AWR: get execution plan from AWR repository.
· DISPLAY_CURSOR: get execution plan by accessing the cursor in memory. 
· DISPLAY_SQL_PLAN_BASELINE: get execution plan within baseline 
· DISPLAY_SQLSET:  get execution plan within a set of SQL TUNING (STS)

· V$SQL : Lists statistics on shared SQL area, they are updated every 5 seconds, we can get statistics about current query.
· V$SQL_PLAN it have recorded within Library Cache
· V$SQL_PLAN_MONITOR
· DBA_HIST_SQL_PLAN it recorded within AWR

· DBA_SQL_PLAN_BASELINES it recorded within Baseline
· STATS$SQL_PLAN (Statspack)

DISPLAY (from plan_table)

#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'));

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

DISPLAY_AWR (from AWR by using sql_id)

note: snapshot must be run before the last snapshot to find within AWR or you must take a new it

To take a snapshot, also AWR only record the relevant query

EXEC dbms_workload_repository.create_snapshot;

To check history AWR sanapshot
select snap_id, snap_level,
to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
from dba_hist_snapshot
order by 1;

to display from AWR by using display_awr

select plan_table_output from table(dbms_xplan.display_awr('&sql_id'));

or  by using dba_hist_sqlstat and dba_hist_snapshot

set linesize 1000
select
  sql.plan_hash_value      plan,
  to_char(s.begin_interval_time,'dd/mm/yyyy hh24:mi')  begin,
  sql.sql_id               id,    
  sql.executions_delta     ,
  sql.OPTIMIZER_COST       ,
  sql.SORTS_DELTA          ,
  sql.DISK_READS_DELTA     ,
  sql.BUFFER_GETS_DELTA    ,
  sql.ROWS_PROCESSED_DELTA ,
  sql.CPU_TIME_DELTA       ,
  sql.ELAPSED_TIME_DELTA
from
   dba_hist_sqlstat        sql,
   dba_hist_snapshot         s
where
   s.snap_id = sql.snap_id
   and sql_id='&sqlid'
order by 1,2;

DISPLAY_CURSOR 

note: by default the last one has been run  

select plan_table_output from table(dbms_xplan.display_cursor);

or find out with sql_id and child_number 

1# get sql_id

select SQL_TEXT, sql_id from v$sql where SQL_TEXT like '%max(sal)%';

2# child_number

select HASH_VALUE, PLAN_HASH_VALUE, CHILD_ADDRESS, CHILD_NUMBER from v$sql_plan where sql_id='$sql_id';

3# get the plan

select plan_table_output from table(dbms_xplan.display_cursor(SQL_ID=>'&sql_id',CURSOR_CHILD_NO=>$CHILD_NUMBER_v$sql_plan'));

DISPLAY_SQL_PLAN_BASELINE

col SQL_HANDLE format a30
col PLAN_NAME format a30
set line 400
select SQL_TEXT, SQL_HANDLE, PLAN_NAME from dba_sql_plan_baselines where SQL_TEXT like '%&sql_text%';

SQL_TEXT                                                     SQL_HANDLE                     PLAN_NAME
------------------------------------------------------------ ------------------------------ ------------------------------
select max(sal) from emp e, dept d where d.DEPTNO=10 and d.D SQL_7cefe92b782277c8           SQL_PLAN_7tvz95dw24xy8c392520a

EPTNO=e.DEPTNO

by default
select plan_table_output from table(dbms_xplan.display_sql_plan_baseline);

or with sql_handle and plan_name
select plan_table_output from table(dbms_xplan.display_sql_plan_baseline(SQL_HANDLE=>'&SQL_HANDLE',PLAN_NAME=>'&PLAN_NAME'));

DISPLAY_SQLSET

select plan_table_output from table(dbms_xplan.display_sql_plan_baseline( SQLSET_NAME=>'&SQLSET_NAME',SQL_ID=>'&SQL_ID'));