RE: shared pool size / library cache locks / too big ?
Date: Sun, 2 Jun 2013 13:57:28 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E887A03A5F72_at_NADCWPMSGCMS10.hca.corpad.net>
Our buffer cache is ~70 GB - Single RAC database (across 3 instances on 3 nodes) - each node having 128 GB of RAM. The reason (weak as it is) that the shared pool is large is that AWR advisor section indicated increasing shared pool would improve performance. I didn't consider the implications of invalidations however and cursor searching when sizing (which is what I am thinking about now).
Thanks
Chris
From: alanbort_at_gmail.com [mailto:alanbort_at_gmail.com] On Behalf Of Guillermo Alan Bort
Sent: Sunday, June 02, 2013 1:36 PM
To: Taylor Christopher - Nashville
Cc: oracle-l-freelists
Subject: Re: shared pool size / library cache locks / too big ?
Wait, you have a 22G shared pool? what kind of database is it? how long does the parsing of new sqls take? I've seen significant performance degradation with a 2G shared pool out of a 10G SGA. Buffer cache rates were affected as well, of course, but not nearly as much as the library cache waits per execute. hth
Alan.-
On Sun, Jun 2, 2013 at 2:21 PM, <Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>> wrote:
Env: 10.2.0.4 3-node RAC.
I've been reading a bit online about library cache lock waits and ran across a forum reply by Charles Hooper in discussion about session_cached_cursors and libary cache locks and it was discussed that perhaps the shared pool was oversized.
I'm curious what symptoms/indicators (besides library cache lock waits) I can look into to determine if our shared pool (~22GB) is oversized and/or increase session cached cursors (20 currently)? The app is very immature and a mix of SQLs using binds and literals.
Chris Taylor
Oracle DBA
Parallon IT&S
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Jun 02 2013 - 20:57:28 CEST