Forum Stats

  • 3,734,275 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

Tool\Scripts required to tune the init parameter.

534533
534533 Member Posts: 95
edited Sep 10, 2008 2:59AM in General Database Discussions
Hello All,
We faced lot of problem during Oracle10G migration for example Performance issue, Tuning of init parameter.

Now we are planning to migrate to Oracle 11G.
Can you please let me know how to tune the init parameter. Is any tool \Scripts available for tuning ?

Our system are windows based (window server 2008 32 bit).

Any suggestion or any pointers will be highly appreciated.


Thanks in advance.

With Regards
Hemant Joshi.

Answers

  • Robert Geier
    Robert Geier Member Posts: 2,989
    Oracle includes a lot of auto-tuning features and recommendations for tuning memory settings. The easiest way to access them is on the Advisor Central page in OEM / DBconsole.

    1. Run a test in 10g to get timing on your normal processes.
    2. Upgrade to 11g with dbua (it will keep most parameters the same)
    3. Re-run the tests and compare performance with 10g.
    4. Then check the Advisor page in dbconsole.
  • 534533
    534533 Member Posts: 95
    Hello Robert Geier,
    Thanks for the response.
    We are not using the DBConsole to create the database. We are using oradim (i.e silent mode installation).
    I want to know what values should be given to init parameter so that it gives best performance.

    Thanks
    With Regards
    Hemant Joshi.
  • Robert Geier
    Robert Geier Member Posts: 2,989
    Start with the same parameters used in 10g, check if the performance is similar to 10g, then read up on parameter memory_target.
  • 534533
    534533 Member Posts: 95
    Hello Mr. Robert,
    Thanks for the response.
    This is one way but it's time consuming.
    We have different set of machine (having different load/memory/CPU speed). I am looking if I can set the parameter optimally so that we have less problem latter phase of our development.




    Thanks
    With Regards
    Hemant Joshi.
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    If there were simple scripts that would tell you how to set a particular initialization parameter to an optimal value, it would be highly likely that Oracle would have incorporated that information into the database to allow it to tune parameters (as it did, for example, with MEMORY_TARGET, that allows Oracle to manage the RAM allocated to SGA and PGA and to the various pools without human intervention). If there is no option to have Oracle automatically tune a particular parameter, it is rather likely that there is no script that would give a reasonably optimal value for that parameter in most instances.

    Justin
  • 534533
    534533 Member Posts: 95
    Hello Justin,
    I am agree with you. 'Memory_Target' will do the job of Automatic Memory Management but i want to tune other parameter as well for example
    shared_servers, max_shared_servers, cursor_sharing, processes, log_checkpoint_timeout etc.
    Any document, link will help me.

    Thanks
    With Regads
    Hemant Joshi
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    My point, though, is that if Oracle has not provided an option to automatically tune a particular parameter, it is very, very unlikely that anyone else has.

    You can read through the documentation to understand what each particular parameter does. You can then use your knowledge of your application to make an educated guess at the proper setting. And you can run tests with appropriate application load to determine whether you have improved performance. But it does not sound like that is what you're looking for.

    With a lot of these settings, the right value is highly application dependent. For example, I would argue that CURSOR_SHARING should always be exact unless you are temporarily covering bugs in your application code. So if you have set it to something other than EXACT, there should be a project to fix the code bugs that force the setting. As code fixes go in to remedy the bugs you've identified, you'd want to re-examine this setting. Obviously, though, no one other than a human in your organization can figure out when a particular piece of code has been fixed to use bind variables in order to determine that you can change cursor_sharing.

    Justin
    JustinCave
This discussion has been closed.