ORACLE收集統(tǒng)計(jì)信息

今天網(wǎng)上看到一篇關(guān)于收集統(tǒng)計(jì)信息的文章，還不錯，特轉(zhuǎn)載下來。

1. 理解什么是統(tǒng)計(jì)信息
優(yōu)化器統(tǒng)計(jì)信息就是一個(gè)更加詳細(xì)描述數(shù)據(jù)庫和數(shù)據(jù)庫對象的集合，這些統(tǒng)計(jì)信息被用于查詢優(yōu)化器，讓其為每條SQL語句選擇最佳的執(zhí)行計(jì)劃。優(yōu)化器統(tǒng)計(jì)信息包括:

·         表的統(tǒng)計(jì)信息
o   行數(shù)
o   Block數(shù)

o   行平均長度
·         列的統(tǒng)計(jì)信息
o   列中不同值的數(shù)量
o   列中null的數(shù)量

o 數(shù)據(jù)分布(柱狀圖/直方圖)

·         索引的統(tǒng)計(jì)信息
o   葉子塊的數(shù)量
o   索引的高度
o   聚簇因子(clustering factor)

· 系統(tǒng)的統(tǒng)計(jì)信息
o I/O性能和利用

o CPU性能和利用

優(yōu)化器統(tǒng)計(jì)信息存儲在下列數(shù)據(jù)字典中
· DBA_TABLES

· DBA_OBJECT_TABLES

· DBA_TAB_STATISTICS

· DBA_TAB_COL_STATISTICS

· DBA_TAB_HISTOGRAMS

· DBA_INDEXES

· DBA_IND_STATISTICS

· DBA_CLUSTERS

· DBA_TAB_PARTITIONS

· DBA_TAB_SUBPARTITIONS

· DBA_IND_PARTITIONS

· DBA_IND_SUBPARTITIONS

· DBA_PART_COL_STATISTICS

· DBA_PART_HISTOGRAMS

· DBA_SUBPART_COL_STATISTICS

· DBA_SUBPART_HISTOGRAMS

· INDEX_STATS 存儲ANALYZE ..VALIDATE STRUCTURE統(tǒng)計(jì)信息

· AUX_STATS$ 存儲CPU統(tǒng)計(jì)信息

· X$KCFIO 存儲I/O統(tǒng)計(jì)信息

因?yàn)閿?shù)據(jù)庫中的對象會經(jīng)常的變化，所以統(tǒng)計(jì)信息必須有規(guī)律的更新以便更加準(zhǔn)確的描述這些數(shù)據(jù)庫對象。統(tǒng)計(jì)信息默認(rèn)是由ORACLE自動維護(hù)的，不過我們也可以用DBMS_STATS包手動收集統(tǒng)計(jì)信息。DBMS_STATS包同樣提供了過程來維護(hù)統(tǒng)計(jì)信息。關(guān)于DBMS_STATS包更詳細(xì)的描述請參閱官方文檔PL/SQL Packages and Types Reference部分。
2. 自動收集統(tǒng)計(jì)信息
Oracle10g中，在安裝Oracle的時(shí)候，就默認(rèn)創(chuàng)建了一個(gè)名為GATHER_STATS_JOB的job來自動收集優(yōu)化器統(tǒng)計(jì)信息。這個(gè)job收集數(shù)據(jù)庫中所有對象的統(tǒng)計(jì)信息。默認(rèn)的情況下這個(gè)job是周一到周五每天晚上10點(diǎn)到第二天早上6點(diǎn)以及整個(gè)周末來收集統(tǒng)計(jì)信息。

可以查看DBA_SCHEDULER_JOBS， DBA_SCHEDULER_PROGRAMS,DBA_SCHEDULER_WINDOWS，DBA_SCHEDULER_JOB_RUN_DETAILS等視圖來查看JOB設(shè)置以及運(yùn)行信息。

自動收集過期的統(tǒng)計(jì)信息依賴于表監(jiān)控特征，在Oracle10g中表監(jiān)控默認(rèn)是開啟的，同時(shí)它也依賴STATISTICS_LEVEL參數(shù)的值，10g中默認(rèn)為typical,只有將STATISTICS_LEVEL參數(shù)設(shè)置為ALL或者TYPICAL才能讓ORACLE識別過期的統(tǒng)計(jì)信息。

3. 關(guān)閉自動收集統(tǒng)計(jì)信息
在某些情況下，我們想關(guān)閉自動收集統(tǒng)計(jì)信息那么我們可以利用如下方法:

BEGIN

DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

END;

4. 何時(shí)該手動收集統(tǒng)計(jì)信息
有時(shí)候自動收集統(tǒng)計(jì)并不合適，因?yàn)樽詣邮占y(tǒng)計(jì)信息是在午夜運(yùn)行的，然而由于對象是在白天被修改了，導(dǎo)致導(dǎo)致的統(tǒng)計(jì)信息變得陳舊，這里有2種這類對象:

· 白天經(jīng)常被delete,或者truncated之后又rebuild的表(經(jīng)常變化的表)

· 批量操作之后有10%或者以上的數(shù)據(jù)被更改的表(批量處理的表)

· 對于經(jīng)常變化的表，可以將其統(tǒng)計(jì)信息設(shè)置為null，當(dāng)ORACLE遇到一個(gè)表沒有統(tǒng)計(jì)信息，ORACLE會動態(tài)采樣以便為查詢優(yōu)化器收集必要的統(tǒng)計(jì)信息。動態(tài)采樣這個(gè)特征受到參數(shù)optimizer_dynamic_sampling的控制，它的默認(rèn)值為2，同時(shí)呢optimizer_mode也能控制動態(tài)采樣，可將其設(shè)置為all.

以SCOTT用戶下的DEPT表為例，將一個(gè)表的統(tǒng)計(jì)信息設(shè)置為null的方法如下：

BEGIN DBMS_STATS.DELETE_TABLE_STATS('SCOTT','DEPT'); DBMS_STATS.LOCK_TABLE_STATS('SCOTT','DEPT');END;/我們也可以在表具有典型的，代表性的時(shí)候收集統(tǒng)計(jì)信息，并且鎖住其統(tǒng)計(jì)信息，因?yàn)樵谝雇碜詣邮占慕y(tǒng)計(jì)信息未必適用于白天的負(fù)載，而典型的統(tǒng)計(jì)信息具有代表意義，所以這個(gè)時(shí)候采取lock其典型的統(tǒng)計(jì)信息更能讓CBO選擇更優(yōu)的執(zhí)行計(jì)劃。

至于上面的兩種方法用哪種，這個(gè)還需要根據(jù)業(yè)務(wù)，實(shí)際情況分析之。
· 對于批量處理的表，應(yīng)該在批量處理完成的時(shí)候立即對其收集統(tǒng)計(jì)信息，可以將收集統(tǒng)計(jì)信息的腳本綁定到批量處理的腳本中。
· 對于外部表，只能通過gather_table_stats過程來收集統(tǒng)計(jì)信息，并且外部表不支持取樣，所以需要把gather_table_stats中的estimate_percent設(shè)置為null。

· 系統(tǒng)的統(tǒng)計(jì)信息也需要手動收集，因?yàn)檫@些信息是不會自動收集的。
· 對于固定對象，比如說動態(tài)性能表，需要手動的執(zhí)行g(shù)ather_fixed_objects_stats過程來收集。固定的對象反映了當(dāng)前數(shù)據(jù)庫的活動。當(dāng)數(shù)據(jù)庫活動處于具有代表性的時(shí)候，就應(yīng)該收集這類統(tǒng)計(jì)信息。

5. 鎖住/解鎖統(tǒng)計(jì)信息

· LOCK_SCHEMA_STATS

· LOCK_TABLE_STATS

· UNLOCK_SCHEMA_STATS

· UNLOCK_TABLE_STATS

6. 手動收集統(tǒng)計(jì)信息
· 如果你選擇手動收集統(tǒng)計(jì)信息，那么你需要手動的收集所有用戶的統(tǒng)計(jì)信息，包括系統(tǒng)用戶。如果你數(shù)據(jù)庫中的數(shù)據(jù)是有規(guī)律的變化的，那么你可以有規(guī)律的收集統(tǒng)計(jì)信息，以便統(tǒng)計(jì)信息能夠準(zhǔn)確的反映數(shù)據(jù)庫中的對象的特征。
· 可以利用DBMS_STATS包，來收集表，索引，列，以及分區(qū)表的統(tǒng)計(jì)信息，DBMS_STATS不能收集CLUSTER 的統(tǒng)計(jì)信息，不過可以收集單個(gè)表來代替收集整個(gè)CLUSTER的統(tǒng)計(jì)信息。

· 當(dāng)你收集表，列，索引的統(tǒng)計(jì)信息的時(shí)候，如果ORACLE在數(shù)據(jù)字典中發(fā)現(xiàn)這個(gè)對象已經(jīng)收集了統(tǒng)計(jì)信息，那么ORACLE會更新已經(jīng)存在的統(tǒng)計(jì)信息，舊的統(tǒng)計(jì)信息會被保存下來，如果你愿意還能還原舊的統(tǒng)計(jì)信息。

· 你可以使用DBMS_STATS.GATHER_DICTIONARY_STATS來收集系統(tǒng)用戶的統(tǒng)計(jì)信息，這個(gè)過程收集所有的系統(tǒng)用戶的統(tǒng)計(jì)信息，包括SYS和SYSTEM，以及其他用戶，比如CTXSYS,DRSYS。

· 當(dāng)數(shù)據(jù)庫對象的統(tǒng)計(jì)信息被更新之后，ORACLE會使已經(jīng)解析的SQL語句作廢，當(dāng)再次運(yùn)行該SQL語句的時(shí)候，ORACLE會重新解析該SQL，優(yōu)化器會自動的根據(jù)新的統(tǒng)計(jì)信息選擇一條新的執(zhí)行計(jì)劃。對于分布式的數(shù)據(jù)庫，不會作廢。

· 收集統(tǒng)計(jì)信息的過程
o GATHER_INDEX_STATS --收集索引統(tǒng)計(jì)信息

o GATHER_TABLE_STATS --收集表，列，索引統(tǒng)計(jì)信息

o GATHER_SCHEMA_STATS --收集schema所有對象統(tǒng)計(jì)信息

o GATHER_DICTIONARY_STATS –-收集所有系統(tǒng)用戶的統(tǒng)計(jì)信息

o GATHER_DATABASE_STATS --收集數(shù)據(jù)庫所有對象統(tǒng)計(jì)信息

·         我們利用上面的過程收集統(tǒng)計(jì)信息的時(shí)候有幾個(gè)需要關(guān)心的參數(shù)
o   采樣
o   并行
o   分區(qū)
o   列統(tǒng)計(jì)以及直方圖/柱狀圖
o   過期的統(tǒng)計(jì)
o   自定義統(tǒng)計(jì)
Ø 在收集統(tǒng)計(jì)信息的操作過程中我們可以使用采樣來評估統(tǒng)計(jì)信息。采樣對于收集統(tǒng)計(jì)信息來說是一項(xiàng)很重要的技術(shù)。如果在收集統(tǒng)計(jì)信息的時(shí)候不使用采樣，那么就需要對表進(jìn)行全表掃描，以及排序整個(gè)表。通過采樣可以降低收集必要的統(tǒng)計(jì)信息所花費(fèi)的資源。
控制采樣的參數(shù)是ESTIMATE_PERCENT，采樣的參數(shù)可以設(shè)置任意值(當(dāng)然要在范圍內(nèi))，不過ORACLE公司推薦設(shè)置ESTIMATE_PERCENT為DBMS_STATS.AUTO_SAMPLE_SIZE。

AUTO_SAMPLE_SILE可以讓ORACLE自己決定最好的采樣值，因?yàn)椴煌愋?table,index,column)的統(tǒng)計(jì)信息有不同的需求。采樣的例子：

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SCOTT’,DBMS_STATS.AUTO_SAMPLE_SIZE);

當(dāng)ESTIMATE_PERCENT參數(shù)是手動指定的，如果手動指定的參數(shù)過小，不能收集到足夠的信息，那么DBMS_STATS可能會自動增長ESTIMATE_PERCENT的值，這樣就能確保收集到足夠的統(tǒng)計(jì)信息。

Ø 我們既可以串行的收集統(tǒng)計(jì)信息，也可以并行的收集統(tǒng)計(jì)信息。參數(shù)DEGREE控制DBMS_STATS是否使用并行特征。ORACLE公司推薦將DEGREE參數(shù)設(shè)置為DBMS_STATS.AUTO_DEGREE。這樣設(shè)置過后，ORACLE就能夠根據(jù)OBJECT的SIZE，以及與并行有關(guān)的init參數(shù)來決定一個(gè)恰當(dāng)?shù)牟⑿卸?，收集統(tǒng)計(jì)信息。注意:cluster index,domain index,bitmap join index不能使用并行特征。

Ø 對于分區(qū)表和分區(qū)索引，DBMS_STATS既可以單獨(dú)的收集分區(qū)統(tǒng)計(jì)信息，也可以收集整個(gè)表/索引的統(tǒng)計(jì)信息。對于組合分區(qū)，DBMS_STATS也能夠收集子分區(qū)，分區(qū)，以及整個(gè)表/索引的統(tǒng)計(jì)信息。參數(shù)GRANULARITY控制分區(qū)統(tǒng)計(jì)信息的收集。因?yàn)榉謪^(qū)統(tǒng)計(jì)信息，全局統(tǒng)計(jì)信息對于大多數(shù)系統(tǒng)來說都是非常重要的，所以O(shè)RACLE公司推薦將其設(shè)置為AUTO來收集分區(qū)，以及全局的統(tǒng)計(jì)信息。

Ø 當(dāng)對表收集統(tǒng)計(jì)信息的時(shí)候，DBMS_STATS會收集列的數(shù)據(jù)分布信息。數(shù)據(jù)分布最基本的統(tǒng)計(jì)信息就是這個(gè)列的最大值與最小值。如果這一列是傾斜的，那么優(yōu)化器僅僅根據(jù)列最大值與最小值是無法制定出準(zhǔn)確的執(zhí)行計(jì)劃的。對于傾斜的數(shù)據(jù)分布，我們可以收集列的直方圖/柱狀圖統(tǒng)計(jì)信息，這樣可以讓優(yōu)化器制定出更加準(zhǔn)確的執(zhí)行計(jì)劃。

參數(shù)METHOD_OPT控制柱狀圖的收集。ORACLE公司推薦設(shè)置METHOD_OPT為FOR ALL COLUMNS SIZE AUTO。這樣設(shè)置過后ORACLE會自動的判斷哪一列需要收集柱狀圖，并且自動的設(shè)置柱狀圖的bucket。你同樣可以手動的設(shè)置哪一列需要收集柱狀圖，以及柱狀圖的bucket。

Ø 為了知道統(tǒng)計(jì)信息是否過期，ORACLE提供了表監(jiān)控功能。將init參數(shù)STATISTICS_LEVEL設(shè)置為ALL或者TYPICAL(默認(rèn))，就開啟了表監(jiān)控的功能(10g已經(jīng)不需要alter table monitor了)。表監(jiān)控功能跟蹤表的insert,update,delete，truncate，操作，并且記錄在DBA_TAB_MODIFICATIONS視圖里面。我們在查詢DBA_TAB_MODIFICATIONS視圖的時(shí)候有可能查詢不到結(jié)果，或者查詢的結(jié)果不準(zhǔn)確，這個(gè)時(shí)候需要用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO過程將內(nèi)存中的信息刷新到

該視圖中。OPTIONS參數(shù)設(shè)置為GATHER STALE或者GATHER AUTO，就會讓DBMS_STATS判斷表的統(tǒng)計(jì)信息是否過期(注意GATHER_TABLE_STATS中沒有這個(gè)參數(shù)，只有GATHER_DATABASE_STATS,GATHER_SCHEMA_STATS過程中有這個(gè)參數(shù))。判斷表的統(tǒng)計(jì)信息是否過期的依據(jù)是是否有10%以上的數(shù)據(jù)被修改過，如果被修改過了，那么ORACLE就認(rèn)為之前的統(tǒng)計(jì)信息過期了，ORACLE會重新收集統(tǒng)計(jì)信息。

Ø 在我們創(chuàng)建了函數(shù)索引之后，我們要為列收集統(tǒng)計(jì)信息，這個(gè)時(shí)候我們需要設(shè)置參數(shù)METHOD_OPT為FOR ALL HIDDEN COLUMNS。

7. 收集統(tǒng)計(jì)信息的策略
通常情況下，我們會將ORACLE自動收集統(tǒng)計(jì)信息功能給關(guān)閉，我們會采用手動的方式給數(shù)據(jù)庫收集統(tǒng)計(jì)信息。至于收集統(tǒng)計(jì)信息的策略需要根據(jù)系統(tǒng)來確定。下面說說幾種常見的情況：

· 如果你系統(tǒng)中的表的數(shù)據(jù)是增量(有規(guī)律)的增加，也就是說你幾乎不做任何的批量處理操作，比如批量刪除，批量加載操作。對于這樣的表收集統(tǒng)計(jì)信息是非常簡單的。你可以通過查看DBA_TAB_MODIFICATIONS視圖來觀察表的變化情況，觀察表中數(shù)據(jù)量的變化是否超過了10%,并且記錄下天數(shù)。這樣你就可以每隔這樣的時(shí)間間隔對其收集一次統(tǒng)計(jì)信息。你可以用CRONTAB,或者JOB調(diào)用GATHER_SCHEMA_STATS或者GATHER_TABLE_STATS過程來收集統(tǒng)計(jì)信息。

·         對于經(jīng)常批量操作的表，那么表的統(tǒng)計(jì)信息就必須在批量操作之后對其收集統(tǒng)計(jì)信息。
·         對于分區(qū)表，通常只有一個(gè)分區(qū)被修改，這種情況下可以只收集單獨(dú)分區(qū)的統(tǒng)計(jì)信息，不過收集整個(gè)表的統(tǒng)計(jì)信息還是非常有必要的。
·         最后我會給出兩個(gè)腳本，判斷該表是否需要收集統(tǒng)計(jì)信息。
8. 收集統(tǒng)計(jì)信息的一些例子
例子1對表收集統(tǒng)計(jì)信息

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
                                 tabname => 'DEPT',
                                 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                 method_opt => 'for all columns size repeat',
                                 degree => DBMS_STATS.AUTO_DEGREE,
                                 cascade=>TRUE
                                 );
END;
/

上面的例子收集SCOTT.DEPT表的統(tǒng)計(jì)信息。這里面值得關(guān)注的一個(gè)參數(shù)就是method_opt。這個(gè)參數(shù)控制是否收集列的直方圖信息。通常情況下，是不會收集直方圖的，關(guān)于直方圖不是三言兩語可以說明白的。它的四個(gè)選項(xiàng)method_opt=>'for all columns size skewonly'

ORACLE會根據(jù)數(shù)據(jù)分布收集直方圖
method_opt=>'for all columns size repeat'

只有以前收集過直方圖，才會收集直方圖信息，所以一般我們會設(shè)置method_opt 為repeat
method_opt=>'for all columns size auto'

ORACLE會根據(jù)數(shù)據(jù)分布以及列的workload來確定是否收集直方圖
method_opt=>'for all columns size interger'

我們自己指定一個(gè)bucket值
例子2對某一個(gè)schma收集統(tǒng)計(jì)信息
BEGIN
   DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT',
                                  estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
                                  ptions => 'gather auto',
                                  degree => DBMS_STATS.AUTO_DEGREE,
                                  method_opt => 'for all columns size repeat',
                                  cascade => TRUE
                                 );
END;
/

上面的例子收集SCOTT模式下所有對象的統(tǒng)計(jì)信息。里面值得注意的一個(gè)參數(shù)就是options。前面已經(jīng)講到過，他與表監(jiān)控有關(guān)。它有四個(gè)選項(xiàng)
Options =>’gather’       收集所有對象的統(tǒng)計(jì)信息
Options =>’gather empty’ 只收集還沒被統(tǒng)計(jì)的表
Options =>’gather stale’ 只收集修改量超過10%的表
Options =>’gather auto’ 相當(dāng)于empty+stale ，所以我們一般設(shè)置為AUTO。
例子3 對一個(gè)分區(qū)表收集統(tǒng)計(jì)信息
BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
                                 tabname => 'P_TEST',
                                 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                 method_opt => 'for all columns size repeat',
                                 degree => DBMS_STATS.AUTO_DEGREE,

                                 granularity => 'ALL',
                                 cascade=>TRUE
                                 );
END;
/

上面的例子收集ROBINSON.P_TEST表的統(tǒng)計(jì)信息。里面值得注意的一個(gè)參數(shù)就是granularity，他有7個(gè)選項(xiàng)。
granularity => 'ALL' 收集分區(qū)，子分區(qū)，全局的統(tǒng)計(jì)信息
granularity => 'AUTO' 這個(gè)是默認(rèn)的設(shè)置，ORACLE會根據(jù)分區(qū)類型來決定用ALL,GLOBAL AND PARTITION ,還是其他的
granularity => 'DEFAULT' 這個(gè)是過期了的
granularity => 'GLOBAL' 收集全局統(tǒng)計(jì)信息
granularity => 'GLOBAL AND PARTITION' 收集全局，分區(qū)統(tǒng)計(jì)信息，但是不收集子分區(qū)統(tǒng)計(jì)信息
granularity => 'PARTITION' 收集分區(qū)統(tǒng)計(jì)信息
granularity => 'SUBPARTITION' 收集子分區(qū)統(tǒng)計(jì)信息
當(dāng)然我們可以指定partname，自己控制對哪個(gè)分區(qū)收集統(tǒng)計(jì)信息
9. 列出表需要收集統(tǒng)計(jì)信息的腳本
普通表
set serveroutput on

declare

-----select OVER THE Change RATE TABLES---------------

cursor overchangerate is

select a.table_owner, a.table_name, a.inserts,a.updates,a.deletes ,b.num_rows

from dba_tab_modifications a, dba_tables b

where a.table_name = b.table_name

and table_owner not in

('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',

'OUTLN', 'TSMSYS', 'MDSYS')

and inserts > 0 and partitioned='NO' and a.inserts/decode(b.num_rows,0,1,b.num_rows)>=0.1

or a.table_name = b.table_name

and table_owner not in

('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',

'OUTLN', 'TSMSYS', 'MDSYS')

and updates > 0 and partitioned='NO' and a.updates/decode(b.num_rows,0,1,b.num_rows)>=0.1 or

a.table_name = b.table_name

and table_owner not in

('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',

'OUTLN', 'TSMSYS', 'MDSYS')

and deletes > 0 and partitioned='NO' and a.deletes/decode(b.num_rows,0,1,b.num_rows)>=0.1 ;

----select the unanalyzed table---------------

cursor nullmonitor is

select owner, table_name

from dba_tables

where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS',

'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP',

'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS')

and last_analyzed is null;

begin

dbms_output.enable(1000000);

----flush the monitorring information into the dba_tab_modifications

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

----display the unanalyzed table--------------

dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');

dbms_output.put_line('Unalalyzed tables:');

for v_null in nullmonitor loop

dbms_output.put_line(v_null.owner || '.' || v_null.table_name ||

' has not been analyzed, consider gathering statistics');

end loop;

----display the information-------------------

dbms_output.put_line('Over the Change_Rate 10%:');

for v_topinsert in overchangerate loop

dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' once has ' || v_topinsert.num_rows || ' rows, ' ||

'till now inserted ' || v_topinsert.inserts || ' rows, updated ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes ||

' rows. consider gathering statistics');

end loop;

end;

下面的是分區(qū)表
set serveroutput on

declare

-----select OVER THE Change RATE TABLES---------------

cursor overchangerate is

select a.table_owner,a.table_name,a.partition_name,sum(a.inserts) inserts,sum(a.updates) updates,sum(a.deletes) deletes,sum(b.num_rows) num_rows

from dba_tab_modifications a,dba_tab_partitions b where a.table_owner =b.table_owner and a.table_name=b.table_name

and a.partition_name=b.partition_name and a.table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS','OUTLN', 'TSMSYS', 'MDSYS')

group by a.table_owner,a.table_name,a.partition_name

having (sum(a.inserts)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1

(sum(a.updates)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1

(sum(a.deletes)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1

order by a.table_name;

begin

dbms_output.enable(1000000);

----flush the monitorring information into the dba_tab_modifications

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

----display the top_n_insert information-------------------

dbms_output.put_line('Over the Change_Rate 10%:');

for v_topinsert in overchangerate loop

dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' partition ' || v_topinsert.partition_name || ' once has ' || v_topinsert.num_rows || ' rows, ' ||

'till now inserted ' || v_topinsert.inserts || ' rows, updated ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes ||

' rows. consider gathering statistics');

end loop;

end;

在此特別聲明一點(diǎn)，在oracle11.2版本中有一個(gè)相關(guān)的BUG

Bug 9272549 - User statistics are ignored when dynamic sampling occurs 9272549.8

該BUG會導(dǎo)致嚴(yán)重的性能問題。

oracle官方申明，只有在12.1版本才解決這個(gè)問題，臨時(shí)解決方案是手動關(guān)閉動態(tài)采樣。

順便貼上10個(gè)level的動態(tài)采樣介紹

Level 0: Do not use dynamic sampling.

Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).

Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.

Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.

Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

分享名稱：ORACLE收集統(tǒng)計(jì)信息
網(wǎng)頁路徑：http://bm7419.com/article20/pcdojo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站策劃、網(wǎng)站設(shè)計(jì)公司、營銷型網(wǎng)站建設(shè)、全網(wǎng)營銷推廣、網(wǎng)站導(dǎo)航、域名注冊

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容