3 Replies Latest reply: Feb 8, 2013 10:47 PM by 891445 RSS

    How To Identify a Hot Block Within The Database Buffer Cache

    venkat vankayala
      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
          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....
            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
              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.