I was testing SQL plan baselines on a base 11.2.0.3 release of Oracle on a 64 bit Linux virtual machine. I ran DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE to create a SQL plan baseline for a test query after running that query once to get its plan in the cursor cache. When I ran the test query after creating the SQL plan baseline and called dbms_xplan.display_cursor to see its new plan I got an ORA-01403 error:
ORCL:SYSTEM>select * from table(dbms_xplan.display_cursor(null,null,'ALL')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------ SQL_ID 4mu5a860ardpz, child number 1 An uncaught error happened in prepare_sql_statement : ORA-01403: no data found NOTE: cannot fetch plan for SQL_ID: 4mu5a860ardpz, CHILD_NUMBER: 1 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan)
I applied patch 19183482 to my test system and the ORA-01403 error went away:
ORCL:SYSTEM>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 4mu5a860ardpz, child number 1
-------------------------------------
select sum(blocks) from test
Plan hash value: 1950795681
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| TEST | 2844 | 8532 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TEST@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM("BLOCKS")[22]
2 - "BLOCKS"[NUMBER,22]
Note
-----
- SQL plan baseline SQL_PLAN_dscatqjvyk4qy6b581ab9 used for this statement
Here is a zip of the script that I ran to create the ORA-01403 error and the output that it generated with and without the patch applied on my test database: zip
Here is a list of the bugs that patch 19183482’s readme says it will fix:
14512308: SPM BASELINE PLAN CANNOT BE REPRODUCED
15858022: ‘LIBRARY CACHE: MUTEX X’ AND LIBRARY CACHE LOCKS PURGED_CURSOR
16400122: SPIKES IN LIBRARY CACHE
16625010: SPM BASELINE NOT WORKING FOR SQL CALLED FROM PL/SQL
I haven’t gotten very far into my investigation of SQL plan baselines but it looks like it would be a good idea to apply 19183482 before using SQL plan baselines on 11.2.0.3. I barely got started using SQL plan baselines and I immediately hit this bug.
– Bobby
