ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Applying “Incremental Statistic” for Oracle Big Partition Table

Applying “Incremental Statistic” for Oracle Big Partition Table

原创 Linux操作系统 作者:realkid4 时间:2012-10-10 14:39:07 0 删除 编辑

 

In CBO, Statistic is critical important for Optimizer. Precise and timely statistics will truly reflect the data distribution and volume, and generate wiser SQL Execution Plan (SEP). After years of improvement, CBO is already widely accepted as the default and desirable solution for Oracle Optimizer.

 

As I mention in many blogs before, statistic is like the raw material for CBO optimizer. Without accurate statistic, it’s impossible to generate optimal SEP for CBO. So in CBO era, DBAs’ concern is about how to collect data statistic and finding out the proper frequency to collect them.

 

Since 10g, Oracle introduces a new feature of data statistic job to collect statistic automatically. The job will run at daily interval to collect statistic, and provide most database object statistic to CBO. The new feature solves many problems in most cases. The statistic collection job and mature dynamic sampling sweep the barrier of CBO’s usage.

 

1.      Big Partition Table Statistic

 

Frankly speaking, Oracle internal functions are already enough for ordinary system requirement. But for some special cases, life would be tougher.

 

For many OLTP/OLAP systems, they usually load bulk of data into some extremely huge table (Mostly Partition Tables) in the night and some other business-free time, and then doing the processing work. The problem is that Oracle would have few chances to collect statistic after the data loading, which would influence the data volume and distribution information.

 

The problem would finally lead to the bad performance in the next processing work. In production environments, we often receive complains about some job processing is extremely slow. But after clearly examination, the old statistic is main reason. After collection, the job would be better. And then in the next loading, errors would happen again.

 

If we insert the statistic collection statement between data loading and processing, there would be some time consuming drawbacks for some big partition tables. For most partitions in these tables, the data is stable, and loading work only affects one or two partitions. But for normal partition statistic works, Oracle will collect all partition including inactive partitions, which consume a lot of resource.

 

In 11g, Oracle introduces a new feature named “Incremental Statistic”, which will only collect the partitions which are new or undergoing huge data changes. So it will save the time of collecting inactive partitions and make collection work shorter.

 

2.      Environment Introduction

 

The new feature of “Incremental Statistic” is first introduced in the update edition of 10.2.0.4, and become formal in Oracle 11gR1. So we chose the Oracle 11gR2 as the test environment.

 

 

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

 

 

I prepare a normal list partition table named T_PART.

 

 

SQL> create table t_part

  2  ( owner varchar2(100),

  3    object_id number,

  4    object_name varchar2(100),

  5    create_date date

  6  )

  7  partition by list(owner)

  8  (partition t_part_pub values ('PUBLIC'),

  9   partition t_part_sys values ('SYS'),

 10   partition t_part_big1 values ('APEX_030200','SYSMAN'),

 11   partition t_part_big2 values ('ORDSYS','MDSYS'),

 12   partition t_part_other values(default));

 

Table created

 

 

3.      No-Incremental Statistic Behavior

 

Firstly, let’s see the default Oracle behavior. for partition statistic.

 

 

SQL> insert into t_part select owner, object_id, object_name, sysdate from dba_objects where owner in ('PUBLIC','ORDSYS');

30228 rows inserted

 

SQL> commit;

Commit complete

 

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

PL/SQL procedure successfully completed

 

SQL> select partition_name, NUM_ROWS, BLOCKS, LAST_ANALYZED, global_stats from dba_tab_partitions where table_owner='SYS' and table_name='T_PART';

 

PARTITION_NAME    NUM_ROWS     BLOCKS LAST_ANALYZED        GLOBAL_STATS

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

T_PART_PUB           27696        197 2012/10/9 22:08:11   YES

T_PART_SYS               0          0 2012/10/9 22:08:11   YES

T_PART_BIG1              0          0 2012/10/9 22:08:10   YES

T_PART_BIG2           2532         20 2012/10/9 22:08:10   YES

T_PART_OTHER             0          0 2012/10/9 22:08:10   YES

 

 

The default behavior. is that Oracle will collect all partitions according to the last_analyzed column in view dba_tab_partition.

 

Doing some modification focus on some partitions, let’s see the collection results.

 

 

SQL> insert into t_part select owner, object_id, object_name, sysdate from dba_objects where owner in ('SYS','APEX_030200');

 

33241 rows inserted

 

SQL> commit;

Commit complete

 

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

PL/SQL procedure successfully completed

 

SQL> select partition_name, NUM_ROWS, BLOCKS, LAST_ANALYZED, global_stats from dba_tab_partitions where table_owner='SYS' and table_name='T_PART';

 

PARTITION_NAME    NUM_ROWS     BLOCKS LAST_ANALYZED        GLOBAL_STATS

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

T_PART_PUB           27696        197 2012/10/9 22:13:31   YES

T_PART_SYS           30835        197 2012/10/9 22:13:31   YES

T_PART_BIG1           2406         20 2012/10/9 22:13:30   YES

T_PART_BIG2           2532         20 2012/10/9 22:13:31   YES

T_PART_OTHER             0          0 2012/10/9 22:13:31   YES

 

 

The insert statement only affects partition T_PART_SYS and T_PART_BIG1. But when we run the collection statement, all partitions will getting involved in the collection work.

 

4.      Incremental Statistic Setting

 

It’s easy to change the statistic strategy for big partition table in Oracle 11g. Using the package dbms_stats will help setting the parameters.

 

The default strategy for Oracle partition table is not incremental setting. Only three parameters will affect the behavior.

 

 

SQL> select dbms_stats.get_prefs('PUBLISH','SYS','T_PART') from dual;

 

DBMS_STATS.GET_PREFS('PUBLISH'

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

TRUE

 

SQL> select dbms_stats.get_prefs('INCREMENTAL','SYS','T_PART') from dual;

 

DBMS_STATS.GET_PREFS('INCREMEN

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

FALSE

 

SQL> select dbms_stats.get_prefs('GRANULARITY','SYS','T_PART') from dual;

 

DBMS_STATS.GET_PREFS('GRANULAR

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

AUTO

 

 

Parameter “PUBLISH” is used to determinate whether CBO use the newest statistic when new statistic coming. It relates to pending statistic feature in most cases. And we need to ensure its value is true (Default Value).

 

Parameter “Incremental” default value is false, which means it would not adopt incremental statistic strategy by default.

 

Parameter “GRANULARITY” is not related to incremental statistic strategy in Oracle 11g, but related in Oracle 10gR2. So we need to keep it “AUTO” in Oracle 11g environment.

 

The estimated_precent should be keep to “AUTO”, in order to maintain the incremental statistic.

 

In the next section, we will see the effect of the new feature.

 

5.      Incremental Statistic Behavior

 

First, we need to change the setting for Big partition table.

 

 

SQL> exec dbms_stats.set_table_prefs(user,'T_PART',pname => 'INCREMENTAL',pvalue => 'TRUE');

 

PL/SQL procedure successfully completed

 

SQL> select dbms_stats.get_prefs('INCREMENTAL','SYS','T_PART') from dual;

 

DBMS_STATS.GET_PREFS('INCREMEN

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

TRUE

 

 

Do some modification work to T_PART, and see the effect of collection.

 

 

SQL> select partition_name, NUM_ROWS, BLOCKS, LAST_ANALYZED, global_stats from dba_tab_partitions where table_owner='SYS' and table_name='T_PART';

 

PARTITION_NAME    NUM_ROWS     BLOCKS LAST_ANALYZED        GLOBAL_STATS

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

T_PART_PUB           27696        197 2012/10/9 22:13:31   YES

T_PART_SYS           30835        197 2012/10/9 22:13:31   YES

T_PART_BIG1           2406         20 2012/10/9 22:13:30   YES

T_PART_BIG2           2532         20 2012/10/9 22:13:31   YES

T_PART_OTHER             0          0 2012/10/9 22:13:31   YES

 

 

SQL> insert into t_part select owner, object_id, object_name, sysdate from dba_objects where owner not in ('SYS','APEX_030200','PUBLIC','SYSMAN','ORDSYS','MDSYS');

3465 rows inserted

 

SQL> insert into t_part select owner, object_id, object_name, sysdate from dba_objects where owner in ('SYS');

 

30835 rows inserted

 

SQL> commit;

Commit complete

 

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

PL/SQL procedure successfully completed

 

SQL> select partition_name, NUM_ROWS, BLOCKS, LAST_ANALYZED, global_stats from dba_tab_partitions where table_owner='SYS' and table_name='T_PART';

 

PARTITION_NAME    NUM_ROWS     BLOCKS LAST_ANALYZED        GLOBAL_STATS

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

T_PART_PUB           27696        197 2012/10/9 22:29:26   YES

T_PART_SYS           61670        398 2012/10/9 22:29:21   YES

T_PART_BIG1           2406         20 2012/10/9 22:29:29   YES

T_PART_BIG2           2532         20 2012/10/9 22:29:22   YES

T_PART_OTHER          3465         23 2012/10/9 22:29:28   YES

 

 

The result is weird. Only two partitions are affected in the insert serial, but after collection, we find all partitions are collected. Not the things should be~

 

Let’s do more continue.

 

--Delete only One Partition Data

SQL> delete t_part where wner='SYS';

61670 rows deleted

 

SQL> commit;

Commit complete

 

SQL> exec dbms_stats.gather_table_stats(user,'T_PART');

PL/SQL procedure successfully completed

 

SQL> select partition_name, NUM_ROWS, BLOCKS, LAST_ANALYZED, global_stats from dba_tab_partitions where table_owner='SYS' and table_name='T_PART';

 

PARTITION_NAME    NUM_ROWS     BLOCKS LAST_ANALYZED        GLOBAL_STATS

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

T_PART_PUB           27696        197 2012/10/9 22:29:26   YES

T_PART_SYS               0        398 2012/10/9 22:42:47   YES

T_PART_BIG1           2406         20 2012/10/9 22:29:29   YES

T_PART_BIG2           2532         20 2012/10/9 22:29:22   YES

T_PART_OTHER          3465         23 2012/10/9 22:29:28   YES

 

 

The desired result comes out. Only the affected partitions is collected according to the last_analyzed column, and the table analyzed time is also changed.

 

 

SQL> select table_name, last_analyzed from dba_tables where wner='SYS' and table_name='T_PART';

 

TABLE_NAME                     LAST_ANALYZED

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

T_PART                         2012/10/9 22:42:49

 

 

If we affect more than one partition at once, how things would be.

 

 

SQL> insert into t_part select owner, object_id, object_name, sysdate from dba_objects where owner in ('PUBLIC');

27696 rows inserted

 

SQL> insert into t_part select owner, object_id, object_name, sysdate from dba_objects where owner in ('APEX_030200');

2406 rows inserted

 

SQL> commit;

Commit complete

 

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

PL/SQL procedure successfully completed

 

SQL> select partition_name, NUM_ROWS, BLOCKS, LAST_ANALYZED, global_stats from dba_tab_partitions where table_owner='SYS' and table_name='T_PART';

 

PARTITION_NAME    NUM_ROWS     BLOCKS LAST_ANALYZED        GLOBAL_STATS

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

T_PART_PUB           55392        388 2012/10/9 22:47:07   YES

T_PART_SYS           30874        398 2012/10/9 22:45:21   YES

T_PART_BIG1           4812         36 2012/10/9 22:47:08   YES

T_PART_BIG2           2532         20 2012/10/9 22:29:22   YES

T_PART_OTHER          3465         23 2012/10/9 22:29:28   YES

 

 

The truncate statement will affect the partitions and let’s see the results.

 

 

SQL> truncate table t_part;

Table truncated

 

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

PL/SQL procedure successfully completed

 

SQL> select partition_name, NUM_ROWS, BLOCKS, LAST_ANALYZED, global_stats from dba_tab_partitions where table_owner='SYS' and table_name='T_PART';

 

PARTITION_NAME    NUM_ROWS     BLOCKS LAST_ANALYZED        GLOBAL_STATS

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

T_PART_PUB               0          0 2012/10/9 22:55:31   YES

T_PART_SYS               0          0 2012/10/9 22:55:31   YES

T_PART_BIG1              0          0 2012/10/9 22:55:31   YES

T_PART_BIG2              0          0 2012/10/9 22:55:31   YES

T_PART_OTHER             0          0 2012/10/9 22:55:31   YES

 

 

SQL> insert into t_part select owner, object_id, object_name, sysdate from dba_objects where owner in ('SYS','APEX_030200');

33280 rows inserted

 

SQL> commit;

Commit complete

 

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

 

PL/SQL procedure successfully completed

 

SQL> select partition_name, NUM_ROWS, BLOCKS, LAST_ANALYZED, global_stats from dba_tab_partitions where table_owner='SYS' and table_name='T_PART';

 

PARTITION_NAME    NUM_ROWS     BLOCKS LAST_ANALYZED        GLOBAL_STATS

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

T_PART_PUB               0          0 2012/10/9 22:55:31   YES

T_PART_SYS           30874        202 2012/10/9 22:58:05   YES

T_PART_BIG1           2406         20 2012/10/9 22:58:07   YES

T_PART_BIG2              0          0 2012/10/9 22:55:31   YES

T_PART_OTHER             0          0 2012/10/9 22:55:31   YES

 

 

Ok, the result is correct.

 

Only one thing we still wandering: Why we first switch the table to incremental statistic mode, and then we do some modification on some partitions. After collection, we found Oracle will collect the statistic for all partition, and then incremental will happen in the next collection.

 

The reasonable explanation is: Like Oracle incremental backup strategy, although you have a full mode backup, Oracle would do the incremental full backup work at first time. As for incremental statistic, things are similar. Oracle first needs a full incremental statistic, and then collects the increments.

 

6.      Conclusion

 

The advantage of incremental statistic is that they can collect only active partitions’ data in the shorter time. The inactive partitions which do not have significant data modification operation will not getting involved in the collection work.

 

For the load data and then process jobs, it is the desirable collection strategy.

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-746066/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7814737