您的位置：首页 → 技术开发 → 数据库文摘 → Oracle统计信息与子分区

Oracle统计信息与子分区

时间：2010/1/19 14:33:00来源：本站整理作者：我要评论(0)

大家都知道，子分区有两种，一种是分区为RANGE，子分区为HASH，另一种是分区为RANGE，子分区为LIST。在10g数据库中，如果在使用dbms_stats收集统计信息时，如果没有显式指定granularity(粒度)，那么granularity就会取自dbms_stats配置：

　　而其缺省值是“AUTO”，而不再是9i下的”DEFAULT”：

　SQL> select dbms_stats.get_param('granularity') param from dual;

　　PARAM

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

　　AUTO

　　SQL> select dbms_stats.get_param('granularity') param from dual;

　　PARAM

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

　　AUTO

　　而10g自带的自动收集统计信息的任务“GATHER_STATS_JOB”，其granularity同样是取自granularity param。当然可以通过下面的SQL来更改其值：

SQL> exec dbms_stats.set_param('granularity','global and partition');

　　SQL> exec dbms_stats.set_param('granularity','global and partition');

　　这样更改后，dbms_stats默认就会收集表以及分区级统计信息，不收集子分区级统计信息。

　　那么，granularity=auto时，到底是怎么样的呢?前面说到了子分区是以list方式分区时，那么就会收集子分区级统计信息，其言外之意就是如果子分区是以hash方式分区时就不会收集子分区统计信息了。到底是不是这样呢?下面做个测试，测试环境是Oracle 10.2.0.4 for Linux AS4：

　SQL> create table t1

　　2 partition by range(object_id)

　　3 subpartition by hash(data_object_id)

　　4 subpartitions 4

　　5 ( partition p1 values less than(10000),

　　6 partition p2 values less than(20000),

　　7 partition p3 values less than (maxvalue)

　　8 )

　　9 as select * from dba_objects;

　　Table created.

　　SQL> create table t2

　　2 partition by range(object_id)

　　3 subpartition by list(object_type)

　　4 subpartition template(

　　5 subpartition sp1 values ('TABLE'),

　　6 subpartition sp2 values ('INDEX'),

　　7 subpartition sp3 values ('VIEW'),

　　8 subpartition sp4 values (DEFAULT)

　　9 )

　　10 ( partition p1 values less than(10000),

　　11 partition p2 values less than(20000),

　　12 partition p3 values less than (maxvalue)

　　13 )

　　14 as select * from dba_objects;

　　Table created.

　　SQL> create table t1

　　2 partition by range(object_id)

　　3 subpartition by hash(data_object_id)

　　4 subpartitions 4

　　5 ( partition p1 values less than(10000),

　　6 partition p2 values less than(20000),

　　7 partition p3 values less than (maxvalue)

　　8 )

　　9 as select * from dba_objects;

　　Table created.

　　SQL> create table t2

　　2 partition by range(object_id)

　　3 subpartition by list(object_type)

　　4 subpartition template(

　　5 subpartition sp1 values ('TABLE'),

　　6 subpartition sp2 values ('INDEX'),

　　7 subpartition sp3 values ('VIEW'),

　　8 subpartition sp4 values (DEFAULT)

　　9 )

　　10 ( partition p1 values less than(10000),

　　11 partition p2 values less than(20000),

　　12 partition p3 values less than (maxvalue)

　　13 )

　　14 as select * from dba_objects;

　　Table created.

　　我们先建再从个测试表，表T1是RANGE+HASH方式的复合(组合)分区表，表T2是RANGE+LIST方式的复合分区表。

　　下面将”granularity” param重新设回为”auto“，然后收集T1和T2的统计信息：

SQL> exec dbms_stats.set_param('granularity','auto');

　　PL/SQL procedure successfully completed.

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

　　PL/SQL procedure successfully completed.

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

　　PL/SQL procedure successfully completed.

　　SQL> exec dbms_stats.set_param('granularity','auto');

　　PL/SQL procedure successfully completed.

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

　　PL/SQL procedure successfully completed.

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

　　PL/SQL procedure successfully completed.

　　接下来我们看看T1和T2子分区的统计信息，首先是T1表的：

　***************

　　SubPartition Level

　　***************

　　Partition SubPartition Number Chain Average

　　Name Name of Rows Blocks Count Row Len

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

　　P1 SYS_SUBP57

　　P2 SYS_SUBP61

　　P3 SYS_SUBP65

　　P2 SYS_SUBP62

　　P3 SYS_SUBP66

　　P1 SYS_SUBP58

　　SYS_SUBP59

　　P2 SYS_SUBP63

　　P3 SYS_SUBP67

　　P1 SYS_SUBP60

　　P3 SYS_SUBP68

　　P2 SYS_SUBP64

　　***************

　　SubPartition Level

　　***************

　　Partition SubPartition Number Chain Average

　　Name Name of Rows Blocks Count Row Len

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

　　P1 SYS_SUBP57

　　P2 SYS_SUBP61

　　P3 SYS_SUBP65

　　P2 SYS_SUBP62

　　P3 SYS_SUBP66

　　P1 SYS_SUBP58

　　SYS_SUBP59

　　P2 SYS_SUBP63

　　P3 SYS_SUBP67

　　P1 SYS_SUBP60

　　P3 SYS_SUBP68

　　P2 SYS_SUBP64

　　可以看到子分区没有任何统计信息，再看看T2表的子分区：

　***************

　　SubPartition Level

　　***************

　　Partition SubPartition Number Chain Average

　　Name Name of Rows Blocks Count Row Len

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

　　P2 P2_SP1 370 6 0 92

　　P3 P3_SP1 346 5 0 86

　　P1 P1_SP1 818 11 0 82

　　P3 P3_SP2 303 4 0 88

　　P1 P1_SP2 916 12 0 85

　　P2 P2_SP2 430 6 0 91

　　P2_SP3 252 4 0 86

　　P3 P3_SP3 517 7 0 87

　　P1 P1_SP3 2,839 34 0 78

　　P2 P2_SP4 8,722 124 0 95

　　P1 P1_SP4 4,973 64 0 86

　　P3 P3_SP4 24,900 352 0 96

　　***************

　　SubPartition Level

　　***************

　　Partition SubPartition Number Chain Average

　　Name Name of Rows Blocks Count Row Len

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

　　P2 P2_SP1 370 6 0 92

　　P3 P3_SP1 346 5 0 86

　　P1 P1_SP1 818 11 0 82

　　P3 P3_SP2 303 4 0 88

　　P1 P1_SP2 916 12 0 85

　　P2 P2_SP2 430 6 0 91

　　P2_SP3 252 4 0 86

　　P3 P3_SP3 517 7 0 87

　　P1 P1_SP3 2,839 34 0 78

　　P2 P2_SP4 8,722 124 0 95

　　P1 P1_SP4 4,973 64 0 86

　　P3 P3_SP4 24,900 352 0 96

　　可以看到T2表的子分区是有统计信息的。 这也证明了前面说到的，如果granularity为auto，list类型的子分区会收集统计信息，而hash类型的子分区不收集统计信息。

　　大多数Oracle数据库里面没有使用子分区，就算有，也比较少量。不过下面一个库的一些统计数据，是我写这篇文章的初衷：

SQL> select sum(bytes)/1024/1024 size_mb from dba_data_files where tablespace_name='SYSTEM';

　　SIZE_MB

　　----------

　　10233.9922

　　SQL> select /*+ parallel(a,8) */ count(*) from hist_head$ a;

　　COUNT(*)

　　----------

　　32643376

　　SQL> select /*+ parallel(a,8) */ count(*) from HISTGRM$ a;

　　COUNT(*)

　　----------

　　14531284

　　SQL> select count(*) from dba_tables;

　　COUNT(*)

　　----------

　　1097

　　SQL> select count(*) from dba_tab_partitions;

　　COUNT(*)

　　----------

　　5492

　　SQL> select count(*) from dba_tab_subpartitions;

　　COUNT(*)

　　----------

　　908470

　　SQL> select count(*) from dba_tables where partitioned='YES';

　　COUNT(*)

　　----------

　　162

　　SQL> select sum(bytes)/1024/1024 size_mb from dba_data_files where tablespace_name='SYSTEM';

　　SIZE_MB

　　----------

　　10233.9922

　　SQL> select /*+ parallel(a,8) */ count(*) from hist_head$ a;

　　COUNT(*)

　　----------

　　32643376

　　SQL> select /*+ parallel(a,8) */ count(*) from HISTGRM$ a;

　　COUNT(*)

　　----------

　　14531284

　　SQL> select count(*) from dba_tables;

　　COUNT(*)

　　----------

　　1097

　　SQL> select count(*) from dba_tab_partitions;

　　COUNT(*)

　　----------

　　5492

　　SQL> select count(*) from dba_tab_subpartitions;

　　COUNT(*)

　　----------

　　908470

　　SQL> select count(*) from dba_tables where partitioned='YES';

　　COUNT(*)

　　----------

　　162

　　上面一组数据来源于某个10g的数据库，SYSTE表空间超过了10g，仅仅只有162个分区表，而子分区数达到了90万个。而与列的统计信息有关的再从个数据字典表，一个其行数达到了3200多万行，另一个其行数达到了1400多万行。终其原因，就在于这个系统太多的子分区，而绝大多数子分区是LIST类型分区的，这样收集统计信息时，会收集子分区一级统计信息，这样导致列统计信息占用了大量的SYSTEM表空间，使SYSTEM表空间暴涨。

　　sys.hist_head$主要存储包括high value和low value的列的统计信息，而sys.histgrm$表存储”column size > 1″时的直方图数据。看看上面提到的hist_head$表，如果一个表，有100个子分区，共有20列，那么在收集了子分区统计信息而收集统计信息时收集了所有列的统计信息的情况下，hist_head$仅包含子分区一级就会有2000行数据。这不难理解上面提到的库，hist_head$为什么会有3200多万行数据。

　　另外值得注意的是，如果10g开启了recyclebin特性，表只是被DROP，还未被PURGE的情况下，其统计信息仍然存储在数据字典中。

　　在数据库中有大量子分区时，是否应该子分区的统计信息，和怎么样收集子分区的统计信息，应结合应用情况，需要仔细考虑。应当避免如上面提到的数据库，过多的统计数据导致SYSTEM表空间暴涨。

相关视频

    没有数据

相关阅读 Oracle PRKC-1002错误原因和解决方案oracle10g安装图解(win7)Oracle错误代码大全oracle查看实例名方法Oracle表空间恢复方案微软亚太研发集团总部大楼 扎根中国"硅谷"亚马逊网络服务推出Oracle RDS如何关闭oracle rac选项 make rac_off

文章评论
发表评论

热门文章 没有查询到任何记录。

最新文章 没有查询到任何记录。 Oracle如何提高SQL执行效率Oracle统计信息与子分区为什么对数据库要加DISTINCTOracle SQL 常见问题方案(一)

人气排行 SQL水晶报表存储过程为什么对数据库要加DISTINCTOracle如何提高SQL执行效率MySQL从后门进企业市场常见数据库系统之比较 - Oracle数据库常见数据库系统之比较 - SYBASE 和 SQL SER用wu-ftpd架设FTP服务器常见数据库系统之比较 - DB2数据库