Question: I have shared pool latch
contention and I don't know what to do about the latch
contention. How do you identify latch contention in
the shared pool and how to you relieve shared pool latch
contention?
Answer:
The Oracle
shared pool contains Oracle's library cache, which is
responsible for collecting, parsing, interpreting, and
executing all of the SQL statements that go against the
Oracle database.
It's also critical to remember that the
shared_pool_size is the most important parameter
relating to shared pool resource usage.
Hence, the
shared pool is a key component which can experience frequent
contention, so it's necessary for the Oracle database
administrator to check for shared pool contention.
See these related notes on
latch contention and
related shared pool contention
articles.
Oracle also published a script using x$ksmsp to
give a listing of the RAM heap to see how free space is
allocated within the shared pool, and the sizes of available
chunks on the freelist for he shared pool, RAM.
select
'0 (<140)'
BUCKET, KSMCHCLS, KSMCHIDX,
10*trunc(KSMCHSIZ/10)
"From",
count(*) "Count" ,
max(KSMCHSIZ) "Biggest",
trunc(avg(KSMCHSIZ)) "AvgSize",
trunc(sum(KSMCHSIZ))
"Total"
from
x$ksmsp
where
KSMCHSIZ<140
and
KSMCHCLS='free'
group by
KSMCHCLS, KSMCHIDX,
10*trunc(KSMCHSIZ/10)
UNION ALL
select
'1
(140-267)' BUCKET,
KSMCHCLS,
KSMCHIDX,
20*trunc(KSMCHSIZ/20)
,
count(*) ,
max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ))
"AvgSize",
trunc(sum(KSMCHSIZ)) "Total"
from
x$ksmsp
where
KSMCHSIZ between 140 and 267
and
KSMCHCLS='free'
group by
KSMCHCLS, KSMCHIDX,
20*trunc(KSMCHSIZ/20)
UNION ALL
select
'2
(268-523)' BUCKET,
KSMCHCLS,
KSMCHIDX,
50*trunc(KSMCHSIZ/50)
,
count(*) ,
max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ))
"AvgSize",
trunc(sum(KSMCHSIZ)) "Total"
from
x$ksmsp
where
KSMCHSIZ between 268 and 523
and
KSMCHCLS='free'
group by
KSMCHCLS, KSMCHIDX,
50*trunc(KSMCHSIZ/50)
UNION ALL
select
'3-5
(524-4107)' BUCKET,
KSMCHCLS,
KSMCHIDX,
500*trunc(KSMCHSIZ/500) ,
count(*) ,
max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize",
trunc(sum(KSMCHSIZ))
"Total"
from
x$ksmsp
where
KSMCHSIZ between
524 and 4107
and
KSMCHCLS='free'
group by
KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
UNION ALL
select
'6+ (4108+)' BUCKET,
KSMCHCLS,
KSMCHIDX,
1000*trunc(KSMCHSIZ/1000) ,
count(*) ,
max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize",
trunc(sum(KSMCHSIZ)) "Total"
from
x$ksmsp
where
KSMCHSIZ >= 4108
and
KSMCHCLS='free'
group
by
KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000)
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
