Tuning SGA and Parameters
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Tuning SGA and Parameters

  1. #1
    Join Date
    Oct 2000
    Posts
    449

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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!
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width