DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: autotrace

  1. #1
    Join Date
    Jan 2000
    Location
    san jose
    Posts
    149
    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.
    it takes
    4 db block gets and 10 considtent gets.

    can you tell me which one is better?

  2. #2
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    solution

    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

    or

    (consistent gets + db block gets - physical reads) / (consistent gets + db block gets)

    or

    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.

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width