定位熱鏈的方法

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'
> /

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: 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值進一步判斷熱塊.
> 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)        1
SQL*Net message to client         1
buffer 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: 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.

定位熱鏈和熱塊的方法

相關文章