This discussion is archived
3 Replies Latest reply: Feb 8, 2013 8:47 PM by 891445 RSS

How To Identify a Hot Block Within The Database Buffer Cache

venkat vankayala Newbie
Currently Being Moderated
Hi ,

I am able to find a metalink doc for "How To Identify a Hot Block Within The Database Buffer Cache." with id " [ID 163424.1]" . Can some please share me the document for 11g database if you have found any .

Thanks
Venkat
  • 1. Re: How To Identify a Hot Block Within The Database Buffer Cache
    Mark D Powell Guru
    Currently Being Moderated
    What specific information in the referenced Oracle document is not valid also for 11g?

    HTH -- Mark D Powell --
  • 2. Re: How To Identify a Hot Block Within The Database Buffer Cache
    Aman.... Oracle ACE
    Currently Being Moderated
    In general, the hot block is that one which is having the high touch count. So what in the mentioned doc is not working or is incorrect?

    I just tried the same queries in the note and it's working for me,
    SQL> column segment_name format a35
    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,
      6         l.child#
      7       from
      8         sys.v$latch_children  l,
      9         sys.x$bh  x,
     10         sys.dba_extents  e
     11       where
     12         x.hladdr  = '&ADDR' and
     13         e.file_id = x.file# and
     14         x.hladdr = l.addr and
     15         x.dbablk between e.block_id and e.block_id + e.blocks -1
     16       order by x.tch desc ;
    Enter value for addr:  6C375CBC
    old  12:        x.hladdr  = '&ADDR' and
    new  12:        x.hladdr  = ' 6C375CBC' and
    
    no rows selected
    
    SQL> /
    Enter value for addr: 6C3756BC
    old  12:        x.hladdr  = '&ADDR' and
    new  12:        x.hladdr  = '6C3756BC' and
    
    SEGMENT_NAME                           EXTENT#     BLOCK#        TCH     CHILD#
    ----------------------------------- ---------- ---------- ---------- ----------
    SYS.C_FILE#_BLOCK#                           7          5          4        238
    SYS.C_FILE#_BLOCK#                          16         18          2        238
    SYS.FIXED_OBJ$                               0          2          0        238
    And the db version is 11201 on Windows XP .

    Aman....
  • 3. Re: How To Identify a Hot Block Within The Database Buffer Cache
    891445 Newbie
    Currently Being Moderated
    sorry to open 2 year old thread.

    I was just wondering if we have similar query for RAC database.
    i am not able to find an alternative x$bh view for RAC instances.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points