Buffer Cache
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Buffer Cache

  1. #1
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174

    Buffer Cache

    I'm trying to understand value you find in v$sysstat for "physical reads", "db block gets", and "consistent gets".

    My book says this:

    Consistent Gets. Number of blocks accessed in the buffer cache for queries without the Select For Update clause.

    DB Block Gets. Number of blocks in the buffer cache that were accessed for INSERT, UPDATE, DELETE, and SELECT For UPDATE.

    Physical Reads. Number of blocks that were read from disks to satisfy a SELECT, SELECT For UPDATE, INSERT, UPDATE, or DELETE.

    I did a basic test on a database with a table that has 20+ million records. I restarted the database to flush out buffer cache.

    Then I simply did a select * from bigtable.

    I then queried the v$sysstat table for the value of "physical reads", "db block gets", and "consistent gets". What I saw, as the query was running and returning results, was that the consistent gets value kept rising much, much faster than physical reads.

    By the time the query was done running, Consistent Gets rose 64,627 and Physical Reads only 1,775.

    Keep in mind, my session was the only one connected to the database.

    Does this make sense to anyone??

    I would have expected Physical Reads to go through the roof, especially with the database just being started.

    Why didn't I see this??

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    how many blocks in the table? how many rows?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    dba_segments show that the table has 566272 blocks (8K blocks). Roughly about 21 million rows.

    The query earlier never really finished...I had to kill it...it would have taken at least an hour for it to return all rows...

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    When reading the table you are requesting row by row. The first row you request does a physical I/O and caches the block. Since the second row is most likely in the same block, you request a row but the block is cached (consistent get). You read all the rows in that block before you need to do another pio.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What book is this, by the way?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by slimdave
    What book is this, by the way?
    I feel a blog post coming...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    This makes sense. Thank you.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by marist89
    I feel a blog post coming...
    An innocent enough enquiry!

    Still I feel that people ought to be warned, just as a public service ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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