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