1.7GB SHARABLE_MEM used by single SQL
- From: Eagle Fan <eagle.f@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 15 Jun 2011 22:41:39 +0800
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:
