基础知识之数据分析与动态采样

SQL> show user

USER 为 "YWBZ"

--创建测试用表T

 

SQL> create table t as select *  from dba_objects;

 

表已创建。

--仅打印执行计划

SQL> set autotrace traceonly explain

--查看动态采样时的执行计划

SQL> select object_id from t whereobject_id<1000;

 

执行计划

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

Plan hash value: 1601196873

 

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

| Id | Operation         | Name |Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0| SELECT STATEMENT  |      |   12|   156 |   292  (1)| 00:00:04 |

|*  1|  TABLE ACCESS FULL| T    |    12|   156 |   292  (1)| 00:00:04 |

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

 

Predicate Information (identified byoperation id):

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

 

   1 -filter("OBJECT_ID"<1000)

 

Note

-----

   -dynamic sampling used for this statement (level=2)—默认动态采样级别2

--收集统计信息

SQL> execdbms_stats.gather_table_stats('YWBZ','T');

 

PL/SQL 过程已成功完成。

--再次查看执行计划

SQL> select object_id from t whereobject_id<1000;

 

执行计划

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

Plan hash value: 1601196873

 

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

| Id | Operation         | Name |Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0| SELECT STATEMENT  |      |  876|  4380 |   292  (1)| 00:00:04 |

|*  1|  TABLE ACCESS FULL| T    |   876 |  4380 |  292   (1)| 00:00:04 |

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

 

Predicate Information (identified byoperation id):

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

 

   1- filter("OBJECT_ID"<1000)

--实际记录数

SQL> select count(*) from t where object_id<1000;

 

 COUNT(*)

----------

      942
综上所示：表分析后执行计划比动态采样更准确