Xref: alice comp.databases.oracle.server:22276 Path: alice!news-feed.fnsi.net!newsfeed.direct.ca!logbridge.uoregon.edu!su-news-hub1.bbnplanet.com!su-news-feed1.bbnplanet.com!news.bbnplanet.com!inet16.us.oracle.com!not-for-mail From: tkyte@us.oracle.com (Thomas Kyte) Newsgroups: comp.databases.oracle.server Subject: Re: Tuning Guidelines vis-a-vis SQL_TRACE Date: Sat, 06 Jun 1998 15:13:46 GMT Organization: Oracle Government Lines: 63 Message-ID: <357b5b25.6388566@192.86.155.100> References: <35792173.A65D12B9@cressoft.com.pk> Reply-To: tkyte@us.oracle.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Newsreader: Forte Agent 1.5/32.451 A copy of this was sent to Umar FArooq (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@us.oracle.com Oracle Government Herndon VA http://govt.us.oracle.com/ -- downloadable utilities ---------------------------------------------------------------------------- Opinions are mine and do not necessarily reflect those of Oracle Corporation 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.