Re: Database tunning - parse / execute ratio

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Thu, 22 May 2003 22:34:46 +0100
Message-ID: <3ecd42ce_2@mk-nntp-1.news.uk.worldonline.com>

"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 Received on Thu May 22 2003 - 16:34:46 CDT