4 Replies Latest reply: Aug 20, 2013 5:10 PM by jgarry RSS

    Gather stats in oracle 11g


      Hi All,


      OS: AIX
      DB: 11G


      I am trying to gather stats for a specific schema using the below mentioned command:

      >exec dbms_stats.gather_schema_stats('schema_name');

      The size of the said schema is 140 gb and it is almost one hour that the above command is still running.
      I need to know if it is normal, is there a way to tune the above command to speedup the gather.

      And how can we check if the above command is properly running from the background, I mean if there is any for the same or something like that?


        • 1. Re: Gather stats in oracle 11g

          default operation for V10 & V11, is that statistics are gathered once every 24 hours.

          There is little benefit for you to duplicate this task.

          • 2. Re: Gather stats in oracle 11g



            You can check v$session to check if session is running and dba_tab_statistics to see if you are reducing the stale tables.

            To speedup you could alter the degree option with dbms_stats.set_param, but I suggest using AUTO to use the CPU limit.

            • 3. Re: Gather stats in oracle 11g



              The runtime for a 140Gb schema looks quite OK to me.

              Without any further parameters, the gather_stats job will use the "compute" option, which takes a long calculation for every table.

              I've written a procedure that checks for 'stale' tables in dba_tab_statistics and only calculates those statistics.


              The query for looking up these tables looks like:


              SELECT table_name, 'NONE' AS partition_name

                         FROM dba_tab_statistics a

                        WHERE     owner = i_schema

                              AND NVL (stale_stats, 'NULL') = 'YES'

                              AND NVL(stattype_locked, 'NULL') = 'NULL'

                              AND NOT EXISTS

                                     (SELECT 1

                                        FROM dba_tab_partitions b

                                       WHERE     table_owner = i_schema

                                             AND a.table_name = b.table_name)

                       UNION ALL

                       SELECT table_name, partition_name

                         FROM dba_tab_statistics a

                        WHERE     owner = i_schema

                              AND NVL (stale_stats, 'NULL') = 'YES'

                              AND NVL(stattype_locked, 'NULL') = 'NULL'

                              AND partition_name IS NOT NULL;



              The above part finds plain tables, the lower part finds stale partitions for partitioned tables.

              It enables the procedure to gather stats for tables and for single or multiple partitions.


              It will skip the tables that have been calculated before by the oracle default job.





              • 4. Re: Gather stats in oracle 11g

                You might want to consider whether your rate of change of those tables depending on default stale percentages makes sense.  http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf