定位热链的方法
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$session
where wait_class <> 'Idle' group by event order by 2;
EVENT COUNT(*)
---------------------------------------------------------- ----------
SQL*Net message to client 1
latch: cache buffers chains 1
二.获取哪些活跃会话正在处于latch: cache buffers chains
select sid,username,event,p1raw,sql_id,logon_time,last_call_et
from v$session where event='latch: cache buffers chains' and status='ACTIVE'
SYS@ora10g> /
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是否出现很多是相同的.
SYS@ora10g> 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
SYS@ora10g> /
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值
SYS@ora10g> undefine latch_addr
SYS@ora10g> 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
SYS@ora10g> /
Enter value for latch_addr: 0000000082E3FCC0
HLADDR FILE# DBABLK TCH OBJ OBJECT_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
0000000082E3FCC0 1 55349 0 92 DEPENDENCY$
0000000082E3FCC0 2 1662 1 4294967295
0000000082E3FCC0 3 963 1 3709 WRI$_ADV_RATIONALE_PK
0000000082E3FCC0 3 4835 1 9246
0000000082E3FCC0 3 5068 1 51296 ORDER_ITEMS
0000000082E3FCC0 3 5068 1 51296 WRH$_PARAMETER
0000000082E3FCC0 4 17383 1 52516 O1
0000000082E3FCC0 4 39306 1 52516 O1
0000000082E3FCC0 4 60530 1 52516 O1
0000000082E3FCC0 4 60763 1 52516 O1
0000000082E3FCC0 4 60996 1 52516 O1
0000000082E3FCC0 4 61229 1 52516 O1
0000000082E3FCC0 4 64868 1 52516 O1
0000000082E3FCC0 4 65101 1 52516 O1
0000000082E3FCC0 4 65334 1 52516 O1
0000000082E3FCC0 4 65567 1 52516 O1
0000000082E3FCC0 4 69206 1 52516 O1
0000000082E3FCC0 4 69439 1 52516 O1
0000000082E3FCC0 4 69672 1 52516 O1
0000000082E3FCC0 4 69905 1 52516 O1
0000000082E3FCC0 4 70138 1 52516 O1
0000000082E3FCC0 3 23119 39 49873 SYS_IOT_OVER_49872
0000000082E3FCC0 4 31 411 51151 EMP
23 rows selected.
五.根据sql_id获取到相应SQL.结合SQL中的对象.结合上面的TCH值进一步判断热块.
SYS@ora10g> 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;
/
SYS@ora10g> select event, count(*) from v$session where wait_class <> 'Idle' group by event order by 2;
SYS@ora10g> /
EVENT COUNT(*)
---------------------------------------------------------- ----------
log file switch (checkpoint incomplete) 1
SQL*Net message to client 1
buffer busy waits 2
SYS@ora10g> 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'
SYS@ora10g> /
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
SYS@ora10g> /
Enter value for filenumber: 4
old 3: WHERE file_id = &FileNumber
new 3: WHERE file_id = 4
Enter value for blocknumber: 32
old 4: AND &BlockNumber BETWEEN block_id AND block_id + blocks -1
new 4: AND 32 BETWEEN block_id AND block_id + blocks -1
OWNER 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 0
2nd level bmb 0 0
3rd level bmb 0 0
bitmap block 0 0
bitmap index block 0 0
data block 0 0
extent map 0 0
file header block 60 240
free list 0 0
save undo block 0 0
save undo header 0 0
segment header 0 0
sort block 0 0
system undo block 0 0
system undo header 0 0
undo block 0 0
undo header 1 0
unused 0 0
18 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18841027/viewspace-1649607/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18841027/viewspace-1649607/