SQL> show parameter optimizer_dynamic_sam
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL> create table t_sample(a int);
------------------------------------ ----------- ------------------------------
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 |
-------------------------------------------------------------------------------
| 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)
- 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 |
-------------------------------------------------------------------------------
| 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)
- 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 |
-------------------------------------------------------------------------------
| 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解析时间,所以要及时收集统计信息
小结:1,如无收集统计信息,会启用动态采样
2,动态采样由参数optimizer_dynamic_sampling控制
3,如设置其为0,关闭动态采样
4,关闭动态采样,oracle自数据字典评估统计信息,如上述最后执行计划,t_sample
rows仅为82,其实记录为几百万,
5,动态采样会减缓sql解析速度,增加sql解析时间,所以要及时收集统计信息
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-751534/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-751534/