Re: 1.7GB SHARABLE_MEM used by single SQL
- From: Eagle Fan <eagle.f@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 15 Jun 2011 23:39:13 +0800
hi:
Is there any way to dump a cursor's sharable memory?
When the SQL's version was not increasing, the sharable memory was
increasing slowly. I'm curious what caused the increasing of sharable
memory.
Thanks.
On Wed, Jun 15, 2011 at 11:15 PM, Eagle Fan <eagle.f@xxxxxxxxx> wrote:
> Hi:
>
> I don't see special information of this in alert log.
>
> Thanks.
>
>
> On Wed, Jun 15, 2011 at 11:07 PM, Paul Drake <bdbafh@xxxxxxxxx> wrote:
>
>> Check the alert log and udump location under the diag dest for trace
>> files.
>> That size is way over what the default notification size is for a heap
>> size exceeding the notification threshold.
>>
>> hth.
>>
>> -Steelers Fan
>>
>>
>>
>> On Wed, Jun 15, 2011 at 10:41 AM, Eagle Fan <eagle.f@xxxxxxxxx> wrote:
>>
>>> Hi:
>>>
>>> We are seeing a single SQL using very big SHARABLE_MEM.
>>>
>>> select VERSION_COUNT,SHARABLE_MEM from v$sqlarea where hash_value=
>>> 2038009379;
>>>
>>> VERSION_COUNT SHARABLE_MEM
>>> ------------- ------------
>>> 96 1888704961
>>>
>>> The database version is 11.2.0.2, and jdbc driver version is 11,2.
>>> cursor_sharing parameter is set as EXACT.
>>>
>>> The problem only happens on 11.2 database with 11g jdbc drivers. The SQL
>>> also runs on other 10g databases using 11g jdbc driver, but the SHARABLE_MEM
>>> is only 4MB.
>>>
>>> 10g database:
>>>
>>> select VERSION_COUNT,SHARABLE_MEM from v$sqlarea where hash_value=
>>> 2038009379;
>>>
>>> VERSION_COUNT SHARABLE_MEM
>>> ------------- ------------
>>> 214 4216097
>>>
>>> If we change the client's jdbc driver back to 10g, the problem is gone.
>>>
>>> Does anybody have the same problem?
>>>
>>> Is there any event or command can dump the SQL's SHARABLE_MEM and check
>>> what content it has?
>>>
>>> Thanks in advance!
>>>
>>> --
>>> Eagle Fan
>>> Sr. DBA
>>>
>>
>>
>>
>>
>>
>
>
> --
> Eagle Fan (www.dbafan.com)
>
--
Eagle Fan (www.dbafan.com)
Other related posts:
