當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Oracle优化07-分析及动态采样-DBMS_STATS 包

發布時間：2025/3/21 编程问答 29 豆豆

生活随笔收集整理的這篇文章主要介紹了 Oracle优化07-分析及动态采样-DBMS_STATS 包小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

思維導圖

07系列文章

Oracle優化07-分析及動態采樣-直方圖

Oracle優化07-分析及動態采樣-DBMS_STATS 包

Oracle優化07-分析及動態采樣-動態采樣

DBMS_STATS包

DBMS_STAS 包不僅能夠對表進行分析，它還可以對數據庫分析進行管理。
按照功能可以分一下幾類：
（ 1）性能數據的收集
（ 2）性能數據的設置
（ 3）性能數據的刪除
（ 4）性能數據的備份和恢

11G 官方文檔：

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS059

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

GATHER_DATABASE_STATS Procedures
GATHER_DICTIONARY_STATS Procedure
GATHER_FIXED_OBJECTS_STATS Procedure
GATHER_INDEX_STATS Procedure
GATHER_SCHEMA_STATS Procedures
GATHER_SYSTEM_STATS Procedures
GATHER_TABLE_STATS Procedure

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

GATHER_TABLE_STATS 收集信息功能

我們分析時最常用到的就是GATHER_TABLE_STATS 了。

在11gR2版本中dbms_stats包下的 gather_table_stats存過定義如下

我們可以在注釋中看到 This procedure gathers table and column (and index) statistics.

procedure gather_table_stats(ownname varchar2, tabname varchar2, partname varchar2 default null,estimate_percent number default DEFAULT_ESTIMATE_PERCENT,block_sample boolean default FALSE,method_opt varchar2 default DEFAULT_METHOD_OPT,degree number default to_degree_type(get_param('DEGREE')),granularity varchar2 default DEFAULT_GRANULARITY,cascade boolean default DEFAULT_CASCADE,stattab varchar2 default null, statid varchar2 default null,statown varchar2 default null,no_invalidate boolean defaultto_no_invalidate_type(get_param('NO_INVALIDATE')),stattype varchar2 default 'DATA',force boolean default FALSE,-- the context is intended for internal use only. context dbms_stats.CContext default null);-- -- This procedure gathers table and column (and index) statistics. -- It attempts to parallelize as much of the work as possible, but there -- are some restrictions as described in the individual parameters. -- This operation will not parallelize if the user does not have select -- privilege on the table being analyzed. -- -- Input arguments: -- 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 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 - method options of the following format -- -- method_opt := FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] -- FOR COLUMNS [size_clause] -- column|attribute [size_clause] -- [,column|attribute [size_clause] ... ] -- -- size_clause := SIZE [integer | auto | skewonly | repeat], -- where integer is between 1 and 254 -- -- column := column name | extension name | extension -- -- default is FOR ALL COLUMNS SIZE AUTO. -- The default value can be changed using set_param procedure. -- Optimizer related table statistics are always gathered. -- -- If an extension is provided, the procedure create the extension if it -- does not exist already. Please refer to create_extended_stats for -- description of extension. -- -- degree - degree of parallelism (NULL means use of table default value -- which was specified by DEGREE clause in CREATE/ALTER TABLE statement) -- Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value -- based on the initialization parameters. -- default for degree is NULL. -- The default value can be changed using set_param procedure. -- granularity - the granularity of statistics to collect (only pertinent -- if the table is partitioned) -- 'AUTO' - the procedure determines what level of statistics to collect -- 'GLOBAL AND PARTITION' - gather global- and partition-level statistics -- 'APPROX_GLOBAL AND PARTITION' - This option is similar to -- 'GLOBAL AND PARTITION'. But the global statistics are aggregated -- from partition level statistics. It will aggregate all statistics except number of -- distinct values for columns and number of distinct keys of indexes. -- The existing histograms of the columns at the table level -- are also aggregated.The global statistics are gathered -- (i.e., going back to GLOBAL AND PARTITION behaviour) -- if partname argument is null. The aggregation will use only -- partitions with statistics, so to get accurate global statistics, -- user has to make sure to have statistics for all partitions. -- -- -- This option is useful when you collect statistics for a new partition added -- into a range partitioned table (for example, a table partitioned by month). -- The new data in the partition makes the global statistics stale (especially -- the min/max values of the partitioning column). This stale global statistics -- may cause suboptimal plans. In this scenario, users can collect statistics -- for the newly added partition with 'APPROX_GLOBAL AND PARTITION' -- option so that the global statistics will reflect the newly added range. -- This option will take less time than 'GLOBAL AND PARTITION' option since the -- global statistics are aggregated from underlying partition level statistics. -- Note that, if you are using APPROX_GLOBAL AND PARTITION, -- you still need to collect global statistics (with granularity = 'GLOBAL' option) -- when there is substantial amount of change at the table level. -- For example you added 10% more data to the table. This is needed to get the -- correct number of distinct values/keys statistic at table level. -- 'SUBPARTITION' - gather subpartition-level statistics -- 'PARTITION' - gather partition-level statistics -- 'GLOBAL' - gather global statistics -- 'ALL' - gather all (subpartition, partition, and global) statistics -- default for granularity is AUTO. -- The default value can be changed using set_param procedure. -- cascade - gather statistics on the indexes for this table. -- Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine -- whether index stats to be collected or not. This is the default. -- The default value can be changed using set_param procedure. -- Using this option is equivalent to running the gather_index_stats -- procedure on each of the table's indexes. -- stattab - The user stat table identifier describing where to save -- the current statistics. -- statid - The (optional) identifier to associate with these statistics -- within stattab. -- 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. -- When the 'cascade' argument is specified, not pertinent with certain -- types of indexes described in the gather_index_stats section. -- force - gather statistics of table even if it is locked. -- context - internal use only. -- -- Exceptions: -- ORA-20000: Table does not exist or insufficient privileges -- ORA-20001: Bad input value -- ORA-20002: Bad user statistics table, may need to upgrade it -- ORA-20005: object statistics are locked -- OWNNAME: 要分析表的擁有者。 TABNAME: 要分析的表名。 PARTNAME: 分區的名字,只對分區表或分區索引有用。 ESTIMATE_PERCENT: 采樣行的百分比,取值范圍[0.000001,100]，使用常DBMS_STATS.AUTO_SAMPLE_SIZE讓ORACLE決定適合的采樣大小，這也是默認值，可以使用DBMS_STATS.SET_PARAM進行修改默認值。NULL可以讓ORACLE采樣全部數據 BLOCK_SAMPLE: 是否采用隨即塊采樣代替行隨即行采樣。 METHOD_OPT: 決定HISTOGRAMS信息是怎樣被統計的.METHOD_OPT的取值如下: FOR ALL COLUMNS:統計所有列的HISTOGRAMS. FOR ALL INDEXED COLUMNS:統計所有INDEXED列的HISTOGRAMS. FOR ALL HIDDEN COLUMNS:統計你看不到列的HISTOGRAMS FOR COLUMNS <LIST> SIZE <INTEGER> | REPEAT | AUTO | INTEGER指的直方圖的BUCKETS數量，取值范圍為[1,254]。 REPEAT上次統計過的HISTOGRAMS。 AUTO:ORACLE根據列數據的分布及相關列的訪問量來決定收集直方圖的列。 SKEWONLY:ORACLE 根據列的數據分布來決定哪些列收集直方圖。 DEGREE: 并行度 GRANULARITY: 收集統計信息的粒度。(只應用于分區表),值包括: 'ALL':搜集(SUBPARTITION,PARTITION,AND GLOBAL)統計信息。 'AUTO':基于分區的類型來決定粒度，默認值。 'DEFAULT':收集GLOBAL和PARTITION LEVEL的統計信息，等同與'GLOBAL AND PARTITION'。 'GLOBAL':收集全局統計信息 'GLOBAL AND PARTITION':收集GLOBAL和PARTITION LEVEL統計信息。 'GPARTITION':收集PARTITION-LEVEL的統計信息。 'SUBPARTITION':收集SUBPARTITION-LEVEL統計信息。 CASCADE: 收集索引的統計信息。是收集索引的信息.默認為FALSE. STATTAB : 設置存儲統計信息的表 STATID : 表的ID STATOWN ：表的OWNER NO_INVALIDATE: 若是設置為TRUE,則Oracle不會立馬使 dependent Cursors失效,若設置為FALSE,Oracle則會立馬使dependent Cursor失效. FORCE: 即使鎖住也要收集表的統計信息

gather_table_STATS使用

在 gather_table_stats 存儲過程的所有參數中，除了 ownname 和 tabname，其他的參數都有默認值。

所以我們在調用這個存儲過程時， Oracle 會使用參數的默認值對表進行分析。

如：

SQL> exec dbms_stats.gather_table_STATS(user,'T');PL/SQL procedure successfully completed

oracel就會對當前用戶下的T表按照所有參數的默認值進行分析，其中user是一個變量，用來返回當前的用戶信息。

當然你也可以指定用戶名,比如：

SQL> exec dbms_stats.gather_table_STATS('cc','xgj');PL/SQL procedure successfully completed

對cc用戶下的xgj表進行分析。

查看gather_table_STATS參數當前的默認值

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

比如查看method_opt的默認值：

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

參數說明

參數的說明：
estimate_percent 參數

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

理論上來講，采樣的數據越多，得到的信息就越接近于實際， CBO做出的執行計劃就越優化，但是，采樣越多，消耗的系統資源必然越多。對系統的影響也越大。

所以對于這個值的設置，要根據業務情況來。

如果數據的直方圖分布比較均勻，就可以使用默認值： AUTO_SAMPLE_SIZE，即讓 Oracle 自己來判斷采樣的比例。

有時，特別是對于批量加載的表，我們可以預估表中的數據量，可以人工地設置一個合理的值。一般，對于一個有 1000 萬數據的表分區，可以把這個參數設置為 0.000001.

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.

degree 參數

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

(1) Null：如果設置為 null， Oracle 將使用被分析表屬性的并行度，比如表在創建時指定的并行度，或者后者使用 alter table 重新設置的并行度。
(2) 一個數值：可以顯示地指定分析時使用的并行度。
(3) Default_degree: 如果設置為 default， Oracle 將根據初始化參數中相關參數的設置來決定使用的并行度。

這個參數的默認值是 Null.即通過表上的并行度屬性來決定分析使用的并行度。

當需要分析的表或表分區非常大，并且系統資源比較充分的時候，就可以考慮使用并行的方式來做分析，這樣就會大大提高分析的速度。相反，如果你的系統資源比較吃緊，那么啟用并行可能會適得其反。

no_invalidate

no_invalidate：缺省值是DBMS_STATS.AUTO_INVALIDATE.
如果想要dbms_stats分析立馬見效,需要使用no_invalidate=false option或者DBA自己手工invalidate游標。

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依據數據的分布情況進行考慮，關鍵是視數據的傾斜程度而定。

GATHER_SCHEMA_STATS

這個存儲過程用于對某個用戶下所有的對象進行分析。如果你的數據用戶對象非常多，單獨對每個對象進行分析設定會非常不方便，這個存儲過程就很方便。

它的好處在于如果需要分析的對象非常多，將可以大大降低 DBA 的工作量，不足之處是所有分析使用相同的分析策略，可能會導致分析不是最優。所以要根據實際情況來決定。

GATHER_INDEX_STATS

該存儲過程用于對索引的分析，如果我們在使用
DBMS_STATS.GATHER_TABLES_STATS 的分析時設置參數 cascade=>true。

那么 Oracle 會同時執行這個存儲過程來對索引進行分析。

DBMS_STATS 包管理功能

獲取分析數據

GET_COLUMN_STATS Procedures
GET_INDEX_STATS Procedures
GET_SYSTEM_STATS Procedure
GET_TABLE_STATS Procedure

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

設置分析數據

SET_COLUMN_STATS Procedures
SET_INDEX_STATS Procedures
SET_SYSTEM_STATS Procedure
SET_TABLE_STATS Procedure

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

SET_TABLE_STATS 實例演示：

SQL> select count(1) from t; COUNT(1) ---------- 33872SQL> exec dbms_stats.set_table_stats(user,'t',numrows => 100000);PL/SQL procedure successfully completed##執行sql SQL>select * from t; .....省略輸出 SQL> select a.SQL_ID ,a.CHILD_NUMBER,a.SQL_TEXT from v$sql a where a.SQL_TEXT like 'select * from t ';SQL_ID CHILD_NUMBER SQL_TEXT ------------- ------------ -------------------------------------------------------------------------------- ckzaraqcmkr2f 0 select * from t SQL> select * from table(dbms_xplan.display_cursor('ckzaraqcmkr2f',0));PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID ckzaraqcmkr2f, child number 0 ------------------------------------- select * from t Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | TABLE ACCESS FULL| T | 100K| 390K| 18 (6)| 00:00:01 | --------------------------------------------------------------------------13 rows selectedSQL>

可以看到，通過這種方式，我們輕松地改變了T表的統計信息，T表中本來有3W多條記錄，通過dbms_stats.set_table_stats將表的記錄更改為100000條。這樣，在執行計劃中，CBO得到表上的記錄數為100000條，銅鼓哦這種方式可以非常容易的模擬出我們希望的各種性能數據

刪除分析數據

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 重新對表做動態采樣分析，得到一個正確的結果。

它可以刪除字段，數據庫，數據字典，基表，索引，表等級別的性能數據。另外這也為我們模擬各種測試環境提供了一種便捷的方式

SQL> exec dbms_stats.delete_table_stats(user,'t');PL/SQL procedure successfully completedSQL>

保存分析數據

CREATE_STAT_TABLE Procedure
DROP_STAT_TABLE Procedure

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

導入和導出分析數據

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

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

鎖定分析數據

LOCK_SCHEMA_STATS Procedure
LOCK_TABLE_STATS Procedure

UNLOCK_SCHEMA_STATS Procedure
UNLOCK_TABLE_STATS Procedure

可能在某些時候，我們覺得當前的統計信息非常好，執行計劃很準確，并且表中數據幾乎不變化，那么可以使用 LOCK_TABLE_STATS Procedure 來鎖定表的統計信息，不允許對表做分析或者設定分析數據。

當表的分析數據被鎖定之后，相關的所有分析數據，包括表級，列級，直方圖，索引的分析數據都將被鎖定，不允許被更新。

分析數據的恢復

RESET_PARAM_DEFAULTS Procedure
RESTORE_DICTIONARY_STATS Procedure
RESTORE_FIXED_OBJECTS_STATS Procedure
RESTORE_SCHEMA_STATS Procedure
RESTORE_SYSTEM_STATS Procedure
RESTORE_TABLE_STATS Procedure

比如我們重新分析了表，發現分析的數據導致了 CBO 選擇了錯誤的執行計劃，為了挽救這種局面，可以將統計信息恢復到從前的那個時間點，也就是 CBO 執行計劃正確的時間點，先解決這個問題，再來分析問題的原因。

SQL> select dbms_stats.get_stats_history_availability from dual;GET_STATS_HISTORY_AVAILABILITY ------------------------------------------ 08-DEC-16 11.13.35.798721000 PM +08:00SQL>

單表分析：分區表：BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'cc',tabname => 't',estimate_percent => 100,no_invalidate => FALSE,--立馬生效degree => 8,granularity => 'ALL', --分區cascade => TRUE);END;/非分區表：BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'cc',tabname => 't',estimate_percent => 100,no_invalidate => FALSE,--立馬生效degree => 8,cascade => TRUE);END;

總結

以上是生活随笔為你收集整理的Oracle优化07-分析及动态采样-DBMS_STATS 包的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： Oracle优化08-并行执行
下一篇： Oracle优化07-分析及动态采样-动