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.
optimizer_capture_sql_plan_baselines is FALSE.
optimizer_use_sql_plan_baselines is TRUE.
To then check the baseline repository contents with this query:
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.