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
