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

Hello listers,

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

Other related posts: