upgrade to 11.2.0.2 gives SQL plan baseline issues

In this case, the database ran using 11.1.0.6, was upgraded to 11.1.0.7, 11.2.0.1 and finally to 11.2.0.2. After this last step the following errors occurred, a bit too often to my taste:

ORA-38141: SQL plan baseline SQL_PLAN_697q92chf3r9rc3f66c3c does not exist

SIGNATURE SQL_HANDLE SQL_TEXT PLAN_NAME CREATOR ORIGIN PARSING_SCHEMA_NAME DESCRIPTION VERSION CREATED LAST_MODIFIED LAST_EXECUTED LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE OPTIMIZER_COST MODULE ACTION EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES END_OF_FETCH_COUNT
7250453534106311991 SYS_SQL_649ec91320e1dd37 select * from dba_scheduler_jobs where owner='JOB_OWNER' and job_name like 'CR_FACTS' SQL_PLAN_697q92chf3r9r51e03d8f DBA_MONITOR AUTO-CAPTURE JOB_OWNER (null) 11.2.0.1.0 2010-07-06 18:08:47.0 2010-07-06 18:08:47.0 (null) (null) YES NO NO YES YES 9 JDBC Thin Client (null) 0 0 0 0 0 0 0 0 0
7250453534106311991 SYS_SQL_649ec91320e1dd37 select * from dba_scheduler_jobs where owner='JOB_OWNER' and job_name like 'CR_FACTS' SQL_PLAN_697q92chf3r9r8d1d2d72 JOB_OWNER AUTO-CAPTURE JOB_OWNER (null) 11.2.0.1.0 2010-07-06 14:38:41.0 2010-07-06 14:38:41.0 (null) (null) YES NO NO YES YES 9 JDBC Thin Client (null) 0 0 0 0 0 0 0 0 0
7250453534106311991 SYS_SQL_649ec91320e1dd37 select * from dba_scheduler_jobs where owner='JOB_OWNER' and job_name like 'CR_FACTS' SQL_PLAN_697q92chf3r9rc311a592 JOB_OWNER AUTO-CAPTURE JOB_OWNER (null) 11.2.0.1.0 2010-08-31 12:17:42.0 2010-08-31 12:17:42.0 (null) (null) YES NO NO YES YES 9 JDBC Thin Client (null) 0 0 0 0 0 0 0 0 0
7250453534106311991 SYS_SQL_649ec91320e1dd37 select * from dba_scheduler_jobs where owner='JOB_OWNER' and job_name like 'CR_FACTS' SYS_SQL_PLAN_20e1dd37c3f66c3c JOB_OWNER AUTO-CAPTURE JOB_OWNER (null) 11.1.0.7.0 2010-03-03 10:58:58.0 2010-03-03 10:58:58.0 2010-03-03 10:58:58.0 (null) YES YES NO YES YES 9 JDBC Thin Client (null) 0 0 0 0 0 0 0 0 0

The problems seems to be that the accepted plan's sql baseline is no longer recognized. A solution is to drop the baselines using the sql_handle using

--/
declare
z number;
begin
z:= dbms_spm.drop_sql_plan_baseline (sql_handle => 'SYS_SQL_649ec91320e1dd37');
dbms_output.put_line ('z:'||z);
end;
/

Here z shows the number of dropped baselines for the sql_handle.

an other way could be to run it as sql:
select dbms_spm.drop_sql_plan_baseline (sql_handle => 'SYS_SQL_649ec91320e1dd37') from dual;
Only this raises:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "SYS.DBMS_SPM", line 2532

Funny is that despite the exception the baseline is deleted.

The feature, sql plan baselines should help us to upgrade without the usual upgrade pain caused by performance problems that are caused by unwanted changes in execution plans. I think the feature is great, if it works. Now it does not slow down the application, now it is not running at all.

R.