12 Managing Optimizer Statistics: Basic Topics

12.1.2.1 Graphical Interface for Optimizer Statistics Management

The Manage Optimizer Statistics page in Cloud Control is a GUI that enables you to manage optimizer statistics.

12.1.2.2 Command-Line Interface for Optimizer Statistics Management

You can use the DBMS_STATS package to perform most optimizer statistics tasks. Use the DBMS_AUTO_TASK_ADMIN PL/SQL package to enable and disable automatic statistics gathering.

12.3.1.1 Procedures for Setting Statistics Gathering Preferences

Table 12-1 summarizes the DBMS_STATS procedures that change the defaults of parameters used by the DBMS_STATS.GATHER_*_STATS procedures. Parameter values set in DBMS_STAT.GATHER_*_STATS override other settings. If a parameter has not been set, then the database checks for a table-level preference. If no table preference exists, then the database uses the global preference. See Oracle Database PL/SQL Packages and Types Reference for descriptions of CASCADE, METHOD_OPT, and the other parameters.

12.3.1.2 Setting Statistics Preferences: Example

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') FROM DUAL;
 
DBMS_STATS.GET_PREFS('INCREMENTAL','SH','COSTS')
------------------------------------------------
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') FROM DUAL;
 
DBMS_STATS.GET_PREFS('INCREMENTAL','SH','COSTS')
------------------------------------------------
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') FROM DUAL;
 
DBMS_STATS.GET_PREFS('INCREMENTAL','SH','COSTS')
------------------------------------------------
TRUE

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

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_STATS.GET_PREFS ('incremental', 'sh',
 'costs') FROM DUAL;
 
DBMS_STATS.GET_PREFS('INCREMENTAL','SH','COSTS')
------------------------------------------------
FALSE

12.4.2.1 Guideline for Accurate Statistics

In the context of optimizer statistics, sampling is the gathering of statistics from a random subset of table rows. By enabling the database to avoid full table scans and sorts of entire tables, sampling minimizes the resources necessary to gather statistics.

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 the ESTIMATE_PERCENT parameter be set to DBMS_STATS.AUTO_SAMPLE_SIZE (the default). With this setting, the database uses 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.

12.4.2.2 Guideline for Gathering Statistics in Parallel

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.

12.4.2.3 Guideline for Partitioned Objects

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.

12.4.2.4 Guideline for Frequently Changing Objects

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.

12.4.2.5 Guideline for External Tables

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.

12.4.7.1 About Concurrent Statistics Gathering

When concurrent statistics gathering mode is enabled, the database can simultaneously gather optimizer statistics for the following:

• Multiple tables in a schema

• Multiple partitions or subpartitions in a table

Concurrency can reduce the overall time required to gather statistics by enabling the database to fully use multiple CPUs.

12.4.7.1.1 How DBMS_STATS Gathers Statistics Concurrently

Oracle Database employs the following tools and technologies to create and manage multiple statistics gathering jobs concurrently:

• Oracle Scheduler

• Oracle Database Advanced Queuing (AQ)

• Oracle Database Resource Manager (the Resource Manager)

Enable concurrent statistics gathering by setting the CONCURRENT preference with DBMS_STATS.SET_GLOBAL_PREF (see "Enabling Concurrent Statistics Gathering").

The database runs as many concurrent jobs as possible. The Job Scheduler decides how many jobs to execute concurrently and how many to queue. As running jobs complete, the scheduler dequeues and runs more jobs until the database has gathered statistics on all tables, partitions, and subpartitions. The maximum number of jobs is bounded by the JOB_QUEUE_PROCESSES initialization parameter and available system resources.

In most cases, the DBMS_STATS procedures create a separate job for each table partition or subpartition. However, if the partition or subpartition is very small or empty, the database may automatically batch the object with other small objects into a single job to reduce the overhead of job maintenance.

The following figure illustrates the creation of jobs at different levels, where Table 3 is a partitioned table, and the other tables are nonpartitioned. Job 3 acts as a coordinator job for Table 3, and creates a job for each partition in that table, and a separate job for the global statistics of Table 3. This example assumes that incremental statistics gathering is disabled; if enabled, then the database derives global statistics from partition-level statistics after jobs for partitions complete.

Description of the illustration tgsql_vm_036.png

See Also:

• Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_STATS package

• Oracle Database Reference to learn about the JOB_QUEUE_PROCESSES initialization parameter

12.4.7.2 Enabling Concurrent Statistics Gathering

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

12.4.7.3 Configuring the System for Parallel Execution and Concurrent Statistics Gathering

When CONCURRENT statistics gathering is enabled, you can execute each statistics gathering job in parallel. This combination is useful when you are analyzing large tables, partitions, or subpartitions.

The following procedure describes the recommended configuration.

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. Enable parallel statement queuing.

   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:

   -- connect as a user with dba privileges
   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_target_percentage       => 90
   ,   max_utilization_limit            => 90
   );
     DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
   END;
   /
   ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'pqq_test' SID='*';
   

12.4.7.4 Monitoring Statistics Gathering Operations

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
  .
  .
  .
  

12.4.8.1 Purpose of Incremental Statistics

In a typical case, an application loads data into a new partition of a range-partitioned table. As applications add new partitions and load data, the database must gather statistics on the new partition and keep global statistics up to date.

Without incremental statistics, statistics collection typically uses a two-pass approach:

1. The database scans the table to gather the global statistics.

2. The database scans the changed partitions to gather their partition-level statistics.

The full scan of the table for global statistics collection can be very expensive, depending on the size of the table. As the table adds partitions, the longer the execution time for GATHER_TABLE_STATS because of the full table scan required for the global statistics. The database must perform the scan of the entire table even if only a small subset of partitions change. In contrast, incremental statistics enable the database to avoid these full table scans.

12.4.8.2 How Incremental Statistics Maintenance Derives Global Statistics

Starting in Oracle Database 11g, the database avoids a full table scan when computing global statistics by deriving global statistics from the partition statistics. The database can accurately derive some statistics from partition statistics. For example, the number of rows at the global level is the sum of number of rows of partitions. Even global histograms can be derived from partition histograms.

However, the database cannot derive all statistics from partition-level statistics. For example, the database cannot derive the NDV of a column from partition-level NDVs. So, the database maintains a structure called a synopsis for each column at the partition level. A synopsis can be viewed as a sample of distinct values. The database can accurately derive the NDV for each column by merging partition-level synopses.

When incremental statistics maintenance is enabled, the database does the following:

• Gathers statistics and creates synopses for changed partitions only

• Merges partition-level synopses into a global synopsis automatically

• Derives global statistics automatically from the partition-level statistics and global synopses

Example 12-3 Deriving Global Statistics

The following graphic shows how the database gathers statistics for the initial six partitions of the sales table, and then creates synopses for each partition (S1, S2, and so on). The database creates global statistics by aggregating the partition-level statistics and synopses.

Description of the illustration tgsql_vm_074.png

The following graphic shows a new partition, containing data for May 24, being added to the sales table. The database gathers statistics for the newly added partition, retrieves synopses for the other partitions, and then aggregates the synopses to create global statistics.

Description of the illustration tgsql_vm_075.png

12.4.8.3 How to Enable Incremental Statistics Maintenance

Use DBMS_STATS.SET_TABLE_PREFS to set the INCREMENTAL value, and in this way control incremental statistics maintenance. When INCREMENTAL is set to false (default), the database always uses a full table scan to maintain global statistics. When the following criteria are met, the database updates global statistics incrementally by scanning only the partitions that have changed:

• The INCREMENTAL value for the partitioned table is true.

• The PUBLISH value for the partitioned table is true.

• The user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.

Enabling incremental statistics maintenance has the following consequences:

• The SYSAUX tablespace consumes additional space to maintain global statistics for partitioned tables.

• If a table uses composite partitioning, then the database only gathers statistics for modified subpartitions. The database does not gather statistics at the subpartition level for unmodified subpartitions. In this way, the database reduces work by skipping unmodified partitions.

• If a table uses incremental statistics, and if this table has a locally partitioned index, then the database gathers index statistics at the global level and for modified (not unmodified) index partitions. The database does not generate global index statistics from the partition-level index statistics. Rather, the database gathers global index statistics by performing a full index scan.

12.4.8.4 Maintaining Incremental Statistics for Partition Maintenance Operations

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.

12.4.8.5 Maintaining Incremental Statistics for Tables with Stale or Locked Partition Statistics

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.

12.5.3.1 About Workload Statistics

Workload statistics include the following statistics listed in Table 12-4:

• Single block (sreadtim) and multiblock (mreadtim) read times

• Multiblock count (mbrc)

• CPU speed (cpuspeed)

• Maximum system throughput (maxthr)

• Average parallel execution throughput (slavethr)

The database computes sreadtim, mreadtim, and mbrc by comparing the number of physical sequential and random reads between two points in time from the beginning to the end of a workload. The database implements these values through counters that change when the buffer cache completes synchronous read requests.

Because the counters are in the buffer cache, they include not only I/O delays, but also waits related to latch contention and task switching. Thus, workload statistics depend on system activity during the workload window. If system is I/O bound (both latch contention and I/O throughput), then the statistics promote a less I/O-intensive plan after the database uses the statistics.

As shown in Figure 12-1, if you gather workload statistics, then the optimizer uses the mbrc value gathered for workload statistics to estimate the cost of a full table scan.

Figure 12-1 Workload Statistics Counters

Description of "Figure 12-1 Workload Statistics Counters"

When gathering workload statistics, the database may not gather the mbrc and mreadtim values if no table scans occur during serial workloads, as is typical of OLTP systems. However, full table scans occur frequently on DSS systems. These scans may run parallel and bypass the buffer cache. In such cases, the database still gathers the sreadtim because index lookups use the buffer cache.

If the database cannot gather or validate gathered mbrc or mreadtim values, but has gathered sreadtim and cpuspeed, then the database uses only sreadtim and cpuspeed for costing. In this case, the optimizer uses the value of the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT to cost a full table scan. However, if DB_FILE_MULTIBLOCK_READ_COUNT is 0 or is not set, then the optimizer uses a value of 8 for calculating cost.

Use the DBMS_STATS.GATHER_SYSTEM_STATS procedure to gather workload statistics. The GATHER_SYSTEM_STATS procedure refreshes the data dictionary or a staging table with statistics for the elapsed period. To set the duration of the collection, use either of the following techniques:

• Specify START the beginning of the workload window, and then STOP at the end of the workload window.

• Specify INTERVAL and the number of minutes before statistics gathering automatically stops. If needed, you can use GATHER_SYSTEM_STATS (gathering_mode=>'STOP') to end gathering earlier than scheduled.

12.5.3.2 Using GATHER_SYSTEM_STATS with START and STOP

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

12.5.3.3 Using GATHER_SYSTEM_STATS with INTERVAL

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;
   /