set autotrace on question

[Sushil Singh_1]

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

Thanks
Sushil Singh

[Jean-Luc Oudart]

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.

Rgds,
Jean-Luc

[Ronnie Doggart]

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]

Hi,

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.

IA

[twang]

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.