DBMS_STATS包的幾個常用功能分別是什么

今天就跟大家聊聊有關DBMS_STATS包的幾個常用功能分別是什么，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

3.1 DBMS_STATS包的幾個常用功能

    性能數據的收集包含這樣幾個存儲過程：

GATHER_DATABASE_STATS Procedures

GATHER_DICTIONARY_STATS Procedure

GATHER_FIXED_OBJECTS_STATS Procedure

GATHER_INDEX_STATS Procedure

GATHER_SCHEMA_STATS Procedures

GATHER_SYSTEM_STATS Procedure

GATHER_TABLE_STATS Procedure

從名字也可以看出各自的作用，這些存儲過程用來收集數據庫不同級別對象的性能數據，包括：數據庫，數據字典，表，索引，SCHEMA的性能等。

3.1.1 GATHER_TABLE_STATS Procedure存儲過程

在10g中, GATHER_TABLE_STATS的參數如下：

DBMS_STATS.GATHER_TABLE_STATS (

ownname VARCHAR2,

tabname VARCHAR2,

partname VARCHAR2 DEFAULT NULL,

estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param('ESTIMATE_PERCENT')),

block_sample BOOLEAN DEFAULT FALSE,

method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),

cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

statown VARCHAR2 DEFAULT NULL,

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

get_param('NO_INVALIDATE')),

force BOOLEAN DEFAULT FALSE);

到了11g，對參數做了調整：

    DBMS_STATS.GATHER_TABLE_STATS (

ownname VARCHAR2,

tabname VARCHAR2,

partname VARCHAR2 DEFAULT NULL,

estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param('ESTIMATE_PERCENT')),

block_sample BOOLEAN DEFAULT FALSE,

method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),

cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

statown VARCHAR2 DEFAULT NULL,

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

get_param('NO_INVALIDATE')),

force BOOLEAN DEFAULT FALSE);

對參數的說明：

Parameter

Description

ownname

Schema of table to analyze

tabname

Name of table

partname

Name of partition

estimate_percent

Percentage of rows to estimate (NULL means compute) The  valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE  to have Oracle determine the appropriate sample size for good statistics.  This is the default.The default value can be changed using the SET_PARAM Procedure.

block_sample

Whether or not to use random block sampling instead of  random row sampling. Random block sampling is more efficient, but if the data  is not randomly distributed on disk, then the sample values may be somewhat  correlated. Only pertinent when doing an estimate statistics.

method_opt

Accepts:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column|attribute  [size_clause] [,column|attribute [size_clause]...]

size_clause is defined as size_clause := SIZE {integer  | REPEAT | AUTO | SKEWONLY}

 - integer : Number of histogram buckets. Must be in the range [1,254].

 - REPEAT : Collects histograms only on the columns that already have  histograms.

 - AUTO : Oracle determines the columns to collect histograms based on data  distribution and the workload of the columns.

 - SKEWONLY : Oracle determines the columns to collect histograms based on the  data distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO.The default  value can be changed using the SET_PARAM Procedure.

degree

Degree of parallelism. The default for degree is NULL.  The default value can be changed using the SET_PARAM Procedure NULL means use the table  default value specified by the DEGREE clause in the CREATE TABLE or ALTER  TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the  default value based on the initialization parameters. The AUTO_DEGREE value  determines the degree of parallelism automatically. This is either 1 (serial  execution) or DEFAULT_DEGREE (the system default value based on number of  CPUs and initialization parameters) according to size of the object.

granularity

Granularity of statistics to collect (only pertinent if  the table is partitioned).

'ALL' - gathers all (subpartition, partition, and  global) statistics

'AUTO'- determines the granularity based on the  partitioning type. This is the default value.

'DEFAULT' - gathers global and partition-level statistics.  This option is obsolete, and while currently supported, it is included in the  documentation for legacy reasons only. You should use the 'GLOBAL AND  PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - gathers global statistics

'GLOBAL AND PARTITION' - gathers the global and  partition level statistics. No subpartition level statistics are gathered  even if it is a composite partitioned object.

'PARTITION '- gathers partition-level statistics

'SUBPARTITION' - gathers subpartition-level statistics.

cascade

Gather statistics on the indexes for this table. Index  statistics gathering is not parallelized. Using this option is equivalent to  running the GATHER_INDEX_STATS Procedure on each of the  table's indexes. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle  determine whether index statistics to be collected or not. This is the  default. The default value can be changed using theSET_PARAM Procedure.

stattab

User statistics table identifier describing where to  save the current statistics

statid

Identifier (optional) to associate with these  statistics within stattab

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate 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 dependent cursors. This is the default. The default can be changed  using the SET_PARAM Procedure.

force

Gather statistics of table even if it is locked

在gather_table_stats 存儲過程的所有參數中，除了ownname和tabname，其他的參數都有默認值。所以我們在調用這個存儲過程時，Oracle 會使用參數的默認值對表進行分析。如：

SQL> exec dbms_stats.gather_table_STATS('SYS','T');

PL/SQL 過程已成功完成。

    如果想查看當前的默認值，可以使用dbms_stats.get_param函數來獲取：

SQL> select dbms_stats.get_param('method_opt') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')

------------------------------------------------------------

FOR ALL COLUMNS SIZE AUTO

結合上面對參數的說明：

  - AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

我們可以看出，就是對所有的列做直方圖分析，直方圖設置的bucket值由Oracle自己決定。

3.1.1.1 estimate_percent 參數

    這個參數是一個百分比值，它告訴分析包需要使用表中數據的多大比例來做分析。

理論上來講，采樣的數據越多，得到的信息就越接近于實際，CBO做出的執行計劃就越優化，但是，采樣越多，消耗的系統資源必然越多。對系統的影響也越大。所以對于這個值的設置，要根據業務情況來。如果數據的直方圖分布比較均勻，就可以使用默認值：AUTO_SAMPLE_SIZE，即讓Oracle 自己來判斷采樣的比例。有時，特別是對于批量加載的表，我們可以預估表中的數據量，可以人工地設置一個合理的值。一般，對于一個有1000萬數據的表分區，可以把這個參數設置為0.000001.

3.1. 1.2 Method_option 參數

    這個參數用來定義直方圖分析的一些值。

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]

    這里給出了4種指定哪些列進行分析的方式：

（1） 所有列：for all column

（2） 索引列：只對有索引的列進行分析，for all indexed columns

（3） 影藏列：只對影藏的列進行分析，for all hidden columns

（4） 顯示指定列：顯示的指定那些列進行分析，for columns columns_name

該參數默認值：for all columns size auto.

3.1. 1.3 degree 參數

用來指定分析時使用的并行度。有以下這些設置：

(1) Null：如果設置為null，Oracle 將使用被分析表屬性的并行度，比如表在創建時指定的并行度，或者后者使用alter table 重新設置的并行度。

(2) 一個數值：可以顯示地指定分析時使用的并行度。

(3) Default_degree: 如果設置為default，Oracle 將根據初始化參數中相關參數的設置來決定使用的并行度。

這個參數的默認值是Null，即通過表上的并行度屬性來決定分析使用的并行度。當需要分析的表或表分區非常大，并且系統資源比較充分的時候，就可以考慮使用并行的方式來做分析，這樣就會大大提高分析的速度。相反，如果你的系統資源比較吃緊，那么啟用并行可能會適得其反。

3.1. 1.4 Granularity

分析的粒度，有以下幾個配置：

（1） ALL : 將會對表的全局（global），分區，子分區的數據都做分析

（2） AUTO: Oracle 根據分區的類型，自動決定做哪一種粒度的分析。

（3） GLOBAL：只做全局級別的分析。

（4） GLOBAL AND PARTITION: 只對全局和分區級別做分析，對子分區不做分析，這是和ALL的一個區別。

（5） PARTITION: 只在分區級別做分析。

（6） SUBPARTITION: 只在子分區做分析。

在生產環境中，特別是OLAP 或者數據倉庫的環境中，這個參數的設置會直接影響到CBO的執行計劃選擇。

在OLAP或者數據倉庫系統中，經常有這樣的事情，新創建一個分區，將批量的數據（通常是很大的數據）加載到分區中，對分區做分析，然后做報表或者數據挖掘。在理想的情況下，對表的全局，分區都做分析，這樣才能得到最充足的數據，但是通常這樣的表都非常大，如果每增加一個分區都需要做一次全局分析，那么會消耗極大的系統資源。但是如果只對新加入的分區進行分區而不做全局分析，oracle 在全局范圍內的信息就會不準確。

    該參數在默認情況下，DBMS_STATS 包會對表級（全局），分區級（對應參數partition）都會進行分析。如果把cascade 設置為true，相應索引的全局和分區級別也都會被分析。如果只對分區級進行分析，而全局沒有分析，那么全局信息沒有更新，依然會導致CBO 作出錯誤的執行計劃。

所以當一些新的數據插入到表中時，如果對這些新的數據進行分析，是一個非常重要的問題。一般參考如下原則：

（1） 看一下新插入的數據在全表中所占的比例，如果所占比例不是很大，那么可以考慮不做全局分析，否則就需要考慮，一句是業務的實際運行情況。

（2） 采樣比例。如果載入的數據量非常大，比如上千萬或者更大，就要把采樣比例壓縮的盡可能地小，但底線是不能影響CBO做出正確的執行計劃，采樣比例的上線是不能消耗太多的資源而影響到業務的正常運行。

（3） 新加載的數據應該要做分區級的數據分析。至于是否需要直方圖分析，以及設置多少個buckets（size參數指定），需要DBA一句數據的分布情況進行考慮，關鍵是視數據的傾斜程度而定。

3.1.2 GATHER_SCHEMA_STATS 存儲過程

    這個存儲過程用于對某個用戶下所有的對象進行分析。如果你的數據用戶對象非常多，單獨對每個對象進行分析設定會非常不方便，這個存儲過程就很方便。它的好處在于如果需要分析的對象非常多，將可以大大降低DBA的工作量，不足之處是所有分析使用相同的分析策略，可能會導致分析不是最優。所以要根據實際情況來決定。

    該存儲過程參數如下：

    DBMS_STATS.GATHER_SCHEMA_STATS (

ownname VARCHAR2,

estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param('ESTIMATE_PERCENT')),

block_sample BOOLEAN DEFAULT FALSE,

method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),

cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

options VARCHAR2 DEFAULT 'GATHER',

objlist OUT ObjectTab,

statown VARCHAR2 DEFAULT NULL,

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

get_param('NO_INVALIDATE')),

force BOOLEAN DEFAULT FALSE,

obj_filter_list ObjectTab DEFAULT NULL);

參數說明如下：

Parameter

Description

ownname

Schema to analyze (NULL means current  schema)

estimate_percent

Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size  for good statistics. This is the default.The default value can be changed  using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.

block_sample

Whether or not to use random block sampling instead of  random row sampling. Random block sampling is more efficient, but if the data  is not randomly distributed on disk, then the sample values may be somewhat  correlated. Only pertinent when doing an estimate statistics.

method_opt

Accepts:

FOR ALL [INDEXED | HIDDEN] COLUMNS[size_clause]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

 - integer : Number of histogram buckets. Must be in the range [1,254].

 - REPEAT : Collects histograms only on the columns that already have histograms.

 - AUTO : Oracle determines the columns to collect histograms based on data  distribution and the workload of the columns.

 - SKEWONLY : Oracle determines the columns to collect histograms based on the data  distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.

degree

Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.  NULL means use the table default value specified by the DEGREE clause in the CREATE  TABLE or ALTER TABLE  statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the  initialization parameters.The AUTO_DEGREEvalue  determines the degree of parallelism automatically. This is either 1 (serial  execution) or DEFAULT_DEGREE (the system  default value based on number of CPUs and initialization parameters)  according to size of the object.

granularity

Granularity of statistics to collect (only pertinent if  the table is partitioned).

'ALL' - gathers all (subpartition, partition, and global) statistics

'AUTO'- determines the granularity based on the partitioning type. This is the  default value.

'DEFAULT' - gathers global and partition-level statistics. This option is  obsolete, and while currently supported, it is included in the documentation  for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - gathers global statistics

'GLOBAL  AND PARTITION' - gathers the global and  partition level statistics. No subpartition level statistics are gathered  even if it is a composite partitioned object.

'PARTITION '- gathers partition-level statistics

'SUBPARTITION' - gathers subpartition-level statistics.

cascade

Gather statistics on the indexes as well. Using this  option is equivalent to running the GATHER_INDEX_STATS Procedure  on each of the indexes in the schema in addition to gathering table and  column statistics. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle  determine whether index statistics to be collected or not. This is the  default. The default value can be changed using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.

stattab

User statistics table identifier describing where to  save the current statistics

statid

Identifier (optional) to associate with these  statistics within stattab

options

Further specification of which objects to gather  statistics for:

GATHER: Gathers statistics on all objects in the schema.

GATHERAUTO: Gathers all necessary statistics automatically.  Oracle implicitly determines which objects need new statistics, and  determines how to gather those statistics. When GATHER AUTO is  specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a  list of processed objects.

GATHERSTALE: Gathers statistics on stale objects as determined by  looking at the *_tab_modifications views. Also, return a list of objects found to be  stale.

GATHEREMPTY: Gathers statistics on objects which currently have no  statistics. also, return a list of objects found to have no statistics.

LIST AUTO: Returns a list of objects to be processed with GATHER AUTO.

LISTSTALE: Returns list of stale objects as determined by  looking at the *_tab_modifications views.

LISTEMPTY: Returns list of objects which currently have no  statistics.

objlist

List of objects found to be stale or empty

statown

Schema containing stattab (if different  than ownname)

no_invalidate

Does not invalidate 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 dependent  cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.

force

Gather statistics on objects even if they are locked

obj_filter_list

A list of object filters. When provided, GATHER_SCHEMA_STATS will gather statistics only on objects which satisfy at least one  object filter in the list as needed. In a single object filter, we can  specify the constraints on the object attributes. The attribute values  specified in the object filter are case- insensitive unless double-quoted.  Wildcard is allowed in the attribute values. Suppose non-NULL values s1, s2, ... are specified for attributes a1, a2, ... in one  object filter. An object o is said to satisfy this object filter if (o.a1  like s1) and (o.a2 like s2) and ... is true. See Applying an Object Filter List.

3.1.3 DBMS_STATS.GATHER_INDEX_STATS 存儲過程

    該存儲過程用于對索引的分析，如果我們在使用DBMS_STATS.GATHER_TABLES_STATS的分析時設置參數cascade=>true。那么Oracle會同時執行這個存儲過程來對索引進行分析。

存儲過程參數：

DBMS_STATS.GATHER_INDEX_STATS (

ownname VARCHAR2,

indname VARCHAR2,

partname VARCHAR2 DEFAULT NULL,

estimate_percent NUMBER DEFAULT to_estimate_percent_type

(GET_PARAM('ESTIMATE_PERCENT')),

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

statown VARCHAR2 DEFAULT NULL,

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type

(GET_PARAM('NO_INVALIDATE')),

force BOOLEAN DEFAULT FALSE);

Parameter

Description

ownname

Schema of index to analyze

indname

Name of index

partname

Name of partition

estimate_percent

Percentage of rows to estimate (NULL means compute). The valid range is [0.000001,100]. Use the  constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size  for good statistics. This is the default.The default value can be changed  using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.

stattab

User statistics table identifier describing where to  save the current statistics

statid

Identifier (optional) to associate with these  statistics within stattab

statown

Schema containing stattab (if different  than ownname)

degree

Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.  NULL means use of table default value that was specified by the DEGREE clause in the CREATE/ALTER  INDEX statement. Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value based on the initialization  parameters. The AUTO_DEGREE value determines the degree of parallelism  automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value  based on number of CPUs and initialization parameters) according to size of  the object.

granularity

Granularity of statistics to collect (only pertinent if  the table is partitioned).

'ALL' - gathers all (subpartition, partition, and global) statistics

'AUTO'- determines the granularity based on the partitioning type. This is the  default value.

'DEFAULT' - gathers global and partition-level statistics. This option is  obsolete, and while currently supported, it is included in the documentation  for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - gathers global statistics

'GLOBAL  AND PARTITION' - gathers the global and  partition level statistics. No subpartition level statistics are gathered  even if it is a composite partitioned object.

'PARTITION '- gathers partition-level statistics

'SUBPARTITION' - gathers subpartition-level statistics.

no_invalidate

Does not invalidate 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 dependent  cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure,  SET_GLOBAL_PREFS Procedure,  SET_SCHEMA_PREFS Procedure  and SET_TABLE_PREFS Procedure.

force

Gather statistics on object even if it is locked

上面討論了三個常用的存儲過程。分析對CBO 來說非常重要，如果不能按照自己的系統指定出切合實際的數據分析方案，可能會導致如下問題的發生：

（1） 分析信息不充分導致CBO 產生錯誤的執行計劃，導致SQL執行效率低下。

（2） 過多的分析工具帶來系統性能的嚴重下降。

3.2 DBMS_STATS包管理功能

3.2.1 獲取分析數據

GET_COLUMN_STATS Procedures

GET_INDEX_STATS Procedures

GET_SYSTEM_STATS Procedure

GET_TABLE_STATS Procedure

這四個存儲過程分別為用戶獲取字段，索引，表和系統的統計信息。它的用法是首先定義要獲取性能指標的變量，然后使用存儲過程將性能指標的值賦給變量，最后將變量的值輸出。如：

SQL> set serveroutput on

SQL> declare

2 dist number;

3 dens number;

4 ncnt number;

5 orec dbms_stats.statrec;

6 avgc number;

7 begin

8 dbms_stats.get_column_stats('SYS','T','object_ID',distcnt=>dist,density=>dens,nullcnt=>ncnt,srec=>orec,avgclen=>avgc);

9 dbms_output.put_line('the distcnt is:' ||to_char(dist));

10 dbms_output.put_line('the density is:' ||to_char(dens));

11 dbms_output.put_line('the nullcnt is:' ||to_char(ncnt));

12 dbms_output.put_line('the srec is:' ||to_char(ncnt));

13 dbms_output.put_line('the avgclen is:' ||to_char(avgc));

14 end;

15 /

the distcnt is:72926

the density is:.0000137125305103804

the nullcnt is:0

the srec is:0

the avgclen is:5

PL/SQL 過程已成功完成。

3.2.2 設置分析數據

SET_COLUMN_STATS Procedures

SET_INDEX_STATS Procedures

SET_SYSTEM_STATS Procedure

SET_TABLE_STATS Procedure

這幾個存儲過程允許我們手工地為字段，索引，表和系統性能數據賦值。它的一個用處是當相應的指標不準確導致執行計劃失敗時，可以使用這種方法手工地來為這些性能數據賦值。在極端情況下，這也不失為一個解決問題的方法。

3.2.3 刪除分析數據

DELETE_COLUMN_STATS Procedure

DELETE_DATABASE_STATS Procedure

DELETE_DICTIONARY_STATS Procedure

DELETE_FIXED_OBJECTS_STATS Procedure

DELETE_INDEX_STATS Procedure

DELETE_SCHEMA_STATS Procedure

DELETE_SYSTEM_STATS Procedure

DELETE_TABLE_STATS Procedure

當性能數據出現異常導致CBO判斷錯誤時，為了立刻修正這個錯誤，刪除性能數據也是一種補救的方法，比如刪除了表的數據，讓CBO重新對表做動態采樣分析，得到一個正確的結果。

    它可以刪除字段，數據庫，數據字典，基表，索引，表等級別的性能數據。

3.2.4 保存分析數據

CREATE_STAT_TABLE Procedure

DROP_STAT_TABLE Procedure

    可以用這兩個存儲過程創建一個表，用于存放性能數據，這樣有利于對性能數據的管理，也可以刪除這個表。

3.2.5 導入和導出分析數據

EXPORT_COLUMN_STATS Procedure

EXPORT_DATABASE_STATS Procedure

EXPORT_DICTIONARY_STATS Procedure

EXPORT_FIXED_OBJECTS_STATS Procedure

EXPORT_INDEX_STATS Procedure

EXPORT_SCHEMA_STATS Procedure

EXPORT_SYSTEM_STATS Procedure

EXPORT_TABLE_STATS Procedure

IMPORT_COLUMN_STATS Procedure

IMPORT_DATABASE_STATS Procedure

IMPORT_DICTIONARY_STATS Procedure

IMPORT_FIXED_OBJECTS_STATS Procedure

IMPORT_INDEX_STATS Procedure

IMPORT_SCHEMA_STATS Procedure

IMPORT_SYSTEM_STATS Procedure

IMPORT_TABLE_STATS Procedure

這些存儲過程可以將已經有的性能指標導入到用戶創建好的表中存放，需要時，可以從表中倒回來。

3.2.6 鎖定分析數據

LOCK_SCHEMA_STATS Procedure

LOCK_TABLE_STATS Procedure

UNLOCK_SCHEMA_STATS Procedure

UNLOCK_TABLE_STATS Procedure

The LOCK_* procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.

可能在某些時候，我們覺得當前的統計信息非常好，執行計劃很準確，并且表中數據幾乎不變化，那么可以使用LOCK_TABLE_STATS Procedure 來鎖定表的統計信息，不允許對表做分析或者設定分析數據。 當表的分析數據被鎖定之后，相關的所有分析數據，包括表級，列級，直方圖，索引的分析數據都將被鎖定，不允許被更新。

看完上述內容，你們對DBMS_STATS包的幾個常用功能分別是什么有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。