Re: Regarding the number of consistent gets.

consistent gets is the blocks in consistent mode (sometimes reconstructed using information from RBS) So this reconstruction from RBS takes more resources (reads actually), which will end up as high consistent gets.

db block gets is the blocks in current mode (whatever it is NOW).

scott_at_9i > set autotrace traceonly statistics scott_at_9i > select * from emp;

14 rows selected.

Statistics

          0  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
       1495  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

--

• Now opened another session.
• did some DML on the table. Left the other session as-is.
• Comeback to original session.
• execute again.
• you can see consistent gets is high.
• This is becuase, this consistent gets is derived using the undo information.
• THe changes done in the other session WILL NOT be reflected here, in this session.
• So as to maintain this consistency, if needed, oracle will use undoinformation
• to consider only the before image (that was before the update in another session
• which has not yet been committed).
  --
  

scott_at_9i > /

14 rows selected.

Statistics

          0  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
         52  redo size
       1495  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

--

• Now issued a commit in other session.
• execute the sql here.
• you see, consistent gets are back to normal.
  --
  --
  scott_at_9i > /

14 rows selected.

Statistics

          0  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
       1462  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed