when use set autotrace on
i find that for first query it takes
16 db block gets and 6 consistent gets.
then i rewrite it.
4 db block gets and 10 considtent gets.
can you tell me which one is better?
Dear BXR, 23rd June 2001 15:48 hrs chennai
The first query works out for you better the reason is as follows.
consistent gets - number of the data blocks which were accessed in the buffer cache for SQL statements, that do not modify data - just SELECT statements.
db block gets - number of the data blocks which were accessed in the buffer cache for SQL statements that modify data - INSERT, UPDATE, DELETE and SELECT FOR UPDATE statements.
physical reads - number of the data blocks that where read from the disk.
The sum of consistent gets and db block gets is the number of the logical reads.
The buffer hit ratio is:
(logical reads - physical reads) / logical reads
(consistent gets + db block gets - physical reads) / (consistent gets + db block gets)
Hit Ratio= 1-(physical reads/(db block gets+consistent gets)
The total of DB block gets and consistent gets gives the total number of requests for data from memory.
Hit ratio value includes requests satisfied by access to buffers in memory and request that cause physical I/O.
So more requests satisfied from memory is better than physical reads to be more.
You can check this with the above formula to see both the conditions what is the hit ratio for your Queries.
Which ever gives you high hit ratio is the better Query to Use and i am sure you would have seen the first query to be best with high Hit ratio.
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it
Click Here to Expand Forum to Full Width