oracle 定位热块和热链的方法

定位热链的方法

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

SYS@ora10g>

二.获取哪些活跃会话正在处于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

SYS@ora10g>

三.确认等待的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

SYS@ora10g>

四.找出这个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.

SYS@ora10g>

五.根据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'

SYS@ora10g>

六.复杂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>

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

SYS@ora10g>

    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@ora10g>

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.