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??
how many blocks in the table? how many rows?
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...
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.
What book is this, by the way?
I feel a blog post coming...
Originally Posted by slimdave
An innocent enough enquiry!
Originally Posted by marist89
Still I feel that people ought to be warned, just as a public service ...
This makes sense. Thank you.
Click Here to Expand Forum to Full Width