3 Replies Latest reply: Jun 18, 2013 5:11 AM by Ed Rudans RSS

    Should I collect system stistics when upgrade from 9i to 10.2.0.5

    ivyliu

      I use exp/imp to migrate the user data.

      and then create a new 10.2.0.5 database , import the data.

      Should I collect the system staitsics ?

      I 've not collect the system stistics before in the 9i ENV.


        • 1. Re: Should I collect system stistics when upgrade from 9i to 10.2.0.5
          Srini Chavali-Oracle


          System statistics should be gathered at regular intervals - see details in this MOS Doc

           

          System Statistics: Scaling the System to Improve CBO optimizer [ID 153761.1]

           

          HTH
          Srini

          • 2. Re: Should I collect system stistics when upgrade from 9i to 10.2.0.5
            Ed Rudans

            The short answer is 'it depemds'. This is the one of these questions that raise more questions than answers.

            We can start with the fact that 10g and 11g come with default values, so collection is not something what is required.

             

            The collection of system stats may affect on how CBO decide on CPU intensive tasks like sorts, and may affect on choosing full table scans or single block reads. So It can affect the performance dramatically if set inproperly. Some more thoughts:

            1. Its recomended to gather the stats on typical workload. Now define what is typical. Often DBA's dont have visibility whats happening on Enterprise Storage system, the performance of the disk may be very variable during the day.

            2. On RAC, the stats collected on one node will be stored as stats for all nodes. If you have different workloads on different nodes then, the stats will be not representative across the cluster.

            3 .In case you have consolidated environment, the the task of setting the stats will be not so obvious. Of course you can play with setting stats depending on workload on different instances

            4. Gathering stats means that you will have different CBO 'settings' across many databases. This adds additional variable into formula when diagnosing the performance issues.

             

            So as a summary, I would advice to leave the system stats as default, unless you know exactly what you are doing.

             

            Regards

            Ed Rudans

            http://erudans.blogspot.com

            • 3. Re: Should I collect system stistics when upgrade from 9i to 10.2.0.5
              Antonio Navarro

              I think so, of course it depends on your application, querys with hint, force execution plans.... But it is necesary to guaranty performance.

               

              HTH

              Antonio NAVARRO