| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning Guidelines vis-a-vis SQL_TRACE
A copy of this was sent to Umar FArooq <umar.farooq_at_cressoft.com.pk>
(if that email address didn't require changing)
On Sat, 06 Jun 1998 16:01:08 +0500, you wrote:
>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.
>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.
>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)
>
>Could anyone tell me how practical are these statistics? I know its
>difficult to prophesize exact figures for this but generally speaking
>how closely do these guidelines represent teh reality.
>
>Anyhow, I think its a great book and strongly recommend to anyone
>seeking to enter ther realm of SQL tuning.
>
>
>Thx..
>
>Umar Farooq Ch.
>Software Engineer
>CresSoft
>Lahore, Pakistan.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jun 06 1998 - 10:13:46 CDT
 |
 |