定位热链的方法
declare
v_num number;begin for i in 1..1000000 loop select sal into v_num from emp where ename='SMITH'; end loop;end;/declare
v_num number;begin for i in 1..1000000 loop select sal into v_num from emp where ename='KING'; end loop;end;/declare
v_num number;begin for i in 1..1000000 loop select sal into v_num from emp where ename='SCOTT'; end loop;end;/一.获取当前系统等待事件
select event, count(*) from v$sessionwhere wait_class <> 'Idle' group by event order by 2;EVENT COUNT(*)
---------------------------------------------------------- ----------SQL*Net message to client 1latch: cache buffers chains 1>
二.获取哪些活跃会话正在处于latch: cache buffers chainsselect sid,username,event,p1raw,sql_id,logon_time,last_call_et from v$session where event='latch: cache buffers chains' and status='ACTIVE'> /
SID USERNAME EVENT P1RAW SQL_ID LOGON_TIM LAST_CALL_ET
----------- ---------------- ------------------------------ ------------------------------ ------------- --------- ------------ 138 SCOTT latch: cache buffers chains 0000000082FED878 766dr19szth9c 07-OCT-14 18 140 SCOTT latch: cache buffers chains 0000000082E3FCC0 5mmqfh10738vp 07-OCT-14 15>
三.确认等待的P1RAW是否出现很多是相同的.
> l 1 select sid,p1raw,p2,p3,seconds_in_wait,wait_time,state from v$session_wait 2* where event='latch: cache buffers chains' order by 3,2> /SID P1RAW P2 P3 SECONDS_IN_WAIT WAIT_TIME STATE
----------- ---------------- ---------- ---------- --------------- ---------- ------------------- 140 0000000082E3FCC0 122 1 15 10 WAITED KNOWN TIME 138 0000000082E3FCC0 122 0 3 -1 WAITED SHORT TIME>
四.找出这个latch管理的对象.
关注TCH值> undefine latch_addr> l select a.hladdr,a.file#,a.dbablk,a.tch,a.obj,b.object_name from x$bh a, dba_objects b where (a.obj = b.object_id or a.obj = b.data_object_id) and a.hladdr = '&&latch_addr' union select hladdr,file#,dbablk,tch,obj,null from x$bh where obj in (select obj from x$bh where hladdr = '&latch_addr' minus select object_id from dba_objects minus select data_object_id from dba_objects) and hladdr = '&latch_addr' order by 4> /Enter value for latch_addr: 0000000082E3FCC0HLADDR FILE# DBABLK TCH OBJ OBJECT_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------0000000082E3FCC0 1 55349 0 92 DEPENDENCY$0000000082E3FCC0 2 1662 1 42949672950000000082E3FCC0 3 963 1 3709 WRI$_ADV_RATIONALE_PK0000000082E3FCC0 3 4835 1 92460000000082E3FCC0 3 5068 1 51296 ORDER_ITEMS0000000082E3FCC0 3 5068 1 51296 WRH$_PARAMETER0000000082E3FCC0 4 17383 1 52516 O10000000082E3FCC0 4 39306 1 52516 O10000000082E3FCC0 4 60530 1 52516 O10000000082E3FCC0 4 60763 1 52516 O10000000082E3FCC0 4 60996 1 52516 O10000000082E3FCC0 4 61229 1 52516 O10000000082E3FCC0 4 64868 1 52516 O10000000082E3FCC0 4 65101 1 52516 O10000000082E3FCC0 4 65334 1 52516 O10000000082E3FCC0 4 65567 1 52516 O10000000082E3FCC0 4 69206 1 52516 O10000000082E3FCC0 4 69439 1 52516 O10000000082E3FCC0 4 69672 1 52516 O10000000082E3FCC0 4 69905 1 52516 O10000000082E3FCC0 4 70138 1 52516 O10000000082E3FCC0 3 23119 39 49873 SYS_IOT_OVER_498720000000082E3FCC0 4 31 411 51151 EMP23 rows selected.
>
五.根据sql_id获取到相应SQL.结合SQL中的对象.结合上面的TCH值进一步判断热块.
> select executions,sql_text from v$sqlarea where sql_id in ('766dr19szth9c','5mmqfh10738vp');EXECUTIONS SQL_TEXT
---------- ------------------------------------------------------------ 7000000 SELECT SAL FROM EMP WHERE ENAME='KING' 45374216 SELECT SAL FROM EMP WHERE ENAME='SCOTT'>
六.复杂SQL,太多表关联,要依据执行计划来判断.
定位热块的方法
latch: buffer busy waits 等待事件
原因: 热块 修改导致declare
v_num number;begin for i in 1..1000000 loop select sal into v_num from emp where ename='SMITH'; update emp set sal=sal+0 where ename='SMITH'; commit; end loop;end;/declare
v_num number;begin for i in 1..1000000 loop select sal into v_num from emp where ename='KING'; update emp set sal=sal+0 where ename='KING'; commit; end loop;end;/declare
v_num number;begin for i in 1..1000000 loop select sal into v_num from emp where ename='SCOTT'; update emp set sal=sal+0 where ename='SCOTT'; commit; end loop;end;/ > select event, count(*) from v$session where wait_class <> 'Idle' group by event order by 2;> /EVENT COUNT(*)
---------------------------------------------------------- ----------log file switch (checkpoint incomplete) 1SQL*Net message to client 1buffer busy waits 2>
> select sid,username,event,p1,p2,p3,sql_id,logon_time,last_call_et from v$session where event='buffer busy waits' and status='ACTIVE'> /
SID USERNAME EVENT P1 P2 P3 SQL_ID LOGON_TIM LAST_CALL_ET
----------- ---------------- ------------------------------ ---------- ---------- ---------- ------------- --------- ------------ 138 SCOTT buffer busy waits 4 32 1 dh3kc1jqtnxw5 07-OCT-14 249 140 SCOTT buffer busy waits 4 32 1 71ssxfx45kyrh 07-OCT-14 246 158 SCOTT buffer busy waits 4 32 1 5vcss49awm6fn 07-OCT-14 252>
SELECT owner , segment_name , segment_type
FROM dba_extents WHERE file_id = &FileNumber AND &BlockNumber BETWEEN block_id AND block_id + blocks -1> /Enter value for filenumber: 4old 3: WHERE file_id = &FileNumbernew 3: WHERE file_id = 4Enter value for blocknumber: 32old 4: AND &BlockNumber BETWEEN block_id AND block_id + blocks -1new 4: AND 32 BETWEEN block_id AND block_id + blocks -1OWNER SEGMENT_NAME SEGMENT_TYPE
------------ ------------------------- ------------------SCOTT EMP TABLE>
SYS_S:191_P:5773_ora11g> select * from v$waitstat order by 1;
CLASS COUNT TIME
------------------ ---------- ----------1st level bmb 0 02nd level bmb 0 03rd level bmb 0 0bitmap block 0 0bitmap index block 0 0data block 0 0extent map 0 0file header block 60 240free list 0 0save undo block 0 0save undo header 0 0segment header 0 0sort block 0 0system undo block 0 0system undo header 0 0undo block 0 0undo header 1 0unused 0 018 rows selected.