DBMS_STAT筆記

The DBMS_STATS subprograms perform the following general operations:

[@more@]

The DBMS_STATS subprograms perform the following general operations:

第一部分：Gathering Optimizer Statistics

1.GATHER_DATABASE_STATS

收集資料庫中所有物件的統計資訊

Parameters:

--estimate_percent:需分析的百分比 (NULL means compute)，預設值可以透過SET_PARAM來改變。The valid range is [0.000001,100]

--block_sample:無論是否是隨機塊取樣替代隨機行取樣，隨機塊取樣的效率都會比較高。除非資料不是隨機分佈在磁碟上的。DEFAULT FALSE

--method_opt:

• 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. 預設值可以透過SET_PARAM來改變

--degree:並行度 ，The default for degree is NULL.

--granularity:只有分割槽表才會用到此引數

--cascade:是否要統計此表上的index,預設為false, 預設值可以透過SET_PARAM來改變

--Stattab：User statistics table identifier describing where to save the current statistics.

--Statid: Identifier (optional) to associate with these statistics within stattab.

--options:預設值GATHER(Gathers statistics on all objects in the schema),其它選項：

GATHER AUTO: Gathers all necessary statistics automatically

GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views.

GATHER EMPTY: Gathers statistics on objects which currently have no statistics.

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

When GATHER AUTO is specified, the only additional valid parameters are stattab, statid, objlist and statown;

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

LIST EMPTY: Returns a list of objects which currently have no statistics.

--objlist: List of objects found to be stale or empty

--statown: Schema containing stattab (if different from current schema)

--gather_sys: Gathers statistics on the objects owned by the 'SYS' user，預設值TRUE

--no_invalidate:設定為TRUE時，相關的遊標不會失效;反之，遊標失效。

2. GATHER_DICTIONARY_STATS

此過程收集統計資料字典使用者’SYS’,’SYSTEM’以及RDBMS元件使用者.

必須有SYSDBA或ANALYZE ANY DICTIONARY 和 ANALYZE ANY SYSTEM 許可權才能執行此過程。

引數：

--comp_id：指定需統計使用者的COMP_ID,透過DBA_REGISTRY檢視來檢視COMP_ID。如此值為NULL，則統計所有RDBMS元件的使用者。此管是否有此引數，’SYS’,’SYSTEM’使用者都會被統計。

其它引數estimate_percent --block_sample --method_opt –degree –granularity –cascade –stattab –statid –options –objlist –statown --no_invalidate都同上

3. GATHER_FIXED_OBJECTS_STATS

此過程收集統計所有的動態效能表

必須有SYSDBA或具有ANALYZE ANY DICTIONARY許可權才能執行此過程。

引數：stattab – statid – statown -- no_invalidate

4. GATHER_INDEX_STATS

此過程收集索引的統計。一些引數被限制的，不會並行處理cluster indexes, domain indexes,和bitmap join indexes，granularity 和 no_invalidate引數在這些index中不起作用。

引數：

--ownname：被統計的schema

-- indname:index名

--Partname：Name of partition

--force：即使表被鎖定，也強行統計

--estimate_percent –stattab –statid –statown –degree –granularity --no_invalidate都同上

5. GATHER_SCHEMA_STATS

此過程收集統計某使用者下所有的物件

引數：

--ownname：需分析的schema, null時則為當前schema

--estimate_percent --block_sample --method_opt –degree –granularity –cascade –stattab –statid –options –objlist –statown --no_invalidate都同上

6. GATHER_SYSTEM_STATS

此過程收集系統統計，用來系統的cpu和IO指標

引數：

--gathering_mode:

--NOWORKLOAD,收集I/O的效能指標，資料庫的大小決定收集時間。收集期間ORACLE將評估IO平均的讀取尋道時間和傳輸速度。

--INTERVAL，指定統計的時間(分鐘數)，統計完成後，統計資訊將建立或更新在資料字典或指定的stattab中。在統計期間可以用GATHER_SYSTEM_STATS (gathering_mode=>'STOP')來中斷統計。

SQL> exec dbms_stats.gather_system_stats('interval',15);

SQL> exec dbms_stats.gather_system_stats('stop');

SQL>select pname,pval1 from sys.aux_stats$; --檢視統計結果

--START | STOP:開始和結束統計

--interval:指定統計的分鐘數，當上面的引數是INTERVAL時才用此引數

--no_invalidate:設定為TRUE時，相關的遊標不會失效;反之，遊標失效。

–statid –statown 同上

7. GATHER_TABLE_STATS

此過程統計table and column (and index)資訊。

引數：

Ownname：使用者名稱

Tabname：TABLE名

Partname：分割槽名Name of partition

--estimate_percent --block_sample --Method_opt –degree –granularity –cascade –stattab –statid –statown --no_invalidate --force都同上

第二部分：Setting or Getting Statistics

1. SET_COLUMN_STATS

設定列相關資訊

引數：

--ownname：使用者名稱

--tabname：TABLE名

--colname：列名

--partname：分割槽名

--stattab：指定存放使用者分析的資料的表名

--statid:stattab的ID,只有當stattab沒有指定時使用

--ext_stats:自定義的統計

--stattypown:統計型別的所屬使用者

--STATTYPNAME:統計型別的名稱

--distinct:有多少個值 (number of distinct values)

--density:列密度。如果此值是null且distinct不為空，那麼此值將參照distinct

--nullcnt：number of NULLS

--srec: StatRec structure filled in by a call to PREPARE_COLUMN_VALUES or GET_COLUMN_STATS

--avgclen:列的平均長度(in bytes)

--flags:ORACLE內部使用，應該為NULL

--statown: Schema containing stattab (if different than ownname)

--no_invalidate: 設定為TRUE時，相關的遊標不會失效;反之，遊標失效。

--force:被設定的column被鎖時也強行設定

2. SET_INDEX_STATS

設定索引相關資訊

3. SET_PARAM

此過程是用來設定DBMS_STATS的值的，可以用GET_PARAM函式來得到引數的當前值。

引數：

--pname:可以是CASCADE,DEGREE,EASTIMAT_PERCENT,METHOD_OPT,NO_INVALIDATE,GRANULARITY,AUTOSTATS_TARGET

--pval:引數的值。如果是NULL，則設成預設值。

To run this procedure, you must have the SYSDBA or both the ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges

舉例：

DBMS_STATS.SET_PARAM('CASCADE','DBMS_STATS.AUTO_CASCADE');

DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','5');

DBMS_STATS.SET_PARAM('DEGREE','NULL');

4. SET_SYSTEM_STATS

設定系統的統計

引數:

--pname:

--iotfrspeed:IO的傳輸速度/毫秒

-- ioseektim: seek time + latency time + operating system overhead time, in milliseconds

--sreadtim: average time to read single block (random read), in milliseconds

--mreadtim : average time to read an mbrc block at once (sequential read), in milliseconds

--cpuspeed : average number of CPU cycles for each second, in millions, captured for the workload (statistics collected using 'INTERVAL' or 'START' and 'STOP' options)

--cpuspeednw:average number of CPU cycles for each second, in millions, captured for the noworkload (statistics collected using 'NOWORKLOAD' option.

--mbrc:average multiblock read count for sequential read, in blocks

--maxthr:I/O的最大吞吐量，bytes/second

--slavethr: average slave I/O throughput, in bytes/second

--pvalue:引數的值

--stattab: 指定存放使用者分析的資料的表名

--statid:使用者ID

--statown：stattab指定表的所屬使用者

--cachedblk:段在buffer cache中的平均塊數

--cachehit：段的平均命中率

5. SET_TABLE_STATS

設定表的統計

引數：

--ownname,--tabname,--partname,--stattab,--statid同上

--numrows:表中有多少行

--numblks:表佔用了多少塊

--avgrlen：平均的行長度

--flags，--statown，--no_invalidate，--cachedblk,--cachehit,--force同上

6. PREPARE_COLUMN_VALUES

7. PREPARE_COLUMN_VALUES_NVARCHAR2

8. PREPARE_COLUMN_VALUES_ROWID

9. GET_COLUMN_STATS Procedures

取欄位的統計資訊

引數：

--ownname,--tabname,--colname, --partname,--stattab,--statid, --ext_stats, --stattypown, --STATTYPNAME, --distinct, --density, --nullcnt,--srec,--avgclen,--statown

10. GET_INDEX_STATS

取索引的統計資訊

引數

--ownname,--indname,--partname,--stattab,--statid,--ext_stats,--stattypown,--STATTYPNAME, --numrows, --numblks

--numdist: Number of distinct keys in the index (partition)

--avglblk: Average integral number of leaf blocks in which each distinct key appears for this index (partition)

--avgdblk: Average integral number of data blocks in the table pointed to by a distinct key for this index (partition)

--clstfct: Clustering factor for the index (partition)

--indlevel: Height of the index (partition)

--statown:stattab的使用者名稱

--guessq：估計INDEX的質量

--cachedblk:段在buffer cache中的平均塊數

--cachehit：段的平均命中率

11．GET_SYSTEM_STATS

此過程取系統的統計資訊

語法：

DBMS_STATS.GET_SYSTEM_STATS (

   status    OUT  VARCHAR2,

   dstart    OUT  DATE,

   dstop     OUT  DATE,

   pname          VARCHAR2,

   pvalue    OUT  NUMBER,

   stattab   IN   VARCHAR2 DEFAULT NULL, 

   statid    IN   VARCHAR2 DEFAULT NULL,

   statown   IN   VARCHAR2 DEFAULT NULL);

引數：

--Status:會輸出以下值之一 COMPLETED,AUTOGATHERING,MANUALGATHERING,BADSTATS

--dstart:收集分析開始的時間

--dstop:收集分析結束的時間

--pname:需要獲取值的引數，可選的值有：iotfrspeed，ioseektim，sreadtim,mreadtim,cpuspeed,cpuspeednw,mbrc,maxthr,slavethr

--pvalue:值

--stattab:統計表所表名，如果此值為null,則會從資料字典中取。

--statid:stattab的id

--statown:stattab的own

12.GET_TABLE_STATUS

取table相關的統計資訊

語法：

DBMS_STATS.GET_TABLE_STATS (

   ownname         VARCHAR2, 

   tabname          VARCHAR2, 

   partname         VARCHAR2 DEFAULT NULL,

   stattab           VARCHAR2 DEFAULT NULL,

   statid            VARCHAR2 DEFAULT NULL,

   numrows         OUT NUMBER, 

   numblks         OUT NUMBER,

   avgrlen         OUT NUMBER,

   statown         VARCHAR2 DEFAULT NULL,

   cachedblk      OUT NUMBER,

   cachehit       OUT NUMBER);

13．CREATE_STAT_TABLE

此過程建立stattab的TABLE，以儲存統計資訊。

語法：

DBMS_STATS.CREATE_STAT_TABLE (

ownname VARCHAR2,

stattab VARCHAR2,

tblspace VARCHAR2 DEFAULT NULL);

引數：

--ownname:使用者名稱

--stattab:需建立的stattab表名。

--tblspace:stattab的tablespace.如果為null則會使用使用者的預設表空間

14．DROP_STAT_TABLE

此過程刪除使用者定義的統計表

語法：

DBMS_STATS.DROP_STAT_TABLE (

   ownname VARCHAR2, 

   stattab VARCHAR2);

第三部分Deleting Statistics

1．DELETE_COLUMN_STATS

語法：

DBMS_STATS.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);

引數：

--ownname:table所屬使用者名稱

--tabname:表名

--colname:column

--partname:分割槽名

--stattab:統計表名，如果為null,則從資料字典中取

--statid:stattab的id(當stattab為null時才需指定)

--cascade_parts:如果指定的表是分割槽表且partname是null，此值為TRUE時會刪除所有指定欄位分割槽的統計資訊。

--statown:stattab的owner

--no_invalidate:如設為TRUE，相關的遊標不失效。

--force: When value of this argument is TRUE, deletes column statistics even if locked

2. DELETE_DATABASE_STATS

此過程刪除資料庫中所有TABLE的統計資訊

語法：

DBMS_STATS.DELETE_DATABASE_STATS (

   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);

3．DELETE_DICTIONARY_STATS

此過程刪除'SYS', 'SYSTEM' and RDBMS component schemas的統計資訊

語法：

                                        

                    

                                                    來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-983165/，如需轉載，請註明出處，否則將追究法律責任。