動態采樣概念
動態采樣(Dynamic Sampling)是在ORACLE 9i Release 2中開始引入的一個技術，引入它的目的是為了應對數據庫對象沒有分析（統計信息缺失）的情況下，優化器生成更好的執行計劃。簡單的說，在數據庫段（表、索引、分區）對象沒有分析的情況下，為了使CBO優化器得到足夠多的信息以保證優化器做出正確執行計劃而發明的一種技術。它會分析一定數量段對象上的數據塊獲取CBO需要的統計信息。動態采樣技術僅僅是統計信息的一種補充，它不能完全替代統計信息分析。
Dynamic sampling first became available in Oracle9i Database Release 2. It is the ability of the cost-based optimizer (CBO) to sample the tables a query references during a hard parse, to determine better default statistics for unanalyzed segments, and to verify its “guesses.” This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling.
The purpose of dynamic sampling is to improve server performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes. The statistics for tables and indexes include table block counts, applicable index block counts, table cardinalities, and relevant join column statistics. These more accurate estimates allow the optimizer to produce better performing plans.
動態采樣在Oracle 11g之前稱為 Dynamic Sampling, ORACLE 12c之後改名為Dynamic Statistic.
動態采樣介紹
如果要理解動態采樣，最好從鮮活的例子開始，向來理論都是枯燥乏味的。創建一個test表，總共有50319行數據。如下所示
SQL> create table test
2 as
3 select owner, object_type
4 from dba_objects;
Table created.
SQL> select count(1) from test;
COUNT(1)
----------
50319
我們使用dynamic_sampling(test 0)提示(hints)來禁用動態采樣（稍後動態采樣級別中介紹），從下面的執行計劃可以看出，在表對象沒有做分析情況下，如果禁用了動態采樣，CBO優化器唯一可以使用的信息為該表存儲在數據字典的一些信息，比如多少個extent，多少個block等，這些信息往往不夠。此時優化器估計表test的行數為11027(如下所示)， 跟實際的表記錄行數50319還是有蠻大的偏差。在復雜環境下，就很有可能導致CBO優化器做出錯誤的執行計劃。
SQL> set autotrace traceonly explain;
SQL> select /*+ dynamic_sampling(test 0) */ * from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11027 | 301K| 31 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 11027 | 301K| 31 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> set autotrace off;
如果啟用動態采樣(默認情況下，動態采樣級別為2)，優化器根據動態采樣得到一些數據信息猜測、估計表TEST的記錄行數為48054，已經接近實際記錄行數50319了。比不做動態采樣分析要好很多了。當然你不能指望動態采樣獲取完全准確的信息，因為它只是采樣了一些數據塊。
SQL> set autotrace traceonly explain;
SQL> select * from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48054 | 1313K| 32 (4)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 48054 | 1313K| 32 (4)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> set autotrace off;
如果我們將動態采樣的級別提高為3，如下所示，發現優化器根據動態采樣得到的信息比默認（默認情況下，動態采樣級別為2）情況獲得的信息更准確。優化器估計表TEST的行數為51463，比48054又接近實際情況一步了。
SQL> set autotrace traceonly explain;
SQL> select /*+ dynamic_sampling(test 3) */ * from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51463 | 703K| 32 (4)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 51463 | 703K| 32 (4)| 00:00:01 |
--------------------------------------------------------------------------
SQL> set autotrace off;
在Tom的這篇文章中提到，在沒有動態采樣的情況下，如果刪除了該表數據，CBO優化器估算的結果集和沒有刪除之前是一樣的。這是因為當一個表的數據被刪除後，這個表所分配的extent和block是不會自動回收的（高水位線不變），所以CBO如果沒有采樣數據塊做分析，只是從數據字典中獲取extend等信息，就會誤認為任然還有那麼多數據。
SQL> delete from test;
50319 rows deleted.
SQL> commit;
Commit complete.
SQL> set autotrace traceonly explain;
SQL> select /*+ dynamic_sampling(test 0) */ * from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11027 | 301K| 31 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 11027 | 301K| 31 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> select * from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 31 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 28 | 31 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL>
什麼時候使用動態采樣？
如下所示，我們使用包dbms_stats.gather_table_stats收集表Test的統計信息過後，你會發現“dynamic sampling used for this statement”不見了，其實也就是說優化器發現有表TEST有分析過，它就不會使用動態采樣技術。其實開篇的時候已經敘說過“應對數據庫對象沒有分析（統計信息缺失）的情況下，才會用到動態采樣技術“
SQL> set autotrace trace exp;
SQL> select * from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 31 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 28 | 31 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> exec dbms_stats.gather_table_stats(user, 'test');
PL/SQL procedure successfully completed.
SQL> select * from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 31 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 28 | 31 (0)| 00:00:01 |
--------------------------------------------------------------------------
第二種情況：當表TEST即使被分析過，如果查詢腳本裡面包含臨時表，就會使用動態采樣技術。因為臨時表是不會被分析，它是沒有統計信息的。如下所示
SQL> drop table test;
SQL> create table test
2 as
3 select owner, object_type
4 from dba_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats(user, 'test');
PL/SQL procedure successfully completed.
SQL> create global temporary table tmp
2 (object_type varchar2(19));
Table created.
SQL> insert into tmp
2 select distinct object_type from dba_objects;
41 rows created.
SQL> commit;
Commit complete.
SQL> set autotrace traceonly explain;
SQL> select t.owner, l.object_type
2 from test t inner join tmp l on t.object_type =l.object_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 19574435
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 35 (6)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 25 | 35 (6)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TMP | 1 | 11 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST | 49422 | 675K| 32 (4)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."OBJECT_TYPE"="L"."OBJECT_TYPE")Note
-----
- dynamic sampling used for this statement
SQL>
動態采樣還有一個獨特能力，可以對不同列之間的相關性做統計。表統計信息都是相對獨立的。當查詢涉及列之間的相關性時,統計信息就顯得有些不足了,請看Tom的例子
創建一個特殊的表t，然後對字段flag1、flag2創建索引t_idx，然後分析收集統計信息
SQL> create table t
2 as select decode(mod(rownum,2),0,'N', 'Y') flag1,
3 decode(mod(rownum,2),0,'Y', 'N') flag2, a.*
4 from all_objects a;
Table created.
SQL> create index t_idx on t(flag1, flag2);
Index created.
SQL> begin
2 dbms_stats.gather_table_stats(user, 'T',
3 method_opt =>'for all indexed columns size 254');
4 end;
5 /
PL/SQL procedure successfully completed.
關於表t的行數情況如下所示，大家先不要糾結為什麼查詢獲取NUM_ROWS數據
SQL> select num_rows, num_rows/2, num_rows/2/2
2 from user_tables
3 where table_name='T';
NUM_ROWS NUM_ROWS/2 NUM_ROWS/2/2
---------- ---------- ------------
49875 24937.5 12468.75
首先看看對flag1過濾條件的SQL語句，CBO優化器猜測、估計的行數24757， 相當接近24937.5記錄數了。
SQL> set autotrace traceonly explain;
SQL> select * from t where flag1='N';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24757 | 2345K| 161 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 24757 | 2345K| 161 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG1"='N')
首先看看對flag2過濾條件的SQL語句，CBO優化器猜測、估計的行數25118， 相當接近24937.5記錄數了。
SQL> select * from t where flag2='N';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25118 | 2379K| 161 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 25118 | 2379K| 161 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG2"='N')
如果條件flag1 = 'N' and flag2 = 'N',我們根據邏輯推理判斷這樣的記錄肯定是不存在的，這也是苦心構造這個特例的初衷。下面看看CBO優化器怎麼探測、預測的
SQL> select * from t where flag1 = 'N' and flag2 = 'N';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12468 | 1181K| 160 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 12468 | 1181K| 160 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG1"='N' AND "FLAG2"='N')
CBO估計的記錄數為12468，和實際情況相差非常遠。其實是CBO優化器這樣估算來的：
flag1=‘N' 的記錄數占總數的1/2
flag2= 'N' 的記錄數占總數的1/2
根據NUM_ROWS/2/2 =12468.這樣顯然是不合理的。下面我們通過提升動態采樣級別，來看看動態采樣是否能避免CBO的錯誤
SQL> select /*+ dynamic_sampling(t 3) */ * from t where flag1 = 'N' and flag2 = 'N';
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 388 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 388 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 4 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG1"='N' AND "FLAG2"='N')Note
-----
- dynamic sampling used for this statement
SQL>
動態采樣級別
ORACLE為動態采樣劃分了11個級別，詳情請見ORACLE 11g官方文檔http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.htm#PFGRF94760
Table 13-10 Dynamic Statistics Levels
Level
When the Optimizer Uses Dynamic Statistics
Sample Size (Blocks)
0
Do not use dynamic statistics
不做動態采樣分析
n/a
1
Use dynamic statistics for all tables that do not have statistics, but only if the following criteria are met:
· There is at least 1 nonpartitioned table in the query that does not have statistics.
· This table has no indexes.
· This table has more blocks than the number of blocks that would be used for dynamic statistics of this table.
Oracle 對沒有分析的表進行動態采樣，但需要同時滿足以下3個條件。
（1） SQL中至少有一個未分析的表(非分區表)
（2） 未分析的表沒有索引
（3） 未分析的表占用的數據塊要大於動態采樣的數據塊（32個）
32
2
Use dynamic statistics if at least one table in the statement has no statistics. This is the default setting.
對所有的未分析表做分析，動態采樣的數據塊是默認數據塊數為64
64
3
Use dynamic statistics if any of the following conditions is true:
·
· The statement meets level 2 criteria.
·
· The statement has one or more expressions used in the WHERE clause predicates, for example, WHERE SUBSTR(cust_last_name,1,3).
采樣的表包含滿足Level 2定義的所有表，同時包括，那些謂詞有可能潛在地需要動態采樣的表，這些動態采樣的數據塊為默認數據塊，對沒有分析的表，動態采樣的默認塊為默認數據塊數量。
64
4
Use dynamic statistics if any of the following conditions is true:
·
· The statement meets level 3 criteria.
·
· The statement uses complex predicates (an OR or AND operator between multiple predicates on the same table).
采樣的表包含滿足Level 3定義的表，同時還包括一些表，他們包含一個單表的謂詞會引用另外的2個列或者更多的列；采樣的塊數是動態采樣默認數據塊數；對沒有分析的表，動態采樣的數據塊為默認數據塊的1倍。
64
5
Use dynamic statistics if the statement meets level 4 criteria.
采樣的表包含滿足Level 4定義的表，同時分別使用動態采樣默認數據塊的2倍的數量來做動態分析。
128
6
Use dynamic statistics if the statement meets level 4 criteria.
采樣的表包含滿足Level 4定義的表，同時分別使用動態采樣默認數據塊的4倍的數量來做動態分析。
256
7
Use dynamic statistics if the statement meets level 4 criteria.
采樣的表包含滿足Level 4定義的表，同時分別使用動態采樣默認數據塊的8倍的數量來做動態分析。
512
8
Use dynamic statistics if the statement meets level 4 criteria.
采樣的表包含滿足Level 4定義的表，同時分別使用動態采樣默認數據塊的32 倍的數量來做動態分析。
1024
9
Use dynamic statistics if the statement meets level 4 criteria.
采樣的表包含滿足Level 4定義的表，同時分別使用動態采樣默認數據塊的128倍的數量來做動態分析。
4086
10
Use dynamic statistics if the statement meets level 4 criteria.
采樣的表包含滿足Level 4定義的表，同時分別使用動態采樣對所有數據塊做動態分析。
All blocks
11
Use dynamic statistics automatically whenever the optimizer deems it necessary.
當優化器探測到需要的采樣時，對段段對象自動采樣
Automatically determined
采樣級別越高，采樣的數據塊越多，得到的分析數據就越接近於真實，但同時伴隨著資源消耗的開銷也增加了。這時一個需要權衡考慮的東西。ORACLE 10 g & 11g的默認采樣級別都為2,如下所示，一般使用在會話中使用dynamic_sampling提示來修改動態采樣級別。
SQL> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
------------------------------ ----------- -----------
optimizer_dynamic_sampling integer 2
SQL>
另外一個方式就是通過提示hints裡修改動態采樣的級別。這個非常靈活、有用。
動態采樣注意事項
凡事有利必有弊，動態采樣也不是神器。它采樣的數據塊越多，系統開銷就越大，這樣會增加SQL硬解析的時間，如果是數據庫倉庫（DW、OLAP）環境，SQL執行時間相當長，硬解析時間只占整個SQL執行時間的一小部分，那麼可以適當的提高動態采樣級別，這樣是有利於優化器獲取更加正確的信息。一般設置為3或4比較合適。
但是在並發比較嚴重的OLTP系統中，每秒中有成千上萬的SQL語句執行，它要求SQL語句短小、執行時間短，所以在OLTP系統中應該減低動態采樣級別或不用動態采樣。可以參考下面Tom的原文：
When should I use dynamic sampling?” is a tricky question. As with any other feature, there are times to use it and times to avoid it. So far I’ve concentrated on the “goodness” of dynamic sampling, and based on that, it seems that you should set the level to 3 or 4 and just let the optimizer always use dynamic sampling to validate its guesses.
That makes sense in an environment in which you spend most of your time executing SQL and very little of your overall time hard-parsing the SQL. That is, the SQL you are executing runs for a long time and the parse time is a small portion of the overall execution time, such as in a data warehousing environment. There, dynamic sampling at levels above the default makes complete sense. You are willing to give the optimizer a little more time during a hard parse (when sampling takes place) to arrive at the optimal plan for these complex queries.
That leaves the other classic type of environment: the online transaction processing (OLTP) system. Here, in general, you are executing queries thousands of times per second and spend very little time executing a given query—the queries are typically small and fast. Increasing the parse time in an OLTP system might well cause you to spend more time parsing than executing SQL. You do not want to increase the parse times here, so higher levels of dynamic sampling would not be advisable
參考資料：
http://www.oracle.com/technetwork/issue-archive/2009/09-jan/o19asktom-086775.html
https://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer
[讓Oracle跑得更快]---譚懷遠
--在數據庫裡創建臨時表存放第一張表的數據
create global temporary table TMP_xxx
(
xxx,
xxx
)
on commit preserve rows;
--在數據庫裡創建臨時表存放第二張表的數據
create global temporary table TMP_xxx2
(
xxx,
xxx
)
on commit preserve rows;
--創建存儲過程
CREATE OR REPLACE PROCEDURE xxx
(
xxxxxxxx
)
as
v_xxx1 datetype;
v_xxx2 datetype;
--定義游標
cursor t_cur is select xxx from tmp_xxx where xxx;
begin
insert into tmp_xxx (xxx,xxx) values(xxx,xxx);--往臨時表一插入查詢的數據
open t_cur;
loop
fetch t_cur into v_xxx1,v_xxx2;--取出游標查詢的值放入一個變量裡，像java的for循環
exit when t_cur%notfound;
--這裡使用v_xxx1,v_xxx2來判斷(使用臨時表的哪個數據，游標裡就查詢哪個數據)，然後將判斷後的值放入臨時表二裡
insert into tmp_xxx2 (xxx,xxx) values(xxx,xxx);
end loop;
close pz_cur;
commit;
end;
在java代碼裡查詢select * from tmp_xxx2，然後執行trunk tmp_xxx2清除臨時表數據即可。
第一部分：ORACLE體系結構（6天）
Oracle的安裝和配置 Linux下Oracle的安裝以及數據庫的創建
Oracle的網絡配置與連接
EM工具的配置和連接
Isqlplus的配置和連接
Oracle的打補丁升級
在Linux下Oracle自動啟動與關閉
Oracle啟動與關閉的流程環節詳細剖析
Oracle的卸載與刪除
Sqldeveloper的安裝與配置
動態性能視圖v$
數據字典DBA/ALL/USER_的表
Oracle內存和進程管理 Oracle的內存結構以及SGA自動管理、ASMM
SGA中各組件的LRU、LRUW算法與內存命中率深入剖析
BUFFER的內存結構以及KEEP\RECYCLE緩沖區的使用
深入剖析Oracle髒數據和髒緩沖概念和原理以及髒寫的過程
深入剖析內存中的Latch
後台進程分析以及並發配置
CKPT隊列及SCN內部時鐘原理。
數據庫實例崩潰恢復原理
內存結構的優化設置
初始化參數設置詳解
PGA自動管理以及PGA優化
殺掉異常進程的方法
Oracle的存儲管理 表空間、段、區、塊的概念和關系
表空間的管理：字典、LMT的區別和優缺點
深入剖析本地管理表空間位圖存儲技術
段空間管理：ASSM與MSSM區別
MSSM下Freelist算法以及爭用
深入剖析ASSM的三級位圖技術
數據文件轉儲
區擴展算法的深入剖析
系統表空間、輔助表空間、臨時表空間、UNDO表空間的管理和維護
輔助表空間的遷移
UNDO表空間的切換
表空間組的概念與使用
默認表空間臨時表空間的設置
表空間配額設置以及和權限的繼承關系
表存儲參數的詳解以及新舊版本之間存儲參數的變化。
各種存儲管理操作維護的方法
ROWID原理及轉換
11g的延遲段空間管理
Oracle控制文件管理 控制文件的概念與內容
控制文件的多路復用
控制文件損壞的處理
控制文件的備份與重建
Resetlog與noresetlog的深入剖析
Oracle重做日志文件管理 Redo文件的概念和內容
Redo文件多路復用
Redo文件切換與檢查點
Redo與redo成員管理
Redo與SCN
歸檔日志設置與管理
Oracle UNDO表空間管理 UNDO參數詳解
UNDO切換
數據一致性與延遲提交
UNDO與SCN
UNDO空間的管理、設置與優化
Oracle網絡管理 監聽配置文件與監聽管理器使用詳解
網絡服務名配置
NETCA使用
NETMGR使用
NOracle專用和共享模式
Oracle表與索引管理 ORACLE各種表的介紹與創建
表的約束創建與管理
索引的創建與管理
用戶、角色、權限管理 權限分類
授權與取消授權
直接授權與級聯授權
角色管理
權限繼承
用戶管理：密碼、賬戶鎖、表空間配額、默認的表空間
資源限制文件詳解
系統權限策略規劃方案
調度器與自動任務 調度器概述
調度器體系結構
公共管理工具
使用任務類
使用調度器程序
使用計劃表
使用調度器窗口與視圖
全球化支持 字符集、字符集文件及字符支持
NLS_LANG設置與影響
導入導出及字符集轉換
字符集的更改
識別導出文件的字符集
使用csscan輔助字符集轉換
亂碼的產生
自定義字符的......余下全文>>
