12 Managing Optimizer Statistics: Basic Topics

When setting statistics preferences, the order of precedence is:

1. Table preference (set for a specific table, all tables in a schema, or all tables in the database)

2. Global preference

3. Default preference

The following table summarizes the relevant DBMS_STATS procedures.

Table 12-2 illustrates the relationship between SET_TABLE_PREFS, SET_SCHEMA_STATS, and SET_DATABASE_PREFS.

SQL> SELECT DBMS_STATS.GET_PREFS 
('incremental', 'sh','costs') 
AS "STAT_PREFS" FROM DUAL;
 
STAT_PREFS
----------
TRUE

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS 
('sh', 'costs', 'incremental', 'false');
 
PL/SQL procedure successfully completed.

SQL> SELECT DBMS_STATS.GET_PREFS 
('incremental', 'sh', 'costs') 
AS "STAT_PREFS" FROM DUAL;

STAT_PREFS 
----------
FALSE

SQL> EXEC DBMS_STATS.SET_SCHEMA_PREFS 
('sh', 'incremental', 'true');

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_STATS.GET_PREFS 
('incremental', 'sh', 'costs') 
AS "STAT_PREFS" FROM DUAL;
 
STAT_PREFS
----------
TRUE

SQL> EXEC DBMS_STATS.SET_DATABASE_PREFS 
('incremental', 'false');

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_STATS.GET_PREFS 
('incremental', 'sh', 'costs') 
AS "STAT_PREFS" FROM DUAL;

STAT_PREFS
----------
FALSE

The database gathers the most accurate statistics when it processes all rows in the table, which is a 100% sample. However, the larger the sample size, the longer the statistics gathering operation. The problem 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 you set the ESTIMATE_PERCENT parameter to DBMS_STATS.AUTO_SAMPLE_SIZE (the default). This setting enables the use of:

• 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 (see "Hybrid Histograms")

By default, the database gathers statistics with the parallelism degree specified at the table or index level. 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.

For partitioned tables and indexes, DBMS_STATS can gather separate statistics for each partition and global statistics for the entire table or index. Similarly, for composite partitioning, DBMS_STATS can gather separate statistics for subpartitions, partitions, and the entire table or index.

Use the granularity argument to the DBMS_STATS procedures to determine the type of partitioning statistics to be gathered. 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.

When tables are frequently modified, gather statistics often enough so that they do not go stale, but not so often that collection overhead degrades performance. 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.

Because the database does not permit data manipulation against external tables, the database never marks statistics on external tables as stale. If new statistics are required for an external table, for example, because the underlying data files change, then regather the statistics. Gather statistics manually for external tables with the same procedures that you use for regular tables.

To enable concurrent statistics gathering, use the DBMS_STATS.SET_GLOBAL_PREFS procedure to set the CONCURRENT preference. 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 (see Oracle Database Administrator's Guide).

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');
   

To configure the system for parallel execution and concurrent statistics gathering:

1. Connect SQL*Plus to the database with the administrator privileges.

2. Disable the parallel adaptive multiuser initialization parameter.

   For example, use the following SQL statement:

   ALTER SYSTEM SET PARALLEL_ADAPTIVE_MULTI_USER=false;
   

3. Configure the database to use a resource plan that has parallel queuing enabled.

   Perform the following steps:

   1. If Oracle Database Resource Manager (the Resource Manager) is not activated, then activate it. By default, the Resource Manager is activated only during the maintenance windows.

   2. Create a temporary resource plan in which the consumer group OTHER_GROUPS has queuing enabled.

   The following sample script illustrates one way to create a temporary resource plan (pqq_test), and enable the Resource Manager with this plan:

   BEGIN
     DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
     DBMS_RESOURCE_MANAGER.CREATE_PLAN('pqq_test', 'pqq_test');
     DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
       'pqq_test'
   ,   'OTHER_GROUPS'
   ,   'OTHER_GROUPS directive for pqq'
   ,   parallel_server_limit            => 90
   ,   max_utilization_limit            => 90
   );
     DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
   END;
   /
   ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'pqq_test' SID='*';
   

To monitor statistics gathering jobs, use the following views:

• 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_NAnME, 
         (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 := 985
  
  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,
         (END_TIME - START_TIME) AS ELAPSED_TIME, NOTES
  FROM   DBA_OPTSTAT_OPERATION_TASKS
  WHERE  STATUS = 'FAILED';
  
  TARGET             JOB_NAME ELAPSED_TIME               NOTES
  ------------------ -------- -------------------------- ----------------------
  SYS.OPATCH_XML_INV          +000000007 02:36:31.130314 <error>ORA-20011:
                                                         Approximate NDV failed: 
                                                         ORA-29913: error in
  .
  .
  .
  

A partition maintenance operation is a partition-related operation such as adding, exchanging, merging, or splitting table partitions. Oracle Database 12c introduces the following enhancements for maintaining incremental statistics:

• 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 (   
       'sh'
   ,   't_sales_01_2010'
   ,   'INCREMENTAL'
   ,   'true'
   ); 
     DBMS_STATS.SET_TABLE_PREFS (  
       'sh'
   ,   't_sales_01_2010'
   ,   'INCREMENTAL_LEVEL'
   ,   '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 (
       'sh'
   ,   'sales'
   ,   'INCREMENTAL'
   ,   '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 (
       'sh'
   ,   'sales'
   ,   'p_sales_01_2010'
   ,   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.

Starting in Oracle Database 12c, incremental statistics can automatically calculate global statistics for a partitioned table even if the partition or subpartition statistics are stale and locked.

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. The necessity to regather 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 (
    null
,   't'
,   'incremental_staleness'
,   '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.

This tutorial explains how to set the workload interval with the START and STOP parameters of GATHER_SYSTEM_STATS.

Assumptions

This tutorial assumes the following:

• The hour between 10 a.m. and 11 a.m. is representative of the daily workload.

• You intend to collect system statistics directly in the data dictionary.

To gather workload statistics using START and STOP:

1. Start SQL*Plus and connect to the database with administrator privileges.

2. Start statistics collection.

   For example, at 10 a.m., execute the following procedure to start collection:

   EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode => 'START' );
   

3. Generate the workload.

4. End statistics collection.

   For example, at 11 a.m., execute the following procedure to end collection:

   EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode => 'STOP' );
   

   The optimizer can now use the workload statistics to generate execution plans that are effective during the normal daily workload.

5. Optionally, query the system statistics.

   For example, run the following query:

   COL PNAME FORMAT a15
   SELECT PNAME, PVAL1 
   FROM   SYS.AUX_STATS$ 
   WHERE  SNAME = 'SYSSTATS_MAIN';
   

This tutorial explains how to set the workload interval with the INTERVAL parameter of GATHER_SYSTEM_STATS.

Assumptions

This tutorial assumes the following:

• The database application processes OLTP transactions during the day and runs OLAP reports at night. To gather representative statistics, you collect them during the day for two hours and then at night for two hours.

• You want to store statistics in a table named workload_stats.

• You intend to switch between the statistics gathered.

To gather workload statistics using INTERVAL:

1. Start SQL*Plus and connect to the production database as administrator dba1.

2. Create a table to hold the production statistics.

   For example, execute the following PL/SQL program to create user statistics table workload_stats:

   BEGIN
     DBMS_STATS.CREATE_STAT_TABLE (
       ownname => 'dba1'
   ,   stattab => 'workload_stats'
   );
   END;
   /
   

3. Ensure that JOB_QUEUE_PROCESSES is not 0 so that DBMS_JOB jobs and Oracle Scheduler jobs run.

   ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 1;
   

4. Gather statistics during the day.

   For example, gather statistics for two hours with the following program:

   BEGIN
     DBMS_STATS.GATHER_SYSTEM_STATS ( 
       interval => 120
   ,   stattab  => 'workload_stats'
   ,   statid   => 'OLTP' 
   );
   END;
   /
   

5. Gather statistics during the evening.

   For example, gather statistics for two hours with the following program:

   BEGIN
     DBMS_STATS.GATHER_SYSTEM_STATS (
       interval => 120
   ,   stattab  => 'workload_stats' 
   ,   statid   => 'OLAP' 
   );
   END;
   /
   

6. In the day or evening, import the appropriate statistics into the data dictionary.

   For example, during the day you can import the OLTP statistics from the staging table into the dictionary with the following program:

   BEGIN
     EXECUTE DBMS_STATS.IMPORT_SYSTEM_STATS (
       stattab => 'workload_stats'
   ,   statid  => 'OLTP' 
   );
   END;
   /
   

   For example, during the night you can import the OLAP statistics from the staging table into the dictionary with the following program:

   BEGIN
     EXECUTE DBMS_STATS.IMPORT_SYSTEM_STATS (
       stattab => 'workload_stats'
   ,   statid  => 'OLAP' 
   );
   END;
   /