Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
Showing results for 
Search instead for 
Did you mean: 

set autotrace on question

set autotrace on question

Hi all,
I am trying to find more about the execution plan of a SQL. My question is what does consistent gets mean. Any resource I can go for help?

I am getting the following

87264565 consistent gets
34534 phy read

Sushil Singh
Jean-Luc Oudart
Honored Contributor

Re: set autotrace on question

As far as I know consistent gets are is the number of blocks read from the buffer cache for your query.

check Metalink note 293440.995 some related question.

fiat lux
Ronnie Doggart
Frequent Advisor

Re: set autotrace on question

Consistent gets are the retrieval of blocks from the buffer cache in read consistent mode and may include reads from the rollback segments. Queries normally generate consistent gets.
Indira Aramandla
Honored Contributor

Re: set autotrace on question


consistent gets are logical reads. That is, they are reads from data that's already cached in the buffer cache.

session logical reads is the sum of all the consistent mode gets (consistent reads) and current mode gets (db block gets).

Then there are physical reads and physical reads direct. Physical reads occur when a request for data is not serviced by the Oracle buffer cache. The data could come from filesystem buffers, a disk array's memory cache, or even a track/sector buffer on the hard drive itself.) Physical reads direct are reads that bypass the buffer cache entirely. This can happen w/ Parallel Query operations, for example. Note that physical reads includes all physical reads including the direct ones.

Never give up, Keep Trying
Honored Contributor

Re: set autotrace on question

We concern the Cache Hit Ratio that shows how many blocks were already in memory (logical reads, which include "db block gets" for blocks you are using and "consistent gets" of original blocks from rollback segments that others are updating) versus how many blocks had to be read from disk ("physical reads").
Oracle recommends that this ratio be at least 80%, but, I like at least 90% myself.