10 Replies Latest reply: Feb 4, 2013 4:14 AM by Dom Brooks RSS

    slow performance on 11g

    Santosh Pradhan
      Hi ,

      Recently we have migrated our database from 10g to 11.2.0.2 enterprise edition .

      Comparing with 10g , we are getting very slow performance on 11g .

      can anyone suggest me how to to tune this migrated database.

      Also Please suggest me the version of TOMCAT server compatible with 11g

      Thanx in advance....
        • 1. Re: slow performance on 11g
          asahide
          Hi,

          What do you mean slow performance?
          You may as described specifically is good.

          BTW, The most common problem is that the impact of the action plan has been changed By Upgrading..

          Regards,
          • 2. Re: slow performance on 11g
            NathanielNie
            generate an AWR report, analyze the top 5 event firstly.
            • 3. Re: slow performance on 11g
              Aman....
              user12045405 wrote:
              Hi ,

              Recently we have migrated our database from 10g to 11.2.0.2 enterprise edition .

              Comparing with 10g , we are getting very slow performance on 11g .

              can anyone suggest me how to to tune this migrated database.
              How did you measure the performance to be slow? Is it the whole database that has become slow or some reports have gone slower?

              Aman....
              • 4. Re: slow performance on 11g
                kuljeet singh -
                must read
                http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-upgrading-10g-to-11g-what-to-ex-133707.pdf
                • 5. Re: slow performance on 11g
                  Rob_J
                  Hi,

                  Please post the key information from the AWR reports for before and after the upgrade.
                  What slowness are you seeing? Perhaps high CPU usage, or massive disk queues?
                  You need to define what slow means and back that up with some evidence of the change.

                  Rob
                  • 6. Re: slow performance on 11g
                    Santosh Pradhan
                    Hi ,

                    Guys thanxx for intrest.......


                    Top 5 Timed Foreground Events

                    Event Waits Time(s) Avg wait (ms) % DB time Wait Class
                    read by other session 26,426 12,257 464 28.52 User I/O
                    db file parallel read 15,321 7,220 471 16.80 User I/O
                    db file sequential read 417,827 5,626 13 13.09 User I/O
                    DB CPU 3,604 8.39
                    db file scattered read 458,541 3,256 7 7.58 User I/O


                    And query was taking 30 Mints in 10g , and now its taking 2 hrs to complete .
                    We have gathered statts on schema , rebuilded indexes

                    Imp Note : Still we havent changed the compatible parameter .
                    It is 10.2.0.3.0 still

                    Do i need to change it to 11.2.0.2 cos we have decided not to downgrade again onto 10g
                    • 7. Re: slow performance on 11g
                      sb92075
                      post EXPLAIN PLAN from both fast & slow SQL
                      • 8. Re: slow performance on 11g
                        rp0428
                        >
                        Do i need to change it to 11.2.0.2 cos we have decided not to downgrade again onto 10g
                        >
                        There is no point leaving it as is if you don't plan to downgrade. And there is no point doing troubleshooting and then making the change afterward.

                        Change the setting and then reexamine the scope of any issue if you still have one. Repost if there is still an issue. But you will need to provide the particulars about the issue, the query, the table/index DDL, record counts, predicate counts and the other things mentioned in the FAQ about how to submit a tuning request.
                        • 9. Re: slow performance on 11g
                          Nikolay Savvinov
                          Hi,

                          your top 5 events are hard to read because of no formatting (use code tags), but if I read it correctly, then an average db file parallel read is 471 ms, which is at least an order of magnitude higher than normal. Was it the same before the upgrade?

                          Best regards,
                          Nikolay
                          • 10. Re: slow performance on 11g
                            Dom Brooks
                            Start looking at your top N SQL.

                            Even if there are a number of statements whose performance has degraded thanks to the upgrade, you might as well start with the worst performing SQL.
                            If you have some performance reports from the period before upgrade then these could be used as comparison.

                            If you preserved any execution plans from before the upgrade, these might come in useful as quick fixes to any specific statement issues.
                            If you still have plans in AWR from before the upgrade, then

                            "Read by other session" is a typical symptom of inefficient execution plan issues where multiple query executions are requesting the same data and it is not in the cache.

                            "db file parallel read" is often a symptom of index prefetching - from your top SQL check statements doing an index full scan, particularly if they didn't use to.

                            See threads following threads for SQL tuning:
                            [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request
                            [url https://forums.oracle.com/forums/thread.jspa?messageID=1812597]When your query takes too long

                            So, in summary, check out your top N SQL, check which queries are doing most "read by other sessions", double check plans with "db file parallel read".

                            You might find a common cause affecting a number of statments or you might just find a couple of queries who are stressing you out and whose execution plan needs some post-upgrade TLC (which is normal).