exec dbms_stats.gather_table_stats(ownname=>'dingjun123',tabname=>'t',cascade=>true,method_opt=>'for all columns size skewonly',no_invalidate=>false);
Elapsed: 00:00:00.00
dingjun123@ORADB> select column_name,histogram from dba_tab_col_statistics where table_name='T';
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
OWNER FREQUENCY
OBJECT_NAME HEIGHT BALANCED
SUBOBJECT_NAME HEIGHT BALANCED
OBJECT_ID NONE
DATA_OBJECT_ID HEIGHT BALANCED
OBJECT_TYPE FREQUENCY
CREATED HEIGHT BALANCED
LAST_DDL_TIME HEIGHT BALANCED
TIMESTAMP HEIGHT BALANCED
STATUS FREQUENCY
TEMPORARY FREQUENCY
GENERATED FREQUENCY
SECONDARY FREQUENCY
NAMESPACE FREQUENCY
EDITION_NAME NONE
TXT NONE
exec dbms_stats.set_table_prefs(ownname=>'dingjun123',tabname=>'t',pname=>'method_opt',pvalue=>'for all columns size 1');
--method_opt不加，按照设置的首选项for all columns size 1收集
dingjun123@ORADB> exec dbms_stats.gather_table_stats(ownname=>'dingjun123',tabname=>'t',cascade=>true,no_invalidate=>false);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.41
dingjun123@ORADB> select column_name,histogram from dba_tab_col_statistics where table_name='T';
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
OWNER NONE
OBJECT_NAME NONE
SUBOBJECT_NAME NONE
OBJECT_ID NONE
DATA_OBJECT_ID NONE
OBJECT_TYPE NONE
CREATED NONE
LAST_DDL_TIME NONE
TIMESTAMP NONE
STATUS NONE
TEMPORARY NONE
GENERATED NONE
SECONDARY NONE
NAMESPACE NONE
EDITION_NAME NONE
TXT NONE
--增加method_opt按照指定的收集
exec dbms_stats.gather_table_stats(ownname=>'dingjun123',tabname=>'t',cascade=>true,method_opt=>'for all columns size skewonly',no_invalidate=>false);
dingjun123@ORADB> exec dbms_stats.gather_table_stats(ownname=>'dingjun123',tabname=>'t',cascade=>true,method_opt=>'for all columns size skewonly',no_invalidate=>false);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.38
dingjun123@ORADB> select column_name,histogram from dba_tab_col_statistics where table_name='T';
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
OWNER FREQUENCY
OBJECT_NAME HEIGHT BALANCED
SUBOBJECT_NAME HEIGHT BALANCED
OBJECT_ID NONE
DATA_OBJECT_ID HEIGHT BALANCED
OBJECT_TYPE FREQUENCY
CREATED HEIGHT BALANCED
LAST_DDL_TIME HEIGHT BALANCED
TIMESTAMP HEIGHT BALANCED
STATUS FREQUENCY
TEMPORARY FREQUENCY
GENERATED FREQUENCY
SECONDARY FREQUENCY
NAMESPACE FREQUENCY
EDITION_NAME NONE
TXT NONE
16 rows selected.
--改为table_pref为skewonly测试
exec dbms_stats.set_table_prefs(ownname=>'dingjun123',tabname=>'t',pname=>'method_opt',pvalue=>'for all columns size skewonly');
--设置schema_pref为columns size 1
exec dbms_stats.set_schema_prefs(ownname=>'dingjun123',pname=>'method_opt',pvalue=>'for all columns size 1');
exec dbms_stats.gather_schema_stats(ownname=>'dingjun123',degree=>10);
--pref设置优先级table
dingjun123@ORADB> select column_name,histogram from dba_tab_col_statistics where table_name='T';
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
OWNER NONE
OBJECT_NAME NONE
SUBOBJECT_NAME NONE
OBJECT_ID NONE
DATA_OBJECT_ID NONE
OBJECT_TYPE NONE
CREATED NONE
LAST_DDL_TIME NONE
TIMESTAMP NONE
STATUS NONE
TEMPORARY NONE
GENERATED NONE
SECONDARY NONE
NAMESPACE NONE
EDITION_NAME NONE
TXT NONE
--如果优先级高的没有设置对应首选项，优先级低的设置了，则按照优先级低的
exec dbms_stats.set_table_prefs(ownname=>'dingjun123',tabname=>'t',pname=>'estimate_percent',pvalue=>'5');
dingjun123@ORADB> exec dbms_stats.gather_table_stats(ownname=>'dingjun123',tabname=>'t',cascade=>true,method_opt=>'for all columns size skewonly',no_invalidate=>false);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.43
dingjun123@ORADB> select num_rows,sample_size from dba_tables where table_name='T' and owner='DINGJUN123';
NUM_ROWS SAMPLE_SIZE
---------- -----------
78280 3914
exec dbms_stats.gather_schema_stats(ownname=>'dingjun123',degree=>10);
dingjun123@ORADB> select num_rows,sample_size from dba_tables where table_name='T' and owner='DINGJUN123';
NUM_ROWS SAMPLE_SIZE
---------- -----------
77140 3857
exec dbms_stats.set_table_prefs(ownname=>'dingjun123',tabname=>'t',pname=>'estimate_percent',pvalue=>'20');
exec dbms_stats.gather_schema_stats(ownname=>'dingjun123',degree=>10);
dingjun123@ORADB> /
NUM_ROWS SAMPLE_SIZE
---------- -----------
77470 15494
1 row selected.
select DBMS_STATS.GET_PREFS (
'method_opt',
'dingjun123',
't')
from dual;
dingjun123@ORADB> exec dbms_stats.set_table_prefs(ownname=>'dingjun123',tabname=>'t',pname=>'method_opt',pvalue=>'for all columns size skewonly');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
dingjun123@ORADB> select DBMS_STATS.GET_PREFS (
2 'method_opt',
3 'dingjun123',
4 't')
5 from dual;
DBMS_STATS.GET_PREFS('METHOD_OPT','DINGJUN123','T')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE SKEWONLY
1 row selected.
1 row selected.
select DBMS_STATS.GET_PREFS (
'estimate_percent',
'dingjun123',
't')
from dual;
dingjun123@ORADB> select DBMS_STATS.GET_PREFS (
2 'estimate_percent',
3 'dingjun123',
4 't')
5 from dual;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT','DINGJUN123','T')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20
1 row selected.
SELECT * FROM dba_tab_stat_prefs;
对于有些表设置首选项，修复其默认策略收集带来的问题。
阅读(339) | 评论(0) | 转发(0) |
