How to cleanup "obj stat memory" component in Shared Pool

My customer has an application which using a lots of "temporary" table. They create so many tables but only operate once. And they are using automatic SGA management,all this cause large memory usage in shared pool, especially the "obj stat memo". Recently they encountered the error 4031 , there are still 1G space available in shared pool, so i believe the root cause is too many obj stat fragment .
I have read the metalink note "Bug 5573238 Shared pool memory use / ORA-4031 due to "obj stat memo" in one subpool", oracle support advise to workaround this problem by setting STATISTICS_LEVEL=BASIC or _object_statistics"=false.
But i am not satisfied with this solution. I try to flush shared pool , and see that "obj stat memo" isn't decreased , as below( in my test env) :
SQL> select * from v$sgastat where name='obj stat memo';

POOL         NAME                            BYTES


------------ -------------------------- ----------

shared pool  obj stat memo                 1454448

SQL> alter system flush shared_pool;

System altered.
SQL> select * from v$sgastat where name='obj stat memo';

POOL         NAME                            BYTES


------------ -------------------------- ----------

shared pool  obj stat memo                 1454448

So i wonder if there is are events or other method to cleanup obj stat memory in shared pool ?

--

Maclean Liu
Oracle Database Administrator
Oracle Certified Master

http://www.youyus.com

--

http://www.freelists.org/webpage/oracle-l Received on Sun May 23 2010 - 00:16:03 CDT