Re: 1.7GB SHARABLE_MEM used by single SQL
- From: Eagle Fan <eagle.f@xxxxxxxxx>
- To: Paul Drake <bdbafh@xxxxxxxxx>
- Date: Wed, 15 Jun 2011 23:15:02 +0800
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)
Other related posts:
