Consistent Gets Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Consistent Gets Question

  1. #1
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Now, I recently was performing some tracing on a query and got the statistic back CONSISTENT_GETS. Yeah, I know what that it... I thought... I actually thought it was reads from a ROLLBACK SEGMENT. i.e. a read needed to keep the data consistent in a query. After I made mods to the query... I started to doubt my understanding of it. Then I decided to go chasing it's REAL MEANING.... which seems to be a pretty hard thing to do. The following are definitions I have found.

    * 'consistent changes' = changes made to block for CONSISTENT READ and changes made to SORT blocks

    'consistent changes' = db_block_changes counts modifications made to CURRENT blocks

    consistent gets - Number of times a consistent read was requested for a block.

    consistent changes -
    Number of times a user process has applied rollback entries to perform a consistent read on the block.

    None of which are really clear about what they are trying to say.

    So, I'm wondering if anyone can provide me with a hard definition once and for all, just so I can clear my understanding of where it is actually reading from.

    Thanks,
    OCP 8i, 9i DBA
    Brisbane Australia

  2. #2
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    consistent_gets is the number of times a consistent read was requested for a block
    db_block_gets tracks the number of blocks obtained in CURRENT mode
    sonofsita
    http://www.ordba.net

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    "consistent_gets is the number of times a consistent read was requested for a block"

    "db_block_gets tracks the number of blocks obtained in CURRENT mode"



    And a CONSISTENT READ would be....? From Where?

    You see, I thought DB BLOCK GETS were from the DB_Buffer_Cache and Consistent reads were from the Rollback Segments and Physical Reads are from the Disk. Which I suppose is strange since Rollback Segments reside on disk.

    Hmmm... more feedback required please.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    I have received the following from Mark J. Bobak an Oracle DBA from ProQuest Information and Learning

    "Hi Greg,

    If you look at V$SYSSTAT, you can gain some insight to the puzzle:
    select name from v$sysstat where name like '%consistent%get%';

    Will return (in my 9i database):
    consistent gets
    consistent gets - examination
    no work - consistent read gets
    cleanouts only - consistent read gets
    rollbacks only - consistent read gets
    cleanouts and rollbacks - consistent read gets

    This may help you sort out the confusion. I'm not sure I can
    explain all of the above, but, let's start w/ the easy one:

    'no work - consistent read gets'. This represents the number of times Oracle was doing a consistent read (select) and when it got the buffer, it was consistent w/ the start SCN of the select statement and had been cleaned out, so it didn't need to rollback any changes or cleanout.

    'cleanouts only - consistent read gets' would be the number of buffers that Oracle got that needed delayed block cleanout, but not rollback.

    'rollbacks only - consistent read gets' is number of buffers that Oracle got that needed rollback but not cleanout.

    'cleanouts and rollbacks - consistent read gets' is number of buffers that needed both cleanout and rollback.

    I'm not sure what the remaining ones are, and I just tried to get the numbers to add up, and I couldn't, so, there's probably more going on behind the scenes.

    But, looking at these numbers, you can get a feel for how much extra work Oracle is doing to support read consistency.

    -Mark "

    Thanks Mark.

    Has anyone else got any additional information.

    Thanks.


    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Aug 2001
    Posts
    111
    Have a look at this site

    It seems the sum is the actual consistent gets value.

    http://www.ixora.com.au/tips/tuning/cache_miss.htm

    At the end of the day reducing consistent gets is important as they can be CPU intensive.

    Have Fun
    Performance... Push the envelope!

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Stale copies of blocks which are still in the memory cache make the consistent reads. Cached blocks that are not stale, are called current. And current blocks can be clean or dirty. Dirty means that the block has changed and still has to be written to disk. Clean means that the block does not contain changes that need to be written to disk.

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Thank you Julian, your input is appreciated and clarifies this issue.

    Cheers,

    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width