4 Replies Latest reply on May 2, 2012 2:24 PM by 808758

    Segment header block contention on RAC 2 nodes

    808758
      Hello,

      In our Production Environment, we are experiencing issue that executing "SELECT" statement on one instance of RAC perform different on the other FAC. When we run the SELECT on 1st node, it produces lot of continuously reading on segment header block for hours and it is consistent for each execution. However for the same statement executed on 2nd node, it reading off data from data block randomly. Why it is behaving this way and why on segment header block since we are not doing "INSERT" or "UPDATE"

      WAIT #103: nam='gc cr disk read' ela= 450 p1=301 p2=57577 p3=4 obj#=4887 tim=7976293138226
      WAIT #103: nam='db file sequential read' ela= 4624 file#=301 block#=57577 blocks=1 obj#=4887 tim=7976293142871
      WAIT #103: nam='gc cr disk read' ela= 518 p1=301 p2=57577 p3=4 obj#=4887 tim=7976293143489
      WAIT #103: nam='db file sequential read' ela= 4428 file#=301 block#=57577 blocks=1 obj#=4887 tim=7976293147936
      WAIT #103: nam='gc cr disk read' ela= 527 p1=301 p2=57577 p3=4 obj#=4887 tim=7976293148576
      WAIT #103: nam='db file sequential read' ela= 3786 file#=301 block#=57577 blocks=1 obj#=4887 tim=7976293152384
      WAIT #103: nam='gc cr disk read' ela= 1244 p1=301 p2=57577 p3=4 obj#=4887 tim=7976293153739
      WAIT #103: nam='db file sequential read' ela= 5173 file#=301 block#=57577 blocks=1 obj#=4887 tim=7976293158940

      And for same query but different table, it produce similar reading on segment header block but no "gc cr disk read"
      WAIT #97: nam='db file sequential read' ela= 993 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679880985
      WAIT #97: nam='db file sequential read' ela= 2029 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679883079
      WAIT #97: nam='db file sequential read' ela= 552 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679883794
      WAIT #97: nam='db file sequential read' ela= 2002 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679885897
      WAIT #97: nam='db file sequential read' ela= 10362 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679896357
      WAIT #97: nam='db file sequential read' ela= 1269 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679897786
      WAIT #97: nam='db file sequential read' ela= 2106 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679900007
      WAIT #97: nam='db file sequential read' ela= 5024 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679905105
      WAIT #97: nam='db file sequential read' ela= 2635 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679908002
      WAIT #97: nam='db file sequential read' ela= 2903 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679911018
      WAIT #97: nam='db file sequential read' ela= 1286 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679912455
      WAIT #97: nam='db file sequential read' ela= 3154 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679915770


      Please help,
      Thank you.

      Edited by: 805755 on May 1, 2012 7:50 AM
        • 1. Re: Segment header block contention on RAC 2 nodes
          user503699
          805755 wrote:
          Hello,

          In our Production Environment, we are experiencing issue that executing "SELECT" statement on one instance of RAC perform different on the other FAC. When we run the SELECT on 1st node, it produces lot of continuously reading on segment header block for hours and it is consistent for each execution. However for the same statement executed on 2nd node, it reading off data from data block randomly. Why it is behaving this way and why on segment header block since we are not doing "INSERT" or "UPDATE"

          WAIT #103: nam='gc cr disk read' ela= 450 p1=301 p2=57577 p3=4 obj#=4887 tim=7976293138226
          WAIT #103: nam='db file sequential read' ela= 4624 file#=301 block#=57577 blocks=1 obj#=4887 tim=7976293142871
          WAIT #103: nam='gc cr disk read' ela= 518 p1=301 p2=57577 p3=4 obj#=4887 tim=7976293143489
          WAIT #103: nam='db file sequential read' ela= 4428 file#=301 block#=57577 blocks=1 obj#=4887 tim=7976293147936
          WAIT #103: nam='gc cr disk read' ela= 527 p1=301 p2=57577 p3=4 obj#=4887 tim=7976293148576
          WAIT #103: nam='db file sequential read' ela= 3786 file#=301 block#=57577 blocks=1 obj#=4887 tim=7976293152384
          WAIT #103: nam='gc cr disk read' ela= 1244 p1=301 p2=57577 p3=4 obj#=4887 tim=7976293153739
          WAIT #103: nam='db file sequential read' ela= 5173 file#=301 block#=57577 blocks=1 obj#=4887 tim=7976293158940

          And for same query but different table, it produce similar reading on segment header block but no "gc cr disk read"
          WAIT #97: nam='db file sequential read' ela= 993 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679880985
          WAIT #97: nam='db file sequential read' ela= 2029 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679883079
          WAIT #97: nam='db file sequential read' ela= 552 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679883794
          WAIT #97: nam='db file sequential read' ela= 2002 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679885897
          WAIT #97: nam='db file sequential read' ela= 10362 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679896357
          WAIT #97: nam='db file sequential read' ela= 1269 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679897786
          WAIT #97: nam='db file sequential read' ela= 2106 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679900007
          WAIT #97: nam='db file sequential read' ela= 5024 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679905105
          WAIT #97: nam='db file sequential read' ela= 2635 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679908002
          WAIT #97: nam='db file sequential read' ela= 2903 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679911018
          WAIT #97: nam='db file sequential read' ela= 1286 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679912455
          WAIT #97: nam='db file sequential read' ela= 3154 file#=301 block#=31457 blocks=1 obj#=4886 tim=7970679915770


          Please help,
          Not sure what do you mean by "same query but different table". How is that possible? If the table used changes, the query is different.
          Based on what you have posted, it appears that when you execute query against table (with object ID 4887), oracle is getting some of
          the data blocks from "Global Cache" i.e. required data blocks are available in buffer cache of another instance in the cluster.
          Whereas when you execute query against table (with object ID 4886), the data is read from the disk as it is probably not available in "Global Cache"
          • 2. Re: Segment header block contention on RAC 2 nodes
            CloudDB
            Hi,

            Please refer the Oracle Wait Event document
            http://orainternals.wordpress.com/tag/gc-cr-disk-read/
            http://www.dba-oracle.com/m_db_file_sequential_read.htm

            Try to restart the oracle rac nodes and check the query result back and share the same

            Regards
            Hitgon

            Edited by: hitgon on May 2, 2012 6:56 PM
            • 3. Re: Segment header block contention on RAC 2 nodes
              kuljeet singh -
              WAIT #103: nam='gc cr disk read' ela= 450 p1=301 p2=57577 p3=4 obj#=4887 tim=7976293138226
              WAIT #103: nam='db file sequential read' ela= 4624 file#=301 block#=57577 blocks=1 obj#=4887 tim=7976293142871

              'gc cr disk read'
              its cluster related wait event.
              Re: What means "gc cr disk read" event?
              • 4. Re: Segment header block contention on RAC 2 nodes
                808758
                But why on the segment header block not the data block when I am doing the SELECT statement?