7 Replies Latest reply: May 1, 2014 1:14 PM by Iordan Iotzov RSS

    Oracle Statistics

    user9085814

      Hi All,

       

      What are the benifits of locking the table/schema statistics ?

      And

      How Statistics can be removed ? What can be done to prevent statistics deletion ?

       

      Thanks in Advance,

        • 3. Re: Oracle Statistics
          user9085814

          Hi ,

           

          Thanks for this ......

          Is there any real time benifits , if we lock table statistics ........ or it's just a mechanism for locking and unlocking the statistics ?

           

          Is which scenario, statistics can be removed ?

           

          Thanks In Advance...

          • 4. Re: Oracle Statistics
            user9085814

            Hi ,

             

            Answers I found

            Statistics can be removed/stale, if DMLs occured on the object will be greater than num_rows of the recent stats gathered for that object.

            To check the DMLs happended on the a object after it was last analyzed , we can refer to [DBA|ALL|USER_TAB_MODIFICATIONS].

             

            There is no table/ROW locking mechanism when statistics has been gathered , but yes database performance will hamper because

            gathering statistics can be a resource consuming operation depending upon the volume of the object.

             

            Request all, please let me know if I am wrong ........

             

            Thanks,

            • 5. Re: Oracle Statistics
              jgarry

              I'm not sure exactly what you are saying, but it seems wrong.  You can lock statistics either with procedures or using the dbconsole - the latter may be easier and more obvious, the former is better for learning and understanding.  Managing Optimizer Statistics: Advanced Topics

               

              In general, it often is just a workaround for avoiding some real problem.  You should find out about the various means of plan stability in various versions.  It is a big enough topic to have books written about it.

               

              I've found a common scenario is "My report is slow," dba checks and sees plan suddenly changed, reverts statistics to when old plan was obtained, then moves on.  What they really need to do is find out what the query should be doing, since it might apply to more than the report, and implement solutions accordingly.

              • 6. Re: Oracle Statistics
                Mark D Powell

                user9085814, the benefit of locking statistics is that you stop the nightly statistics gathering process from re-gathering statistics on the locked tables.  You would normally lock the statistics only for tables where the normal processing produces bad plans compared to the saved statistics so you need to take control of the statistics collection for a specific table.

                - -

                With 11g+ you probably do not need to lock statistics since Oracle provides a means to use table specific statistics gathering parameters.  This feature was unavailable prior to 11g so if you needed to change the sample size or number of histograms for a specific table you needed to save the code and run it when necessary.  See the new with 11g dba_tab_stat_prefs view.

                - -

                HTH -- Mark D Powell --

                 

                 

                 


                • 7. Re: Oracle Statistics
                  Iordan Iotzov

                  Locking statistics is useful in managing the performance of SQLs based on volatile tables. Volatile tables change their content (number of records and/or distribution) rapidly, so it could be difficult to keep in sync the stats with the actual data in the table.

                  In this situation, the stats are locked when the table is at “its biggest”.

                   

                   

                  Since in most cases the underlying data changes over time, locking stats (regardless of the reason why they were locked) is a temporary solution. Over time, the stats (slowly) stop to properly represent the data in the table, which may lead to performance issues.

                     

                  HTH,

                  Iordan Iotzov