ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SHARED POOL优化小记

SHARED POOL优化小记

原创 Linux操作系统 作者:ltj09 时间:2012-07-12 10:08:46 0 删除 编辑

(1) 监控V$SHARED_POOL_ADVICE视图的ESTD_LC_TIME_SAVED_FACTOR列(注意该列的值是当前负载情况下的反映)

(2) 通过视图观察不同SHARED_POOL尺寸时的响应时间(秒):

SELECT 'Shared Pool' component,
shared_pool_size_for_estimate estd_sp_size,
estd_lc_time_saved_factor parse_time_factor,
CASE
WHEN current_parse_time_elapsed_s + adjustment_s < 0 THEN
0
ELSE current_parse_time_elapsed_s + adjustment_s
END response_time
FROM (SELECT shared_pool_size_for_estimate, shared_pool_size_factor,
 estd_lc_time_saved_factor,a.estd_lc_time_saved,e.VALUE / 100 current_parse_time_elapsed_s,
 c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s
 FROM v$shared_pool_advice a,(SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e,
 (SELECT estd_lc_time_saved FROM v$shared_pool_advice WHERE shared_pool_size_factor = 1) c)
/

(3) 最重要的一点:硬解析比率:SELECT * FROM V$SYSSTAT  WHERE NAME IN ('parse count (total)','parse count (hard)'); 比值越小越好

(4) 判断library cache情况: SELECT SUM(PINS) "EXECUTIONS",SUM(RELOADS) "CACHE MISSSES",SUM(RELOADS)/SUM(PINS) "RELOAD-TO-PINS" FROM V$LIBRARYCACHE;  比值应小于0.01,否则需加大shared pool

(5) 判断dictionary cache情况:SELECT SUM(GETS) AS GETS,SUM(GETMISSES) AS MISSES,SUM(GETMISSES)/SUM(GETS) AS MISS_RATIO FROM V$ROWCACHE; 比值应小于0.02,否则,可能需要增加shared pool

(6) shared pool等待事件

library cache latch

一、 V$LATCH 中按 SLEEPS 排名前5位里是否有它,SELECT LATCH#,NAME,SLEEPS FROM V$LATCH WHERE SLEEPS>=1 ORDER BY SLEEPS DESC;   如果事件 library cache latch 比较靠前,

二、V$LATCH_CHILDREN 中每个library cache latch 的等待是否比较平均,如果相差较大,说明可能有没被有效利用的 latch :     SELECT LATCH#,CHILD#,GETS,ROUND(RATIO_TO_REPORT(GETS) OVER (), 2) AS GETS_RATIO,MISSES,ROUND(RATIO_TO_REPORT(MISSES) OVER (), 2) AS MISSES_RADIO,MISSES/GETS FROM V$LATCH_CHILDREN WHERE NAME='library cache'; 查找其中MISSES/GETS高于0.01的行,取得CHILD#,该子latch可能管理了过多的 library cache object :

三、V$DB_OBJECT_CACHE ,查看哪些sql对象处于该latch里: SELECT NAME,EXECUTIONS,CHILD_LATCH FROM V$DB_OBJECT_CACHE WHERE CHILD_LATCH=5;  然后可以看看这些sql是否可以改写一下,让其关联到其他latch里去,比如给列加个别名,或在where子句里添加个条件,如 "1=1" ,从而达到分散子 library cache latch 来达到降低 latch 等待的目的。

如果所有的子 library cache latch  都均匀分布,则需要检查sql语句是否使用了绑定变量、是否大小写一致、空格是否相同等。若还没问题,则检查 shared pool 是否过大,如果设置也合理,就增加隐藏参数 _kgl_latch_count 的值, 以增加 library cache latch 的数量。

library cache lock 和 library cache pin:

通过下面语句获得是哪个session通过哪条sql获得了 library cache pin , 又是哪个session通过哪条sql去申请 library cache pin:

select Distinct /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
od.to_owner object_owner,
od.to_name object_name,
oc.Type,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested,
xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql
from dba_kgllock w, dba_kgllock h, v$session w1,
v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xh
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
And od.to_address = w.kgllkhdl
And od.to_name=oc.Name
And od.to_owner=oc.owner
And w1.sid=xw.KGLLKSNM
And h1.sid=xh.KGLLKSNM
And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)
And (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH)
/

另一个查询,显示哪个session正持有lock或pin,哪个session又在等待lock或pin。

select /*+ ordered */ w1.sid waiting_session, h1.sid holding_session, w.kgllktype lock_or_pin,
w.kgllkhdl address, decode(h.kgllkmod,0,'None',1,'Null',2,'Share',3,'Exclusive','Unknown') mode_held,
decode(w.kgllkreq,0,'None',1,'Null',2,'Share',3,'Exclusive','Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where (((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and (((w.kgllkmod = 0) or (w.kgllkmod = 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/

通常这个两个等待都是由DDL引起,因此可通过查询 DBA_DDL_LOCK 视图来查看当前哪些session正对表操作:

SELECT SESSION_ID,OWNER,TYPE,MODE_HELD,MODE_REQUESTED FROM DBA_DDL_LOCKS WHERE WNER='SYS' AND NAME='TAB_TEST';

注:对于锁来说,是会话持有和请求。

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

上一篇: trace文件的查看
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-03-09

  • 博文量
    9
  • 访问量
    56559