3、如何收集统计信息

基于成本的优化器（CBO）使用统计信息来确定特定查询的执行计划。随着采样量的变化，采样可能产生不同的统计数据，改变SQL语句执行计划。

在11g时，建议使用计划的统计信息收集脚本收集统计信息。在大多数情况下，默认脚本提供足够的采样级别，并考虑以下建议：

足够大的采样率

在11g的支持下，建议对ESTIMATE_PERCENT使用默认DBMS_STATS.AUTO_SAMPLE_SIZE。这将为表生成100％的估计样本大小（如果可能的话可以在维护窗口中进行）。如果数据量较大，100%采样无法完成，至少使用30％的估计值，由于11g使用散列算法来计算统计量，因此在大多数情况下性能应该是可以接受的。

所有对象收集统计信息

确保所有对象（表和索引）都已收集统计信息。实现这一点的简单方法是使用CASCADE参数。

倾斜数据列收集直方图

确保任何具有偏斜数据分布的列都收集了直方图，并使用METHOD_OPT参数以足够的分辨率进行。通常，支持建议使用“AUTO”的默认列统计信息设置，这意味着DBMS_STATS将决定哪些列添加直方图，以确定它们可能有助于生成更好的计划。“，只有在已知需要的情况下才添加直方图”是一种保守和更加规划稳定的方法，而不是在所有列上收集列统计信息。

收集分区对象的全局统计信息

如果分区正在使用，如果可能，由于时间限制，收集全局统计信息。全球统计数据非常重要，但由于需要的大小和时间长短，往往会避免收集。如果100％的样品是不可能的，那么支持将建议至少为1％。收集小样本（例如0.001，0.0001，0.00001等）可以非常有效，但同样地，大部分数据将不被检查，这可能对优化器的计划选择是决定性的。请注意，ESTIMATE_PERCENT参数的可用范围是非常灵活的[0.000001 - > 100]，可以使用非常小的适用于巨大分区表的样本大小。测试将显示每个系统最合适的设置。

4、统计信息收集语句

非分区表：

begin

DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',

tabname => 'TEST',

estimate_percent => 30,

method_opt => 'for all columns size repeat',

no_invalidate => FALSE,

degree => 4,

cascade => TRUE);

end;

分区表：

begin DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',

tabname => 'TEST',

estimate_percent => 30, --关于采集率：1、小于5G，100%收集;2、大于5G，30%收集，根据数据量逐渐降低

method_opt => 'for all columns size repeat',--稳定系统建议使用repeat,有确定直方图收集的，单独执行

no_invalidate => FALSE,

degree => 8, --关于并行度，建议系统总CPU/2,个人经验，建议不超过16

granularity => 'ALL',

cascade => TRUE

);

end;

/

参考文档：

Oracle? Database PL/SQL Packages and Types Reference 11 g Release 2 (11.2)

5、练习2：统计信息收集

CREATE TABLE ht.a_amt_p1

(amt_id number primary key,

cons_no number,

amt_ym varchar2(20),

amt number)

PARTITION BY LIST (amt_ym)

(

PARTITION p01 VALUES ('201701'),

PARTITION p02 VALUES ('201702'),

PARTITION p03 VALUES ('201703'),

PARTITION p04 VALUES ('201704'),

PARTITION p05 VALUES ('201705'),

PARTITION p06 VALUES ('201706'));

insert into ht.a_amt_p1 select * from ht.a_amt;

请分别对表ht.a_amt、ht.a_amt_p1收集统计信息?

SQL> set line 200

col owner for a10

col table_name for a20

col LAST_ANALYZED for a30

select owner,table_name,NUM_ROWS,BLOCKS,PARTITIONED,SAMPLE_SIZE,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi') LAST_ANALYZED from dba_tables where owner='HT';SQL> SQL> SQL> SQL>

OWNER TABLE_NAME NUM_ROWS BLOCKS PARTITION SAMPLE_SIZE LAST_ANALYZED

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

HT A_AMT 59968 244 NO 59968 2017-08-17 22:01

HT C_CONS 9817 58 NO 2945 2017-08-17 13:17

HT A_AMT_BAK 59967 247 NO 59967 2017-08-17 22:01

HT TEST 77695 1261 NO 77695 2017-08-18 22:03

HT A_AMT_P1 59968 276 YES 59968 2017-08-19 18:02

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',tabname => 'A_AMT',estimate_percent => 100,method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 4,cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',tabname => 'A_AMT_P1',estimate_percent => 100,method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 4,granularity => 'ALL',cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> set line 200

col owner for a10

col table_name for a20

col LAST_ANALYZED for a30

select owner,table_name,NUM_ROWS,BLOCKS,PARTITIONED,SAMPLE_SIZE,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi') LAST_ANALYZED from dba_tables where owner='HT';SQL> SQL> SQL> SQL>

OWNER TABLE_NAME NUM_ROWS BLOCKS PARTITION SAMPLE_SIZE LAST_ANALYZED

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

HT A_AMT 59968 244 NO 59968 2017-08-19 18:33

HT C_CONS 9817 58 NO 2945 2017-08-17 13:17

HT A_AMT_BAK 59967 247 NO 59967 2017-08-17 22:01

HT TEST 77695 1261 NO 77695 2017-08-18 22:03

HT A_AMT_P1 59968 276 YES 59968 2017-08-19 18:33

SQL> !date

Sat Aug 19 18:34:27 CST 2017

SQL>

6、小结

1、统计信息采样率建议100%，如果数据量较大，至少建议30%。-- estimate_percent => 30, --关于采集率：1、小于5G，100%收集;2、大于5G，30%收集，根据数据量逐渐降低

2、所有对象收集统计信息，使用 CASCADE参数。-- cascade => TRUE

3、稳定系统直方图收集，建议 method_opt => 'for all columns size repeat'，否则根据需求收集。

4、分区表，收集全局统计信息， -- granularity => 'ALL'。

5、关于并行度，建议系统总CPU/2,个人经验，建议不超过32 -- degree => 8,

ITPub博客

微课sql优化(3)、统计信息收集(2)-如何收集统计信息

3、如何收集统计信息

4、统计信息收集语句

参考文档：

Oracle? Database PL/SQL Packages and Types Reference 11 g Release 2 (11.2)

5、练习2：统计信息收集

6、小结

1、统计信息采样率建议100%，如果数据量较大，至少建议30%。-- estimate_percent => 30, --关于采集率：1、小于5G，100%收集;2、大于5G，30%收集，根据数据量逐渐降低

2、所有对象收集统计信息，使用 CASCADE参数。-- cascade => TRUE

3、稳定系统直方图收集，建议 method_opt => 'for all columns size repeat'，否则根据需求收集。

4、分区表，收集全局统计信息， -- granularity => 'ALL'。

5、关于并行度，建议系统总CPU/2,个人经验，建议不超过32 -- degree => 8,

最新文章

ITPub博客

微课sql优化(3)、统计信息收集(2)-如何收集统计信息

3、如何收集统计信息

4、统计信息收集语句

参考文档：

Oracle? Database PL/SQL Packages and Types Reference 11 g Release 2 (11.2)

5、练习2：统计信息收集

6、小结

1、统计信息采样率建议100%，如果数据量较大，至少建议30%。-- estimate_percent => 30, --关于采集率：1、小于5G，100%收集;2、大于5G，30%收集，根据数据量逐渐降低

2、所有对象收集统计信息，使用 CASCADE参数。-- cascade => TRUE

3、稳定系统直方图收集，建议 method_opt => 'for all columns size repeat'，否则根据需求收集。

4、 分区表， 收集全局统计信息， -- granularity => 'ALL'。

5、 关于并行度，建议系统总CPU/2,个人经验，建议不超过32 -- degree => 8,

最新文章

4、分区表，收集全局统计信息， -- granularity => 'ALL'。

5、关于并行度，建议系统总CPU/2,个人经验，建议不超过32 -- degree => 8,