Latch:cache buffers Chains Fault Treatment summary (reprint)

Early in the morning to receive the developer's phone, said the database CPU utilization rate is 100%, the application of the corresponding delay. Hurried to the scene found that the basic inspection after the discovery is Latch:cache buffers chains, the process is still smooth, at that time forgot to record log, here summarizes the processing ideas, so that the next review.

Fault Analysis Ideas

View wait events to determine the cause of the failure

Sql>select * FROM (select sid,event,p1,p2,p3,p1text,wait_time,seconds_in_wait from v$session_wait where wait_class# <> 6
ORDER BY wait_time Desc) where RowNum <=10;

Check the latch hit rate after confirming the failure caused by Latch:cache buffers chains

Sql>select name, gets, misses, sleeps,
Immediate_gets, immediate_misses
From V$latch
WHERE name = ' Cache buffers chains ';

The column names have the following meanings

Name:latch Name
Immediate_gets: Number of requests latch in IMMEDIATE mode
Immediate_misses: Number of request failures
GETS: Number of requests latch in willing to wait request mode
MISSES: Number of unsuccessful first attempt requests
Spin_gets: The first attempt failed, but succeeded in the next round
SLEEP[X]: Sleeping number of times before successful acquisition
Wait_time: The time spent waiting for latch

It is important to note that if the misses/gets is around 10%, then there is a serious latch contention, you can also view the other latch information by querying the V$latch_children view, the statement is as follows

Sql> SELECT *
From (SELECT addr, child#, gets, misses, sleeps, immediate_gets igets,
Immediate_misses Imiss, Spin_gets sgets
From V$latch_children
WHERE NAME = ' Cache buffers chains '
ORDER by Sleeps DESC)
WHERE ROWNUM < 11;

Statistics on latch, mainly focus on the following parts

What is the ratio of misses/gets?

What is the percentage of misses obtained from spinning?

How many times did latch request

How many times did latch sleep?

View Hotspot objects and access information, tch column indicates the number of times an object has been accessed

Sql> SELECT *
From (SELECT addr,
ts#,
file#,
Dbarfil,
DBABLK,
Tch
From X$BH
ORDER by Tch DESC)
WHERE ROWNUM < 11;

View specific object information by object's file number and block number

Sql>select owner, Segment_name, Partition_name, Tablespace_name
From Dba_extents
where relative_fno = &v_dba_rfile
and &v_dba_block between block_id and block_id + blocks-1;

You can also find hotspot blocks by using the following SQL, mainly

SELECT *

From (SELECT O.owner, O.object_name, O.object_type, SUM (TCH) touchtime

From X$bh B, dba_objects O

WHERE b.obj = o.data_object_id

and b.ts# > 0

GROUP by O.owner, O.object_name, O.object_type

ORDER by SUM (TCH) DESC)

WHERE ROWNUM <= 10;

View the SQL that caused Latch:cache buffers chains

Sql> SELECT * FROM (select 
    Count (*),  
    sql_id, 
    NVL (o.object_name,ash.current_obj#) objn,
    substr (o.object_type,0,10) Otype,
     3    4    5    6         current_file# FN,
         current_block# Blockn
   from  v$active_session_history ash
      , All_ Objects o
   where event like ' Latch:cache buffers chains '
     and o.object_id (+) = Ash. current_obj#
   GROUP by sql_id, current_obj#, current_file#,
                   current_block#, O.object_name,o.object_type
   Order by  count (*) desc) where rownum <=10;

View SQL full text based on the SQL_ID information obtained above

Sql>select Sql_fulltext from V$sqlarea where sql_id= ' &sqlid ';

View the execution plan for SQL
Sql>select * FROM table (Dbms_xplan. Display_cursor ((' &sql_id ', 0));

You can also view SQL's actual execution plan by SQL_ID View SQL address and hash_value in situations where the SQL execution plan is not considered accurate

Sql>select address, hash_value from V$sql
WHERE sql_id= ' &sql_id ';
Sql>select operation, Options, object_name, cost from V$sql_plan
WHERE address = ' &addr ' and hash_value = ' hash_v ';

When a session holds latch for a long time, the SQL information can be viewed through federated v$latchholder and v$session views

Sql>select s.sql_hash_value,s.sql_id,s.address, L.name
From V$session S, V$latchholder l
WHERE s.sid = L.sid;

The idea of fault handling

1, according to the SQL execution plan to determine whether the execution plan is correct, SQL execution too long often means that long time to hold latch.

2, optimize nested loop join, if possible to use a hash join instead of nested loop join. You can also use hash partitioning for hot block indexes, or use hash clusters to slow down hot block phenomena.

3. Adjust the Pctfree value of the table to distribute the data as far as possible into multiple blocks

4. Adjustment application

For hot blocks, you can refer to the author's following articles

http://czmmiao.iteye.com/blog/1474472

Ext.: http://czmmiao.iteye.com/blog/1766442

Latch:cache buffers Chains Fault Treatment summary (reprint)