dbms_stats包dbms_stats.gather_table_stats&动态采样

来源：互联网发布：新手怎么做淘宝编辑：程序博客网时间：2024/05/17 13:08

dbms_stats包中的主要存储过程：参考博客

1、dbms_stats.gather_table_stats

----DBMS_STATS.GATHER_TABLE_STATS实例SQL> create table t as select * from dba_objects;表已创建。SQL> create index ind_t_type on t(object_type);索引已创建。SQL> select num_rows from dba_tables where table_name = 'T';  NUM_ROWS----------SQL> set autot trace expSQL> select object_id,object_name from t where object_type = 'TABLE';执行计划----------------------------------------------------------Plan hash value: 3351898915------------------------------------------------------------------------------------------| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |            |  2500 |   219K|   110   (0)|00:00:02 ||   1 |  TABLE ACCESS BY INDEX ROWID| T          |  2500 |   219K|   110   (0)|00:00:02 ||*  2 |   INDEX RANGE SCAN          | IND_T_TYPE |  2500 |       |     7   (0)|00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_TYPE"='TABLE')Note-----   - dynamic sampling used for this statement (level=2)--因表T没有被分析，所以此处用了动态采样统计信息，因用了动态采样，使rows值接近于真实值。SQL> set autot offSQL> select num_rows from dba_tables where table_name = 'T';  NUM_ROWS----------SQL> exec dbms_stats.gather_table_stats('SYSTEM','T',estimate_percent => 100,cascade => true,method_opt => 'FOR ALL COLUMNS SIZE AUTO');PL/SQL 过程已成功完成。SQL> select num_rows from dba_tables where table_name = 'T';  NUM_ROWS----------     72473SQL> SET AUTOT TRACE EXP;SQL> select object_id,object_name from t where object_type = 'TABLE';执行计划----------------------------------------------------------Plan hash value: 3351898915------------------------------------------------------------------------------------------| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |            |  2856 |   108K|   127   (0)|00:00:02 ||   1 |  TABLE ACCESS BY INDEX ROWID| T          |  2856 |   108K|   127   (0)|00:00:02 ||*  2 |   INDEX RANGE SCAN          | IND_T_TYPE |  2856 |       |     8   (0)|00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_TYPE"='TABLE')SQL> select count(*) from t where object_type = 'TABLE';  COUNT(*)----------      2856SQL>

使用gather_table_stats前dba_tables中的num_rows字段为空，收集表的统计信息之后num_rows的值为表的行数。

执行计划中rows值在收集统计信息之后较为接近真实行数，使优化器在选择更优的执行计划

收集统计信息使优化器选择更优的执行计划。

2、动态采样dynamic sampling 参考博客

---动态采样实例，/*+ dynamic_sampling(h 0) */  动态采样级别设置为0表示不做动态采样SQL> create table h as select * from dba_tables;表已创建。SQL> select /*+ dynamic_sampling(h 0) */ * from h;----不做动态采样执行计划----------------------------------------------------------Plan hash value: 3725287857--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |  7923 |  3992K|    29   (4)| 00:00:01 ||   1 |  TABLE ACCESS FULL| H    |  7923 |  3992K|    29   (4)| 00:00:01 |--------------------------------------------------------------------------SQL> select count(*) from h;----动态采样执行计划----------------------------------------------------------Plan hash value: 3272090800-------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |-------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |     1 |    28   (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |      |     1 |            |          ||   2 |   TABLE ACCESS FULL| H    |  2771 |    28   (0)| 00:00:01 |-------------------------------------------------------------------Note-----   - dynamic sampling used for this statement (level=2)----动态采样SQL>SQL> set autot offSQL> select count(*) from h;COUNT(*)----------      2783SQL>

可以看出使用动态采样rows的值更接近于真实值，当表没有被分析时使用动态采样。

0 0