1.7GB SHARABLE_MEM used by single SQL

From: Eagle Fan <eagle.f_at_gmail.com>
Date: Wed, 15 Jun 2011 22:41:39 +0800
Message-ID: <BANLkTincRsVMMoY6uyHGWe2sd11tnDssaQ_at_mail.gmail.com>



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 - 09:41:39 CDT

Original text of this message