What's worse, a little latch contention or a low library cache and dictionary cache hit ratio?
These are things that you have to balance. I personally would take a little latch contention over
a low hit ratio anyday.
Document what your current hit ratios and your shared_pool latch contention are for say a week or
two, then drop down the shared pool size. Document the affect it has on your library cache,
dictionary cache, and latch contention and make your own judgement.
- Chris Call <Chris.Call_at_rm.panurgy.com> wrote:
> Is there any "magic" formula for sizing the shared pool? I am having more
> latch contention and I believe it could be related to this sizing because my
> shared_pool_latch is highly contended for. I believe this is in turn
> causing other latch waits, such as cache buffer chains. I ran Steve Adams
> script latch_sleeps.sql and received the following output:
>
> LATCH TYPE IMPACT SLEEP RATE WAITS HOLDING LEVEL
> ------------------------------------- ----------- ---------- -------------
> -----
> shared pool 13577008 0.82% 7672142
> 7
> cache buffers chains 12017429 0.00% 9453410 1
> library cache 3707017 0.15% 2441940
> 5
> row cache objects 255912 0.01% 131535403
> 4
> cache buffers lru chain 7546 0.00% 74709
> 3
> Checkpoint queue latch 2242 0.00% 9602
> 7
> redo writing 1877 0.01%
> 195466 5
> session idle bit 1104 0.00%
> 0 1
> messages 1039 0.00%
> 65635 8
> latch wait list 899 0.02%
> 11775 9
> multiblock read objects 727 0.00% 6870
> 3
> session allocation 486 0.00% 4997
> 5
> undo global data 269 0.00% 150
> 5
> library cache load lock 251 0.04% 3762
> 5
> redo allocation 213 0.00% 8435
> 6
> enqueues 180 0.00% 1357
> 5
> cache buffer handles 143 0.00% 6604
> 3
> enqueue hash chains 135 0.00% 1096
> 4
> transaction allocation 69 0.00% 257
> 8
>
> It seems that the shared_pool latch is giving me the most problems and then
> maybe causing others as well. My shared_pool is currently sized to 512M on
> this highly transactional database. I am toying with idea of cutting the
> size of the shared_pool in half. Any ideas?
>
> Chris
> --
> Author: Chris Call
> INET: Chris.Call_at_rm.panurgy.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
Received on Wed Jul 05 2000 - 14:36:46 CDT
