Re: 1.7GB SHARABLE_MEM used by single SQL

From: Eagle Fan <eagle.f_at_gmail.com>
Date: Tue, 5 Jul 2011 11:03:51 +0800
Message-ID: <CANHUaOpJMGtWc79VJ4WEsEtr_R+-6DMnfL-ZYUKEAk+-94znrw_at_mail.gmail.com>

We have applied the patch on production and it really fixed the problem. The parent cursor only uses 227K memory now.

Thank you very much!

On Fri, Jun 17, 2011 at 8:33 PM, Tanel Poder <tanel_at_tanelpoder.com> wrote:

> Some cursors get created because of bind length differences - even when the
> datatype is the same. But in your case, the *number* of child cursors
> isn't probably the worst problem ... especially if you have a lot of bind
> variables in the statement with fluctuating letngths ... and anyway - it's
> your *parent* cursor which is so large - even if you flush out 90% of the
> children, the parent still consumes this memory... it's some bug - memory
> leak in parent cursor. Look into that bug I sent ...
>
> --
> Tanel Poder
> http://blog.tanelpoder.com
>
>
> On Fri, Jun 17, 2011 at 8:36 AM, Eagle Fan <eagle.f_at_gmail.com> wrote:
>
>> And it doesn't have data type conversion:
>>
>> SQL> select * from v$sql_bind_metadata where address in (select
>> CHILD_ADDRESS from v$sql_shared_cursor where ADDRESS='0000000F3BC53E78')
>> 2 and (position,datatype) not in
>> 3 ((26,1),
>> 4 (25,2),
>> 5 (24,2),
>> 6 (23,2),
>> 7 (22,2),
>> 8 (21,1),
>> 9 (20,1),
>>
>

-- 
Eagle Fan (www.dbafan.com)

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

Received on Mon Jul 04 2011 - 22:03:51 CDT