This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jun 19, 2013 11:23 PM by Aman.... RSS

performance_parameters_in 11r2

Santosh Pradhan Explorer
Currently Being Moderated
Hi ,

We are planning to migrate from 10.2.0.3 to 11.2.0.2 enterprise .
For this we have test setup of oracle 11.2.0.2 and we tried to run the reports(selects) on this new server.
But reports on 11R2 are taking long time than existing 10.2.0.3 reports time .

Kindly let me the parameters setting for 11gR2
  • 1. Re: performance_parameters_in 11r2
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Same hardware ? Hardware settings ? Same data set ? How has the database been upgraded ?
    How anyone could know what to do to make your queries faster ? Keep away from compulsive tuning disorder, but analyze first (AWR...).

    Nicolas.
  • 2. Re: performance_parameters_in 11r2
    Santosh Pradhan Explorer
    Currently Being Moderated
    Thnx for reply

    New Hardware is 3 times faster than old
    Current os : IBM AIX 7.1

    And yes same data volume .
    We used EXPDP/IMPDP for upgrade

    We analyzed AWR reports but it's showing that database is totally idle and smooth(also the same is replied by ORACLE )

    We have 12 core and As we have observed that only 1 core is being utilized.
    There should be some parameters setting regarding PT that will force to use more than 1 cpu

    Edited by: Santosh Pradhan on Jun 6, 2013 6:06 PM
  • 3. Re: performance_parameters_in 11r2
    Oviwan Pro
    Currently Being Moderated
    maybe a trace/explain plan would help of that report-statement. compare the xplan from the old and new db...
  • 4. Re: performance_parameters_in 11r2
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    CPU_COUNT ?
    You said "new hardware", what was the old one ?
    Did you get all the init parameters out from 10g ? Did you change anything such as compatible, optimizer_features_enable... ?
    Do you ran the AWR for the time the report ran ? Any useful output ? Could you post one (txt mode) ?

    Nicolas.
  • 5. Re: performance_parameters_in 11r2
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    With everything else being equal, most queries should run faster on 11gR2 than on 10gR2. There will be some queries that may run slower - these will need to be tuned (or rewritten) individually. It is unusual to state that performance in general is worse than before - this likely indicates a missed step in the upgrade process. Pl see if these can help

    Things to Consider Before Upgrading to 11.2.0.2 to Avoid Poor Performance or Wrong Results [ID 1320966.1]
    TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance [ID 160089.1]
    Using SQL Performance Analyzer to Test SQL Performance Impact of an Upgrade [ID 562899.1]
    Best Practices: Proactively Avoiding Database and Query Performance Issues [ID 1482811.1]
    Best Practices: Proactive Data Collection for Performance Issues [ID 1477599.1]

    HTH
    Srini
  • 6. Re: performance_parameters_in 11r2
    rahulras Explorer
    Currently Being Moderated
    Upgrade to 10g had more changes/features than upgrade to 11g.

    1) How much is the buffer cache and SGA size on 11g instance? how much was it on 10g? 11g parameter memory_max_target ?

    2) Before running queries (i.e. reports) on new 11g instance and after you loaded data, have you done the stats (at least on important/big tables)?

    3) When you say, report is running slow, that means effectively, some SQL is running slow. Did you check the plan of the slow running SQLs? Investigating the slow running SQLs might/will highlight a generic issue with the whole database setup .

    4) In AWR report, which are the top 5 wait events ?
  • 7. Re: performance_parameters_in 11r2
    Ed Rudans Explorer
    Currently Being Moderated

    First things first - are the execution plans for reports the same comparing 10g and 11g? You may consider use plan stability features in 11g.

    The cpu count is determined by oracle on startup (unless it set manually). Check with your UNIX team if they set any CPU usage restrictions on the LPAR.

     

    Regards

    Ed

  • 8. Re: performance_parameters_in 11r2
    marksmithusa Journeyer
    Currently Being Moderated

    11gR2 has a new parameter: db_fast

     

    By default, this is set to 'disabled, because we hate you'. In 11gR2, there are two other values which can be used: 'sort of enabled (10g edition)' and 'enabled, you're welcome'. There's also the undocumented value of 'Oracle ACS' which allows for amazing performance but at a large consulting cost.

     

    Seriously, I would hazard a guess that your stats are all messed up. Did you backup your statistics before the upgrade? Have you tried running your '10g statistics' on the upgraded database (preferably on a copy first)?

     

    What is your 'optimizer_features_enable' parameter set to? What about your 'compatible' parameter?

     

    Did you check out the 'Things to Consider Before an Upgrade' document?

     

    You did volumetric testing before you upgrading, right? What's different between that database and this one?

     

    Mark

  • 9. Re: performance_parameters_in 11r2
    jgarry Guru
    Currently Being Moderated

    Please put the following in your favorite search engine:

     

    11g what to expect from the optimizer

     

    You should find a white paper about what to expect from the optimizer and another about SQL plan management.  After understanding those, look at your AWR or statspack, as your licensing allows.

  • 10. Re: performance_parameters_in 11r2
    Aman.... Oracle ACE
    Currently Being Moderated

    11gR2 has a new parameter: db_fast

    Oh come on Mark, you are not supposed to tell the secrets so easily on a public forum and moreover, the parameter is wrong . The actual one is __make_my_db_fast(notice the double underscores) and it's only set if the DBA sets one more parameter _i_read_documentation=NEVER explicitly using ALTER SYSTEM! .

     

    Regards

    Aman....

  • 11. Re: performance_parameters_in 11r2
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    >We have 12 core and As we have observed that only 1 core is being utilized

    That is interesting.  As if you have only 1 session using CPU at any time.  How many active database sessions do you have ?  Are they waiting on I/O and not running on CPU ?

     

     

    Hemant K Chitale

  • 12. Re: performance_parameters_in 11r2
    marksmithusa Journeyer
    Currently Being Moderated

    Sorry, I was referring to the Exadata parameter - which uses db_fast but has a pre-req of having a NULL value for the reading_fine_manuals parameter

  • 13. Re: performance_parameters_in 11r2
    jgarry Guru
    Currently Being Moderated

    HemantKChitale wrote:

     

    >We have 12 core and As we have observed that only 1 core is being utilized

    That is interesting.  As if you have only 1 session using CPU at any time.  How many active database sessions do you have ?  Are they waiting on I/O and not running on CPU ?

     

     

    Hemant K Chitale

    I'd also want to know how that observation is made.  It could be everything is running fast on many processors, but only one is running the slow process (or process that is slowing everyone down).  It could be AIX has been told to limit Oracle to 1 core.  It could be the log writer bogging.  Who knows?

  • 14. Re: performance_parameters_in 11r2
    Aman.... Oracle ACE
    Currently Being Moderated

    Aah my bad then, haven't got an Exadata yet for myself to play much with it :-) .

     

    Regards

    Aman....

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points