Re: Database tunning - parse / execute ratio

Paul Brewer wrote:

> "Wes Brooks" <wes_brooks_at_yahoo.com> wrote in message
> news:a2b371c5.0305200725.18f11424_at_posting.google.com...
> > Hi expert,
> >
> > I got the following stats from TOAD:
> >
> > 1. Buffer cache hit ratio 84.7041 - May need to increase
> > db_block_buffers. (My db_block_buffers is 3200).
> >
> > 2. Parse / Execute Ratio 80.1188 high parse to execute ratio. (My
> > shared_pool_size is 30000000.) How much I can increase?
> >
> > 3. DBWR avg scan depth 415.4 #DB_FILES too high? (My DB_FILES is
> > 1500).
> >
> > The system is client server. Please help and tell me how to tun up
> > the database?
> >
> > Thanks.
>
> You are starting from the wrong place.
>
> Start looking at performance when there are complaints about performance. If
> nobody is complaining about performance, rest easy, and worry about
> something else.
>
> And if there are complaints about performance, don't tune ratios; make the
> app run quicker. And 99% of the time, the way to make the app run quicker is
> to tune the SQL.
>
> When you have optimised all the SQL, then it might be an idea to start
> looking at instance tuning. But at that stage I wouldn't be inclined to pay
> too much attention to what TOAD thinks.
>
> Regards,
> Paul

I agree. If there are no complaints you have more important things to consider.

If so start with DBMS_PROFILER.

Then take the pieces that take the most time and/or execute most often and check them using EXPLAIN PLAN and TKPROF.

Info. on all of these available at http://tahiti.oracle.com.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)