Maintaining statistics on large partitioned tables

We have gotten a lot of questions recently regarding how to gather and maintain optimizer statistics on large partitioned tables. The majority of these questions can be summarized into two topics:

1. When queries access a single partition with stale or non-existent partition level statistics I get a sub optimal plan due to “Out of Range” values


2. Global statistics collection is extremely expensive in terms of time and system resources

This article will describe both of these issues and explain how you can address them both in Oracle Database 10gR2 and 11gR1.

Out of Range
Large tables are often decomposed into smaller pieces called partitions in order to improve query performance and ease of data management. The Oracle query optimizer relies on both the statistics of the entire table (global statistics) and the statistics of the individual partitions (partition statistics) to select a good execution plan for a SQL statement. If the query needs to access only a single partition, the optimizer uses only the statistics of the accessed partition. If the query access more than one partition, it uses a combination of global and partition statistics.

“Out of Range” means that the value supplied in a where clause predicate is outside the domain of values represented by the [minimum, maximum] column statistics. The optimizer prorates the selectivity based on the distance between the predicate value and the maximum value (assuming the value is higher than the max), that is, the farther the value is from the maximum value, the lower the selectivity will be. This situation occurs most frequently in tables that are range partitioned by a date column, a new partition is added, and then queried while rows are still being loaded in the new partition. The partition statistics will be stale very quickly due to the continuous trickle feed load even if the statistics get refreshed periodically. The maximum value known to the optimizer is not correct leading to the “Out of Range” condition. The under-estimation of selectivity often leads the query optimizer to pick a sub optimal plan. For example, the query optimizer would pick an index access path while a full scan is a better choice.

The "Out of Range" condition can be prevented by using the new copy table statistics procedure available in Oracle Database10.2.0.4 and 11g. This procedure copies the statistics of the source [sub] partition to the destination [sub] partition. It also copies the statistics of the dependent objects: columns, local (partitioned) indexes etc. It adjusts the minimum and maximum values of the partitioning column as follows; it uses the high bound partitioning value as the maximum value of the first partitioning column (it is possible to have concatenated partition columns) and high bound partitioning value of the previous partition as the minimum value of the first partitioning column for range partitioned table. It can optionally scale some of the other statistics like the number of blocks, number of rows etc. of the destination partition.

Assume we have a table called SALES that is ranged partitioned by quarter on the SALES_DATE column. At the end of every day data is loaded into latest partition. However, statistics are only gathered at the end of every quarter when the partition is fully loaded. Assuming global and partition level statistics (for all fully loaded partitions) are up to date, use the following steps in order to prevent getting a sub-optimal plan due to “out of range”.

1. Lock the table statistics using LOCK_TABLE_STATS procedure in DBMS_STATS. This is to avoid interference from auto statistics job.
   
   
   EXEC DBMS_STATS.LOCK_TABLE_STATS('SH','SALES');
   



2. Before beginning the initial load into each new partition (say SALES_Q4_2000) copy the statistics from the previous partition (say SALES_Q3_2000) using COPY_TABLE_STATS. You need to specify FORCE=>TRUE to override the statistics lock.
   
   EXEC DBMS_STATS.COPY_TABLE_STATS ('SH', 'SALES', 'SALES_Q3_2000', 'SALES_Q4_2000', FORCE=>TRUE);
   

Expensive global statistics collection

In data warehouse environment it is very common to do a bulk load directly into one or more empty partitions. This will make the partition statistics stale and may also make the global statistics stale. Re-gathering statistics for the effected partitions and for the entire table can be very time consuming. Traditionally, statistics collection is done in a two-pass approach:

• In the first pass we will scan the table to gather the global statistics


• In the second pass we will scan the partitions that have been changed 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. Note that the scan of the entire table is done even if we change a small subset of partitions.

In Oracle Database 11g, we avoid scanning the whole table when computing global statistics by deriving the global statistics from the partition statistics. Some of the statistics can be derived easily and accurately from partition statistics. For example, number of rows at global level is the sum of number of rows of partitions. Even global histogram can be derived from partition histograms. But the number of distinct values (NDV) of a column cannot be derived from partition level NDVs. So, Oracle maintains another structure called a synopsis for each column at the partition level. A synopsis can be considered as sample of distinct values. The NDV can be accurately derived from synopses. We can also merge multiple synopses into one. The global NDV is derived from the synopsis generated by merging all of the partition level synopses. To summarize

1. Gather statistics and create synopses for the changed partitions only
2. Oracle automatically merges partition level synopses into a global synopsis
3. The global statistics are automatically derived from the partition level statistics and global synopses

Incremental maintenance feature is disabled by default. It can be enabled by changing the INCREMENTAL table preference to true. It can also be enabled for a particular schema or at the database level. If you are interested in more details of the incremental maintenance feature, please refer to the following paper presented in SIGMOD 2008 and to our previous blog entry on new ndv gathering in 11g.

Assume we have table called SALES that is range partitioned by day on the SALES_DATE column. At the end of every day data is loaded into latest partition and partition statistics are gathered. Global statistics are only gathered at the end of every month because gathering them is very time and resource intensive. Use the following steps in order to maintain global statistics after every load.

1. Turn on incremental feature for the table.
   
   
   EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE');
   


2. At the end of every load gather table statistics using GATHER_TABLE_STATS command. You don’t need to specify the partition name. Also, do not specify the granularity parameter. The command will collect statistics for partitions with stale or missing statistics and update the global statistics based on the partition level statistics and synopsis.
   
   
   EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES');
   

Note: that the incremental maintenance feature was introduced in Oracle Database 11g Release 1. However, we also provide a solution in Oracle Database10g Release 2 (10.2.0.4) that simulates the same behavior. The 10g solution is a new value, 'APPROX_GLOBAL AND PARTITION' for the GRANULARITY parameter of the GATHER_TABLE_STATS procedures. It behaves the same as the incremental maintenance feature except that we don’t update the NDV for non-partitioning columns and number of distinct keys of the index at the global level. For partitioned column we update the NDV as the sum of NDV at the partition levels. Also we set the NDV of columns of unique indexes as the number of rows of the table. In general, non-partitioning column NDV at the global level becomes stale less often. It may be possible to collect global statistics less frequently then the default (when table changes 10%) since approx_global option maintains most of the global statistics accurately.

Let's take a look at an example to see how you would effectively use the Oracle Database 10g approach.

After the data load is complete, gather statistics using DBMS_STATS.GATHER_TABLE_STATS for the last partition (say SALES_11FEB2009), specify granularity => 'APPROX_GLOBAL AND PARTITION'. It will collect statistics for the specified partition and derive global statistics from partition statistics (except for NDV as described before).

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SH', 'SALES', 'SALES_11FEB2009', GRANULARITY => 'APPROX_GLOBAL AND PARTITION');

It is necessary to install the one off patch for bug 8719831 if you are using copy_table_stats procedure or APPROX_GLOBAL option in 10.2.0.4 (patch 8877245) or in 11.1.0.7 (patch 8877251).

Maria Colgan

SQL Plan Management (Part 4 of 4): User Interfaces and Other Features

In the first three parts of this article, we have seen how SQL plan baselines are created, used and evolved. In this final installment, we will show some user interfaces, describe the interaction of SPM with other features and answer some of your questions.

DBMS_SPM package

A new package, DBMS_SPM, allows you to manage plan histories. We have already seen in previous examples how you can use it to create and evolve SQL plan baselines. Other management functions include changing attributes (like enabled status and plan name) of plans or dropping plans. You need the ADMINISTER SQL MANAGEMENT OBJECT privilege to execute this package.

Viewing the plan history

Regardless of how a plan history is created, you can view details about the various plans in the view DBA_SQL_PLAN_BASELINES. At the end of Part 3 of this blog, we saw that the SQL statement had two accepted plans:

SQL> select sql_text, sql_handle, plan_name, enabled, accepted
  2  from dba_sql_plan_baselines;

SQL_TEXT                 SQL_HANDLE               PLAN_NAME                     ENA ACC
------------------------ ------------------------ ----------------------------- --- ---
select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
  and s.time_id = t.time
_id
  and p.prod_id < :pid

select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
  and s.time_id = t.time
_id
  and p.prod_id < :pid

The SQL handle is a unique identifier for each SQL statement that you can use when managing your plan history using the DBMS_SPM package.

Creating an accepted plan by modifying the SQL text

Some of you may be manually tuning SQL statements by adding hints or otherwise modifying the SQL text. If you enable automatic capture of SQL plans and then execute this statement, you will be creating a SQL plan baseline for this modified statement. What you most likely want, however, is to add this plan to the plan history of the original SQL statement. Here's how you can do this using the above SQL statement as an example.

Let's modify the SQL statement, execute it and look at the plan:


SQL> var pid number
SQL> exec :pid := 100;

PL/SQL procedure successfully completed.

SQL> select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
2    from sales s, products p, times t
3    where s.prod_id = p.prod_id
4      and s.time_id = t.time_id
5      and p.prod_id < :pid;

PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
9 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('b17wnz4y8bqv1', 0, 'basic note'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t where s.prod_id = p.prod_id   and
s.time_id = t.time_id   and p.prod_id < :pid

Plan hash value: 2290436051

---------------------------------------------------------------
| Id  | Operation                            | Name           |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |
|   1 |  HASH JOIN                           |                |
|   2 |   HASH JOIN                          |                |
|   3 |    TABLE ACCESS FULL                 | TIMES          |
|   4 |    PARTITION RANGE ALL               |                |
|   5 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   6 |      BITMAP CONVERSION TO ROWIDS     |                |
|   7 |       BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|   8 |   TABLE ACCESS BY INDEX ROWID        | PRODUCTS       |
|   9 |    INDEX RANGE SCAN                  | PRODUCTS_PK    |
---------------------------------------------------------------


23 rows selected.

We can now create a new accepted plan for the original SQL statement by associating the modified statement's plan to the original statement's sql handle (obtained from DBA_SQL_PLAN_BASELINES):

SQL> var pls number
SQL> exec :pls := dbms_spm.load_plans_from_cursor_cache( -
>                    sql_id => 'b17wnz4y8bqv1', -
>                    plan_hash_value => 2290436051, -
>                    sql_handle => 'SYS_SQL_4bf04d85fcc170b0');

If the original SQL statement does not already have a plan history (and thus no SQL handle), another version of load_plans_from_cursor_cache allows you to specify the original statement's text.

To confirm that we now have three accepted plans for our SQL statement, let's check in DBA_SQL_PLAN_BASELINES:

SQL> select sql_text, sql_handle, plan_name, enabled, accepted
  2  from dba_sql_plan_baselines;

SQL_TEXT                 SQL_HANDLE               PLAN_NAME                     ENA ACC
------------------------ ------------------------ ----------------------------- --- ---
select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0888547d3 YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
  and s.time_id = t.time
_id
  and p.prod_id < :pid

select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
  and s.time_id = t.time
_id
  and p.prod_id < :pid

select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
  and s.time_id = t.time
_id
  and p.prod_id < :pid

Displaying plans

When the optimizer uses an accepted plan for a SQL statement, you can see it in the plan table (for explain) or V$SQL_PLAN (for shared cursors). Let's explain the SQL statement above and display its plan:

SQL> explain plan for
  2  select p.prod_name, s.amount_sold, t.calendar_year
  3  from sales s, products p, times t
  4  where s.prod_id = p.prod_id
  5    and s.time_id = t.time_id
  6    and p.prod_id < :pid;

Explained.

SQL> select * from table(dbms_xplan.display('plan_table', null, 'basic note'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2787970893

----------------------------------------------------------------
| Id  | Operation                             | Name           |
----------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |
|   1 |  NESTED LOOPS                         |                |
|   2 |   NESTED LOOPS                        |                |
|   3 |    HASH JOIN                          |                |
|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |
|   5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |
|   6 |     PARTITION RANGE ALL               |                |
|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   8 |       BITMAP CONVERSION TO ROWIDS     |                |
|   9 |        BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|  10 |    INDEX UNIQUE SCAN                  | TIME_PK        |
|  11 |   TABLE ACCESS BY INDEX ROWID         | TIMES          |
----------------------------------------------------------------

Note
-----
- SQL plan baseline "SYS_SQL_PLAN_fcc170b0a62d0f4d" used for this statement

22 rows selected.

The note at the bottom tells you that the optimizer used an accepted plan.

A plan history might have multiple plans. You can see one of the accepted plans if the optimizer selects it for execution. But what if you want to display some or all of the other plans? You can do this using the display_sql_plan_baseline function in the DBMS_XPLAN package. Using the above example, here's how you can display the plan for all plans in the plan history.

SQL> select *
  2  from table(dbms_xplan.display_sql_plan_baseline(
  3               sql_handle => 'SYS_SQL_4bf04d85fcc170b0', format => 'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_4bf04d85fcc170b0
SQL text: select p.prod_name, s.amount_sold, t.calendar_year from sales s,
          products p, times t where s.prod_id = p.prod_id   and s.time_id =
          t.time_id   and p.prod_id < :pid
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_fcc170b0888547d3
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2290436051

---------------------------------------------------------------
| Id  | Operation                            | Name           |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |
|   1 |  HASH JOIN                           |                |
|   2 |   HASH JOIN                          |                |
|   3 |    TABLE ACCESS FULL                 | TIMES          |
|   4 |    PARTITION RANGE ALL               |                |
|   5 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   6 |      BITMAP CONVERSION TO ROWIDS     |                |
|   7 |       BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|   8 |   TABLE ACCESS BY INDEX ROWID        | PRODUCTS       |
|   9 |    INDEX RANGE SCAN                  | PRODUCTS_PK    |
---------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_fcc170b08cbcb825
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 2361178149

------------------------------------------
| Id  | Operation             | Name     |
------------------------------------------
|   0 | SELECT STATEMENT      |          |
|   1 |  HASH JOIN            |          |
|   2 |   HASH JOIN           |          |
|   3 |    PARTITION RANGE ALL|          |
|   4 |     TABLE ACCESS FULL | SALES    |
|   5 |    TABLE ACCESS FULL  | TIMES    |
|   6 |   TABLE ACCESS FULL   | PRODUCTS |
------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_fcc170b0a62d0f4d
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 2787970893

----------------------------------------------------------------
| Id  | Operation                             | Name           |
----------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |
|   1 |  NESTED LOOPS                         |                |
|   2 |   NESTED LOOPS                        |                |
|   3 |    HASH JOIN                          |                |
|   4 |     TABLE ACCESS BY INDEX ROWID       | PRODUCTS       |
|   5 |      INDEX RANGE SCAN                 | PRODUCTS_PK    |
|   6 |     PARTITION RANGE ALL               |                |
|   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   8 |       BITMAP CONVERSION TO ROWIDS     |                |
|   9 |        BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|  10 |    INDEX UNIQUE SCAN                  | TIME_PK        |
|  11 |   TABLE ACCESS BY INDEX ROWID         | TIMES          |
----------------------------------------------------------------

72 rows selected.

Parameters

Two parameters allow you to control SPM. The first, optimizer_capture_sql_plan_baselines, which is FALSE by default, allows you to automatically capture plans. SPM will start managing every repeatable SQL statement that is executed and will create a plan history for it. The first plan that is captured will beautomatically accepted. Subsequent plans for these statements will not be accepted until they are evolved.

The second parameter, optimizer_use_sql_plan_baselines, is TRUE by default. It allows the SPM aware optimizer to use the SQL plan baseline if available when compiling a SQL statement. If you set this parameter to FALSE, the SPM aware optimizer will be disabled and you will get the regular cost-based optimizer which will select the best plan based on estimated cost.

SPM and SQL profiles

A SQL statement can have both a SQL profile and a SQL plan baseline. Such a case was described in Part 3 where we evolved a SQL plan baseline by accepting a SQL profile. In this case, the SPM aware optimizer will use both the SQL profile and the SQL plan baseline. The SQL profile contains additional information that helps the optimizer to accurately cost each accepted plan and select the best one. The SPM aware optimizer may choose a different accepted plan when a SQL profile is present than when it is not.

SPM and stored outlines

It is possible for a SQL statement to have a stored outline as well as a SQL plan baseline. If a stored outline exists for a SQL statement and is enabled for use, then the optimizer will use it, ignoring the SQL plan baseline. In other words, the stored outline trumps a SQL plan baseline. If you are using stored outlines, you can test SPM by creating SQL plan baselines and disabling the stored outlines. If you are satisfied with SPM, you can either drop the stored outlines or leave them disabled. If SPM doesn't work for you (and we would love to know why), you can re-enable the stored outlines.

If you are using stored outlines, be aware of their limitations:

• You can only have one stored outline at a time for a given SQL statement. This may be fine in some cases, but a single plan is not necessarily the best when the statement is executed under varying conditions (e.g., bind values).
  
• The second limitation is related to the first. Stored outlines do not allow for evolution. That is, even if a better plan exists, the stored outline will continue to be used, potentially degrading your system's performance. To get the better plan, you have to manually drop the current stored outline and generate a new one.
  
• If an access path (e.g., an index) used in a stored outline is dropped or otherwise becomes unusable, the partial stored outline will continue to be used with the potential of a much worse plan.
  

One question that readers have is what we plan to do with the stored outlines feature. Here is the official word in Chapter 20 of Oracle's Performance Tuning Guide:

Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.

If you have existing stored outlines, consider migrating them to SQL plan baselines by using the LOAD_PLANS_FROM_CURSOR_CACHE or LOAD_PLANS_FROM_SQLSET procedure of the DBMS_SPM package. When the migration is complete, you should disable or remove the stored outlines.

SPM and adaptive cursor sharing

Adaptive cursor sharing (ACS) may generate multiple cursors for a given bind sensitive SQL statement if it is determined that a single plan is not optimal under all conditions. Each cursor is generated by forcing a hard parse of the statement. The optimizer will normally select the plan with the best cost upon each hard parse.

When you have a SQL plan baseline for a statement, the SPM aware optimizer will select the best accepted plan as the optimal plan. This also applies for the hard parse of a bind sensitive statement. There may be multiple accepted plans, each of which is optimal for different bind sets. With SPM and ACS enabled, the SPM aware optimizer will select the best plan for the current bind set.

Thus, if a hard parse occurs, the normal SPM plan selection algorithm is used regardless of whether a statement is bind sensitive.

Enterprise Manager

You can view SQL plan baselines and configure and manage most SPM tasks through the Enterprise Manager. The screenshots below show two of these tasks.

Setting init.ora parameters for SPM

Loading SQL plan baselines from cursor cache

Further Reading

More details about SPM are available in the Oracle documentation, especially Chapter 15 of the Performance Tuning Guide. There is also a whitepaper, and a paper published in the VLDB 2008 conference. The VLDB paper also has experimental results that show how SPM prevents performance regressions while simultaneously allowing better plans to be used.