Oracle 分析及動態取樣
Oracle 分析及動態取樣
http://blog.csdn.net/tianlesoftware/article/details/5845028
之前在說 Optimizer中的CBO時講到，當表沒有做分析的時候，Oracle 會使用動態取樣來收集統計資訊。 獲取準確的段物件（表，表分割槽，索引等）的分析資料，是CBO存在的基石，CBO的機制就是收集儘可能多的物件資訊和系統資訊，透過對這些資訊進行計算，分析，評估，最終得出一個成本最低的執行計劃。 所以對於CBO，資料段的分析就非常重要。
Oracle Optimizer CBO RBO
http://blog.csdn.net/tianlesoftware/archive/2010/08/19/5824886.aspx
一一：先演示一個示例，來理解分析的作用
1.1建立表
SQL> create table t as select object_id,object_name from dba_objects where 1=2;
表已建立。
SQL> create index index_t on t(object_id);
索引已建立。
SQL> insert into t select object_id,object_name from dba_objects;
已建立72926行。
SQL> commit;
提交完成。
1.2檢視分的分析及執行計劃
SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- --------------
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- --------------
0 0 0 25-8月 -10
從查詢結果看出，表的行數，行長，佔用的資料塊數及最後的分析時間都是空。 索引的相關資訊也沒有，說明這個表和說因都沒有被分析，如果此時有一條SQL 對錶做查詢，CBO 由於無法獲取這些資訊，很可能生成錯誤的執行計劃，如：
SQL> set linesize 200
SQL> set autot trace exp;
SQL> select /*+dynamic_sampling(t 0) */ * from t where object_id>30;
執行計劃
----------------------------------------------------------
Plan hash value: 80339723
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 316 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 316 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_T | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">30)
在Oracle 10g以後，如果一個表沒有做分析，將自動對它做動態取樣分析，所以這裡採用hint的方式將動態取樣的級別設定為0，即不使用動態取樣。
從這個執行計劃，看書CBO 估計出表中滿足條件的記錄為4條，索引使用了索引。 我們對錶做一下分析，用結果比較一下。
1.3 分析表及檢視分析之後的執行計劃
分析可以透過兩中方式：
一種是analyze 命令，如：
analyze table tablename compute statistics for all indexes;
還有一種就是透過DBMS_STATS包來分析，從9i 開始，Oracle 推薦使用DBMS_STATS包對錶進行分析操作，因為DBMS_STATS 提供了更多的功能，以及靈活的操作方式。
SQL> exec dbms_stats.gather_table_stats('SYS','T');
PL/SQL 過程已成功完成。
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- --------------
1 263 72926 25-8月 -10
SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- --------------
72926 29 345 25-8月 -10
從上面的結果，可以看出DBMS_STATS.gather_table_stats已經對錶和索引都做了分析。 現在我們在來看一下執行計劃。
SQL> set autot trace exp;
SQL> select * from t where object_id>30;
執行計劃
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72899 | 2064K| 96 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 72899 | 2064K| 96 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">30)
從這個計劃，我們看出CBO 估算出的結果是72899 條記錄，與實際的72926很近。 此時選擇全表掃描更優。 透過這個例子，我們也看出了分析對執行計劃的重要性。
二：直方圖（Histogram）
DBMS_STATS 包對段表的分析有三個層次：
（1）表自身的分析： 包括表中的行數，資料塊數，行長等資訊。
（2）列的分析：包括列值的重複數，列上的空值，資料在列上的分佈情況。
（3）索引的分析： 包括索引葉塊的數量，索引的深度，索引的聚合因子等。
直方圖就是列分析中 資料在列上的分佈情況。
當Oracle 做直方圖分析時，會將要分析的列上的資料分成很多數量相同的部分，每一部分稱為一個bucket,這樣CBO就可以非常容易地知道這個列上的數的分佈情況，這種資料的分佈將作為一個非常重要的因素納入到執行計劃成本的計算當中。
對於資料分佈非常傾斜的表，做直方圖是非常有用的。 如： 1,10,20,30,40,50. 那麼在一個數值範圍（bucket）內，它的資料記錄基本上一樣。 如果是：1,5,5,5,5,10,10,20,50,100. 那麼它在bucket內，資料分佈就是嚴重的傾斜。
直方圖有時對於CBO非常重要，特別是對於有欄位資料非常傾斜的表，做直方圖分析尤為重要。 可以用dbms_stats包來分析。 預設情況下，dbms_stats 包會對所有的列做直方圖分析。 如：
SQL> exec dbms_stats.gather_table_stats('SYS','T',cascade=>true);
PL/SQL 過程已成功完成。
然後從user_histograms檢視上檢視到相關的資訊：
SQL> select table_name,column_name,endpoint_number,endpoint_value from user_histograms where table_name='T';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE------------------------------ -------------------- --------------- --------------
T OBJECT_ID 0 2
T OBJECT_NAME 0 2.4504E+35
T OBJECT_ID 1 76685
T OBJECT_NAME 1 1.0886E+36
如果一個列上的資料有比較嚴重的傾斜，對這個列做直方圖是必要的，但是，Oracle 對資料分析是需要消耗資源的，特別是對於一些很大的段物件，分析的時間尤其長。對於OLAP系統，可能需要幾個小時才能完成。
所以做不做分析就需要DBA 權衡好了。 但有一點要注意， 不要在生產環境中隨便修改分析方案，除非你有十足的把握。 否則可能導致非常嚴重的後果。
三．DBMS_STATS包
DBMS_STAS包不僅能夠對表進行分析，它還可以對資料庫分析進行管理。 按照功能可以分一下幾類：
（1） 效能資料的收集
（2） 效能資料的設定
（3） 效能資料的刪除
（4） 效能資料的備份和恢
3.1 DBMS_STATS包的幾個常用功能：效能的手機，設定 和刪除
效能資料的收集包含這樣幾個儲存過程：
GATHER_DATABASE_STATS Procedures
GATHER_DICTIONARY_STATS Procedure
GATHER_FIXED_OBJECTS_STATS Procedure
GATHER_INDEX_STATS Procedure
GATHER_SCHEMA_STATS Procedures
GATHER_SYSTEM_STATS Procedure
GATHER_TABLE_STATS Procedure
從名字也可以看出各自的作用，這些儲存過程用來收集資料庫不同級別物件的效能資料，包括：資料庫，資料字典，表，索引，SCHEMA的效能等。
3.1.1 GATHER_TABLE_STATS Procedure 儲存過程
在10g中, GATHER_TABLE_STATS的引數如下：
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
到了11g，對引數做了調整：
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
對引數的說明：
|
Parameter |
Description |
|
ownname |
Schema of table to analyze |
|
tabname |
Name of table |
|
partname |
Name of partition |
|
estimate_percent |
Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_PARAM Procedure. |
|
block_sample |
Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics. |
|
method_opt |
Accepts: FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...] size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure. |
|
degree |
Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object. |
|
granularity |
Granularity of statistics to collect (only pertinent if the table is partitioned). 'ALL' - gathers all (subpartition, partition, and global) statistics 'AUTO'- determines the granularity based on the partitioning type. This is the default value. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'. 'GLOBAL' - gathers global statistics 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object. 'PARTITION '- gathers partition-level statistics 'SUBPARTITION' - gathers subpartition-level statistics. |
|
cascade |
Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using theSET_PARAM Procedure. |
|
stattab |
User statistics table identifier describing where to save the current statistics |
|
statid |
Identifier (optional) to associate with these statistics within stattab |
|
statown |
Schema containing stattab (if different than ownname) |
|
no_invalidate |
Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure. |
|
force |
Gather statistics of table even if it is locked |
在gather_table_stats 儲存過程的所有引數中，除了ownname和tabname，其他的引數都有預設值。 所以我們在呼叫這個儲存過程時，Oracle 會使用引數的預設值對錶進行分析。如：
SQL> exec dbms_stats.gather_table_STATS('SYS','T');
PL/SQL 過程已成功完成。
如果想檢視當前的預設值，可以使用dbms_stats.get_param函式來獲取：
SQL> select dbms_stats.get_param('method_opt') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
結合上面對引數的說明：
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
我們可以看出，就是對所有的列做直方圖分析，直方圖設定的bucket值由Oracle自己決定。
3.1.1.1 estimate_percent 引數
這個引數是一個百分比值，它告訴分析包需要使用表中資料的多大比例來做分析。
理論上來講，取樣的資料越多，得到的資訊就越接近於實際，CBO做出的執行計劃就越最佳化，但是，取樣越多，消耗的系統資源必然越多。 對系統的影響也越大。 所以對於這個值的設定，要根據業務情況來。 如果資料的直方圖分佈比較均勻，就可以使用預設值：AUTO_SAMPLE_SIZE，即讓Oracle 自己來判斷取樣的比例。有時，特別是對於批次載入的表，我們可以預估表中的資料量，可以人工地設定一個合理的值。 一般，對於一個有1000萬資料的表分割槽，可以把這個引數設定為0.000001.
3.1. 1.2 Method_option 引數
這個引數用來定義直方圖分析的一些值。
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]
這裡給出了4種指定哪些列進行分析的方式：
（1） 所有列：for all column
（2） 索引列：只對有索引的列進行分析，for all indexed columns
（3） 影藏列：只對影藏的列進行分析，for all hidden columns
（4） 顯示指定列：顯示的指定那些列進行分析，for columns columns_name
該引數預設值：for all columns size auto.
3.1. 1.3 degree 引數
用來指定分析時使用的並行度。 有以下這些設定：
(1) Null： 如果設定為null，Oracle 將使用被分析表屬性的並行度，比如表在建立時指定的並行度，或者後者使用alter table 重新設定的並行度。
(2) 一個數值： 可以顯示地指定分析時使用的並行度。
(3) Default_degree: 如果設定為default，Oracle 將根據初始化引數中相關引數的設定來決定使用的並行度。
這個引數的預設值是Null，即透過表上的並行度屬性來決定分析使用的並行度。 當需要分析的表或表分割槽非常大，並且系統資源比較充分的時候，就可以考慮使用並行的方式來做分析，這樣就會大大提高分析的速度。 相反，如果你的系統資源比較吃緊，那麼啟用並行可能會適得其反。
3.1. 1.4 Granularity
分析的粒度，有以下幾個配置：
（1） ALL : 將會對錶的全域性（global），分割槽，子分割槽的資料都做分析
（2） AUTO: Oracle 根據分割槽的型別，自動決定做哪一種粒度的分析。
（3） GLOBAL：只做全域性級別的分析。
（4） GLOBAL AND PARTITION: 只對全域性和分割槽級別做分析，對子分割槽不做分析，這是和ALL的一個區別。
（5） PARTITION: 只在分割槽級別做分析。
（6） SUBPARTITION: 只在子分割槽做分析。
在生產環境中，特別是OLAP 或者資料倉儲的環境中，這個引數的設定會直接影響到CBO的執行計劃選擇。
在OLAP或者資料倉儲系統中，經常有這樣的事情，新建立一個分割槽，將批次的資料（通常是很大的資料）載入到分割槽中，對分割槽做分析，然後做報表或者資料探勘。 在理想的情況下，對錶的全域性，分割槽都做分析，這樣才能得到最充足的資料，但是通常這樣的表都非常大，如果每增加一個分割槽都需要做一次全域性分析，那麼會消耗極大的系統資源。 但是如果只對新加入的分割槽進行分割槽而不做全域性分析，oracle 在全域性範圍內的資訊就會不準確。
該引數在預設情況下，DBMS_STATS 包會對錶級（全域性），分割槽級（對應引數partition）都會進行分析。 如果把cascade 設定為true，相應索引的全域性和分割槽級別也都會被分析。 如果只對分割槽級進行分析，而全域性沒有分析，那麼全域性資訊沒有更新，依然會導致CBO 作出錯誤的執行計劃。
所以當一些新的資料插入到表中時，如果對這些新的資料進行分析，是一個非常重要的問題。 一般參考如下原則：
（1） 看一下新插入的資料在全表中所佔的比例，如果所佔比例不是很大，那麼可以考慮不做全域性分析，否則就需要考慮，一句是業務的實際執行情況。
（2） 取樣比例。 如果載入的資料量非常大，比如上千萬或者更大，就要把取樣比例壓縮的儘可能地小，但底線是不能影響CBO做出正確的執行計劃，取樣比例的上線是不能消耗太多的資源而影響到業務的正常執行。
（3） 新載入的資料應該要做分割槽級的資料分析。 至於是否需要直方圖分析，以及設定多少個buckets（size引數指定），需要DBA一句資料的分佈情況進行考慮，關鍵是視資料的傾斜程度而定。
3.1.2 GATHER_SCHEMA_STATS 儲存過程
這個儲存過程用於對某個使用者下所有的物件進行分析。如果你的資料使用者物件非常多，單獨對每個物件進行分析設定會非常不方便，這個儲存過程就很方便。 它的好處在於如果需要分析的物件非常多，將可以大大降低DBA的工作量，不足之處是所有分析使用相同的分析策略，可能會導致分析不是最優。 所以要根據實際情況來決定。
該儲存過程引數如下：
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname VARCHAR2,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT 'GATHER',
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
obj_filter_list ObjectTab DEFAULT NULL);
引數說明如下：
|
Parameter |
Description |
|
ownname |
Schema to analyze (NULL means current schema) |
|
estimate_percent |
Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
block_sample |
Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics. |
|
method_opt |
Accepts: FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
degree |
Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters.The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object. |
|
granularity |
Granularity of statistics to collect (only pertinent if the table is partitioned). 'ALL' - gathers all (subpartition, partition, and global) statistics 'AUTO'- determines the granularity based on the partitioning type. This is the default value. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'. 'GLOBAL' - gathers global statistics 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object. 'PARTITION '- gathers partition-level statistics 'SUBPARTITION' - gathers subpartition-level statistics. |
|
cascade |
Gather statistics on the indexes as well. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the indexes in the schema in addition to gathering table and column statistics. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
stattab |
User statistics table identifier describing where to save the current statistics |
|
statid |
Identifier (optional) to associate with these statistics within stattab |
|
options |
Further specification of which objects to gather statistics for: GATHER: Gathers statistics on all objects in the schema. GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects. GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale. GATHER EMPTY: Gathers statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics. LIST AUTO: Returns a list of objects to be processed with GATHER AUTO. LIST STALE: Returns list of stale objects as determined by looking at the *_tab_modifications views. LIST EMPTY: Returns list of objects which currently have no statistics. |
|
objlist |
List of objects found to be stale or empty |
|
statown |
Schema containing stattab (if different than ownname) |
|
no_invalidate |
Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
force |
Gather statistics on objects even if they are locked |
|
obj_filter_list |
A list of object filters. When provided, GATHER_SCHEMA_STATS will gather statistics only on objects which satisfy at least one object filter in the list as needed. In a single object filter, we can specify the constraints on the object attributes. The attribute values specified in the object filter are case- insensitive unless double-quoted. Wildcard is allowed in the attribute values. Suppose non-NULL values s1, s2, ... are specified for attributes a1, a2, ... in one object filter. An object o is said to satisfy this object filter if (o.a1 like s1) and (o.a2 like s2) and ... is true. See Applying an Object Filter List. |
3.1.3 DBMS_STATS.GATHER_INDEX_STATS 儲存過程
該儲存過程用於對索引的分析，如果我們在使用DBMS_STATS.GATHER_TABLES_STATS的分析時設定引數cascade=>true。 那麼Oracle會同時執行這個儲存過程來對索引進行分析。
儲存過程引數：
DBMS_STATS.GATHER_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(GET_PARAM('ESTIMATE_PERCENT')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
|
Parameter |
Description |
|
ownname |
Schema of index to analyze |
|
indname |
Name of index |
|
partname |
Name of partition |
|
estimate_percent |
Percentage of rows to estimate (NULL means compute). The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
stattab |
User statistics table identifier describing where to save the current statistics |
|
statid |
Identifier (optional) to associate with these statistics within stattab |
|
statown |
Schema containing stattab (if different than ownname) |
|
degree |
Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. NULL means use of table default value that was specified by the DEGREE clause in the CREATE/ALTER INDEX statement. Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object. |
|
granularity |
Granularity of statistics to collect (only pertinent if the table is partitioned). 'ALL' - gathers all (subpartition, partition, and global) statistics 'AUTO'- determines the granularity based on the partitioning type. This is the default value. 'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'. 'GLOBAL' - gathers global statistics 'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object. 'PARTITION '- gathers partition-level statistics 'SUBPARTITION' - gathers subpartition-level statistics. |
|
no_invalidate |
Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. |
|
force |
Gather statistics on object even if it is locked |
上面討論了三個常用的儲存過程。 分析對CBO 來說非常重要，如果不能按照自己的系統指定出切合實際的資料分析方案，可能會導致如下問題的發生：
（1） 分析資訊不充分導致CBO 產生錯誤的執行計劃，導致SQL執行效率低下。
（2） 過多的分析工具帶來系統效能的嚴重下降。
3.2 DBMS_STATS包管理功能
3.2.1 獲取分析資料
GET_COLUMN_STATS Procedures
GET_INDEX_STATS Procedures
GET_SYSTEM_STATS Procedure
GET_TABLE_STATS Procedure
這四個儲存過程分別為使用者獲取欄位，索引，表和系統的統計資訊。 它的用法是首先定義要獲取效能指標的變數，然後使用儲存過程將效能指標的值賦給變數，最後將變數的值輸出。 如：
SQL> set serveroutput on
SQL> declare
2 dist number;
3 dens number;
4 ncnt number;
5 orec dbms_stats.statrec;
6 avgc number;
7 begin
8 dbms_stats.get_column_stats('SYS','T','object_ID',distcnt=>dist,density=>dens,nullcnt=>ncnt,srec=>orec,avgclen=>avgc);
9 dbms_output.put_line('the distcnt is:' ||to_char(dist));
10 dbms_output.put_line('the density is:' ||to_char(dens));
11 dbms_output.put_line('the nullcnt is:' ||to_char(ncnt));
12 dbms_output.put_line('the srec is:' ||to_char(ncnt));
13 dbms_output.put_line('the avgclen is:' ||to_char(avgc));
14 end;
15 /
the distcnt is:72926
the density is:.0000137125305103804
the nullcnt is:0
the srec is:0
the avgclen is:5
PL/SQL 過程已成功完成。
更多資訊參考：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.2.2 設定分析資料
SET_COLUMN_STATS Procedures
SET_INDEX_STATS Procedures
SET_SYSTEM_STATS Procedure
SET_TABLE_STATS Procedure
這幾個儲存過程允許我們手工地為欄位，索引，表和系統效能資料賦值。 它的一個用處是當相應的指標不準確導致執行計劃失敗時，可以使用這種方法手工地來為這些效能資料賦值。 在極端情況下，這也不失為一個解決問題的方法。
關於這4個儲存過程的絕提用法參考 oracle 聯機文件：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.2.3 刪除分析資料
DELETE_COLUMN_STATS Procedure
DELETE_DATABASE_STATS Procedure
DELETE_DICTIONARY_STATS Procedure
DELETE_FIXED_OBJECTS_STATS Procedure
DELETE_INDEX_STATS Procedure
DELETE_SCHEMA_STATS Procedure
DELETE_SYSTEM_STATS Procedure
DELETE_TABLE_STATS Procedure
當效能資料出現異常導致CBO判斷錯誤時，為了立刻修正這個錯誤，刪除效能資料也是一種補救的方法，比如刪除了表的資料，讓CBO重新對錶做動態取樣分析，得到一個正確的結果。
它可以刪除欄位，資料庫，資料字典，基表，索引，表等級別的效能資料。
具體參考oracle 聯機文件：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.2.4 儲存分析資料
CREATE_STAT_TABLE Procedure
DROP_STAT_TABLE Procedure
可以用這兩個儲存過程建立一個表，用於存放效能資料，這樣有利於對效能資料的管理，也可以刪除這個表。
具體參考oracle 聯機文件：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.2.5 匯入和匯出分析資料
EXPORT_COLUMN_STATS Procedure
EXPORT_DATABASE_STATS Procedure
EXPORT_DICTIONARY_STATS Procedure
EXPORT_FIXED_OBJECTS_STATS Procedure
EXPORT_INDEX_STATS Procedure
EXPORT_SCHEMA_STATS Procedure
EXPORT_SYSTEM_STATS Procedure
EXPORT_TABLE_STATS Procedure
IMPORT_COLUMN_STATS Procedure
IMPORT_DATABASE_STATS Procedure
IMPORT_DICTIONARY_STATS Procedure
IMPORT_FIXED_OBJECTS_STATS Procedure
IMPORT_INDEX_STATS Procedure
IMPORT_SCHEMA_STATS Procedure
IMPORT_SYSTEM_STATS Procedure
IMPORT_TABLE_STATS Procedure
這些儲存過程可以將已經有的效能指標匯入到使用者建立好的表中存放，需要時，可以從表中倒回來。
具體參考oracle 聯機文件：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.2.6 鎖定分析資料
LOCK_SCHEMA_STATS Procedure
LOCK_TABLE_STATS Procedure
UNLOCK_SCHEMA_STATS Procedure
UNLOCK_TABLE_STATS Procedure
The LOCK_* procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
可能在某些時候，我們覺得當前的統計資訊非常好，執行計劃很準確，並且表中資料幾乎不變化，那麼可以使用LOCK_TABLE_STATS Procedure 來鎖定表的統計資訊，不允許對錶做分析或者設定分析資料。 當表的分析資料被鎖定之後，相關的所有分析資料，包括表級，列級，直方圖，索引的分析資料都將被鎖定，不允許被更新。
具體參考oracle 聯機文件：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
3.2.7 分析資料的恢復
RESET_PARAM_DEFAULTS Procedure
RESTORE_DICTIONARY_STATS Procedure
RESTORE_FIXED_OBJECTS_STATS Procedure
RESTORE_SCHEMA_STATS Procedure
RESTORE_SYSTEM_STATS Procedure
RESTORE_TABLE_STATS Procedure
Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION Procedure.
比如我們重新分析了表，發現分析的資料導致了CBO選擇了錯誤的執行計劃，為了挽救這種局面，可以將統計資訊恢復到從前的那個時間點，也就是CBO執行計劃正確的時間點，先解決這個問題，再來分析問題的原因。
具體參考oracle 聯機文件：
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461
四．動態取樣
4.1 什麼是動態取樣
動態取樣（Dynamic Sampling）技術的最初提出是在Oracle 9i R2，在段（表，索引，分割槽）沒有分析的情況下，為了使CBO 最佳化器得到足夠的資訊以保證做出正確的執行計劃而發明的一種技術，可以把它看做分析手段的一種補充。
當段物件沒有統計資訊時（即沒有做分析），動態取樣技術可以透過直接從需要分析的物件上收集資料塊（取樣）來獲得CBO需要的統計資訊。
一個簡單的例子：
建立表：
SQL> create table t
2 as
3 select owner,object_type from all_objects;
表已建立。
檢視錶的記錄數：
SQL> select count(*) from t;
COUNT(*)
----------
72236 -- 記錄數
這裡建立了一張普通表，沒有做分析，我們在hint中用0級來限制動態取樣，此時CBO 唯一可以使用的資訊就是表儲存在資料字典中的一些資訊，如有多少個extent，有多少個block，但是這些資訊是不夠的。
SQL> set autot traceonly explain
SQL> select /*+dynamic_sampling(t 0) */ * from t;
執行計劃
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15928 | 435K| 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 15928 | 435K| 55 (0)| 00:00:01 |
在沒有做動態分析的情況下，CBO 估計的記錄數是15928條，與真實的72236 相差甚遠。
我們用動態分析來檢視一下：
SQL> select * from t;
執行計劃
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80232 | 2193K| 56 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 80232 | 2193K| 56 (2)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
在Oracle 10g中預設對沒有分析的段做動態取樣，上面的查詢結果顯示使用了Level 2級的動態取樣，CBO 估計的結果是80232 與72236 很接近了。
注意一點：
在沒有動態取樣的情況下，對於沒有分析過的段，CBO也可能錯誤地將結果判斷的程度擴大話。 如：
SQL> delete from t;
已刪除72236行。
SQL> commit;
提交完成。
SQL> select /*+dynamic_sampling(t 0) */ * from t;
執行計劃
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15928 | 435K| 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 15928 | 435K| 55 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> select * from t;
執行計劃
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 1 | 28 | 55 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
如果細心一點，可能看出2個執行計劃的差別。 在沒有采用動態分析的情況下，CBO 對t表估計的還是15928行記錄，但是用動態分析就顯示1條記錄。 而表中的資料在查詢之前已經刪除掉了。 出現這種情況的原因是因為高水位。 雖然表的資料已經刪除，但是表分配的extent 和block 沒有被回收，所以在這種情況下CBO 依然認為有那麼多的資料在那。
透過這一點，我們可以看出，此時CBO能夠使用的資訊非常有限，也就是這個表有幾個extent，有幾個block。 但動態取樣之後，Oracle 立即發現，原來資料塊中都是空的。
關於Oracle 高水位，參考我的blog：Oracle 高水位(HWM)
http://blog.csdn.net/tianlesoftware/archive/2009/10/22/4707900.aspx
動態取樣有兩方面的作用：
（1） CBO 依賴的是充分的統計分析資訊，但是並不是每個使用者都會非常認真，及時地去對每個表做分析。 為了保證執行計劃都儘可能地正確，Oracle 需要使用動態取樣技術來幫助CBO 獲取儘可能多的資訊。
（2） 全域性臨時表。 通常來講，臨時表的資料是不做分析的，因為它存放的資料是臨時性的，可能很快就釋放了，但是當一個查詢關聯到這樣的臨時表時，CBO要想獲得臨時表上的統計資訊分析資料，就只能依賴於動態取樣了。
動態取樣除了可以在段物件沒有分析時，給CBO提供分析資料之外，還有一個獨特的能力，它可以對不同列之間的相關性做統計。
相對的，表分析的資訊是獨立的。 如：
（1） 表的行數，平均行長。
（2） 表的每個列的最大值，最小值，重複率，也可能包含直方圖。
（3） 索引的聚合因子，索引葉的塊數目，索引的高度等。
儘管看到動態取樣的優點，但是它的缺點也是顯而易見，否則Oracle 一定會一直使用動態取樣來取代資料分析：
（1） 取樣的資料塊有限，對於海量資料的表，結果難免有偏差。
（2） 取樣會消耗系統資源，特別是OLTP資料庫，尤其不推薦使用動態取樣。
4.2 動態取樣的級別
Oracle 為動態取樣劃分了11個級別，在Oracle 的官網上詳細的介紹。
13.5.7.4 Dynamic Sampling Levels
The sampling levels are as follows if the dynamic sampling level used is from a cursor hint or from the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter:
Level 0: Do not use dynamic sampling.
Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for a predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is twice the default number of dynamic sampling blocks.
Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.
The sampling levels are as follows if the dynamic sampling level for a table is set using the optimizer hint:
Level 0: Do not use dynamic sampling.
Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32).
Levels 2, 3, 4, 5, 6, 7, 8, and 9: The number of blocks sampled is 2, 4, 8, 16, 32, 64, 128, or 256 times the default number of dynamic sampling blocks respectively.
Level 10: Read all blocks in the table.
4.2.1 Level 0
不做動態分析
4.2.2 Level 1
Oracle 對沒有分析的表進行動態取樣，但需要同時滿足以下4個條件。
（1） SQL中至少有一個未分析的表
（2） 未分析的表出現在關聯查詢或者子查詢中
（3） 未分析的表沒有索引
（4） 未分析的表佔用的資料塊要大於動態取樣預設的資料塊（32個）
4.2.3 Level 2
對所有的未分析表做分析，動態取樣的資料塊是預設資料塊的2倍。
4.2.4 Level 3
取樣的表包含滿足Level 2定義的所有表，同時包括，那些謂詞有可能潛在地需要動態取樣的表，這些動態取樣的資料塊為預設資料塊，對沒有分析的表，動態取樣的預設塊為預設資料塊的2倍。
4.2.5 Level 4
取樣的表包含滿足Level 3定義的表，同時還包括一些表，他們包含一個單表的謂詞會引用另外的2個列或者更多的列；取樣的塊數是動態取樣預設資料塊數；對沒有分析的表，動態取樣的資料塊為預設資料塊的2倍。
4.2.6 Level 5，6，7，8，9
取樣的表包含滿足Level 4定義的表，同時分別使用動態取樣預設資料塊的2，4，8，32，128 倍的數量來做動態分析。
4.2.7 Level 10
取樣的表包含滿足Level 9定義的所有表，同時對錶的所有資料進行動態取樣。
取樣的資料塊越多，得到的分析資料就越接近與真實，但同時伴隨著資源消耗的也越大。
4.3 什麼時候使用動態取樣
動態取樣也需要額外的消耗資料庫資源，所以，如果 SQL 被反覆執行，變數被繫結，硬分析很少，在這樣一個環境中，是不宜使用動態取樣的，就像OLTP系統。 動態取樣發生在硬分析時，如果很少有硬分析發生，動態取樣的意義就不大。
而在OLAP或者資料倉儲環境下，SQL執行消耗的資源要遠遠大於SQL解析，那麼讓解析在消耗多一點資源做一些動態取樣分析，從而做出一個最優的執行計劃是非常值得的。 實際上在這樣的環境中，硬分析消耗的資源幾乎是可以忽略的。
所以，一般在OLAP 或者資料倉儲環境中，將動態取樣的level 設定為3或者4 比較好。 相反，在OLTP系統下，不應該使用動態取樣。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2137208/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 分析及動態取樣（轉帖）Oracle
- Oracle動態取樣分析Oracle
- Oracle 動態取樣Oracle
- Oracle 執行計劃 分析和動態取樣Oracle
- 【原創】ORACLE 資料分析和動態取樣Oracle
- Oracle動態取樣學習Oracle
- oracle動態取樣_optimizer_dynamic_samplingOracle
- 【sql調優】動態取樣SQL
- oracle動態取樣_optimizer_dynamic_sampling(二)Oracle
- sql profile禁用oracle動態取樣dynamic samplingSQLOracle
- 臨時表(GLOBAL TEMPORARY TABLE)及統計資訊收集，動態取樣
- CBO與動態統計量取樣
- 如何找出使用動態取樣的SQLSQL
- oracle動態取樣dynamic sampling hint為何不生效一點思考Oracle
- 對於沒有任何統計資訊的表，ORACLE可能會動態取樣。Oracle
- 如何檢視哪些SQL使用了動態取樣？SQL
- 示例說明動態取樣的作用，並演示動態取樣對有內在關係的多列查詢的影響
- oracle隨機取樣Oracle隨機
- Java JDK 動態代理使用及實現原理分析JavaJDK
- Java 動態代理機制分析及擴充套件Java套件
- Java JDK 動態代理（AOP）使用及實現原理分析JavaJDK
- dtrace動態分析
- Oracle listener靜態及動態註冊總結(轉自網路）Oracle
- oracle 10g 中的靜態及動態註冊問題Oracle 10g
- lit動態修改樣式
- Java-JDK動態代理（AOP）使用及實現原理分析JavaJDK
- 【AWR】測試AWR手動取樣對系統自動取樣的影響
- dbms_stats.lock_table_stats與動態取樣（Dynamic Sampling）
- Oracle 動態SQLOracleSQL
- [Oracle] Oracle收集統計資訊的取樣比例Oracle
- php動態獲取常量PHP
- 動態規劃分析動態規劃
- 動態變更vue樣式Vue
- javascript 動態修改css樣式JavaScriptCSS
- 幾種常見取樣方法及原理
- oracle 監聽 靜態 動態Oracle
- Oracle資料庫啟動過程及狀態詳解Oracle資料庫
- Oracle10g中的靜態註冊及動態註冊問題Oracle