Troubleshooting ORA-600[qsmoDropPlans:1] on SPM

Some time ago I got this error message:

Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb1/trace/mydb1_m000_15032.trc  (incident=354123):
ORA-00600: internal error code, arguments: [qsmoDropPlans:1], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SPM", line 2444
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 775

As the error message points to DBMS_SPM and qsmoDropPlans, I assume there is some issue with the procedure that drops unused baselines after they expire. Let’s check.

A quick select on dba_sql_management_config shows that PLAN_RETENTION_WEEKS is set to the default value of 53 weeks.

Related parameters:

optimizer_capture_sql_plan_baselines is FALSE.
optimizer_use_sql_plan_baselines is TRUE.

To then check the baseline repository contents with this query:

select trunc(LAST_EXECUTED,'mm'),count(*)
from dba_sql_plan_baselines
group by trunc(LAST_EXECUTED,'mm')
order by trunc(LAST_EXECUTED,'mm')

And find out there are some four million baselines stored, of which only 500k were used in the last six months and another 500k are older than 53 weeks. In some point the optimizer_capture_sql_plan_baselines parameter was set to TRUE, capturing plans that aged out.

Although the definitive solution would be to log a SR with Oracle Support and wait for the bugfix for the automatic purging procedure, I chose the faster way and used this code to manually purge the plans not used in the last 180 days. The most recent 180 days worth of baselines I’m keeping as they are in use by the database.

This entry was posted in Troubleshoot and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s