[20120905]删除直方图.txt
维护直方图是一个成本很高的操作,9i的缺省模式Method_Opt=> 'FOR ALL COLUMNS SIZE REPEAT ',而10g的缺省模式
Method_Opt => 'FOR ALL COLUMNS SIZE AUTO ',这样会导致在许多不必要的字段建立直方图。
我自己在维护10g的数据库也存在许多sql执行计划不稳定的情况,我后来采取的方法就是把这些控制在自己的手中。
修改10g的缺省模式为Method_Opt=> 'FOR ALL COLUMNS SIZE REPEAT ',这样把直方图的建立控制在自己管理的范围内。
SQL> column x format a30
SQL> select DBMS_STATS.get_param('METHOD_OPT') x from dual;
X
------------------------------
FOR ALL COLUMNS SIZE AUTO
SQL> exec DBMS_STATS.SET_PARAM(pname => 'METHOD_OPT', pval => 'FOR ALL COLUMNS SIZE REPEAT');
--但是删除建立的直方图,一直没有好方法。今天看书,发现11G下很容易操作:文档如下:
procedure delete_column_stats(
ownname varchar2, tabname varchar2, colname varchar2,
partname varchar2 default null,
stattab varchar2 default null, statid varchar2 default null,
cascade_parts boolean default true,
statown varchar2 default null,
no_invalidate boolean default
to_no_invalidate_type(get_param('NO_INVALIDATE')),
force boolean default FALSE,
col_stat_type varchar2 default 'ALL');
--
-- Deletes column-related statistics
--
-- Input arguments:
-- ownname - The name of the schema
-- tabname - The name of the table to which this column belongs
-- colname - The name of the column or extension
-- partname - The name of the table partition for which to delete
-- the statistics. If the table is partitioned and partname
-- is null, global column statistics will be deleted.
-- stattab - The user stat table identifier describing from where
-- to delete the statistics. If stattab is null, the statistics
-- will be deleted directly from the dictionary.
-- statid - The (optional) identifier to associate with these statistics
-- within stattab (Only pertinent if stattab is not NULL).
-- cascade_parts - If the table is partitioned and partname is null,
-- setting this to true will cause the deletion of statistics for
-- this column for all underlying partitions as well.
-- statown - The schema containing stattab (if different then ownname)
-- no_invalidate - Do not invalide 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 dependend cursors. This is the default. The default
-- can be changed using set_param procedure.
-- force - delete statistics even if it is locked
-- col_stat_type - Type of column statitistics to be deleted.
-- This argument takes the following values:
-- 'HISTOGRAM' - delete column histogram only
-- 'ALL' - delete base column stats and histogram
--
-- Exceptions:
-- ORA-20000: Object does not exist or insufficient privileges
-- ORA-20002: Bad user statistics table, may need to upgrade it
-- ORA-20005: object statistics are locked
--
建立测试例子操作如下:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table t as select rownum id1 , mod(rownum,100)+1 id2, 'test' name from dual connect by level<=1e4;
select column_name,histogram from dba_tab_columns where table_name='T';
SQL> select column_name,histogram from dba_tab_columns where wner=user and table_name='T';
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
ID1 NONE
ID2 NONE
NAME NONE
SQL> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> select column_name,histogram from dba_tab_columns where wner=user and table_name='T';
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
ID1 NONE
ID2 NONE
NAME NONE
--以前采用的方法!这样要重新分析表。太麻烦了。
SQL> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1,for columns id1 size 254 ,for columns id2 size 254');
PL/SQL procedure successfully completed.
SQL> select column_name,num_distinct,histogram from dba_tab_columns where wner=user and table_name='T';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
ID1 10000 HEIGHT BALANCED
ID2 100 FREQUENCY
NAME 1 NONE
--如果执行如下命令,统计信息也删除了。因为缺省是col_stat_type varchar2 default 'ALL'。
SQL> execute dbms_stats.delete_column_stats(ownname=>user,tabname=>'t',colname=>'id2');
PL/SQL procedure successfully completed.
SQL> select column_name,num_distinct,histogram from dba_tab_columns where wner=user and table_name='T';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
ID1 10000 HEIGHT BALANCED
ID2 NONE
NAME 1 NONE
--删除直方图id2字段。
SQL> execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1,for columns id1 size 254 ,for columns id2 size 254');
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.delete_column_stats(ownname=>user,tabname=>'t',colname=>'id2',col_stat_type=>'histogram');
PL/SQL procedure successfully completed.
SQL> select column_name,num_distinct,histogram from dba_tab_columns where wner=user and table_name='T';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
ID1 10000 HEIGHT BALANCED
ID2 100 NONE
NAME 1 NONE
--听说还有另外一种方法,就是人为地修改统计字段的一些属性,比如distcnt。测试如下:
SQL> select column_name,num_distinct,histogram from dba_tab_columns where wner=user and table_name='T';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
ID1 10000 HEIGHT BALANCED
ID2 100 NONE
NAME 1 NONE
SQL> execute dbms_stats.set_column_stats(ownname=>user,tabname=>'T',colname=> 'id1',distcnt=>1e4);
PL/SQL procedure successfully completed.
SQL> select column_name,num_distinct,histogram from dba_tab_columns where wner=user and table_name='T';
COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
ID1 10000 NONE
ID2 100 NONE
NAME 1 NONE
--可以发现直方图ID1也删除了.
SQL> column ENDPOINT_ACTUAL_VALUE noprint
SQL> select * from dba_tab_histograms where wner=user and table_name='T' and column_name='ID1';
OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------- ---------- --------------- --------------
SCOTT T ID1 0 1
SCOTT T ID1 1 10000
--这个在10g也许是最快的方法!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-742746/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-742746/