Re: 1.7GB SHARABLE_MEM used by single SQL
- From: Paul Drake <bdbafh@xxxxxxxxx>
- To: eagle.f@xxxxxxxxx
- Date: Wed, 15 Jun 2011 11:07:24 -0400
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
>
Other related posts:
