Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Sizing of shared_pool

Sizing of shared_pool

From: Chris Call <Chris.Call_at_rm.panurgy.com>
Date: Wed, 5 Jul 2000 11:04:13 -0600
Message-Id: <10549.111236@fatcity.com>


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? Received on Wed Jul 05 2000 - 12:04:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US