How to manual purge WRH$_SQL_PLAN old data from sysaux - Need help urgently

Advertisement
Hi All,
In our production environment, we're encoutner SYSAUX tablespace high usage, the sysaux occupant show that the WRH$_SQL_PLAN occupied more than 50GB,
we have logged a SR to oracle since last year Novemember, unfortunetly they're not able to provide any solution right now, they have asked us applied a lot of patch/workaround
but still we found that the WRH$_SQL_PLAN never get purged at all, the old snap id not purge since 2011 June till now, and oracle auto purge keep failure everytime.
Below is the top 5 occupant in SYSAUX.
SEGMENT_NAME|SEGMENT_TYPE|GB
SYS_LOB0000005981C00038$$|LOBSEGMENT|26.484375
WRH$_SQL_PLAN|TABLE|19.0712890625
WRH$_SQL_PLAN_PK|INDEX|8.3974609375
Just would like to check anyone facing the same issue? And how to resolve it?
We have applied below oracle patch but still not resolve the issue:
- Stats history retention reduced to 8 days, AWR historical retention reduced to 4 days
- Patch 8553944 deployed on Nov 2011 issue unresolved, continue investigation with Oracle Support
- Patch 6394861 deployed on December 10, 2011, issue unresolved, continue investigation with Oracle Support
- Event 10445 enabled as part of patch 6394861 on January 15, 2012, issue unresolved, continue investigation with Oracle Support
- New bug 13788152 logged by Oracle Support to Development on Feburary 2012, pending findings from Oracle Development
I would like to know any efficient way we can manual purge the WRH$_SQL_PLAN table (LOB segment) manually? Thanks in advance
Regards,
klng
Advertisement

Replay

>
I would like to know any efficient way we can manual purge the WRH$_SQL_PLAN table (LOB segment) manually? Thanks in advance
>
I would be very cautious about doing anything if Oracle support hasn't been able to help you.
That said, you might want to take a look through this blog that shows how another user did it.
http://juniororacledba.wordpress.com/2011/07/28/oracle-optimizer-stats-not-being-purged/
Edited to add missing link by: rp0428 on Apr 23, 2012 8:16 PM