Re: 1.7GB SHARABLE_MEM used by single SQL

From: Paul Drake <bdbafh_at_gmail.com>
Date: Wed, 15 Jun 2011 11:07:24 -0400
Message-ID: <BANLkTik5Rf-NE8aFJ6eZ4DcqT3JSLFT8Qw_at_mail.gmail.com>

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
>

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

Received on Wed Jun 15 2011 - 10:07:24 CDT