oracle動態取樣_optimizer_dynamic_sampling

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解析時間,所以要及時收集統計資訊