Re: 1.7GB SHARABLE_MEM used by single SQL
- From: Eagle Fan <eagle.f@xxxxxxxxx>
- To: Yong Huang <yong321@xxxxxxxxx>
- Date: Sun, 19 Jun 2011 08:53:40 +0800
hi Yong:
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@xxxxxxxxx> 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)
Other related posts:
