SQL> show parameter optimizer_dynamic_sam

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL> create table t_sample(a int);

表已创建。

SQL> insert into t_sample select level from dual connect by level<=2e6;

已创建2000000行。

SQL> commit;

提交完成。

SQL> explain plan for select count(a) from t_sample;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3712214945

-------------------------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    13 |   548   (3)| 00:00:07 |
|   1 | SORT AGGREGATE    |          |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| T_SAMPLE | 2435K|    30M|   548   (3)| 00:00:07 |
-------------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----
- dynamic sampling used for this statement (level=2)

已选择13行。

SQL> alter session set optimizer_dynamic_sampling=3;

会话已更改。

SQL> explain plan for select count(a) from t_sample;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3712214945

-------------------------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    13 |   548   (3)| 00:00:07 |
|   1 | SORT AGGREGATE    |          |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| T_SAMPLE | 2435K|    30M|   548   (3)| 00:00:07 |
-------------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----
- dynamic sampling used for this statement (level=3)

已选择13行。

SQL> alter session set optimizer_dynamic_sampling=0;

会话已更改。

SQL> explain plan for select count(a) from t_sample;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3712214945

-------------------------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    13 |     2   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |          |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| T_SAMPLE |    82 | 1066 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

已选择9行。

SQL>
小结:1,如无收集统计信息,会启用动态采样
     2,动态采样由参数optimizer_dynamic_sampling控制
     3,如设置其为0,关闭动态采样
     4,关闭动态采样,oracle自数据字典评估统计信息,如上述最后执行计划,t_sample
       rows仅为82,其实记录为几百万,
     5,动态采样会减缓sql解析速度,增加sql解析时间,所以要及时收集统计信息

ITPub博客

oracle动态采样_optimizer_dynamic_sampling

最新文章