分析shared pool latch争用

分析shared pool latch争用[@more@]

分析shared pool latch争用
1.评估在子latches中的分布
检查共享池中各种子latches的分布情况。看看是否是倾斜的。如果有倾斜的情况，这些subheaps也许正经历intense pressure.

select child#, gets, misses, immediate_gets, immediate_misses from v$latch_children where name = 'shared pool';

SQL> select child#, gets, misses, immediate_gets, immediate_misses from v$latch_
children where name = 'shared pool';

CHILD# GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- ---------- -------------- ----------------
1 292433 336 0 0
2 13 0 0 0
3 13 0 0 0
4 13 0 0 0
5 13 0 0 0
6 13 0 0 0
7 13 0 0 0

已选择7行。

如果gets在children中是倾斜的，则理解在这些subheaps中分配的chunks的类型非常重要。至少有两种方法处理，
一则可以通过Statspack和AWR报告,它们有一个部分显示了各种shared pool area size的增加或减小。

二则可以查询x$ksmss，以下查询显示top20使用共享池最多的area

select ksmdsidx, ksmssnam, size_area from (
select ksmdsidx, ksmssnam, sum(ksmsslen) size_area from x$ksmss
where ksmssnam != 'free memory'
group by ksmdsidx, ksmssnam
order by 3 desc
)
where rownum < 21;

2.检查共享池碎片
select
ksmchidx,ksmchdur,
case
when ksmchsiz < 1672 then trunc((ksmchsiz-32)/8)
when ksmchsiz < 4120 then trunc((ksmchsiz+7928)/48)
when ksmchsiz < 8216 then 250
when ksmchsiz < 16408 then 251
when ksmchsiz < 32792 then 252
when ksmchsiz < 65560 then 253
when ksmchsiz >= 65560 then 253
end bucket,
sum(ksmchsiz) free_space,
count(*) free_chunks,
trunc(avg(ksmchsiz)) average_size,
max(ksmchsiz) biggest
from
sys.x$ksmsp
where
inst_id = userenv('Instance') and
ksmchcls = 'free'
group by
case
when ksmchsiz < 1672 then trunc((ksmchsiz-32)/8)
when ksmchsiz < 4120 then trunc((ksmchsiz+7928)/48)
when ksmchsiz < 8216 then 250
when ksmchsiz < 16408 then 251
when ksmchsiz < 32792 then 252
when ksmchsiz < 65560 then 253
when ksmchsiz >= 65560 then 253
end ,
ksmchidx, ksmchdur
order by ksmchidx , ksmchdur
/

3.检查引起flushing的对象
set lines 160 pages 100
spool ksmlru.lst
select * from x$ksmlru order by ksmlrnum;
spool off

4.标识使用literal values的SQL statements
v$sql在标识没有使用绑定变量的SQL语句非常有用。
select * from (
select plan_hash_value, count(distinct(hash_value)), sum(executions),
sum(parse_calls)
from v$sql
group by plan_hash_value
having count(distinct(hash_value)) > 10
order by 2 desc
) where rownum<21;

select sql_text from v$sql where plan_hash_value=703834417 and rownum<10;

在一些cases中， 一条SQL语句的连续执行也许不被共享，会创建许多子游标。

select * from (
select hash_value, module, action, executions, parse_calls,
version_count, sharable_mem,persistent_mem from v$sqlarea
where version_count>10
order by version_count desc
) where rownum<21
/

v$sql_shared_cursor列出了为什么子游标不能被共享的原因。

5.解决共享池latch争用
避免unnecessary heaps
避免和减少由于共享sql statements带来的碎片
避免_kghdsidx_count=1
避免flush shared pool
避免shared pool reserved free list碎片

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9523925/viewspace-1050845/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9523925/viewspace-1050845/