寻找热块

 

Steps to track down hot blocks when cache buffers chains latch sleeps are high:

1. Create a table to hold sleep statistics for cache buffers chains latch children:

SQL> CREATE TABLE sleep97_a as select addr,
  2  child#, sleeps
  3  FROM v$latch_children
  4  WHERE latch#= 97;

2. Wait a little (20 seconds) and create a second table like the first:

SQL> CREATE TABLE sleep97_b as select addr,
  2  child#, sleeps
  3  FROM v$latch_children
  4  WHERE latch#= 97;

 

3. Join the two tables, calculating the difference to get recent sleeps, order by sleeps. The last rows of the query result should show obvious skewing and will be the hot blocks.

SQL> SELECT a.addr, a.child#,(b.sleeps - a.sleeps)

  2  SLEEPS

  3  FROM sleep97_a a, sleep97_b b

  4  WHERE a.addr= b.addr

  5  ORDER BY sleeps;

ADDR         CHILD#     SLEEPS

-------- ---------- ----------

0254FC8C       1013          0

0254FDC0       1014          0

0254FEF4       1015          0

02550028       1016          2

0255015C       1017          2

02550290       1018          2

025503C4       1019          2

025504F8       1020          2

0255062C       1021          2

02550760       1022          5

02550894       1023          5

 

4. Track down the actual hot block. Use the following query to get the results in the report above:

SQL> SELECT  hladdr, tch, ts#, dbarfil, dbablk,
  2  class, DECODE (state, 2, 'shared current',

  3  3, 'cr version', 1, 'exclusive current', 0)

  4  FROM  sys.x$bh

  5  WHERE  hladdr IN ('0254FC8C','0254FDC0',

  6  '0254FEF4','02550028','0255015C','02550290',

  7  '025503C4','025504F8','0255062C','02550760',

  8  '02550894') AND  tch > 100

  9  ORDER BY tch, hladdr, dbablk;

Because of how the touchcount algorithm works in the buffer cache, this query should be repeated several times over a 10-second period, to get a true indication of the “pattern” of hot blocks in the cache.

 

 

5. Querying dba_extents by using the file_id and the database block number that was identified from the previous query against x$bh will find the object where the touchcount value is the highest. You should investigate all objects that appear with a high touchcount (therefore appear as “hot” blocks), and determine what action to take depending on the object that is found. Object details can be further isolated by using other views depending on object type. For example, querying dba_segments will show the number of free lists for a given object. Increasing the number of free lists on an object can reduce contention on particular data blocks.

 

 

SQL> SELECT  segment_name, segment_type, owner,
  2  tablespace_name
  3  FROM  sys.dba_extents
  4  WHERE  file_id = 7

  5  AND  353482 between block_id and
  6  (block_id + (blocks-1));

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9375/viewspace-607680/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9375/viewspace-607680/