A tip I picked up from Nigel Bayliss regards purging SQL Plan Directives, I’ve been using it a lot recently and can’t see it documented elsewhere.
As some background these records, exposed via the DBA_SQL_PLAN_DIRECTIVES view, are cardinality corrections created and used when running with Adaptive Statistics enabled. There is a job that should automatically purge all records unused for longer than the value of SPD_RETENTION_WEEKS, but we’ve experienced occasions when this job doesn’t work as expected.
The records can be individually purged by calling DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE, but that’s a pain if you’ve got a lot of them
However what the documentation doesn’t mention is that you can call the procedure, passing in NULL for the mandatory directive_id parameter:
exec sys.DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE (NULL);
This will purge all records based on retention rules that the auto-purge job follows. If you really want to Purge ‘Em All then you can set the retention to 0 before calling the procedure.
exec sys.DBMS_SPD.SET_PREFS('SPD_RETENTION_WEEKS', '0');