[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.