Find the SQL Plan Baseline for a plan operation

By Franck Pachot

.
If you decide to capture SQL Plan Baselines, you achieve plan stability by being conservative: if the optimizer comes with a new execution plan, it is loaded into the SQL Plan Management base, but not accepted. One day, you may add an index to improve some queries. Then you should check if there is any SQL Plan Baseline for queries with the same access predicate. Because the optimizer will probably find this index attractive, and add the new plan in the SPM base, but it will not be used unless you evolve it to accept it. Or you may remove the SQL Plan Baseline for these queries now that you know you provided a very efficient access path.

But how do you find all SQL Plan Baselines that are concerned? Here is an example.

I start with the SCOTT schema where I capture the SQL Plan Baselines for the following queries:

set time on sqlprompt 'SQL> '
host TWO_TASK=//localhost/CDB1A_PDB1.subnet.vcn.oraclevcn.com sqlplus sys/"demo##OracleDB20c" as sysdba @ ?/rdbms/admin/utlsampl.sql
connect scott/tiger@//localhost/CDB1A_PDB1.subnet.vcn.oraclevcn.com
alter session set optimizer_mode=first_rows optimizer_capture_sql_plan_baselines=true;
select * from emp where ename='SCOTT';
select * from emp where ename='SCOTT';

This is a full table scap because I have no index here.
Now I create an index that helps for this kind of queries:

alter session set optimizer_mode=first_rows optimizer_capture_sql_plan_baselines=false;
host sleep 1
create index emp_ename on emp(ename);
host sleep 1
select * from emp where ename='SCOTT';

I have now, in addition to the accepted FULL TABLE SCAN baseline, the loaded, but not accepted, plan with INDEX access.
Here is the detail the list of plans:

SQL> select sql_handle,plan_name,created,enabled ENA,accepted ACC,fixed FIX,origin from dba_sql_plan_baselines;

             SQL_HANDLE                         PLAN_NAME            CREATED    ENA    ACC    FIX          ORIGIN
_______________________ _________________________________ __________________ ______ ______ ______ _______________
SQL_62193752b864a1e8    SQL_PLAN_6469raaw698g854d6b671    17-apr-20 19:37    YES    NO     NO     AUTO-CAPTURE
SQL_62193752b864a1e8    SQL_PLAN_6469raaw698g8d8a279cc    17-apr-20 19:37    YES    YES    NO     AUTO-CAPTURE

Full table scan:

SQL> select * from dbms_xplan.display_sql_plan_baseline('SQL_62193752b864a1e8','SQL_PLAN_6469raaw698g8d8a279cc'
);

                                                                  PLAN_TABLE_OUTPUT
___________________________________________________________________________________

--------------------------------------------------------------------------------
SQL handle: SQL_62193752b864a1e8
SQL text: select * from emp where ename='SCOTT'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6469raaw698g8d8a279cc         Plan id: 3634526668
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    87 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"='SCOTT')

Index access – not accepted

SQL> select * from dbms_xplan.display_sql_plan_baseline('SQL_62193752b864a1e8','SQL_PLAN_6469raaw698g854d6b671'
);

                                                                                   PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________

--------------------------------------------------------------------------------
SQL handle: SQL_62193752b864a1e8
SQL text: select * from emp where ename='SCOTT'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6469raaw698g854d6b671         Plan id: 1423357553
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 2855689319

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |     1 |    87 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |     1 |    87 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_ENAME |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ENAME"='SCOTT')

SQL Plan Baseline lookup by plan operation

Now, I want to know all queries in this case, where a SQL Plan Baseline references this index because I’ll probably want to delete all plans for this query, or maybe evolve the index access to be accepted.
Here is my query on sys.sqlobj$plan

select sql_handle,plan_name,created,enabled ENA,accepted ACC,fixed FIX,origin
 ,operation,options,object_name
from (
 -- SPM execution plans
 select signature,category,obj_type,plan_id
 ,operation, options, object_name
 from sys.sqlobj$plan
) natural join (
 -- SQL Plan Baselines
 select signature,category,obj_type,plan_id
 ,name plan_name
 from sys.sqlobj$
 where obj_type=2
) natural join (
 select plan_name
 ,sql_handle,created,enabled,accepted,fixed,origin
 from dba_sql_plan_baselines
)
where operation='INDEX' and object_name like 'EMP_ENAME'
/

This gets the signature and plan identification from sys.sqlobj$plan then joins to sys.sqlobj$ to get the plan name, and finally dba_sql_plan_baselines to get additional information:

             SQL_HANDLE                         PLAN_NAME            CREATED    ENA    ACC    FIX          ORIGIN    OPERATION       OPTIONS    OBJECT_NAME
_______________________ _________________________________ __________________ ______ ______ ______ _______________ ____________ _____________ ______________
SQL_62193752b864a1e8    SQL_PLAN_6469raaw698g854d6b671    17-apr-20 19:37    YES    NO     NO     AUTO-CAPTURE    INDEX        RANGE SCAN    EMP_ENAME

You can see that I like natural joins but be aware that I do that only when I fully control the columns by defining, in subqueries, the column projections before the join.

I have the following variant if I want to lookup by the outline hints:

select sql_handle,plan_name,created,enabled ENA,accepted ACC,fixed FIX,origin
 ,operation,options,object_name
 ,outline_data
from (
 -- SPM execution plans
 select signature,category,obj_type,plan_id
 ,operation, options, object_name
 ,case when other_xml like '%outline_data%' then extract(xmltype(other_xml),'/*/outline_data').getStringVal() end outline_data
 from sys.sqlobj$plan
) natural join (
 -- SQL Plan Baselines
 select signature,category,obj_type,plan_id
 ,name plan_name
 from sys.sqlobj$
 where obj_type=2
) natural join (
 select plan_name
 ,sql_handle,created,enabled,accepted,fixed,origin
 from dba_sql_plan_baselines
)
where outline_data like '%INDEX%'
/

This is what we find on the OTHER_XML and it is faster to filter here rather than calling dbms_xplan for each:

             SQL_HANDLE                         PLAN_NAME            CREATED    ENA    ACC    FIX          ORIGIN       OPERATION                   OPTIONS    OBJECT_NAME                                                                                                                                                                                                                                                                                                                                                                                                                             OUTLINE_DATA
_______________________ _________________________________ __________________ ______ ______ ______ _______________ _______________ _________________________ ______________ ________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
SQL_62193752b864a1e8    SQL_PLAN_6469raaw698g854d6b671    17-apr-20 19:37    YES    NO     NO     AUTO-CAPTURE    TABLE ACCESS    BY INDEX ROWID BATCHED    EMP            <outline_data><hint><![CDATA[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "EMP"@"SEL$1")]]></hint><hint><![CDATA[INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."ENAME"))]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[FIRST_ROWS]]></hint><hint><![CDATA[DB_VERSION('20.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('20.1.0')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data>

Those SYS.SQLOBJ$ tables are the tables where Oracle stores the queries for the SQL Management Base (SQL Profiles, SQL Plan Baselines, SQL Patches, SQL Quarantine).

If you want to find the SQL_ID from a SQL Plan Baseline, I have a query in a previous post:
https://medium.com/@FranckPachot/oracle-dba-sql-plan-baseline-sql-id-and-plan-hash-value-8ffa811a7c68