.
\nIf 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.<\/p>\n
But how do you find all SQL Plan Baselines that are concerned? Here is an example.<\/p>\n
I start with the SCOTT schema where I capture the SQL Plan Baselines for the following queries:<\/p>\n
\nset time on sqlprompt 'SQL> '\nhost TWO_TASK=\/\/localhost\/CDB1A_PDB1.subnet.vcn.oraclevcn.com sqlplus sys\/\"demo##OracleDB20c\" as sysdba @ ?\/rdbms\/admin\/utlsampl.sql\nconnect scott\/tiger@\/\/localhost\/CDB1A_PDB1.subnet.vcn.oraclevcn.com\nalter session set optimizer_mode=first_rows optimizer_capture_sql_plan_baselines=true;\nselect * from emp where ename='SCOTT';\nselect * from emp where ename='SCOTT';\n<\/code><\/pre>\nThis is a full table scap because I have no index here.
\nNow I create an index that helps for this kind of queries:<\/p>\n
\nalter session set optimizer_mode=first_rows optimizer_capture_sql_plan_baselines=false;\nhost sleep 1\ncreate index emp_ename on emp(ename);\nhost sleep 1\nselect * from emp where ename='SCOTT';\n<\/code><\/pre>\nI have now, in addition to the accepted FULL TABLE SCAN baseline, the loaded, but not accepted, plan with INDEX access.
\nHere is the detail the list of plans:<\/p>\n
\nSQL> select sql_handle,plan_name,created,enabled ENA,accepted ACC,fixed FIX,origin from dba_sql_plan_baselines;\n\n SQL_HANDLE PLAN_NAME CREATED ENA ACC FIX ORIGIN\n_______________________ _________________________________ __________________ ______ ______ ______ _______________\nSQL_62193752b864a1e8 SQL_PLAN_6469raaw698g854d6b671 17-apr-20 19:37 YES NO NO AUTO-CAPTURE\nSQL_62193752b864a1e8 SQL_PLAN_6469raaw698g8d8a279cc 17-apr-20 19:37 YES YES NO AUTO-CAPTURE\n<\/code><\/pre>\nFull table scan:<\/p>\n
\nSQL> select * from dbms_xplan.display_sql_plan_baseline('SQL_62193752b864a1e8','SQL_PLAN_6469raaw698g8d8a279cc'\n);\n\n PLAN_TABLE_OUTPUT\n___________________________________________________________________________________\n\n--------------------------------------------------------------------------------\nSQL handle: SQL_62193752b864a1e8\nSQL text: select * from emp where ename='SCOTT'\n--------------------------------------------------------------------------------\n\n--------------------------------------------------------------------------------\nPlan name: SQL_PLAN_6469raaw698g8d8a279cc Plan id: 3634526668\nEnabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE\nPlan rows: From dictionary\n--------------------------------------------------------------------------------\n\nPlan hash value: 3956160932\n\n--------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n--------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |\n|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 |\n--------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n 1 - filter(\"ENAME\"='SCOTT')\n<\/code><\/pre>\nIndex access – not accepted<\/p>\n
\nSQL> select * from dbms_xplan.display_sql_plan_baseline('SQL_62193752b864a1e8','SQL_PLAN_6469raaw698g854d6b671'\n);\n\n PLAN_TABLE_OUTPUT\n____________________________________________________________________________________________________\n\n--------------------------------------------------------------------------------\nSQL handle: SQL_62193752b864a1e8\nSQL text: select * from emp where ename='SCOTT'\n--------------------------------------------------------------------------------\n\n--------------------------------------------------------------------------------\nPlan name: SQL_PLAN_6469raaw698g854d6b671 Plan id: 1423357553\nEnabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE\nPlan rows: From dictionary\n--------------------------------------------------------------------------------\n\nPlan hash value: 2855689319\n\n-------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n-------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |\n| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 87 | 2 (0)| 00:00:01 |\n|* 2 | INDEX RANGE SCAN | EMP_ENAME | 1 | | 1 (0)| 00:00:01 |\n-------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n 2 - access(\"ENAME\"='SCOTT')\n\n<\/code><\/pre>\nSQL Plan Baseline lookup by plan operation<\/h3>\n
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.
\nHere is my query on sys.sqlobj$plan<\/p>\n
\nselect sql_handle,plan_name,created,enabled ENA,accepted ACC,fixed FIX,origin\n ,operation,options,object_name\nfrom (\n -- SPM execution plans\n select signature,category,obj_type,plan_id\n ,operation, options, object_name\n from sys.sqlobj$plan\n) natural join (\n -- SQL Plan Baselines\n select signature,category,obj_type,plan_id\n ,name plan_name\n from sys.sqlobj$\n where obj_type=2\n) natural join (\n select plan_name\n ,sql_handle,created,enabled,accepted,fixed,origin\n from dba_sql_plan_baselines\n)\nwhere operation='INDEX' and object_name like 'EMP_ENAME'\n\/\n<\/code><\/pre>\nThis 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:<\/p>\n
\n SQL_HANDLE PLAN_NAME CREATED ENA ACC FIX ORIGIN OPERATION OPTIONS OBJECT_NAME\n_______________________ _________________________________ __________________ ______ ______ ______ _______________ ____________ _____________ ______________\nSQL_62193752b864a1e8 SQL_PLAN_6469raaw698g854d6b671 17-apr-20 19:37 YES NO NO AUTO-CAPTURE INDEX RANGE SCAN EMP_ENAME\n<\/code><\/pre>\nYou 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.<\/p>\n
I have the following variant if I want to lookup by the outline hints:<\/p>\n
\nselect sql_handle,plan_name,created,enabled ENA,accepted ACC,fixed FIX,origin\n ,operation,options,object_name\n ,outline_data\nfrom (\n -- SPM execution plans\n select signature,category,obj_type,plan_id\n ,operation, options, object_name\n ,case when other_xml like '%outline_data%' then extract(xmltype(other_xml),'\/*\/outline_data').getStringVal() end outline_data\n from sys.sqlobj$plan\n) natural join (\n -- SQL Plan Baselines\n select signature,category,obj_type,plan_id\n ,name plan_name\n from sys.sqlobj$\n where obj_type=2\n) natural join (\n select plan_name\n ,sql_handle,created,enabled,accepted,fixed,origin\n from dba_sql_plan_baselines\n)\nwhere outline_data like '%INDEX%'\n\/\n<\/code><\/pre>\nThis is what we find on the OTHER_XML and it is faster to filter here rather than calling dbms_xplan for each:<\/p>\n
\n SQL_HANDLE PLAN_NAME CREATED ENA ACC FIX ORIGIN OPERATION OPTIONS OBJECT_NAME OUTLINE_DATA\n_______________________ _________________________________ __________________ ______ ______ ______ _______________ _______________ _________________________ ______________ ________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________\nSQL_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>\n<\/code><\/pre>\nThose 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). <\/p>\n