X$系列之四：x$bh关于db cache

1：线索X$BH.HLADDR=v$latch_children.addr <= v$session.p1

2：从v$session入手，可以找到sql_id，也可以根据p1找出latch addr
SQL> select sid,status,username,sql_id,program from v$session where event='latch: cache buffers chains';

       SID STATUS   USERNAME                       SQL_ID        PROGRAM
---------- -------- ------------------------------ ------------- --------------                   
      6270 ACTIVE   ANDY                            an0d78kfj3fy8 ANDY.exe
      6523 ACTIVE   ANDY                            an0d78kfj3fy8 ANDY.exe
     
select * from (  
select  
event,  
trim(to_char(p1, 'XXXXXXXXXXXXXXXX')) latch_addr,  
trim(round(ratio_to_report(count(*)) over () * 100, 1))||'%' pct,  
count(*)  
from  
v$active_session_history  
where 
event = 'latch: cache buffers chains' 
and session_state = 'WAITING' 
group by event,p1 
order by count(*) desc 
) 
where rownum <= 5;

EVENT                                       LATCH_ADDR        PCT               COUNT(*)
------------------------------------------------------------- --------------------------
latch: cache buffers chains                 F0ED14168         24.6%                  428
latch: cache buffers chains                 130FF11678        4.9%                    85
latch: cache buffers chains                 110F052610        2.3%                    40
latch: cache buffers chains                 B0D9F8A68         2.1%                    37
latch: cache buffers chains                 D0FCD7F18         1.7%                    30

select trim(to_char(p1, 'xxxxxxxxxx')) latch_addr,count(1) from v$session
where event='latch: cache buffers chains' and sql_id='an0d78kfj3fy8' group by p1;

3：直接找出争用最严重的latch addr，然后从x$bh里找出对象
SQL>  SELECT *
  2      FROM (  SELECT CHILD#,
  3                     ADDR,
  4                     GETS,
  5                     MISSES,
  6                     SLEEPS
  7                FROM v$latch_children
  8               WHERE name = 'cache buffers chains' AND misses > 0 AND sleeps > 0
  9            ORDER BY 5 DESC,
 10                     1,
 11                     2,
 12                     3)
 13  WHERE ROWNUM < 6;

    CHILD# ADDR                   GETS     MISSES     SLEEPS
---------- ---------------- ---------- ---------- ----------
    223121 0000001D1ADA95B8 1525413826    5662406     108517
    775902 0000001D07F8A408 3987641758   19817584      94746
    199886 0000001D1451A410 1771766682    3776173      40239
    808245 0000001D00E9BE58   24247852     637902      22706
    362653 0000001D1DE42A28 1524551132    4489287      20770

SQL>

SQL> SELECT OBJ DATA_OBJECT_ID, FILE#, DBABLK, CLASS, STATE, TCH
  2  FROM X$BH
  3  WHERE HLADDR='0000001D1ADA95B8'
  4  ORDER BY TCH DESC;

DATA_OBJECT_ID      FILE#     DBABLK      CLASS      STATE        TCH
-------------- ---------- ---------- ---------- ---------- ----------
         59073        193     280084          1          2        686
        127826        469     477609          1          2         52
        385520        595     478989          1          2         40
        303596        447     350621          1          2         38
         51109        604     347739          1          2         31
        127820        336      90003          1          2         20
        285896        513     219845          1          2         17
       
SQL> SELECT OBJECT_NAME, SUBOBJECT_NAME
  2  FROM DBA_OBJECTS
  3  WHERE DATA_OBJECT_ID='59073';

OBJECT_NAME                                        SUBOBJECT_NAME
-------------------------------------------------- ------------------------------
HT_MODIFIED_QUOTATION_U1

SQL>

--下面这个查询比较慢
SQL> SELECT /*+ RULE */
  2          e.owner || '.' || e.segment_name segment_name,
  3          e.extent_id extent#,
  4          x.dbablk - e.block_id + 1 block#,
  5          x.tch,                       /* sometimes tch=0,we need to see tim */
  6          x.tim,
  7          l.child#
  8  FROM v$latch_children l, x$bh x, dba_extents e
  9  WHERE x.hladdr = '0000001D1ADA95B8'
 10  AND e.file_id = x.file#
 11  AND x.hladdr = l.addr
 12  AND x.dbablk BETWEEN e.block_id AND e.block_id + e.blocks - 1
 13  ORDER BY x.tch DESC; 

SEGMENT_NAME                               EXTENT#     BLOCK#        TCH        TIM     CHILD#
------------------------------------------------- ---------- ---------- ---------- ----------
ANDY.PARAMETER_INFO_IDX1                         0         25         88 1336462327     223121
ANDY.ANDY_REQ_GROUPTASK_INFO                     1        481         42 1336462294     223121
ANDY.ANDY_REMIND_MSG_INDEX                       3        317          7 1336460615     223121
ANDY.ANDY_SERIAL_TASK_INFO                     214         11          7 1336461225     223121
ANDY.ANDY_BT_TASK_UNTRACE                      322        352          1 1336462149     223121
ANDY.ANDY_AUTO_CREATION_TI                     509        188          1 1336462065     223121

4：最后分析sql_id的执行计划和热点对象的关系找出解决办法。
select sql_text from v$sqltext where sql_id='an0d78kfj3fy8' order by piece;
select * from table(dbms_xplan.display_cursor('an0d78kfj3fy8',null,'ALLSTATS +PEEKED_BINDS'));
select * from table(dbms_xplan.display_awr('an0d78kfj3fy8',null,null,'basic'));