Explain plan for SQL_BASELINES using DBMS package in ORACLE

Explain plan for SQL_BASELINES

This post explains to find out the explain plan used by the SQL plan baselines of the SQL.

set lines 200 pages 2000
column sql_handle for a50
col plan_name for a40
col SQLTEXT_Trimmed for a60
select profile, sql_handle,plan_name,created, substr(sql_text,0,50) SQLTEXT_Trimmed,accepted, fixed, enabled 
from dba_sql_plan_baselines order by created desc;

From above command you can find the details of the baseline like name of the Baselines , SQL PLAN handle and PLAN name. Note: these plan name and SQL_HANDLE will be different from the SQLID and PLAN_HASH_VLAUE. To find out the sql_handle you can query the details from V$SQL for specific SQLID.

Now till now we have 2 things handy -

1.  If we have the SQLID , then from V$SQL we have the SQL_PLAN_HANDLE name.
2. From the SQL_PLAN_HANDLE, there can be multiple PLAN_NAMES mentioned in the DBA_SQL_PLAN_BASELINES view.

If you have only SQLPLAN_HANDLE name -


select * from table(dbms_xplan.display_sql_plan_baseline('&SQL_PLAN_HANDLE')); 

If you want to check the explain plan of specific SQL_PLAN NAME, then you can add the second argument in the above command.


select * from table(dbms_xplan.display_sql_plan_baseline('&SQL_PLAN_HANDLE','&PLAN_NAME');