Re: 1.7GB SHARABLE_MEM used by single SQL

Note 1114533.1 is for windows platform. We are using 11.2.0.2 version (Solaris sparc 64 bit). The bug is not fixed in this version.

I also found somebody else had the same problem on oracle-l, but in his case the cursor was not so big. And there was no solution provided in his email thread.

I will contact oracle support for the patch on solaris sparc.

When active sessions are using the SQL, you can't flush it out. dbms_shared_pool.purge could lead to mutex contention in this case when you have so big cursor.

Thanks.

On Sat, Jun 18, 2011 at 5:57 AM, Yong Huang <yong321_at_yahoo.com> wrote:

> 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
>

-- 
Eagle Fan (www.dbafan.com)

--
http://www.freelists.org/webpage/oracle-l