RE: sharable memory issues

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Mon, 23 Aug 2004 21:20:04 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09EFEB20@bosmail00.bos.il.pqe>

Terry,

Here are a few thoughts, in no particular order:

1.) I've never been able to determine any real cause/effect = relationship
when it comes to determining or predicting how much sharable memory a=20 particular statement will use. (Granted, I've never really spent a lot of time on it, either, since it's presumably something we have little or no control over.)

2.) Where are you getting SHARABLE_MEMORY from? V$SQL or V$SQLAREA? (Note that V$SQLAREA is totals, summed for all children by parent = handle,
which leads to my next question.)

3.) For a given SQL, do you see multiple children? If so, you should use V$SQL_SHARED_CURSOR to determine the reason they're not being = shared.

4.) If #2 and #3 don't apply in your case, does one system make much = more
use of public synonyms than the other? Though I don't *think* that = would=20
affect the SHARABLE_MEMORY statistic, it could certainly have an impact on the library cache, particularly w/ large numbers of users.

5.) Which leads me to, do the similar systems have similar numbers=20 of concurrent users, or does the number vary significantly?

Just some thoughts, probably none of which eactly matches your = issue.....

Perhaps others will have other thoughts,

-Mark

-----Original Message-----

From:	oracle-l-bounce_at_freelists.org on behalf of Terry Sutton
Sent:	Mon 8/23/2004 6:47 PM
To:	oracle-l_at_freelists.org
Cc:=09
Subject:	sharable memory issues

I'm dealing with a situation where there are lots of reloads of queries = for a database. There are actually 2 databases, which are roughly = identical (I realized the risks of the term "roughly"). One database = isn't doing lots of reloads and the other is, even though the queries = executed against each are very similar. I have found queries which are = identical (they look identical to my eyes, and they have the same hash = value) but use different amounts of sharable memory. In one case a = query uses 25409 bytes on database DB1 and 32801 on database DB2. The = difference doesn't always favor DB1; there is one query which uses 25278 = bytes on DB1 and 9890 bytes on DB2. But the overall totals of a certain = class of query wind up using much more sharable memory on DB2. Increasing the shared pool could reduce the reloading, but I hate doing = that if I can solve the problem otherwise, because of potential latching = issues. Can anyone suggest why identical queries could be using = different amounts of sharable memory? If I can possibly reduce the = sharable memory consumed, it would be better than increasing the shared = pool.

Thanks,

Terry

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Mon Aug 23 2004 - 20:15:44 CDT