[20160516]統計分析引數method_opt.txt
[20160516]統計分析引數method_opt.txt
--統計分析引數method_opt我個人感覺是最能折騰人的引數.我自己曾經在這個引數上栽過跟頭,透過例子來說明看看.
1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--如果你看包定義,可以發現有趣的事情,比如預設定義:
DEFAULT_CASCADE CONSTANT BOOLEAN := null;
DEFAULT_DEGREE_VALUE CONSTANT NUMBER := 32766;
DEFAULT_ESTIMATE_PERCENT CONSTANT NUMBER := 101;
DEFAULT_METHOD_OPT CONSTANT VARCHAR2(1) := 'Z';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DEFAULT_NO_INVALIDATE CONSTANT BOOLEAN := null;
DEFAULT_GRANULARITY CONSTANT VARCHAR2(1) := 'Z';
DEFAULT_PUBLISH CONSTANT BOOLEAN := true;
DEFAULT_INCREMENTAL CONSTANT BOOLEAN := false;
DEFAULT_STALE_PERCENT CONSTANT NUMBER := 10;
DEFAULT_AUTOSTATS_TARGET CONSTANT VARCHAR2(1) := 'Z';
DEFAULT_STAT_CATEGORY CONSTANT VARCHAR2(20) := 'OBJECT_STATS';
--可以發現預設定義實際上是'Z'.
2.建立一個表測試看看:
SCOTT@book> create table t as select * from dba_objects;
Table created.
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Method_Opt => 'Z',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@book> select column_name, num_distinct, DENSITY,histogram,NUM_BUCKETS from dba_tab_columns where table_name='T' and owner=user;
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM NUM_BUCKETS
-------------------- ------------ ---------- --------------- -----------
OWNER 29 .034482759 NONE 1
OBJECT_NAME 52164 .00001917 NONE 1
SUBOBJECT_NAME 149 .006711409 NONE 1
OBJECT_ID 86946 .000011501 NONE 1
DATA_OBJECT_ID 9044 .000110571 NONE 1
OBJECT_TYPE 46 .02173913 NONE 1
CREATED 1036 .000965251 NONE 1
LAST_DDL_TIME 1307 .000765111 NONE 1
TIMESTAMP 1336 .000748503 NONE 1
STATUS 2 .5 NONE 1
TEMPORARY 2 .5 NONE 1
GENERATED 2 .5 NONE 1
SECONDARY 2 .5 NONE 1
NAMESPACE 21 .047619048 NONE 1
EDITION_NAME 0 0 NONE 0
15 rows selected.
SCOTT@book> select dbms_stats.GET_PARAM('METHOD_OPT') c30 from dual ;
C30
------------------------------
FOR ALL COLUMNS SIZE AUTO
--預設實際上是FOR ALL COLUMNS SIZE AUTO.順便做一個查詢包含全部欄位.
SCOTT@book> select * from t where owner=user and OBJECT_NAME='T' and SUBOBJECT_NAME='1' and OBJECT_ID=12 and DATA_OBJECT_ID=12 and OBJECT_TYPE='TABLE' and CREATED=sysdate;
no rows selected
SCOTT@book> execute dbms_stats.flush_database_monitoring_info
PL/SQL procedure successfully completed.
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Method_Opt => 'Z',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@book> select column_name, num_distinct, DENSITY,histogram,NUM_BUCKETS from dba_tab_columns where table_name='T' and owner=user;
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM NUM_BUCKETS
-------------------- ------------ ---------- --------------- -----------
OWNER 29 5.6432E-06 FREQUENCY 23
OBJECT_NAME 52164 .00001917 NONE 1
SUBOBJECT_NAME 149 .00094518 FREQUENCY 149
OBJECT_ID 86946 .000011501 NONE 1
DATA_OBJECT_ID 9044 .000110571 NONE 1
OBJECT_TYPE 46 5.6432E-06 FREQUENCY 32
CREATED 1036 .002824859 HEIGHT BALANCED 254
LAST_DDL_TIME 1307 .000765111 NONE 1
TIMESTAMP 1336 .000748503 NONE 1
STATUS 2 .5 NONE 1
TEMPORARY 2 .5 NONE 1
GENERATED 2 .5 NONE 1
SECONDARY 2 .5 NONE 1
NAMESPACE 21 .047619048 NONE 1
EDITION_NAME 0 0 NONE 0
15 rows selected.
--估計'Z'=>'FOR ALL COLUMNS SIZE AUTO'.
3.最常見的錯誤,就是少寫ALL,寫成了Method_Opt => 'Method_Opt => 'FOR COLUMNS SIZE 1',這樣並不會刪除直方圖.
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Method_Opt => 'FOR COLUMNS SIZE 1',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@book> select column_name, num_distinct, DENSITY,histogram,NUM_BUCKETS from dba_tab_columns where table_name='T' and owner=user;
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM NUM_BUCKETS
-------------------- ------------ ---------- --------------- -----------
OWNER 29 5.6432E-06 FREQUENCY 23
OBJECT_NAME 52164 .00001917 NONE 1
SUBOBJECT_NAME 149 .00094518 FREQUENCY 149
OBJECT_ID 86946 .000011501 NONE 1
DATA_OBJECT_ID 9044 .000110571 NONE 1
OBJECT_TYPE 46 5.6432E-06 FREQUENCY 32
CREATED 1036 .002824859 HEIGHT BALANCED 254
LAST_DDL_TIME 1307 .000765111 NONE 1
TIMESTAMP 1336 .000748503 NONE 1
STATUS 2 .5 NONE 1
TEMPORARY 2 .5 NONE 1
GENERATED 2 .5 NONE 1
SECONDARY 2 .5 NONE 1
NAMESPACE 21 .047619048 NONE 1
EDITION_NAME 0 0 NONE 0
15 rows selected.
--可以發現直方圖資訊不會刪除.
4.另外一個錯誤是寫成如下,例子:
Method_Opt => 'FOR COLUMNS owner,object_name SIZE 10'
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Method_Opt => 'FOR COLUMNS owner,object_name SIZE 10',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@book> select column_name, num_distinct, DENSITY,histogram,NUM_BUCKETS from dba_tab_columns where table_name='T' and owner=user;
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM NUM_BUCKETS
-------------------- ------------ ---------- --------------- -----------
OWNER 29 5.7092E-06 FREQUENCY 24
OBJECT_NAME 52164 .000031083 HEIGHT BALANCED 10
SUBOBJECT_NAME 149 .00094518 FREQUENCY 149
OBJECT_ID 86946 .000011501 NONE 1
DATA_OBJECT_ID 9044 .000110571 NONE 1
OBJECT_TYPE 46 5.6432E-06 FREQUENCY 32
CREATED 1036 .002824859 HEIGHT BALANCED 254
LAST_DDL_TIME 1307 .000765111 NONE 1
TIMESTAMP 1336 .000748503 NONE 1
STATUS 2 .5 NONE 1
TEMPORARY 2 .5 NONE 1
GENERATED 2 .5 NONE 1
SECONDARY 2 .5 NONE 1
NAMESPACE 21 .047619048 NONE 1
EDITION_NAME 0 0 NONE 0
15 rows selected.
--可以發現bucket的數量不對.寫成如下更加明確:
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Method_Opt => 'FOR COLUMNS owner,object_name SIZE 1',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@book> select column_name, num_distinct, DENSITY,histogram,NUM_BUCKETS from dba_tab_columns where table_name='T' and owner=user;
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM NUM_BUCKETS
-------------------- ------------ ---------- --------------- -----------
OWNER 29 5.7486E-06 FREQUENCY 22
OBJECT_NAME 52164 .00001917 NONE 1
SUBOBJECT_NAME 149 .00094518 FREQUENCY 149
OBJECT_ID 86946 .000011501 NONE 1
DATA_OBJECT_ID 9044 .000110571 NONE 1
OBJECT_TYPE 46 5.6432E-06 FREQUENCY 32
CREATED 1036 .002824859 HEIGHT BALANCED 254
LAST_DDL_TIME 1307 .000765111 NONE 1
TIMESTAMP 1336 .000748503 NONE 1
STATUS 2 .5 NONE 1
TEMPORARY 2 .5 NONE 1
GENERATED 2 .5 NONE 1
SECONDARY 2 .5 NONE 1
NAMESPACE 21 .047619048 NONE 1
EDITION_NAME 0 0 NONE 0
15 rows selected.
--owner 欄位的bucket數量不是1.當然這種錯誤不常見,一般建立直方圖我會選擇254.
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Method_Opt => 'FOR COLUMNS owner,object_name SIZE 254',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@book> select column_name, num_distinct, DENSITY,histogram,NUM_BUCKETS from dba_tab_columns where table_name='T' and owner=user;
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM NUM_BUCKETS
-------------------- ------------ ---------- --------------- -----------
OWNER 29 .000005758 FREQUENCY 23
OBJECT_NAME 52164 .000031516 HEIGHT BALANCED 254
SUBOBJECT_NAME 149 .00094518 FREQUENCY 149
OBJECT_ID 86946 .000011501 NONE 1
DATA_OBJECT_ID 9044 .000110571 NONE 1
OBJECT_TYPE 46 5.6432E-06 FREQUENCY 32
CREATED 1036 .002824859 HEIGHT BALANCED 254
LAST_DDL_TIME 1307 .000765111 NONE 1
TIMESTAMP 1336 .000748503 NONE 1
STATUS 2 .5 NONE 1
TEMPORARY 2 .5 NONE 1
GENERATED 2 .5 NONE 1
SECONDARY 2 .5 NONE 1
NAMESPACE 21 .047619048 NONE 1
EDITION_NAME 0 0 NONE 0
15 rows selected.
5.比較正確的寫法是:
Method_Opt => 'FOR COLUMNS SIZE 10 owner, OBJECT_NAME'
或者
Method_Opt => 'FOR COLUMNS SIZE 10 owner for columnssize 10 OBJECT_NAME'
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Method_Opt => 'FOR COLUMNS SIZE 10 owner , OBJECT_NAME',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
或者
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Method_Opt => 'FOR COLUMNS SIZE 10 owner for columns size 10 OBJECT_NAME',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@book> select column_name, num_distinct, DENSITY,histogram,NUM_BUCKETS from dba_tab_columns where table_name='T' and owner=user;
COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM NUM_BUCKETS
-------------------- ------------ ---------- --------------- -----------
OWNER 29 .023255814 HEIGHT BALANCED 10
OBJECT_NAME 52164 .000031943 HEIGHT BALANCED 10
SUBOBJECT_NAME 149 .00094518 FREQUENCY 149
OBJECT_ID 86946 .000011501 NONE 1
DATA_OBJECT_ID 9044 .000110571 NONE 1
OBJECT_TYPE 46 5.6432E-06 FREQUENCY 32
CREATED 1036 .002824859 HEIGHT BALANCED 254
LAST_DDL_TIME 1307 .000765111 NONE 1
TIMESTAMP 1336 .000748503 NONE 1
STATUS 2 .5 NONE 1
TEMPORARY 2 .5 NONE 1
GENERATED 2 .5 NONE 1
SECONDARY 2 .5 NONE 1
NAMESPACE 21 .047619048 NONE 1
EDITION_NAME 0 0 NONE 0
15 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2100870/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 多元統計分析03：多元正態分佈的引數估計
- 留存統計 引數聚合函式函式
- Ceph配置引數分析
- 數學-概率與統計-數理統計-總結(四)：方差分析及迴歸分析
- Oracle引數檔案解析——引數檔案分析獲取Oracle
- MySQL索引統計資訊更新相關的引數MySql索引
- Oracle收集統計資訊之NO_INVALIDATE引數Oracle
- Oracle 統計量NO_INVALIDATE引數配置（下）Oracle
- Oracle 統計量NO_INVALIDATE引數配置（上）Oracle
- BW 系統引數
- SQL Server效能分析引數 (轉)SQLServer
- CUDA __global__ function 引數分析Function
- 小程式內引數和掃碼引數統一
- 資料統計與視覺化複習總結（二）：非引數檢驗、生存分析視覺化
- MySQL中的統計資訊相關引數介紹MySql
- SAP配置系統引數
- AIX 系統引數配置AI
- 硬碟預讀引數變化分析硬碟
- 【7】JVM引數說明和分析JVM
- 淺析SQL Server效能分析引數SQLServer
- flume 寫往hdfs引數理解分析
- 【統計】能夠在session級別和system級別修改的oracle引數統計SessionOracle
- SciTech-Mathmatics-Probability+Statistics-Population:Region-統計量與引數估計
- 【引數】REMOTE_LOGIN_PASSWORDFILE引數三種取值及其行為特性分析REM
- linux修改系統引數Linux
- SAP系統引數設定
- AIX 系統引數配置 -- 轉AI
- SAP 系統引數設定
- 更改oracle系統引數例子Oracle
- kettle 引數——變數引數和常量引數變數
- C# 中的 in 引數和效能分析C#
- Nagios資料庫引數配置分析iOS資料庫
- 數理統計實（試）驗——單因素方差分析——excel操作分析Excel
- 多元統計分析01：多元統計分析基礎
- 基於ZooKeeper，Spring設計實現的引數系統Spring
- MySQL 配置InnoDB配置非持久優化器統計資訊引數MySql優化
- MySQL 5.6所有系統變數（系統引數）MySql變數
- [zt] 統計分析工具dbms_stats 預設引數在 9i、10g中的變化