9 Replies Latest reply: Jun 7, 2013 4:02 AM by rahulras RSS

    stats gathering

    sam995972
      Hi All,

      oracle 11gr2
      Linux

      am new to database, how to gather statistics in oracle 11g? like database/schema/table wise?
      what is the impact gathering stats daily basis? how to automate stats gathering database wise?
      can anyone please suggest & explain with example if possible.

      thanks,
      Sam.
        • 1. Re: stats gathering
          Aman....
          sam995972 wrote:
          Hi All,

          oracle 11gr2
          Linux

          am new to database, how to gather statistics in oracle 11g? like database/schema/table wise?
          what is the impact gathering stats daily basis? how to automate stats gathering database wise?
          can anyone please suggest & explain with example if possible.
          Please read,
          http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#PFGRF94712

          Aman....
          • 2. Re: stats gathering
            sam995972
            thanks Aman for sharing the link.

            i have one doubt

            1 exec dbms_stats.gather_schema_statistics('test',CASCADE=>TRUE,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE)

            2 exec dbms_stats.gather_schema_statistics('test',CASCADE=>TRUE).

            what is the difference between above statement.which one is recommended ?

            thanks,
            Sam.
            • 3. Re: stats gathering
              Lubiez Jean-Valentin
              Hello,

              what is the difference between above statement.which one is recommended ?
              Normally, there's no difference as DBMS_STATS.AUTO_SAMPLE_SIZE is the default (in 11.2), except if you change the default settings. You'll find more details on the link below:

              GATHER_SCHEMA_STATS


              Hope this help.
              Best regards,
              Jean-Valentin Lubiez

              Edited by: Lubiez Jean-Valentin on Jun 7, 2013 9:48 AM
              • 4. Re: stats gathering
                Fran
                AUTO_SAMPLE_SIZE lets Oracle Database determine the best sample size necessary for good statistics, based on the statistical property of the object. Because each type of statistics has different requirements, the size of the actual sample taken may not be the same across the table, columns, or indexes.
                from the link posted before by Aman

                If you ommited this clause, full scan will be done: (again in the document attached by Aman)
                Gathering statistics without sampling requires full table scans and sorts of entire tables. Sampling minimizes the resources necessary to gather statistics
                • 5. Re: stats gathering
                  sam995972
                  thanks Jean-Valentin Lubiez for quick reply.

                  actually one of my prod box we are not gathered any stats yet.business is planning to gather the database stats for better performance.
                  what will be the impact ?any idea?what happens if we gather the stats now?any plans will changes?

                  Edited by: sam995972 on Jun 7, 2013 12:55 AM
                  • 6. Re: stats gathering
                    Lubiez Jean-Valentin
                    Hello,

                    what will be the impact ?any idea?what happens if we gather the stats now?any plans will changes?
                    Yes, the Execution Plans may change. So, it's better to test before applying on a Production Database.

                    So as to reduce risks of regression, you may use SQL Plan Baselines:

                    Overview of SQL Plan Baselines


                    Hope this help,
                    Best regards,
                    Jean-Valentin Lubiez
                    • 7. Re: stats gathering
                      sam995972
                      thanks for reply.


                      actually my server is running with 8 cpu, do i need mention degree as 8 or more.

                      below statement is it correct approach?please suggest.

                      exec dbms_stats.gather_schema_stats( -
                      ownname => 'SCOTT', -
                      estimate_percent => dbms_stats.auto_sample_size, -
                      DEGREE=>4, -
                      CASCADE=>TRUE -
                      )

                      Edited by: sam995972 on Jun 7, 2013 1:36 AM
                      • 8. Re: stats gathering
                        Lubiez Jean-Valentin
                        Hello,


                        You have also the value DBMS_STATS.AUTO_DEGREE which let Oracle choose between 1 and the DEFAULT_DEGREE.

                        Anyway, don't forget that increasing parallelism could be CPU intensive. It's good to read the following Note of My Oracle Support so as to make the better choice:
                        *How To Troubleshoot Statistics Collection Running or Not running In Parallel Issues [ID 1408464.1]*
                        Hope this help,
                        Best regards,
                        Jean-Valentin Lubiez

                        Edited by: Lubiez Jean-Valentin on Jun 7, 2013 10:55 AM
                        • 9. Re: stats gathering
                          rahulras
                          On 11g, when you create a database, Oracle creates a job, which will run at 22:00 every day and do the stats on all objects in the database, which are marked as stale.
                          Unless your DBAs have explicitely disabled/stopped this job, you need not do anything for stats.
                          select * from dba_autotask_operation ;
                          Run following query on your database (this has objects from SYS as well), which will show when the stats was done on your database
                          select table_name, object_type, last_analyzed, stale_stats 
                          from dba_tab_statistics order by last_analyzed desc ;