13 Managing Optimizer Statistics

13.2.2.1 When to Use Manual Statistics

Automatic optimizer statistics collection should be sufficient for most database objects being modified at a moderate speed. However, in some cases the collection may not be adequate. Because the collection runs during maintenance windows, the statistics on tables that are significantly modified throughout the day may become stale. There are typically two types of such objects:

• Volatile tables that are deleted or truncated and rebuilt during the course of the day.

• Objects that are the target of large bulk loads which add 10% or more to the object's total size.

For highly volatile tables, there are two approaches:

• The statistics on these tables can be null. When Oracle Database encounters a table with no statistics, the database dynamically gathers the necessary statistics as part of query optimization. The OPTIMIZER_DYNAMIC_SAMPLING parameter controls this dynamic sampling feature. Set this parameter to a value of 2 (default) or higher. You can set the statistics to null by deleting and then locking the statistics:

  BEGIN
    DBMS_STATS.DELETE_TABLE_STATS('OE','ORDERS');
    DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS');
  END;
  /
  

  See "Dynamic Sampling Levels" for information about the sampling levels that you can set.

• The statistics on these tables can be set to values that represent the typical state of the table. You should gather statistics on the table when the tables has a representative number of rows, and then lock the statistics.

  This may be more effective than automatic optimizer statistic collection, because any statistics generated on the table during the overnight batch window may not be the most appropriate statistics for the daytime workload.

For tables that are bulk-loaded, run the the statistics-gathering procedures on the tables immediately following the load process. Preferably, run the procedures as part of the same script or job that is running the bulk load.

For external tables, statistics are not collected during GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS, and automatic optimizer statistics collection processing. However, you can collect statistics on an individual external table using GATHER_TABLE_STATS. Sampling on external tables is not supported, so you should explicitly set the ESTIMATE_PERCENT option to NULL. Because the database does not permit data manipulation against external tables, it is sufficient to analyze external tables when the corresponding file changes.

If the monitoring feature is disabled by setting STATISTICS_LEVEL to BASIC, then automatic optimizer statistics collection cannot detect stale statistics. In this case, you must manually gather statistics. See "Determining Stale Statistics" to learn about the automatic monitoring facility.

System statistics are another type of statistic that you must gather manually. The database does not gather these statistics automatically. See "System Statistics" for more information.

You must manually collect statistics on fixed objects, such as the dynamic performance tables, using GATHER_FIXED_OBJECTS_STATS procedure. Fixed objects record current database activity. You should gather statistics when the database has representative activity.

13.2.2.2 Restoring Previous Versions of Statistics

Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring. You can restore statistics using RESTORE procedures of DBMS_STATS package. See "Restoring Previous Versions of Statistics" for more information.

13.2.2.3 Locking Statistics

In some cases, you may want to prevent any new statistics from being gathered on a table or schema by the DBMS_STATS_JOB process, such as highly volatile tables discussed in "When to Use Manual Statistics". In those cases, the DBMS_STATS package provides procedures for locking the statistics for a table or schema. See "Locking Statistics for a Table or Schema" for more information.

13.3.1.1 Statistics Gathering Using Sampling

The statistics-gathering operations can utilize sampling to estimate statistics. Sampling is an important technique for gathering statistics. Gathering statistics without sampling requires full table scans and sorts of entire tables. Sampling minimizes the resources necessary to gather statistics.

Sampling is specified using the ESTIMATE_PERCENT argument to the DBMS_STATS procedures. While you can set the sampling percentage to any value, Oracle recommends setting the ESTIMATE_PERCENT parameter of the DBMS_STATS gathering procedures to DBMS_STATS.AUTO_SAMPLE_SIZE to maximize performance gains while achieving necessary statistical accuracy. AUTO_SAMPLE_SIZE lets Oracle Database determine the best sample size necessary for good statistics, based on the statistical property of the object. Because each type of statistics has different requirements, the size of the actual sample taken may not be the same across the table, columns, or indexes. For example, to collect table and column statistics for all tables in the OE schema with auto-sampling, you could use:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('OE',DBMS_STATS.AUTO_SAMPLE_SIZE);

When the ESTIMATE_PERCENT parameter is manually specified, the DBMS_STATS gathering procedures may automatically increase the sampling percentage if the specified percentage did not produce a large enough sample. This ensures the stability of the estimated values by reducing fluctuations.

13.3.1.2 Parallel Statistics Gathering

The statistics-gathering operations can run either serially or in parallel. You can specify the degree of parallelism with the DEGREE argument to the DBMS_STATS gathering procedures. The database can use parallel statistics gathering in conjunction with sampling. Oracle recommends setting the DEGREE parameter to DBMS_STATS.AUTO_DEGREE. This setting allows Oracle Database to choose an appropriate degree of parallelism based on the size of the object and the settings for the parallel-related init.ora parameters.

Note that certain types of index statistics are not gathered in parallel, including cluster indexes, domain indexes, and bitmap join indexes.

13.3.1.3 Statistics on 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. The type of partitioning statistics to be gathered is specified in the GRANULARITY argument to the DBMS_STATS gathering procedures.

Depending on the SQL statement being optimized, the optimizer can choose to use either the partition (or subpartition) statistics or the global statistics. Both types of statistics are important for most applications, and Oracle recommends setting the GRANULARITY parameter to AUTO to gather both types of partition statistics.

With partitioned tables, you usually add new data into a new partition. As you add new partitions and load data, you must gather statistics on the new partition and keep global statistics up to date. If the INCREMENTAL value for a partition table is set to TRUE, and if you gather statistics on that table with the GRANULARITY parameter set to AUTO, then the database gathers statistics on the new partition and updates the global table statistics by scanning only unmodified partitions. If the INCREMENTAL value for the partitioned table is set to FALSE (default value), then the database uses a full table scan to maintain the global statistics. This is a highly resource intensive and time consuming operation for large tables.

13.3.1.4 Column Statistics and Histograms

When gathering statistics on a table, DBMS_STATS gathers information about the data distribution of the columns within the table. The most basic information about the data distribution is the maximum value and minimum value of the column. However, this level of statistics may be insufficient for the optimizer's needs if the data within the column is skewed. For skewed data distributions, histograms can also be created as part of the column statistics to describe the data distribution of a given column. Histograms are described in more details in "Viewing Histograms".

Histograms are specified using the METHOD_OPT argument of the DBMS_STATS gathering procedures. Oracle recommends setting the METHOD_OPT to FOR ALL COLUMNS SIZE AUTO. With this setting, Oracle Database automatically determines which columns require histograms and the number of buckets (size) of each histogram. You can also manually specify which columns should have histograms and the size of each histogram.

13.3.1.5 Extended Statistics

Oracle Database can also gather statistics on a group of columns within a table or an expression on a column. For more details on these, refer to:

• MultiColumn Statistics

• Expression Statistics

13.3.1.6 MultiColumn Statistics

When the WHERE clause of a query specifies multiple columns from a single table (multiple single column predicates), the relationship between the columns can strongly affect the combined selectivity for the column group.

For example, consider the customers table in the SH schema. The columns cust_state_province and country_id are related, with cust_state_province determining the country_id for each customer. Suppose you query the customers table where the cust_state_province is California:

SELECT COUNT(*)
FROM   sh.customers 
WHERE  cust_state_province = 'CA';

The preceding query returns the following value:

COUNT(*)
----------
    3341

Adding an extra predicate on the country_id column does not change the result when the country_id is 52790 (United States of America). Assume that you run the following query:

SELECT COUNT(*) 
FROM   customers 
WHERE  cust_state_province = 'CA' 
AND    country_id=52790;

The preceding query returns the same value as the previous query:

COUNT(*)
----------
    3341

Assume that the country_id has a different value, such as 52775 (Brazil), as in the following query:

SELECT COUNT(*) 
FROM   customers 
WHERE  cust_state_province = 'CA' 
AND    country_id=52775;

In this case the returned value is as follows:

COUNT(*)
----------
       0

With individual column statistics, the optimizer has no way of knowing that the cust_state_province and the country_id columns are related. By gathering statistics on these columns as a group (column group), the optimizer has a more accurate selectivity value for the group, instead of having to generate the value based on the individual column statistics.

By default, Oracle Database creates column groups for a table based on the workload analysis, similar to the case of histograms. You can also create column groups manually by using the DBMS_STATS package. You can use this package to create a column group, get the name of a column group, or delete a column group from a table.

DECLARE
  cg_name varchar2(30);
BEGIN
  cg_name := dbms_stats.create_extended_stats(null,'customers',  
             '(cust_state_province,country_id)');
END;
/

select sys.dbms_stats.show_extended_stats_name('sh','customers',
       '(cust_state_province,country_id)') col_group_name 
from dual;

COL_GROUP_NAME
----------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM

exec dbms_stats.drop_extended_stats('sh','customers','(cust_state_province,country_id)');

Select extension_name, extension 
from user_stat_extensions 
where table_name='CUSTOMERS';

EXTENSION_NAME                     EXTENSION
-------------------------------------------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_     ("CUST_STATE_PROVINCE","COUNTRY_ID")

select e.extension col_group, t.num_distinct, t.histogram
  2  from user_stat_extensions e, user_tab_col_statistics t
  3  where e.extension_name=t.column_name
  4  and e.table_name=t.table_name
  5  and t.table_name='CUSTOMERS';

COL_GROUP                             NUM_DISTINCT        HISTOGRAM
-------------------------------------------------------------------------------
("COUNTRY_ID","CUST_STATE_PROVINCE")  145                 FREQUENCY

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',METHOD_OPT =>
'FOR ALL COLUMNS SIZE SKEWONLY 
FOR COLUMNS (CUST_STATE_PROVINCE,COUNTRY_ID) SIZE SKEWONLY');

13.3.1.7 Expression Statistics

When a function is applied to a column in the where clause of a query (function(col1)=constant), the optimizer has no way of knowing how that function affects the selectivity of the column. By gathering expression statistics on the expression function(col1), the optimizer obtains a more accurate selectivity value.

An example of such a function is:

SELECT COUNT(*)
FROM   CUSTOMERS
WHERE  LOWER(CUST_STATE_PROVINCE)='CA';

exec dbms_stats.gather_table_stats('sh','customers', method_opt =>
'for all columns size skewonly 
 for columns (lower(cust_state_province)) size skewonly');

select 
dbms_stats.create_extended_stats(null,'customers','(lower(cust_state_province))') 
from dual;

Select extension_name, extension 
from user_stat_extensions 
where table_name='CUSTOMERS';

EXTENSION_NAME                    EXTENSION
------------------------------------------------------------------------
SYS_STUBPHJSBRKOIK9O2YV3W8HOUE    (LOWER("CUST_STATE_PROVINCE"))

select e.extension col_group, t.num_distinct, t.histogram
  2  from user_stat_extensions e, user_tab_col_statistics t
  3  where e.extension_name=t.column_name
  4  and t.table_name='CUSTOMERS';

COL_GROUP                        NUM_DISTINCT          HISTOGRAM
------------------------------------------------------------------------
(LOWER("CUST_STATE_PROVINCE"))   145                   FREQUENCY

exec dbms_stats.drop_extended_stats(null,'customers','(lower(country_id))');

13.3.1.8 Determining Stale Statistics

You must regularly gather statistics on database objects as these database objects are modified over time. To determine whether a given database object needs new database statistics, Oracle Database provides a table monitoring facility. This monitoring is enabled by default when STATISTICS_LEVEL is set to TYPICAL or ALL.

Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table and whether the table has been truncated since the last time statistics were gathered. You can access information about changes of tables in the USER_TAB_MODIFICATIONS view. Following a data-modification, there may be a few minutes delay while Oracle Database propagates the information to this view. Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.

The GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS procedures gather new statistics for tables with stale statistics when the OPTIONS parameter is set to GATHER STALE or GATHER AUTO. If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.

13.3.1.9 User-Defined Statistics

You can create user-defined optimizer statistics to support user-defined indexes and functions. When you associate a statistics type with a column or domain index, Oracle Database calls the statistics collection method in the statistics type whenever statistics are gathered for database objects.

You should gather new column statistics on a table after creating a function-based index to allow Oracle Database to collect column statistics equivalent information for the expression. You can perform this task by calling the statistics-gathering procedure with the METHOD_OPT argument set to FOR ALL HIDDEN COLUMNS.

13.4.1.1 Gathering Workload Statistics

To gather workload statistics, perform either of the following tasks:

• Run the DBMS_STATS.GATHER_SYSTEM_STATS('start') procedure at the beginning of the workload window, then the DBMS_STATS.GATHER_SYSTEM_STATS('stop') procedure at the end of the workload window.

• Run DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval=>N) where N is the number of minutes when statistics gathering is stopped automatically.

To delete system statistics, run dbms_stats.delete_system_stats(). Workload statistics are deleted and reset to the default noworkload statistics.

13.4.1.2 Multiblock Read Count

If you gather workload statistics, then the mbrc value gathered as part of the workload statistics is used to estimate the cost of a full table scan. However, during the gathering process of workload statistics, Oracle Database may not gather the mbrc and mreadtim values if no table scans are performed during serial workloads, as is often the case with OLTP systems. However, full table scans occur frequently on DSS systems but may run parallel and bypass the buffer cache. In such cases, Oracle Database still gathers the sreadtim value because the database performs index lookup using the buffer cache.

If Oracle Database cannot gather or validate gathered mbrc or mreadtim values, but has gathered sreadtim and cpuspeed values, then the database uses only the sreadtim and cpuspeed values 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 not set or is set to 0 (zero), then the optimizer uses a value of 8 for costing.

13.4.2.1 Gathering Noworkload Statistics

To gather noworkload statistics, run DBMS_STATS.GATHER_SYSTEM_STATS() with no arguments. There is an overhead on the I/O system during the gathering process of noworkload statistics. The gathering process may take from a few seconds to several minutes, depending on I/O performance and database size.

The information is analyzed and verified for consistency. In some cases, the value of noworkload statistics may remain its default value. In such cases, repeat the statistics gathering process or set the value manually to values that the I/O system has according to its specifications by using the DBMS_STATS.SET_SYSTEM_STATS procedure.

13.5.7.1 How Dynamic Sampling Works

The primary performance attribute is compile time. Oracle Database determines at compile time whether a query would benefit from dynamic sampling. If so, a recursive SQL statement is issued to scan a small random sample of the table's blocks, and to apply the relevant single table predicates to estimate predicate selectivities. The sample cardinality can also be used, in some cases, to estimate table cardinality. Any relevant column and index statistics are also collected.

Depending on the value of the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter, a certain number of blocks are read by the dynamic sampling query.

13.5.7.2 When to Use Dynamic Sampling

For a query that normally completes quickly (in less than a few seconds), you do not want to incur the cost of dynamic sampling. However, dynamic sampling can be beneficial under any of the following conditions:

• Dynamic sampling can find a better plan.

• The sampling time is a small fraction of total execution time for the query.

• The query is executed many times.

You can apply dynamic sampling to a subset of a single table's predicates and combined with standard selectivity estimates of predicates for which dynamic sampling is not done.

13.5.7.3 How to Use Dynamic Sampling to Improve Performance

You control dynamic sampling with the OPTIMIZER_DYNAMIC_SAMPLING parameter, which you can set to a value from 0 to 10. The default is 2.

• A value of 0 means that the database does not perform dynamic sampling.

• Increasing the value of the parameter results in more aggressive application of dynamic sampling, in terms of both the type of tables sampled (analyzed or unanalyzed) and the amount of I/O spent on sampling.

Dynamic sampling is repeatable if no rows have been inserted, deleted, or updated in the table being sampled. The parameter OPTIMIZER_FEATURES_ENABLE turns off dynamic sampling if you set it to a version before 9.2.0.

13.5.7.4 Dynamic Sampling Levels

The sampling levels are as follows if the dynamic sampling level used is from a cursor hint or from the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter:

• Level 0: Do not use dynamic sampling.

• Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).

• Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.

• Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for a predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is twice the default number of dynamic sampling blocks.

• Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

• Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.

• Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

The sampling levels are as follows if the dynamic sampling level for a table is set using the DYNAMIC_SAMPLING optimizer hint:

• Level 0: Do not use dynamic sampling.

• Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32).

• Levels 2, 3, 4, 5, 6, 7, 8, and 9: The number of blocks sampled is 2, 4, 8, 16, 32, 64, 128, or 256 times the default number of dynamic sampling blocks respectively.

• Level 10: Read all blocks in the table.

  See Also:

  Oracle Database SQL Language Reference to learn about setting the sampling levels with the DYNAMIC_SAMPLING hint

13.6.2.1 Height-Balanced Histograms

In a height-balanced histogram, the column values are divided into buckets so that each bucket contains approximately the same number of rows. The histogram shows where the endpoints fall in the range of values.

Consider a column my_col with values between 1 and 100 and a histogram with 10 buckets. If the data in my_col is uniformly distributed, then the histogram looks similar to Figure 13-1, where the numbers are the endpoint values. For example, the 7th bucket has rows with values between 60 and 70.

Figure 13-1 Height-Balanced Histogram with Uniform Distribution

Description of "Figure 13-1 Height-Balanced Histogram with Uniform Distribution"

The number of rows in each bucket is 10% the total number of rows. In this example of uniform distribution, 40% of the rows have values between 60 and 100.

If the data is not uniformly distributed, then the histogram may look like Figure 13-2. In this case, most of the rows have the value 5 for the column. Only 10% of the rows have values between 60 and 100.

Figure 13-2 Height-Balanced Histogram with Non-Uniform Distribution

Description of "Figure 13-2 Height-Balanced Histogram with Non-Uniform Distribution"

You can view height-balanced histograms using the USER_TAB_HISTOGRAMS table, as shown in Example 13-1.

BEGIN
  DBMS_STATS.GATHER_table_STATS ( 
    OWNNAME    => 'OE', 
    TABNAME    => 'INVENTORIES', 
    METHOD_OPT => 'FOR COLUMNS SIZE 10 quantity_on_hand' );
END;
/

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM 
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME = 'INVENTORIES' 
AND    COLUMN_NAME = 'QUANTITY_ON_HAND';

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
QUANTITY_ON_HAND                        237          10 HEIGHT BALANCED

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE 
FROM   USER_TAB_HISTOGRAMS
WHERE  TABLE_NAME = 'INVENTORIES'
AND    COLUMN_NAME = 'QUANTITY_ON_HAND'
ORDER BY ENDPOINT_NUMBER;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0              0
              1             27
              2             42
              3             57
              4             74
              5             98
              6            123
              7            149
              8            175
              9            202
             10            353

13.6.2.2 Frequency Histograms

In a frequency histogram, each value of the column corresponds to a single bucket of the histogram. Each bucket contains the number of occurrences of this single value. For example, suppose that 36 rows contain the value 1 for column warehouse_id. The endpoint value 1 has an endpoint number 36.

The database automatically creates frequency histograms instead of height-balanced histograms when the number of distinct values is less than or equal to the number of histogram buckets specified. You can view frequency histograms using the USER_TAB_HISTOGRAMS view, as shown in Example 13-2.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (
    OWNNAME    => 'OE', 
    TABNAME    => 'INVENTORIES', 
    METHOD_OPT => 'FOR COLUMNS SIZE 20 warehouse_id' );
END;
/

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM 
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME = 'INVENTORIES' 
AND    COLUMN_NAME = 'WAREHOUSE_ID';

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
WAREHOUSE_ID                              9           9 FREQUENCY

SELECT   ENDPOINT_NUMBER, ENDPOINT_VALUE 
FROM     USER_TAB_HISTOGRAMS
WHERE    TABLE_NAME = 'INVENTORIES' 
AND      COLUMN_NAME = 'WAREHOUSE_ID'
ORDER BY ENDPOINT_NUMBER;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
             36              1
            213              2
            261              3
            370              4
            484              5
            692              6
            798              7
            984              8
           1112              9

In Example 13-2, the first bucket is for the warehouse_id of 1. The value appears 36 times in the table, as confirmed by the following query:

oe@PROD> SELECT COUNT(*) FROM inventories WHERE warehouse_id = 1;
 
  COUNT(*)
----------
        36