Re: 1.7GB SHARABLE_MEM used by single SQL

I don't see special information of this in alert log.

Thanks.

On Wed, Jun 15, 2011 at 11:07 PM, Paul Drake <bdbafh_at_gmail.com> 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_at_gmail.com> 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)

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