Re: Tuning Guidelines vis-a-vis SQL_TRACE

Thomas Kyte wrote in message <357b5b25.6388566_at_192.86.155.100>...
>
>>Im presently reading a book on SQL Tuning by Guy Harrison. TAlking about
>>the SQL_TRACE parameters the book says that:
>>
>>1- (Parse count)/(Execute count) must be close to 1 (or else unnecessary
>>parsing is being performed)
>
>wrong. if parse count = 1000 and execute count = 1000 unnecessary parsing
is
>being done. Hopefully, in a well written application, parse count = 1 and
>execution count >= 1.
>
>You want parse/execute to approach ZERO, not ONE.

Yes I agree. The quote is actually "Ideally, the parse count should be close to one".

>
>>2-(Blocks read )/(rows retrieved for execute/fetch/parse) must be less
>>then 10-20 (i dont understand the implication of this)
>
>If blocks-read/rows-retrieved = 10 for example 100 blocks/10 rows, that
means
>you read 10blocks to get any given row. thats really bad -- unless of
course
>you were joining 10 tables together -- then its pretty good.
>
>Its a generalization and you would have to look at it on a case/case basis.

Agreed is a generalization - my point was that the higher this ratio is, the more likely that the SQL statement is untuned. Once you exceed 10-20 blocks/row you start to get virtually certain that some improvement is possible. Ratios of 10-20 reads/row are still quite common in well-tuned SQL - remember that even a single row index lookup will usually require 4-5 block reads. Once you add subqueries, joins, sparse index or table scans, visiting rollback segment blocks the ratio quite often increases.

>
>>3-(Rows fetched)/(Fetch Calls) must be close to 10 or 15 (arrray
>>fetching)
>
>right on with this one. if rows fetched = fetch calls and fetch calls > 1,
then
>the you need to starting using array fetches for performance.
>
>>4-(Disk reads)/(Logical reads) must be equal to 0.1 (or else cache miss
>>rate is too high)

I'm not sure I meant that. What I said was "we usually aim to get this ratio less than about 10%". It depends a lot on the type of statement: table scans won't usually get a good hit ratio whereas index lookups should get a very good hit ratio (once the cache is populated).

Regards,

Guy Received on Mon Jun 15 1998 - 21:28:50 CDT