Oracle11g data statistics Pending processing

Starting from 10 Gb, Oracle introduced a job to automatically collect statistics to ensure that the statistics in the data dictionary correctly reflect the Data Object status. This greatly relieves

Starting from 10 Gb, Oracle introduced a job to automatically collect statistics to ensure that the statistics in the data dictionary correctly reflect the Data Object status. This greatly relieves

In the CBO era, the execution plan of SQL statements relies entirely on the statistical information stored in the data dictionary and the computing formula parameters of Optimizer. From 9i to 11gR2, we say that the CBO optimizer has been mature and complete. In general, our SQL statements can get better execution plans and execution efficiency.

In practice, we often encounter inefficient execution plans. However, most of the root causes of such failures are statistics errors or failures. Incorrect statistics are generated together with inappropriate execution plans, which leads to inefficient execution processes. In the 9i era, when RBO and CBO are used together, we often need to customize the statistic collection process.

Starting from 10 Gb, Oracle introduced a job to automatically collect statistics to ensure that the statistics in the data dictionary correctly reflect the Data Object status. This greatly relieves the issue of statistic expiration due to data changes. However, in our actual work, we will still find a sudden change in the execution plan. The reason is that the statistics collected at a certain time point may not reflect the full picture of the data (such as the intermediate table ).

1. Statistics Pending

In system O & M, we often want to maintain the stability of the SQL Execution Plan. Many DBAs and developers rely heavily on hint. This is also due to the fact that the execution plan is too dependent on the statistics in CBO scenarios, making it easy to form an unstable execution plan.

Then, the stability of the SQL statement execution plan becomes the stability of the statistics. The next step is to update the new statistics. Whether it is manually collected or automatically collected, can the SQL statement be promoted to generate a more efficient execution plan.

Therefore, the idea is that when new statistics are collected and generated, the execution plan should not be put into effect for the time being. Wait until the final confirmation statistic is correct before putting it into the production environment.

In Oracle 11g, a new technology of Statistic Management-Pending Statistic technology was introduced, providing this function.

To put it simply, we can set the pending attribute for a series of data tables. After the pending attribute is set, the data statistics are locked in the data dictionary. However, after the new statistic is generated, instead of directly replacing the original data, it is stored in the pending data dictionary.

Statistics in the pending dictionary are not involved in the production of SQL Execution plans by default. Only when the SQL test is passed and manually confirmed by the user will the Publish be released to replace the original statistical information.

In this way, we can provide O & M DBAs with an idea of maintaining the stability of the execution plan. By using fixed statistics, the new statistics are pending to fix the original statistics to stabilize the execution plan. Furthermore, the pending statistics are tested. The original plan will be replaced only when the plan is better executed.

Next, we use experiments to verify the use of the pending statistic.

2. experiment environment construction

We chose 11gR2 for the experiment.

SQL> select * from v $ version;

BANNER

-----------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

Create a data table T and corresponding indexes. Note: First, we do not save any data in the data table.

SQL> create table t as select * from dba_objects where 1 = 0;

Table created

SQL> create index idx_t_owner on t (owner );

Index created

SQL> create index idx_t_id on t (object_id );

Index created

When statistics are not explicitly collected, there is no corresponding data table statistics.

SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name = 'T ';

NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

-----------------------------------------------------

SQL> select count (*) from user_tab_col_statistics where table_name = 'T ';

COUNT (*)

----------

0

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR NUM_ROWS from user_ind_statistics where index_name = 'idx _ T_OWNER ';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS

--------------------------------------------

0 0 0 0

Collect statistics to obtain the latest data distribution.

SQL> exec dbms_stats.gather_table_stats (user, 't', cascade => true );

PL/SQL procedure successfully completed

When we modify the data content without collecting statistics, there will be new and old differences.

SQL> insert into t select * from dba_objects;

72202 rows inserted

SQL> commit;

Commit complete

SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name = 'T ';

NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

-----------------------------------------------------

0 0 0 0 0