[20120905]删除直方图.txt

[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

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也许是最快的方法！