1 2 Previous Next 16 Replies Latest reply on Jun 20, 2013 6:23 AM by Aman....

    performance_parameters_in 11r2

    Santosh Pradhan
      Hi ,

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

      Kindly let me the parameters setting for 11gR2
        • 1. Re: performance_parameters_in 11r2
          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...).

          • 2. Re: performance_parameters_in 11r2
            Santosh Pradhan
            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
              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
                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) ?

                • 5. Re: performance_parameters_in 11r2
                  Srini Chavali-Oracle
                  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 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]

                  • 6. Re: performance_parameters_in 11r2
                    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

                      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.




                      • 8. Re: performance_parameters_in 11r2

                        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?



                        • 9. Re: performance_parameters_in 11r2

                          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

                            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! .




                            • 11. Re: performance_parameters_in 11r2
                              Hemant K Chitale

                              >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

                                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

                                  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

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




                                    1 2 Previous Next