The database gathers the most accurate statistics when it processes all rows in the table, which is a 100% sample. However, larger sample sizes increase the time of statistics gathering operations. The challenge is determining a sample size that provides accurate statistics in a reasonable time.

DBMS_STATS uses sampling when a user specifies the parameter ESTIMATE_PERCENT, which controls the percentage of the rows in the table to sample. To maximize performance gains while achieving necessary statistical accuracy, Oracle recommends that the ESTIMATE_PERCENT parameter use the default setting of DBMS_STATS.AUTO_SAMPLE_SIZE. In this case, Oracle Database chooses the sample size automatically. This setting enables the use of the following:

• A hash-based algorithm that is much faster than sampling

  This algorithm reads all rows and produces statistics that are nearly as accurate as statistics from a 100% sample. The statistics computed using this technique are deterministic.

• Incremental statistics

• Concurrent statistics

• New histogram types

The DBA_TABLES.SAMPLE_SIZE column indicates the actual sample size used to gather statistics.

You can override this setting with the degree argument to the DBMS_STATS gathering procedures. Oracle recommends setting degree to DBMS_STATS.AUTO_DEGREE. This setting enables the database to choose an appropriate degree of parallelism based on the object size and the settings for the parallelism-related initialization parameters.

The database can gather most statistics serially or in parallel. However, the database does not gather some index statistics in parallel, including cluster indexes, domain indexes, and bitmap join indexes. The database can use sampling when gathering parallel statistics.

Similarly, for composite partitioning, DBMS_STATS can gather separate statistics for subpartitions, partitions, and the entire table or index.

To determine the type of partitioning statistics to be gathered, specify the granularity argument to the DBMS_STATS procedures. Oracle recommends setting granularity to the default value of AUTO to gather subpartition, partition, or global statistics, depending on partition type. The ALL setting gathers statistics for all types.

You may only need to gather new statistics every week or month. The best practice is to use a script or job scheduler to regularly run the DBMS_STATS.GATHER_SCHEMA_STATS and DBMS_STATS.GATHER_DATABASE_STATS procedures.

For external tables, use the same DBMS_STATS procedures that you use for internal tables. Note that the scanrate parameter of DBMS_STATS.SET_TABLE_STATS and DBMS_STATS.GET_TABLE_STATS specifies the rate (in MB/s) at which Oracle Database scans data in tables, and is relevant only for external tables. The SCAN_RATE column appears in the DBA_TAB_STATISTICS and DBA_TAB_PENDING_STATS data dictionary views.

Possible values are as follows:

• MANUAL

  Concurrency is enabled only for manual statistics gathering.

• AUTOMATIC

  Concurrency is enabled only for automatic statistics gathering.

• ALL

  Concurrency is enabled for both manual and automatic statistics gathering.

• OFF

  Concurrency is disabled for both manual and automatic statistics gathering. This is the default value.

This tutorial in this section explains how to enable concurrent statistics gathering.

Prerequisites

This tutorial has the following prerequisites:

• In addition to the standard privileges for gathering statistics, you must have the following privileges:

  • CREATE JOB

  • MANAGE SCHEDULER

  • MANAGE ANY QUEUE

• The SYSAUX tablespace must be online because the scheduler stores its internal tables and views in this tablespace.

• The JOB_QUEUE_PROCESSES initialization parameter must be set to at least 4.

• The Resource Manager must be enabled.

  By default, the Resource Manager is disabled. If you do not have a resource plan, then consider enabling the Resource Manager with the system-supplied DEFAULT_PLAN.

Assumptions

This tutorial assumes that you want to do the following:

• Enable concurrent statistics gathering

• Gather statistics for the sh schema

• Monitor the gathering of the sh statistics

To enable concurrent statistics gathering:

1. Connect SQL*Plus to the database with the appropriate privileges, and then enable the Resource Manager.

   The following example uses the default plan for the Resource Manager:

   ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN';
   

2. Set the JOB_QUEUE_PROCESSES initialization parameter to at least twice the number of CPU cores.

   In Oracle Real Application Clusters, the JOB_QUEUE_PROCESSES setting applies to each node.

   Assume that the system has 4 CPU cores. The following example sets the parameter to 8 (twice the number of cores):

   ALTER SYSTEM SET JOB_QUEUE_PROCESSES=8;
   

3. Confirm that the parameter change took effect.

   For example, enter the following command in SQL*Plus (sample output included):

   SHOW PARAMETER PROCESSES;
   
   NAME                             TYPE        VALUE
   -------------------------------- ----------- -----
   _high_priority_processes         string      VKTM
   aq_tm_processes                  integer     1
   db_writer_processes              integer     1
   gcs_server_processes             integer     0
   global_txn_processes             integer     1
   job_queue_processes              integer     8
   log_archive_max_processes        integer     4
   processes                        integer     100
   

4. Enable concurrent statistics.

   For example, execute the following PL/SQL anonymous block:

   BEGIN
     DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','ALL');
   END;
   /
   

5. Confirm that the statistics were enabled.

   For example, execute the following query (sample output included):

   SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;
   
   DBMS_STATS.GET_PREFS('CONCURRENT')
   ----------------------------------
   ALL
   

6. Gather the statistics for the SH schema.

   For example, execute the following procedure:

   EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SH');
   

7. In a separate session, monitor the job progress by querying DBA_OPTSTAT_OPERATION_TASKS.

   For example, execute the following query (sample output included):

   SET LINESIZE 1000
    
   COLUMN TARGET FORMAT a8
   COLUMN TARGET_TYPE FORMAT a25
   COLUMN JOB_NAME FORMAT a14
   COLUMN START_TIME FORMAT a40
    
   SELECT TARGET, TARGET_TYPE, JOB_NAME, 
          TO_CHAR(START_TIME, 'dd-mon-yyyy hh24:mi:ss')
   FROM   DBA_OPTSTAT_OPERATION_TASKS 
   WHERE  STATUS = 'IN PROGRESS' 
   AND    OPID = (SELECT MAX(ID) 
                  FROM   DBA_OPTSTAT_OPERATIONS 
                  WHERE  OPERATION = 'gather_schema_stats');
   
   TARGET    TARGET_TYPE               JOB_NAME       TO_CHAR(START_TIME,'
   --------- ------------------------- -------------- --------------------
   SH.SALES  TABLE (GLOBAL STATS ONLY) ST$T292_1_B29  30-nov-2012 14:22:47
   SH.SALES  TABLE (COORDINATOR JOB)   ST$SD290_1_B10 30-nov-2012 14:22:08
   

8. In the original session, disable concurrent statistics gathering.

   For example, execute the following query:

   EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','OFF');

The following views are relevant:

• DBA_OPTSTAT_OPERATION_TASKS

  This view contains the history of tasks that are performed or currently in progress as part of statistics gathering operations (recorded in DBA_OPTSTAT_OPERATIONS). Each task represents a target object to be processed in the corresponding parent operation.

• DBA_OPTSTAT_OPERATIONS

  This view contains a history of statistics operations performed or currently in progress at the table, schema, and database level using the DBMS_STATS package.

The TARGET column in the preceding views shows the target object for that statistics gathering job in the following form:

OWNER.TABLE_NAME.PARTITION_OR_SUBPARTITION_NAME

All statistics gathering job names start with the string ST$.

To display currently running statistics tasks and jobs:

• To list statistics gathering currently running tasks from all user sessions, use the following SQL statement (sample output included):

  SELECT OPID, TARGET, JOB_NAME, 
         (SYSTIMESTAMP - START_TIME) AS elapsed_time
  FROM   DBA_OPTSTAT_OPERATION_TASKS
  WHERE  STATUS = 'IN PROGRESS';
  
  OPID TARGET                    JOB_NAME      ELAPSED_TIME
  ---- ------------------------- ------------- --------------------------
   981 SH.SALES.SALES_Q4_1998    ST$T82_1_B29  +000000000 00:00:00.596321
   981 SH.SALES                  ST$SD80_1_B10 +000000000 00:00:27.972033
  

To display completed statistics tasks and jobs:

• To list only completed tasks and jobs from a particular operation, first identify the operation ID from the DBA_OPTSTAT_OPERATIONS view based on the statistics gathering operation name, target, and start time. After you identify the operation ID, you can query the DBA_OPTSTAT_OPERATION_TASKS view to find the corresponding tasks in that operation

  For example, to list operations with the ID 981, use the following commands in SQL*Plus (sample output included):

  VARIABLE id NUMBER
  EXEC :id := 981
  
  SELECT TARGET, JOB_NAME, (END_TIME - START_TIME) AS ELAPSED_TIME
  FROM   DBA_OPTSTAT_OPERATION_TASKS
  WHERE  STATUS <> 'IN PROGRESS'
  AND    OPID = :id;
  
  TARGET                    JOB_NAME      ELAPSED_TIME
  ------------------------- ------------- --------------------------
  SH.SALES_TRANSACTIONS_EXT               +000000000 00:00:45.479233
  SH.CAL_MONTH_SALES_MV     ST$SD88_1_B10 +000000000 00:00:45.382764 
  SH.CHANNELS               ST$SD88_1_B10 +000000000 00:00:45.307397
  

To display statistics gathering tasks and jobs that have failed:

• Use the following SQL statement (partial sample output included):

  SET LONG 10000
  
  SELECT TARGET, JOB_NAME AS NM,
         (END_TIME - START_TIME) AS ELAPSED_TIME, NOTES
  FROM   DBA_OPTSTAT_OPERATION_TASKS
  WHERE  STATUS = 'FAILED';
  
  TARGET             NM ELAPSED_TIME               NOTES
  ------------------ -- -------------------------- -----------------
  SYS.OPATCH_XML_INV    +000000007 02:36:31.130314 <error>ORA-20011:
                                                   Approximate NDV
                                                   failed: ORA-29913:
                                                   error in

Oracle Database provides the following support for incremental statistics maintenance:

• If a partition maintenance operation triggers statistics gathering, then the database can reuse synopses that would previously have been dropped with the old segments.

• DBMS_STATS can create a synopsis on a nonpartitioned table. The synopsis enables the database to maintain incremental statistics as part of a partition exchange operation without having to explicitly gather statistics on the partition after the exchange.

When the DBMS_STATS preference INCREMENTAL is set to true on a table, the INCREMENTAL_LEVEL preference controls which synopses are collected and when. This preference takes the following values:

• TABLE

  DBMS_STATS gathers table-level synopses on this table. You can only set INCREMENTAL_LEVEL to TABLE at the table level, not at the schema, database, or global level.

• PARTITION (default)

  DBMS_STATS only gathers synopsis at the partition level of partitioned tables.

When performing a partition exchange, to have synopses after the exchange for the partition being exchanged, set INCREMENTAL to true and INCREMENTAL_LEVEL to TABLE on the table to be exchanged with the partition.

Assumptions

This tutorial assumes the following:

• You want to load empty partition p_sales_01_2010 in a sales table.

• You create a staging table t_sales_01_2010, and then populate the table.

• You want the database to maintain incremental statistics as part of the partition exchange operation without having to explicitly gather statistics on the partition after the exchange.

To maintain incremental statistics as part of a partition exchange operation:

1. Set incremental statistics preferences for staging table t_sales_01_2010.

   For example, run the following statement:

   BEGIN
     DBMS_STATS.SET_TABLE_PREFS (   
         ownname  =>  'sh'
   ,     tabname  =>  't_sales_01_2010'
   ,     pname    =>  'INCREMENTAL'
   ,     pvalue   =>  'true'
   ); 
     DBMS_STATS.SET_TABLE_PREFS (  
         ownname  =>  'sh'
   ,     tabname  =>  't_sales_01_2010'
   ,     pname    =>  'INCREMENTAL_LEVEL'
   ,     pvalue   =>  'table'
   );
   END;
   

2. Gather statistics on staging table t_sales_01_2010.

   For example, run the following PL/SQL code:

   BEGIN
     DBMS_STATS.GATHER_TABLE_STATS ( 
         ownname  => 'SH'
   ,     tabname  => 'T_SALES_01_2010'
   );
   END;
   /
   

   DBMS_STATS gathers table-level synopses on t_sales_01_2010.

3. Ensure that the INCREMENTAL preference is true on the sh.sales table.

   For example, run the following PL/SQL code:

   BEGIN
     DBMS_STATS.SET_TABLE_PREFS (
         ownname  =>  'sh'
   ,     tabname  =>  'sales'
   ,     pname    =>  'INCREMENTAL'
   ,     pvalue   =>  'true'
   );
   END;
   /
   

4. If you have never gathered statistics on sh.sales before with INCREMENTAL set to true, then gather statistics on the partition to be exchanged.

   For example, run the following PL/SQL code:

   BEGIN
     DBMS_STATS.GATHER_TABLE_STATS (
         ownname  =>  'sh'
   ,     tabname  =>  'sales'
   ,     pname    =>  'p_sales_01_2010'
   ,     pvalue   =>  granularity=>'partition'
   );
   END;
   /
   

5. Perform the partition exchange.

   For example, use the following SQL statement:

   ALTER TABLE sales EXCHANGE PARTITION p_sales_01_2010 WITH TABLE t_sales_01_2010;
   

   After the exchange, the partitioned table has both statistics and a synopsis for partition p_sales_01_2010.

   In releases before Oracle Database 12c, the preceding statement swapped the segment data and statistics of p_sales_01_2010 with t_sales_01_2010. The database did not maintain synopses for nonpartitioned tables such as t_sales_01_2010. To gather global statistics on the partitioned table, you needed to rescan the p_sales_01_2010 partition to obtain its synopses.

When incremental statistics are enabled in releases before Oracle Database 12c, if any DML occurs on a partition, then the optimizer considers statistics on this partition to be stale. Thus, DBMS_STATS must gather the statistics again to accurately aggregate the global statistics. Furthermore, if DML occurs on a partition whose statistics are locked, then DBMS_STATS cannot regather the statistics on the partition, so a full table scan is the only means of gathering global statistics. Regathering statistics creates performance overhead.

In Oracle Database 12c, the statistics preference INCREMENTAL_STALENESS controls how the database determines whether the statistics on a partition or subpartition are stale. This preference takes the following values:

• USE_STALE_PERCENT

  A partition or subpartition is not considered stale if DML changes are less than the STALE_PERCENT preference specified for the table. The default value of STALE_PERCENT is 10, which means that if DML causes more than 10% of row changes, then the table is considered stale.

• USE_LOCKED_STATS

  Locked partition or subpartition statistics are not considered stale, regardless of DML changes.

• NULL (default)

  A partition or subpartition is considered stale if it has any DML changes. This behavior is identical to the Oracle Database 11g behavior. When the default value is used, statistics gathered in incremental mode are guaranteed to be the same as statistics gathered in nonincremental mode. When a nondefault value is used, statistics gathered in incremental mode might be less accurate than those gathered in nonincremental mode.

You can specify USE_STALE_PERCENT and USE_LOCKED_STATS together. For example, you can write the following anonymous block:

BEGIN
  DBMS_STATS.SET_TABLE_PREFS (
    ownname     => null
,   table_name  => 't'
,   pname       => 'incremental_staleness'
,   pvalue      => 'use_stale_percent,use_locked_stats'
);
END;

Assumptions

This tutorial assumes the following:

• The STALE_PERCENT for a partitioned table is set to 10.

• The INCREMENTAL value is set to true.

• The table has had statistics gathered in INCREMENTAL mode before.

• You want to discover how statistics gathering changes depending on the setting for INCREMENTAL_STALENESS, whether the statistics are locked, and the percentage of DML changes.

To test for tables with stale or locked partition statistics:

1. Set INCREMENTAL_STALENESS to NULL.

   Afterward, 5% of the rows in one partition change because of DML activity.

2. Use DBMS_STATS to gather statistics on the table.

   DBMS_STATS regathers statistics for the partition that had the 5% DML activity, and incrementally maintains the global statistics.

3. Set INCREMENTAL_STALENESS to USE_STALE_PERCENT.

   Afterward, 5% of the rows in one partition change because of DML activity.

4. Use DBMS_STATS to gather statistics on the table.

   DBMS_STATS does not regather statistics for the partition that had DML activity (because the changes are under the staleness threshold of 10%), and incrementally maintains the global statistics.

5. Lock the partition statistics.

   Afterward, 20% of the rows in one partition change because of DML activity.

6. Use DBMS_STATS to gather statistics on the table.

   DBMS_STATS does not regather statistics for the partition because the statistics are locked. The database gathers the global statistics with a full table scan.

   Afterward, 5% of the rows in one partition change because of DML activity.

7. Use DBMS_STATS to gather statistics on the table.

   When you gather statistics on this table, DBMS_STATS does not regather statistics for the partition because they are not considered stale. The database maintains global statistics incrementally using the existing statistics for this partition.

8. Set INCREMENTAL_STALENESS to USE_LOCKED_STATS and USE_STALE_PERCENT.

   Afterward, 20% of the rows in one partition change because of DML activity.

9. Use DBMS_STATS to gather statistics on the table.

   Because USE_LOCKED_STATS is set, DBMS_STATS ignores the fact that the statistics are stale and uses the locked statistics. The database maintains global statistics incrementally using the existing statistics for this partition.