RE: shared pool size / library cache locks / too big ?

From: <Christopher.Taylor2_at_parallon.net>
Date: Mon, 3 Jun 2013 07:42:00 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E887A03A6080_at_NADCWPMSGCMS10.hca.corpad.net>



See earlier replies back to the list - buffer cache is approximately 70GB, we have a floor for both buffer cache and shared pool sizes. Each server has 128GB of RAM and are dedicated RAC database servers. Approximately 15K distinct SQL_IDs, and 4K distinct FORCE_MATCHING_SIGNATURES on 1 node based on what AWR has captured over a 24 hour period. Regards,
Chris

From: Chris Dunscombe [mailto:cdunscombe_at_yahoo.com] Sent: Monday, June 03, 2013 3:54 AM
To: Taylor Christopher - Nashville; hurleyjohnb_at_yahoo.com Cc: oracle-l_at_freelists.org
Subject: Re: shared pool size / library cache locks / too big ?

Hi,

My 2 cents, pence etc.

I've seen many cases ( > 10) where Oracle (in some sort of AMM/ASMM) has kept on increasing the shared pool at the expense of the buffer cache. The max I saw the shared pool was approx 20 GB and in another case the buffer cache was squeezed to 25 MB. I think this is basically a flaw in how Oracle does the memory management. Normally this shared pool "bloating" is down to literal SQL or some "bug" where Oracle creates lots of child cursors for specific SQL statements. Personally I can't see there being many cases where the shared pool really needs be bigger than 2GB and in most cases 1 GB is enough.

BTW I've this on 11.1 through to 11.2.0.3

So, almost certainly, a 22GB shared pool is way too big, giving you no performance benefit at all and probably hampering performance and I'm sure that there's something much better you could use the "extra" 20GB of bloat for.

Cheers,

Chris



From: "Christopher.Taylor2_at_parallon.net" <Christopher.Taylor2_at_parallon.net> To: hurleyjohnb_at_yahoo.com
Cc: oracle-l_at_freelists.org
Sent: Sunday, 2 June 2013, 20:04
Subject: RE: shared pool size / library cache locks / too big ?

AMM is on but we have a floor for both shared pool and buffer cache. Buffer Cache is approximately 70gb - each server in the cluster has 128GB ram.

Checking DBA_HIST_ACTIVE_SESS_HISTORY for a 24-hour period on node2 there are 15K DISTINCT SQL_IDs.

There are 4K distinct FORCE_MATCHING_SIGNATURES over the same period (so LOTS of similar sqls with different literals).

Chris

From: John Hurley [mailto:hurleyjohnb_at_yahoo.com<mailto:hurleyjohnb_at_yahoo.com>] Sent: Sunday, June 02, 2013 1:40 PM
To: Taylor Christopher - Nashville
Subject: Re: shared pool size / library cache locks / too big ?

Are you using the automated memory management stuff and letting the shared pool grow to 22 gb ( not a big fan at all of the automatic stuff ) or is it hard coded to be that large? Most people probably "do not need" / "do not use (well)" a shared pool more than 4 or 5 gb. How many distinct sql statements are in your shared pool? How many are the same except for literal values?

How large is your buffer cache?

How large is the servers physical memory?

In general for many of the questions that you submit here supplying more background information rather than just a couple of small tidbits might contribute to getting a better discussion.

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jun 03 2013 - 14:42:00 CEST

Original text of this message