Re: Database tunning - parse / execute ratio

"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).

Ignore the ratio! It means nothing on its own.

>
> 2. Parse / Execute Ratio 80.1188 high parse to execute ratio. (My
> shared_pool_size is 30000000.) How much I can increase?

This is unlikely to be an issue of size, but of the quality (specifically, the shareability) of your SQL code. Forget resizing, look at the SQL.

>
> 3. DBWR avg scan depth 415.4 #DB_FILES too high? (My DB_FILES is
> 1500).

And how many actual data files have you got? If lots, maybe extra DBWRs might help.

>
> The system is client server. Please help and tell me how to tun up
> the database?

That's a bit like asking how long is a piece of string. And without details of the nature of the string, to boot.

All I can suggest is that you forget all this nonsense about tuning to get a bunch of artificial ratios looking good, and read up on how to tune properly. For a start, visit www.oraperf.com, and feed it your utlbstat/utlestat report, or -better- your statspack output. See what it recommends. Then read some of the tuning papers Anjo has available there.

Visit www.hotsos.com and have a look at some of the whitepapers there...

Trawl around Jonathan's site (www.jlcomp.demon.co.uk), and Steve Adams' website (www.ixora.com.au).

Read up on tuning wait events out of the system... it's a much more functional way to tune than targetting ratios.

Regards
HJR
>
> Thanks.
Received on Tue May 20 2003 - 14:58:34 CDT