微課sql最佳化(6)、統計資訊收集(4)-關於動態取樣

orastar發表於2020-03-03
SQL

1、關於動態取樣

動態取樣會增加缺少或不足的最佳化器統計資訊。使用動態取樣，最佳化器可以透過對謂詞選擇性做出更好的估計來改進計劃。動態取樣可以補充諸如表塊計數，適用的索引塊計數，表基數（估計行數）和相關連線列統計資訊。
特點總結：
1、適用場景：統計資訊缺失或不足時使用
2、優點：  動態取樣可以幫忙最佳化器做出更好的執行計劃選擇
3、缺點： 佔用系統資源。--非常佔用資源
4、做好充足的統計資訊收集，避免動態取樣。
預設情況下，在資料庫中啟用動態取樣。您可以透過設定初始化引數來禁用該功能OPTIMIZER_DYNAMIC_SAMPLING=0。
動態取樣級別控制如何觸發及取樣率。你可以使用OPTIMIZER_DYNAMIC_SAMPLING初始化引數設定動態抽樣率或SQL語句hint。

2、動態取樣級別

3、練習4，最佳化以下語句，消除動態取樣

create table ht.c_cons_ds as select * from ht.c_cons;
查詢語句： select count(1) from ht.c_cons_ds;
set autot traceonly
select count(1) from ht.c_cons_ds;
查詢執行計劃
SQL> set autot traceonly
SQL> select count(1) from ht.c_cons_ds;
Execution Plan
----------------------------------------------------------
Plan hash value: 3975303649
------------------------------------------------------------------------
| Id  | Operation       | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    18     (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |        |           |
|   2 |   TABLE ACCESS FULL| C_CONS_DS | 10000 |    18     (0)| 00:00:01 |
------------------------------------------------------------------------
Note
-----
   -   dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
  58  consistent gets
   0  physical reads
   0  redo size
526  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
   1  rows processed
收集統計資訊
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HT',tabname => 'C_CONS_DS',estimate_percent => 100,method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 4,cascade => TRUE);
再交查詢執行計劃
Execution Plan
----------------------------------------------------------
Plan hash value: 3975303649
------------------------------------------------------------------------
| Id  | Operation       | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    18     (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |        |           |
|   2 |   TABLE ACCESS FULL| C_CONS_DS | 10000 |    18     (0)| 00:00:01 |
------------------------------------------------------------------------
Statistics
----------------------------------------------------------
   1  recursive calls
   0  db block gets
  57  consistent gets
   0  physical reads
   0  redo size
526  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
   1  rows processed

4、小結

1、適用場景：統計資訊缺失或不足時使用
2、優點：  動態取樣可以幫忙最佳化器做出更好的執行計劃選擇
3、缺點： 佔用系統資源。--非常佔用資源
4、做好充足的統計資訊收集，避免動態取樣。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31442014/viewspace-2678255/，如需轉載，請註明出處，否則將追究法律責任。

相關文章