Consistent Gets Question

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,

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

"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,

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.

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

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.

Thank you Julian, your input is appreciated and clarifies this issue.

Cheers,