Archives du mot-clé statistics

New Exadata mode for DBMS_STATS.GATHER_SYSTEM_STATS

There is a new configuration in DBMS_STATS that can help Oracle to understand that it is working on an Exadata.

Most of the time the FULL TABLE SCAN is more performant than an INDEX SCAN on Exadata systems (i say most of the time, so i mean you have to test by yourself). The problem of this is with default system stats.
With the default system stats does not know that a FTS may be less expensive than accessing an index.

To help Oracle to know it is working on a EXADATA system there is a new GATHER_SYSTEM_STATS mode exclusively for EXADATA.

To enable this new mode you have to execute the following command

exec dbms_stats.gather_system_stats('EXADATA');

This new mode is available from the 11.2.0.2.18 or 11.2.0.3.8 (nore information on Metalink note Oracle Sun Database Machine Setup/Configuration Best Practices [ID 1274318.1])

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 */
               CASE
                  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,
               CASE
                  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# = st.bo#)
                  ELSE NULL
               END PARTITION,
               CASE
                  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',
                                  'ERROR'
                                 )
                  ELSE NULL
               END STATUS,
               CASE
                  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
                                                      (session_id,
                                                       '[0-9,]'
                                                      ) 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)