表分区的性能分析-技术开发专区

表分区的性能分析

作者：IT168 开心果译编辑：晓熊 2008-12-26 11:15 来源：IT168�

　　【IT168 技术文档】

　　DBMS_STATS vs. ANALYZE

　　基于成本的优化依赖于统计数据，应该统计你的SQL语句访问到的所有表、簇和所有类型的索引，如果你的表大小和数据分布改变得很频繁，那就要定期生成统计数据，要让统计数据反应的是表中数据的真实情况。　

SELECT * FROM TEST PARTITION;

　　这个语句使用了全局查询，没有使用谓词。

　SELECT * FROM TEST S WHERE S.AMOUNT_OF_SHIFT > 1000;

　　这个语句为所有分区使用了一个谓词，但可能也使用了全局查询。

SELECT * FROM TEST PARTITION (SEP2009) S WHERE S.AMOUNT_OF_SHIFT > 1000;

　　这个语句使用了全局查询，并只为一个分区使用了谓词。

　　使用DBMS_STATS包收集全局统计数据更有用，因为ANALYZE总是串行的，而DBMS_STATS既可以串行又可以并行，只要可能，DBMS_STATS会使用并行方式收集统计数据，否则，它就调用串行查询或ANALYZE语句，但索引统计数据不能并行收集。

　　ANALYZE从单个分区收集统计数据，然后从分区统计数据计算出全局统计数据;DBMS_STATS可以为每个分区单独收集统计数据，并且可以为整个表或索引收集全局统计数据。根据被优化的不同SQL语句，优化器可能会选择分区(或子分区)统计，也可能选择全局统计。　

CREATE TABLE PARTTAB(

　　ordid NUMBER,

　　PARTCOL DATE,

　　DETAILS NUMBER,

　　AMOUNT NUMBER)

　　PARTITION BY RANGE(PARTCOL)

　　SUBPARTITION BY HASH(DETAILS) SUBPARTITIONS 2

　　(PARTITION q1 VALUES LESS THAN(TO_DATE('01-04-2009','DD-MM-YYYY')) TABLESPACE TBLSPCE1,

　　PARTITION q2 VALUES LESS THAN(TO_DATE('01-07-2009','DD-MM-YYYY')) TABLESPACE TBLSPCE2,

　　PARTITION q3 VALUES LESS THAN(TO_DATE('01-10-2009','DD-MM-YYYY')) TABLESPACE TBLSPCE3,

　　PARTITION q4 VALUES LESS THAN(TO_DATE('01-12-2009','DD-MM-YYYY')) TABLESPACE TBLSPCE4

　　);

　　一个本地非前缀索引将会与之关联：

　CREATE INDEX IDX_PARTTAB ON PARTTAB (ordid) LOCAL;

　　在开始下面的例子之前，表PARTTAB已经创建完毕。

　　GATHER_TABLE_STATS

　　收集表、列和索引统计数据。

SQL> execute dbms_stats.gather_table_stats(-

　　>ownname => 'test',-

　　>tabname => 'PARTTAB',-

　　>partname => null,- --> 收集所有分区状态

　　>estimate_percent => null,- --> 计算模式

　　>block_sample => false,- --> 默认值，计算模式下无意义

　　>method_opt => 'FOR ALL COLUMNS SIZE 1',- --> 表和列统计，不生成直方图

　　>degree => null,- --> 基于PARTTAB表上的DOP设置的默认并行度

　　>granularity => 'default',- -->收集全局和分区统计数据

　　>cascade => true ,- --> 产生所有统计数据

　　>stattab => null,- -->统计数据将被存储在字典中

　　>statid => null,-

　　>statown => null);

　　默认情况下，如果CASCADE=>TRUE，不会计算索引统计。

　SQL> select table_name, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN, GLOBAL_STATS, USER_STATS, sample_size from user_tables

　　where table_name = 'PARTTAB';

　　TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN GLOBAL_STATS USER_STATS SAMPLE_SIZE

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

　　PARTTAB 400 8 0 0 11 YES NO 400

　　至此，统计数据就被更新了。GLOBAL_STATS列也被初始化了。

　SQL> select partition_name "Partition", NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN,

　　SAMPLE_SIZE, global_stats, user_stats

　　from user_tab_partitions

　　where table_name = 'PARTTAB'

　　order by partition_position

　　/

　　Partition NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN GLOBAL_STATS USER_STATS SAMPLE_SIZE

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

　　Q1 100 2 0 0 11 YES NO 100

　　Q2 100 2 0 0 11 YES NO 100

　　Q3 100 2 0 0 11 YES NO 100

　　Q4 100 2 0 0 11 YES NO 100

　　GLOBAL_STATS统计数据在表级再次被收集。

SQL> select partition_name \"Partition\", subpartition_name \"Subpartition\", NUM_ROWS, BLOCKS, EMPTY_BLOCKS

　　SAMPLE_SIZE, global_stats, user_stats

　　from user_tab_subpartitions

　　where table_name = 'PARTTAB'

　　order by partition_name, subpartition_position

　　/

　　Partition Subpartition NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE GLOBAL_STATS USER_STATS

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

　　Q1 SYS_SUBP10365 NO NO

　　Q1 SYS_SUBP10366 NO NO

　　Q2 SYS_SUBP10367 NO NO

　　Q2 SYS_SUBP10368 NO NO

　　Q3 SYS_SUBP10369 NO NO

　　Q3 SYS_SUBP10370 NO NO

　　Q4 SYS_SUBP10371 NO NO

　　Q4 SYS_SUBP10372 NO NO

　　子分区级的统计数据没有被计算进去。　

SQL>select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED from

　　user_tab_col_statistics where table_name = 'PARTTAB'

　　/

　　COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED

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

　　ORDID 0 0 400 1 12-DEC-02

　　PARTCOL 4 .25 0 1 12-DEC-02

　　DETAILS 100 .01 0 1 12-DEC-02

　　AMOUNT 0 0 400 1 12-DEC-02

　　因为这里没有直方图，所有NUM_BUCKETS的值是1，但列统计被初始化了。

　　下面显示的是每个分区列相同的结果：　

SQL>select partition_name, COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED from user_part_col_statistics

　　where table_name = 'PARTTAB'

　　/

　　PARTITION_ COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED

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

　　Q1 ORDID 0 0 100 1 12-DEC-07

　　Q1 PARTCOL 1 1 0 1 12-DEC-07

　　Q1 DETAILS 100 .01 0 1 12-DEC-07

　　Q1 AMOUNT 0 0 100 1 12-DEC-07

　　Q2 ORDID 0 0 100 1 12-DEC-07

　　Q2 PARTCOL 1 1 0 1 12-DEC-07

　　Q2 DETAILS 100 .01 0 1 12-DEC-07

　　Q2 AMOUNT 0 0 100 1 12-DEC-07

　　Q3 ORDID 0 0 100 1 12-DEC-07

　　Q3 PARTCOL 1 1 0 1 12-DEC-07

　　Q3 DETAILS 100 .01 0 1 12-DEC-07

　　Q3 AMOUNT 0 0 100 1 12-DEC-07

　　Q4 ORDID 0 0 100 1 12-DEC-07

　　Q4 PARTCOL 1 1 0 1 12-DEC-07

　　Q4 DETAILS 100 .01 0 1 12-DEC-07

　　Q4 AMOUNT 0 0 100 1 12-DEC-07

　　下面显示的是PARTTAB表的子分区装入的统计数据：　　

SQL> select subpartition_name \"Subpartition\", COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS,

　　NUM_BUCKETS from dba_subpart_col_statistics where table_name = 'PARTTAB'

　　order by column_name

　　/

　　Subpartition COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS

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

　　SYS_SUBP10365 PARTCOL

　　SYS_SUBP10365 ORDID

　　SYS_SUBP10365 DETAILS

　　SYS_SUBP10365 AMOUNT

　　SYS_SUBP10366 PARTCOL

　　SYS_SUBP10366 ORDID

　　SYS_SUBP10366 DETAILS

　　SYS_SUBP10366 AMOUNT

　　SYS_SUBP10367 PARTCOL

　　SYS_SUBP10367 ORDID

　　SYS_SUBP10367 DETAILS

　　SYS_SUBP10367 AMOUNT

　　SYS_SUBP10368 PARTCOL

　　SYS_SUBP10368 ORDID

　　SYS_SUBP10368 DETAILS

　　SYS_SUBP10368 AMOUNT

　　SYS_SUBP10369 PARTCOL

　　SYS_SUBP10369 ORDID

　　SYS_SUBP10369 DETAILS

　　SYS_SUBP10369 AMOUNT

　　SYS_SUBP10370 PARTCOL

　　SYS_SUBP10370 ORDID

　　SYS_SUBP10370 DETAILS

　　SYS_SUBP10370 AMOUNT

　　SYS_SUBP10371 PARTCOL

　　SYS_SUBP10371 ORDID

　　SYS_SUBP10371 DETAILS

　　SYS_SUBP10371 AMOUNT

　　SYS_SUBP10372 PARTCOL

　　SYS_SUBP10372 ORDID

　　SYS_SUBP10372 DETAILS

　　SYS_SUBP10372 AMOUNT

　　子分区列上没有装入统计数据。

　　这里的分区对象包括了不止一套统计数据，这是因为统计数据既可以为整个对象产生，也可以为分区和子分区产生。

关注我们