Tuning SGA and Parameters

Hello :

I use TOAD for checking server statistics apart from running physical queries to check all the ratios.. It has resulted very favorably many times. But for few environments, constantly I see some parameters which I cannot get rid of..

HIGH PARSE:EXECUTE RATIO 65%

HIGH PARSE CPU OVERHEAD @24

Shared Pool area too small (I should increase variable size)
Library Cache Pin Hit ratio 83%(for above)
Library Cache Get Hit Ratio 83% (dynamic or shareable sql?)

DBWR average scan depth 276 (Number of dbfiles too high??) and I have only 7 tablespaces and 21 datafiles at 20GB size of database.

The above are across multiple instances. (5 having an SGA of 1G and about 200GB totally)

What is the general recommendation for looking into the above?

Thanks, ST2000

Hi,

What % of your SGA is the shared pool? Any way shared_pool_size is something you could easily experiment with.

The high parse/execute ratio could be due to your applications not using bind variables:
. . where column = parameter . .
is parsed & optimised for each value of the parameter
. . where column = :b . .
only gets parsed once (any occupies less of your shared_pool_size).

"DBWR average scan depth 276 (Number of dbfiles too high??)" - TOAD always says something like that - I've stopped worrying about it!