Archives mensuelles : février 2013

How to find tables analyzed by dbms_stats job

Sometime it can be usefull to know what have done the automatic stats jobs

Here is a statment that will let you know the objects that have been analyzed by the job

As SYS execute the following

SELECT /*+ dynamic_sampling(4) dynamic_sampling_est_cdn */
                  WHEN o.type# IN (1, 20, 35)
                     THEN 'INDEX'
                  WHEN o.type# IN (2, 19, 34)
                     THEN 'TABLE'
                  ELSE 'UNKNOWN'
               END object_type,
               u.NAME owner, o.NAME object_name,
                  WHEN o.type# IN (19, 20)
                     THEN o.subname
                  WHEN o.type# IN (34, 35)
                     THEN (SELECT po.subname
                             FROM SYS.obj$ po
                            WHERE po.obj# =
                  ELSE NULL
               END PARTITION,
                  WHEN o.type# IN (34, 35)
                     THEN o.subname
                  ELSE NULL
               END SUBPARTITION,
               CASE st.STATUS
                  WHEN 0
                     THEN 'PENDING'
                  WHEN 1
                     THEN 'BEING PROCESSED'
                  WHEN 2
                     THEN 'COMPLETED'
                  WHEN 3
                     THEN DECODE (BITAND (st.flags, 8),
                                  8, 'TIMEOUT',
                  ELSE NULL
               END STATUS,
                  WHEN st.staleness <= -99
                     THEN 'MISSING'
                  ELSE 'STALE'
               END reason
          FROM SYS.stats_target$ st, SYS.obj$ o, SYS.user$ u
         WHERE st.obj# = o.obj#
           AND o.owner# = u.user#
           AND st.STATUS != 4
           AND (st.SID, st.serial#) IN (
                  SELECT /*+ no_unnest */
                         REGEXP_REPLACE (session_id, ',.*') SID,
                         REGEXP_REPLACE (session_id, '.*,') serial#
                    FROM (SELECT   CASE
                                      WHEN REGEXP_REPLACE
                                                      ) IS NULL
                                         THEN session_id
                                      ELSE NULL
                                   END session_id
                              FROM dba_scheduler_job_run_details
                             WHERE job_name = 'GATHER_STATS_JOB'
                          ORDER BY actual_start_date DESC)
                   WHERE ROWNUM < 2)