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

SQL> show userUSER 为 "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综上所示：表分析后执行计划比动态采样更准确