dbms_stats包中的主要存储过程: 参考博客
1、dbms_stats.gather_table_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 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 | | 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 off
SQL> 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
----------
72473
SQL> 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(*)
----------
2856
SQL>
使用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 off
SQL> select count(*) from h;
COUNT(*)
----------
2783
SQL>
可以看出使用动态采样rows的值更接近于真实值,当表没有被分析时使用动态采样。