This discussion is archived
    13 Replies Latest reply: Feb 27, 2014 4:30 AM by AnnPricks E RSS

    Which gather stats?

      Hi all,

       

      11.2.0.1

       

      Which of the ff. command includes gathering of index statistics?

       

      EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');

      EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', cascade => TRUE);

       

      Thanks,

      pK

        • 1. Re: Which gather stats?
          BeGin

          Hi,

           

          The second explicitely incudes stats gathering.

          The first does not but according to dbms_stat doc DBMS_STATS , as cascade => true is the default value; unless you have changed it, it will gather your stats index too.

           

          Regards,

           

          --

          Bertrand

          • 2. Re: Which gather stats?

            Thanks dear

             

            But the batch-users said their batch program still slow?

             

            The gave me this scripts which they used to run before. Is this good or effective?

             

             

             

            execute dbms_stats.gather_system_stats('START');

            exec dbms_stats.gather_index_stats('HR','EMP_KEY1');

            exec dbms_stats.gather_index_stats('HR','EMP_KEY2');

            exec dbms_stats.gather_index_stats('HR','EMP_KEY3');

            exec dbms_stats.gather_index_stats('HR','EMP_KEY4');

            exec dbms_stats.gather_index_stats('HR','DEPT_KEY1');

            exec dbms_stats.gather_index_stats('HR','DEPT_KEY2');

             

            Is this running constantly because of START?

             

            thanks,

            • 3. Re: Which gather stats?
              Partha Sarathy S

              If you just specify

               

              EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');

              this means you are gathering statistics for table alone.

               

              The second one

               

              EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', cascade => TRUE);

                includes the indexes associated with the table indicated by the CASCADE keyword. See this link

               

              Oracle Concepts - Oracle Tables and Statistics

              • 4. Re: Which gather stats?
                BeGin

                There are two different things in this script.

                 

                The gather_system_stats('START'); is there to collect I/o and CPU performance of you system. But this syntax is there to collect data with a representative workload, and it should be followed by a gather_system_stats('STOP'); at the end of the workload.

                I Have never tried to let it run without a stop, and I haven't found any references to what it can do if you don't stop it ...

                 

                Then you have a list of stats gathering on indexes but not on tables. The cascade => true should gather stats on all indexes.

                 

                But anyway if the stats on tables or indexes are stale they should be refreshed during next maintenance window.

                 

                Regards,

                 

                --

                Bertrand

                • 5. Re: Which gather stats?
                  Partha Sarathy S

                  As BEGIN said,

                  Gather_stats('Start') will start to collect hardware statistics such as I/O and CPU perfomance and utilization.  This should be followed by a Gather_stats('STOP')But the others will collect INDEX_statistics when you run those scripts. See this link.

                   

                  http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm

                  • 6. Re: Which gather stats?

                    What happen if I exited the connection and did not execute STOP?

                    • 7. Re: Which gather stats?
                      Anand...

                      Hi,

                       

                      But the batch-users said their batch program still slow?

                       

                      Have you checked what the wait events associated with the sessions run as part of batch program? What are the involved sqlid  and their execution plans? Have the execution plans changed for sqlid?

                       

                      Gathering and keeping the stats latest is good but you need to dig more based on wait events / sqlids / execution plans etc.

                       

                      You can check v$session to check your connection to db and what it is doing. If you have done cntrl + c , the session would have exited and gather stats would have got cancelled.

                      • 8. Re: Which gather stats?
                        Partha Sarathy S

                        I am not very much aware or worked in this.  But on reading I can get like if you execute Gather_stats('Start')  then this is gonna start collecting statistics as i mentioned earlier.

                         

                        SQL> exec dbms_stats.gather_system_stats('START');

                         

                        After an hour or several hours run:

                         

                        SQL> exec dbms_stats.gather_system_stats('STOP');

                         

                        Your system stats will be stored in sys.aux_stats$.

                        • 10. Re: Which gather stats?
                          AnnPricks E

                          Hi..

                          Normally statistics is maintenance activity. It should not be done at business time. Which obviously impact you system.

                          • 11. Re: Which gather stats?
                            AnnPricks E

                            Hi..

                            Can you please tell me, are you all stats in business time? Because when you gather the statistics it will lock the objects some time. I am not sure that point just i am guessing. You can see whether did you get any object lock while gathering statistics.

                            • 12. Re: Which gather stats?
                              Partha Sarathy S

                              Normaly system statistics would be collected only once. Of course, if your system changes drastically, for example while upgrading with faster CPUs or faster disks, then you would normaly recollect them. But there is certanly no need to recollect them repeatedly every day.

                              • 13. Re: Which gather stats?
                                AnnPricks E

                                Hi...

                                 

                                You must gather system statistics with dbms_stats.gather_system_stats during a typical load on your system. You can simply run the following:

                                 

                                SQL> exec dbms_stats.gather_system_stats('START');

                                 

                                After an hour or several hours run:

                                 

                                SQL> exec dbms_stats.gather_system_stats('STOP');

                                 

                                Your system stats will be stored in sys.aux_stats$. Check the values in this table whether the value for MREADTIM is bigger than SREADTIM. If this is not the case than system statistics will not be used.