13 Managing Optimizer Statistics

13.2.1 Enabling and Disabling Automatic Optimizer Statistics Collection

The automated maintenance tasks infrastructure (known as AutoTask) schedules tasks to run automatically in Oracle Scheduler windows known as maintenance windows. By default, one window is scheduled for each day of the week. Automatic optimizer statistics collection runs as part of AutoTask and is enabled by default to run in all predefined maintenance windows.

If for some reason automatic optimizer statistics collection is disabled, then you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
     client_name => 'auto optimizer stats collection' 
,    operation   => NULL
,    window_name => NULL
);
END;
/

When you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
     client_name => 'auto optimizer stats collection'
,    operation   => NULL 
,    window_name => NULL
);
END;
/

Automatic optimizer statistics collection relies on the modification monitoring feature, described in "Determining Stale Statistics". If this feature is disabled, then the automatic optimizer statistics collection job cannot detect stale statistics. This feature is enabled when the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. TYPICAL is the default value.

13.2.2 Considerations When Gathering Statistics

This section discusses:

• When to Use Manual Statistics

• Restoring Previous Versions of Statistics

• Locking Statistics

13.3.1 Gathering Statistics with DBMS_STATS Procedures

You can gather statistics with the DBMS_STATS package. This PL/SQL package is also used to modify, view, export, import, and delete statistics.

The DBMS_STATS package can gather statistics on table and indexes and individual columns and partitions of tables. It does not gather cluster statistics. However, you can use DBMS_STATS to gather statistics on individual tables instead of the whole cluster.

If you generate statistics for a table, column, or index, and if the data dictionary contains statistics for the object, then Oracle Database updates the existing statistics. The older statistics are saved. You can restore them later if necessary. See "Restoring Previous Versions of Statistics".

When gathering statistics on system schemas, you can use the procedure DBMS_STATS.GATHER_DICTIONARY_STATS. This procedure gathers statistics for all system schemas, including SYS and SYSTEM, and other optional schemas, such as CTXSYS and DRSYS.

When statistics are updated for a database object, Oracle Database invalidates any currently parsed SQL statements that access the object. The next time such a statement executes, the statement is re-parsed and the optimizer automatically chooses a new execution plan based on the new statistics. Distributed statements accessing objects with new statistics on remote databases are not invalidated. The new statistics take effect the next time the SQL statement is parsed.

Table 13-1 lists the procedures in the DBMS_STATS package for gathering statistics on database objects.

When using any of these procedures, there are several important considerations for statistics gathering:

• Statistics Gathering Using Sampling

• Parallel Statistics Gathering

• Statistics on Partitioned Objects

• Column Statistics and Histograms

• Determining Stale Statistics

• User-Defined Statistics

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

13.3.2 Setting Preferences for Manual Statistics Gathering

You can use the DBMS_STATS.SET_*_PREFS procedures to set the default values for parameters used by the DBMS_STATS procedures that gather statistics. You can set a preference for each parameter at a table, schema, database, and global level, thus providing a fine granularity of control.

You can use the DBMS_STATS.SET_*_PREFS procedures to change the following parameters:

• AUTOSTATS_TARGET (SET_GLOBAL_PREFS only)

• CASCADE

• DEGREE

• ESTIMATE_PERCENT

• GRANULARITY

• INCREMENTAL

• METHOD_OPT

• NO_INVALIDATE

• PUBLISH

• STALE_PERCENT

Table 13-2 lists the DBMS_STATS procedures for setting preferences. Parameter values set in the DBMS_STAT.GATHER_*_STATS procedures overrule 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.

13.3.3 When to Gather Statistics

When gathering statistics manually, you not only need to determine how to gather statistics, but also when and how often to gather new statistics.

For an application in which tables are incrementally modified, you may only need to gather new statistics every week or every month. The simplest way to gather statistics in these environments is to use a script or job scheduling tool to regularly run the GATHER_SCHEMA_STATS and GATHER_DATABASE_STATS procedures. The frequency of collection intervals should balance the task of providing accurate statistics for the optimizer against the processing overhead incurred by the statistics collection process.

For tables that are substantially modified in batch operations, such as with bulk loads, gather statistics on these tables as part of the batch operation. Call the DBMS_STATS procedure as soon as the load operation completes.

Sometimes only a single partition is modified. In such cases, you can gather statistics only on the modified partitions rather than on the entire table. However, gathering global statistics for the partitioned table may still be necessary.

13.3.4 Comparing Statistics with DBMS_STATS Functions

DBMS_STATS enables you to compare statistics for a table from two different sources. Table 13-3 lists the functions in the DBMS_STATS package for comparing statistics.

The functions in Table 13-3 also compare the statistics of dependent objects such as indexes, columns, and partitions. They display statistics of the objects from both sources if the difference between those statistics exceeds a certain threshold. You can specify the threshold as an argument to the function, with a default of 10%. Oracle Database uses the statistics corresponding to the first source as basis for computing the differential percentage.

13.4.1 Workload Statistics

Workload statistics include the following:

• Single and multiblock read times

• mbrc

• CPU speed (cpuspeed)

• Maximum system throughput

• Average slave throughput

single and multiblock read times, mbrc, CPU speed (cpuspeed), maximum system throughput, and average slave throughput. 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. Workload statistics thus depend on the activity the system had during the workload window. If system is I/O bound (both latch contention and I/O throughput), then the statistics reflect this situation and therefore promotes a less I/O-intensive plan after the database uses the statistics. Furthermore, workload statistics gathering does not generate additional overhead.

13.4.2 Noworkload Statistics

Noworkload statistics consist of I/O transfer speed, I/O seek time, and CPU speed (cpuspeednw). The major difference between workload statistics and noworkload statistics lies in the gathering method.

Noworkload statistics gather data by submitting random reads against all data files, while workload statistics uses counters updated when database activity occurs. ioseektim represents the time it takes to position the disk head to read data. Its value usually varies from 5 ms to 15 ms, depending on disk rotation speed and the disk or RAID specification. The I/O transfer speed represents the speed at which one operating system process can read data from the I/O subsystem. Its value varies greatly, from a few MBs per second to hundreds of MBs per second. Oracle Database uses relatively conservative default settings for I/O transfer speed.

Oracle Database uses noworkload statistics and the CPU cost model by default. The values of noworkload statistics are initialized to defaults at the first instance startup:

ioseektim = 10ms
iotrfspeed = 4096 bytes/ms
cpuspeednw = gathered value, varies based on system

If workload statistics are gathered, then Oracle Database ignores noworkload statistics and uses workload statistics instead.

13.5.1 Pending Statistics

Starting with Oracle Database 11g Release 2 (11.2), you have the following options when gathering statistics:

• Publish the statistics automatically at the end of the gather operation (default behavior)

• Save the new statistics saved as pending

Saving the new statistics as pending allows you to validate the new statistics and publish them only if they are satisfactory.

To check whether the statistics will be automatically published as soon as they are gathered, use the DBMS_STATS package as follows:

SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;

The preceding query returns either TRUE or FALSE. TRUE indicates that the statistics will be published as and when they are gathered, while FALSE indicates that the statistics will be kept pending.

You can change the PUBLISH setting at either the schema or the table level. For example, to change the PUBLISH setting for the customers table in the SH schema, execute the statement:

EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH', 'false');

Subsequently, when you gather statistics on the customers table, the statistics will not be automatically published when the gather job completes. Instead, the database stores the newly gathered statistics in the USER_TAB_PENDING_STATS table.

By default, the optimizer uses the published statistics stored in the data dictionary views. If you want the optimizer to use the newly collected pending statistics, then set the initialization parameter OPTIMIZER_USE_PENDING_STATISTICS to TRUE (the default value is FALSE), and run a workload against the table or schema:

ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;

The optimizer will use the pending statistics instead of the published statistics when compiling SQL statements. If the pending statistics are valid, then you can make them public by executing the following statement:

EXEC DBMS_STATS.PUBLISH_PENDING_STATS(null, null);

You can also publish the pending statistics for a specific database object. For example, by using the following statement:

EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SH','CUSTOMERS');

If you do not want to publish the pending statistics, delete them by executing the following statement:

EXEC DBMS_STATS.DELETE_PENDING_STATS('SH','CUSTOMERS');

You can export pending statistics using DBMS_STATS.EXPORT_PENDING_STATS function. Exporting pending statistics to a test system enables you to run a full workload against the new statistics.

13.5.2 Managing Extended Statistics

DBMS_STATS enables you to collect extended statistics, which are statistics that can improve cardinality estimates when multiple predicates exist on different columns of a table, or when predicates use expressions. An extension is either a column group or an expression.

Oracle Database supports the following types of extended statistics:

• Column group statistics

  This type of extended statistics can improve cardinality estimates when multiple columns from the same table occur together in a SQL statement. See "Managing Column Group Statistics".

• Expression statistics

  This type of extended statistics improves optimizer estimates when predicates use expressions, for example, built-in or user-defined functions. See "Managing Expression Statistics".

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

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

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

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

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

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

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
FROM   USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
WHERE  e.EXTENSION_NAME=t.COLUMN_NAME
AND    e.TABLE_NAME=t.TABLE_NAME
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');

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
FROM   USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
WHERE  e.EXTENSION_NAME=t.COLUMN_NAME
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.5.3 Restoring Previous Versions of Statistics

Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoration. You can restore statistics using RESTORE procedures of DBMS_STATS package. These procedures use a time stamp as an argument and restore statistics as of that time stamp. This is useful in case newly collected statistics leads to some sub-optimal execution plans and the administrator wants to revert to the previous set of statistics.

There are dictionary views that display the time of statistics modifications. These views are useful in determining the time stamp to be used for statistics restoration.

• Catalog view DBA_OPTSTAT_OPERATIONS contain history of statistics operations performed at schema and database level using DBMS_STATS.

• The views *_TAB_STATS_HISTORY views (ALL, DBA, or USER) contain a history of table statistics modifications.

The database purges old statistics automatically at regular intervals based on the statistics history retention setting and the time of the recent analysis of the system. You can configure retention using the ALTER_STATS_HISTORY_RETENTION procedure of DBMS_STATS. The default value is 31 days, which means that you would be able to restore the optimizer statistics to any time in last 31 days.

Automatic purging is enabled when STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If automatic purging is disabled, then you must purge the old versions of statistics manually using the PURGE_STATS procedure.

The other DBMS_STATS procedures related to restoring and purging statistics include:

• PURGE_STATS: This procedure can manually purge old versions beyond a time stamp.

• GET_STATS_HISTORY_RETENTION: This function can get the current statistics history retention value.

• GET_STATS_HISTORY_AVAILABILITY: This function gets the oldest time stamp where statistics history is available. Users cannot restore statistics to a time stamp older than the oldest time stamp.

When restoring previous versions of statistics, the following limitations apply:

• RESTORE procedures cannot restore user-defined statistics.

• Old versions of statistics are not stored when the ANALYZE command has been used for collecting statistics.

13.5.4 Exporting and Importing Statistics

You can export and import statistics from the data dictionary to user-owned tables, enabling you to create multiple versions of statistics for the same schema. You can also copy statistics from one database to another database. You may want to do this to copy the statistics from a production database to a scaled-down test database.

Before exporting statistics, you first need to create a table for holding the statistics. The procedure DBMS_STATS.CREATE_STAT_TABLE creates the statistics table. After table creation, you can export statistics from the data dictionary into the statistics table using the DBMS_STATS.EXPORT_*_STATS procedures. You can then import statistics using the DBMS_STATS.IMPORT_*_STATS procedures.

Note that the optimizer does not use statistics stored in a user-owned table. The only statistics used by the optimizer are the statistics stored in the data dictionary. To have the optimizer use the statistics in a user-owned tables, you must import those statistics into the data dictionary using the statistics import procedures.

To move statistics from one database to another, you must first export the statistics on the first database, then copy the statistics table to the second database, using the Data Pump Export and Import utilities or other mechanisms, and finally import the statistics into the second database.

13.5.5 Restoring Statistics Versus Importing or Exporting Statistics

The functionality for restoring statistics is similar in some respects to the functionality of importing and exporting statistics. In general, use the restore capability when:

• You want to recover older versions of the statistics. For example, to restore the optimizer behavior to an earlier date.

• You want the database to manage the retention and purging of statistics histories.

You should use EXPORT/IMPORT_*_STATS procedures when:

• You want to experiment with multiple sets of statistics and change the values back and forth.

• You want to move the statistics from one database to another database. For example, moving statistics from a production system to a test system.

• You want to preserve a known set of statistics for a longer period than the desired retention date for restoring statistics.

13.5.6 Locking Statistics for a Table or Schema

Statistics for a table or schema can be locked. After statistics are locked, you can make no modifications to the statistics until the statistics have been unlocked. Locking procedures are useful in a static environment in which you want to guarantee that the statistics never change.

The DBMS_STATS package provides two procedures for locking (LOCK_SCHEMA_STATS and LOCK_TABLE_STATS) and two procedures for unlocking statistics (UNLOCK_SCHEMA_STATS and UNLOCK_TABLE_STATS).

13.5.7 Setting Statistics

You can set table, column, index, and system statistics using the SET_*_STATISTICS procedures. Setting statistics in the manner is not recommended, because inaccurate or inconsistent statistics can lead to poor performance.

13.5.8 Handling Missing Statistics

When Oracle Database encounters a table with missing statistics, by default the database dynamically gathers the statistics needed by the optimizer. However, for certain types of tables, including remote tables and external tables, Oracle Database does not gather dynamic statistics. In these cases and also when dynamic statistics have been disabled, the optimizer uses default values for its statistics, shown in Table 13-8 and Table 13-9.

13.6.1 Purpose of Dynamic Statistics

By augmenting missing or insufficient optimizer statistics, the optimizer can improve plans by making better estimates for predicate selectivity. Dynamic statistics can supplement statistics such as table block counts, applicable index block counts, table cardinalities (estimated number of rows), and relevant join column statistics.

13.6.2 Dynamic Statistics Concepts

Dynamic statistics are enabled in the database by default. You can disable the feature by setting the initialization parameter OPTIMIZER_DYNAMIC_SAMPLING=0.

13.6.3 Setting Dynamic Statistics Levels Manually

When setting the dynamic statistics level, the best practice is to use ALTER SESSION to set the value for the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter. Determining a systemwide setting that would be beneficial to all SQL statements can be difficult.

Assumptions

This tutorial assumes the following:

• You want correct selectivity estimates for the following query, which has WHERE clause predicates on two correlated columns:

    SELECT *
    FROM   sh.customers
    WHERE  cust_city='Los Angeles'
    AND    cust_state_province='CA';
  

• The preceding query uses serial processing.

• The sh.customers table contains 932 rows that meet the conditions in the query.

• You have gathered statistics on the sh.customers table.

• You created an index on the cust_city and cust_state_province columns.

• The OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is set to the default level of 2.

To set the dynamic statistics level manually: 

1. Connect SQL*Plus to the database with the appropriate privileges, and then explain the execution plan as follows:

   EXPLAIN PLAN FOR
     SELECT *
     FROM   sh.customers
     WHERE  cust_city='Los Angeles'
     AND    cust_state_province='CA';
   

2. Query the plan as follows:

   SET LINESIZE 130
   SET PAGESIZE 0
   SELECT * 
   FROM   TABLE(DBMS_XPLAN.DISPLAY);
   

   The output appears below (the example has been reformatted to fit on the page):

   -------------------------------------------------------------------------------
   |Id| Operation                   | Name             |Rows|Bytes|Cost | Time   |
   -------------------------------------------------------------------------------
   | 0| SELECT STATEMENT            |                   | 53| 9593|53(0)|00:00:01|
   | 1|  TABLE ACCESS BY INDEX ROWID|CUSTOMERS          | 53| 9593|53(0)|00:00:01|
   |*2|   INDEX RANGE SCAN          |CUST_CITY_STATE_IND| 53| 9593| 3(0)|00:00:01|
   -------------------------------------------------------------------------------
    
   Predicate Information (identified by operation id):
   ---------------------------------------------------
    
      2 - access("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA')
   

   The columns in the WHERE clause have a real-world correlation, but the optimizer is not aware that Los Angeles is in California and assumes both predicates reduce the number of rows returned. Thus, the table contains 932 rows that meet the conditions, but the optimizer estimates 53, as shown in bold.

   If the database had used dynamic statistics for this plan, then the Note section of the plan output would have indicated this fact. The optimizer did not use dynamic statistics because the statement executed serially, standard statistics exist, and the parameter OPTIMIZER_DYNAMIC_SAMPLING is set to the default of 2.

3. Set the dynamic statistics level to 4 in the session using the following statement:

   ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=4;
   

4. Explain the plan again:

   EXPLAIN PLAN FOR
     SELECT *
     FROM   sh.customers
     WHERE  cust_city='Los Angeles'
     AND    cust_state_province='CA';
   

   The new plan shows a more accurate estimate of the number of rows, as shown by the value 932 in bold:

   PLAN_TABLE_OUTPUT
   -------------------------------------------------------------------------------
   Plan hash value: 2008213504
    
   -------------------------------------------------------------------------------
   | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
   -------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT  |           |   932 |   271K|   406   (1)| 00:00:05 |
   |*  1 |  TABLE ACCESS FULL| CUSTOMERS |   932 |   271K|   406   (1)| 00:00:05 |
   -------------------------------------------------------------------------------
    
   Predicate Information (identified by operation id):
   ---------------------------------------------------
    
      1 - filter("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA')
    
   Note
   -----
      - dynamic statistics used for this statement (level=4)
   

   The note at the bottom of the plan indicates that the sampling level is 4. The additional dynamic statistics made the optimizer aware of the real-world relationship between the cust_city and cust_state_province columns, thereby enabling it to produce a more accurate estimate for the number of rows: 932 rather than 53.

13.6.4 Disabling Dynamic Statistics

In general, the best practice is not to incur the cost of dynamic statistics for queries whose compile times must be as fast as possible, for example, unrepeated OLTP queries. You can disable the feature by setting the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter.

To disable dynamic statistics at the session level: 

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

2. Set the dynamic statistics level to 0.

   For example, run the following statement:

   ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=0;
   

13.7.1 Statistics on Tables, Indexes and Columns

The database stores statistics on tables, indexes, and columns in the data dictionary. To view statistics in the data dictionary, query the appropriate data dictionary view (USER, ALL, or DBA). These views include the following:

• DBA_TABLES and DBA_OBJECT_TABLES

• DBA_TAB_STATISTICS and DBA_TAB_COL_STATISTICS

• DBA_TAB_HISTOGRAMS

• DBA_TAB_COLS

• DBA_COL_GROUP_COLUMNS

• DBA_INDEXES and DBA_IND_STATISTICS

• DBA_CLUSTERS

• DBA_TAB_PARTITIONS and DBA_TAB_SUBPARTITIONS

• DBA_IND_PARTITIONS and DBA_IND_SUBPARTITIONS

• DBA_PART_COL_STATISTICS

• DBA_PART_HISTOGRAMS

• DBA_SUBPART_COL_STATISTICS

• DBA_SUBPART_HISTOGRAMS

13.7.2 Viewing Histograms

You can store column statistics as histograms. These histograms provide accurate estimates of the distribution of column data. Histograms provide improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with nonuniform data distributions.

Oracle Database uses the following types of histograms for column statistics:

• Height-Balanced Histograms

• Frequency Histograms

The database stores this type of histogram in the HISTOGRAM column of the *TAB_COL_STATISTICS views (USER and DBA). This column can have values of HEIGHT BALANCED, FREQUENCY, or NONE.

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

Example 13-1 Viewing Height-Balanced Histogram Statistics

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

In the Example 13-1 query output, one row (1-10) corresponds to each bucket in the histogram. Oracle Database added a special 0th bucket to this histogram because the value in the 1st bucket (27) is not the minimum value for the quantity_on_hand column. The 0th bucket holds the minimum value of 0 for quantity_on_hand.

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

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