Re: 1.7GB SHARABLE_MEM used by single SQL

From: Yong Huang <yong321_at_yahoo.com>
Date: Fri, 17 Jun 2011 14:57:14 -0700 (PDT)
Message-ID: <89919.43888.qm_at_web80604.mail.mud.yahoo.com>

I looked at our 11.2.0.1 database (on RHEL5 64-bit) to see if there's a huge heap 0 cursor. The biggest I find is 12M:

SQL> select max(kglobhs0), max(kglobhs6) from x$kglob;

MAX(KGLOBHS0) MAX(KGLOBHS6)
------------- -------------

     12971512 547536

That object is a very simple SQL, a query on a simple table with a few simple column types. So 12M does look buggy. This SQL has only a few child cursors. But the client is "JDBC Thin Client", possibly 11gR2 as well. The biggest component in the cursor heap 0 is also permanent chunks.

If the Bug is 10082277, then the good news is, the bug is fixed in 11.2.0.2, according to Note:1114533.1. For the time being, you can purge the SQL to release memory with dbms_shared_pool.purge. But may need to schedule a job to do it so the heap won't grow too big.

Yong Huang
--

http://www.freelists.org/webpage/oracle-l Received on Fri Jun 17 2011 - 16:57:14 CDT