深入理解oracle優化器統計資料(Optimizer Statistics)

                               理解oracle優化器統計資料

首先來介紹oracle資料庫使用基於規則優化器(RBO)來決定如何執行一個sql語句.基於規則優化器顧名思義,它是遵循一組規則來判斷一個sql語句的執行計劃.這組規則是有排名的如果有兩種可能的規則應該應用於一個sql語句,那麼排名較低的規則會被使用

在oracle 7中,引入了基於成本的優化器使用優化器的功能得以增強包括並行執行,分割槽,還會考慮使用者真實資料的內容和分佈情況.基於成本的優化器會檢查一個sql語句的所有執行計劃並選擇一個成本值最低的,這裡的成本代表一個指定執行計劃所要消耗的資源使用情況.一個執行計劃成本值越低該執行計劃越有效.為了讓基於成本的優化器精確的判斷一個執行計劃的成本它必需有描述該語句所訪問的所有物件(表和索引)的資訊和描述執行這個語句的系統資訊.這些需要的資訊通常被稱為優化器統計資料.理解和管理優化器統計資料是優化sql執行的關鍵.知道何時以及及時的怎樣收集統計資料對於維護一個穩定的效能來說很關鍵.優化器統計資料包括以下方面的資訊:

什麼是優化器統計資料

收集統計資料

管理統計資料

其它型別的統計資料

什麼是優化器統計資料

優化器統計資料是一組描述資料庫和資料庫中物件資訊的集合.這些統計資料在優化器給每一個sql語句選擇一個最優的執行計劃時會被使用.統計資料儲存在資料字典中且它們能通過資料字典檢視比如象user_tab_statistics來進行訪問.優化器統計資料與v$檢視來檢視的效能統計資料不同.v$檢視中的資訊是系統狀態和執行sql工作負載的資訊.

表和列統計資料

表統計資料包括了表中的行記錄數,表用來儲存這些資料的資料塊數,表中的平均行長度等資訊.優化器使用這些資訊並結合其它的統計資料來計算執行計劃中各個操作的成本並評估每一個操作將返回的行數.例如,一個表掃描的成本是使用表所使用的資料塊數和引數db_file_multiblock_read_count來計算出來的.可以查詢user_tab_statistics檢視來檢視錶的統計資料.

列統計資料包括一個列中的不同值的個數(NDV)還有在這個列中的最小值/最大值.可能查詢user_tab_col_statistics檢視來檢視這列的統計資料.優化器使用列統計資料並結合表的統計資料(行數)來評估一個sql操作將要返回的行數.例如一個表有100行記錄且對一個有10個不同值的列使用等號謂詞評估,那麼優化器會假設統一的資料分佈,那麼評估的基數等於表的記錄數除以這個列不同值的個數:100/10=10.

額外的列統計資料

基本表和列統計資料沒有提供一種機制來告訴優化器關於表中或列中的資料的特性.例如,這些統計資料不能告訴優化器表中的列中的資料是否有傾斜或者列之間是否存在關聯.資料特性的資訊可以通過擴充套件基本的統計資料象,直方圖,列組和表示式統計資料提供給優化器.

直方圖

直方圖告訴優化器關於列中的資料分佈情況.預設情況(沒有直方圖),優化器會假設一個列中的不同值會均勻分佈的.同上所述,優化器評估一個等號謂詞的基數是通過這個表中的行數除以這個等號謂詞列中不同值的個數得到的.如果資料分佈不是均勻的(比如資料傾斜),那麼這樣的基數評估就會出錯.為了精確的反映非均勻的資料分佈就需要對列建立直方圖.直方圖的存在改變了優化器評估基數的公式且會讓優化器生成一個更精確的執行計劃.

Oracle基於列的使用資訊(SYS.COL_USAGE$)和資料的傾斜情況能自動判斷這個列是否需要直方圖.例如只在一個等號謂詞中看到的唯一性列oracle是不會對這個唯一性列自動建立直方圖.

有兩種型別的直方圖,頻率直方圖和高度平衡直方圖.oracle會基於列中的不同值的個數來決定建立直方圖的型別.

頻率直方圖

當列中的不同值的個資料小於254時會建立頻率直方圖.oracle使用下面的步驟來建立頻率直方圖:

1.      讓我們假設oracle正對promotions表的promo_category_id建立一個頻率直方圖.第一步從promotions表中通過對promo_category_id排序來查詢promo_category_id

2.      每一個promo_category_id被指派到它所屬的直方圖bucket中

在這個步驟中可能直方圖的bucket數超過254,因此擁有相同值的bucket會被壓縮到使用這個值的最高bucket中.在這種情況下,bucket 2到 115會被壓縮到bucket 115,且bucket 484到503會被壓縮到bucket 503直到總的bucket數仍然保持與列中不同值的個數相等.注意上面的步驟是出於演示.dbms_stats包對直接構造壓縮直方圖進行了優化.

優化器現在使用頻率直方圖可以精確的判斷謂詞promo_category_id的基數.例如,對於謂詞promo_category_id=10,優化器首先需要判斷在直方圖中10作為end point的bucket的數量.通過找到endpoint為10的bucket 503,然後減去前面的bucket數,bucket 483,503-483=20.然後基數評估將使用下面的公式進行計算(number of bucket endpoints/ total number of bucket) * num_rows,20/503*503,所以在promotoins表中promo_category_id=10的記錄有20行.

當列中的不同值的數理超過254時就會建立高度平衡直方圖.在高度平衡直方圖中,列值被劃分到bucket中所以每一個bucket可能包含相同數量的行.oracle使用下面的步驟來建立一個高度平衡直方圖.

1.      我們假設oracle將要對customers表中的cust_city_id列建立一個高度平衡直方圖因cust_city_id列的不同值的數量超過了254.與頻率直方圖類似,第一步是執行ordered by cust_city_id子句來查詢customers表中的cust_city_id.

2.      在customers表中有55500行直方圖中最多有254個bucket.為了讓每一個bucket中有一個相等的行數,oracle必須在每一個bucket中存入219行.第219行的cust_city_id將成為第一個bucket的endpoint在這種情況下是51043.第438行的cust_city_id將成為第二個bucket的endpoint並且一直到所有254buckets被填滿為止.

3. 一旦bucket建立完成後oracle會檢查是否第一個bucket的endpoint值是否是cust_city_id列中的最小值,如果不是,一個”zero”bucket會被新增到直方圖中而且cust_city_id列中的最小值作為它的endpoint.

4. 與頻率直方圖一樣最後的步驟是壓縮高度平衡直方圖並且刪除有重複endpoint的bucket.在cust_city_id列的高度平衡直方圖中51166是bucket24和bucket25的endpoint.因此bucket24將會被壓縮到bucket 25中

5. 使用高度平衡直方圖現在優化器能對謂詞cust_city_id列進行更好的基數評估.例如,對於謂詞cust_city_id=51806,優化器首先會檢查在直方圖中使用51806作為endpoint的有多少個bucket.在這種情況下136,137,138和139的endpoint是51806(檢視user_histograms).優化器將會使用下面的計算公式:

(Number of bucket endpoints / total number of buckets) *  number of rows in the table

在這種情況下: 4/254 * 55500=874

然而如果謂詞cust_city_id=52500它不是任何bucket的endpoint值那麼優化器會使用一個不同的計算公式.對於只是一個bucket或者不是任何bucket的endpoint的值優化器將使用下面的計算公式:

DENSITY  * number of rows in the table

這裡的density是對直方圖使用內部演算法計算出來的.density的值可以通過檢視user_tab_col_statistics得到這個值是從oracle database10.2.0.4之前使用.這個值是為了向後相容,這個值在oracle database 9i和前期的oracle database10g中使用.因此如果optimizer_features_enable被設定的版本比10.2.0.4前那麼檢視中的density值將會被使用.

擴充套件統計資料

在oracle database11g中引入了擴充套件列統計資料.擴充套件統計資料包括另外兩種額外型別的統計數:列組和表示式統計資料.

列組

在真實的資料中,在相同表中儲存在不同列中的資料之間通常是有關聯的.例如,在customers表中,cust_state_province列會受country_id列的影響,比如當state為California那麼country只能是United States.只使用基本的列統計資料優化器是沒有辦法知道真實資料之間的關係如果一個語句中的where子句中有多個列來自同一個表那麼有可能會計算出錯誤的基數.通過將這些列作為一個團體組來擴充套件它的統計資料使優化器知道這些真實世界的關係.

通過對一組列建立統計資料,當來自同一個表的幾個列同時一起出現在語句中的where子句中時優化器能更好的評估基數.可以使用dbms_stats.create_extended_stats函式來對要收集列統計資料的這些列定義一個列組.當一個列組被建立後,當對這個表收集統計資料時和其它原始列一樣oracle將自動對列組維護統計資料.

在建立列組和重新收集統計資料之後,在檢視user_tab_col_statistics中可以看到一個系統生成名字的列.這個新列代表列組:

為了將系統生成列名對映到列組上可檢視使用者下其它擴充套件的統計資料,可以查詢user_stat_extensions

現在當這些列一起出現在where子句中優化器就能使用列組統計資料而不使用單列統計資料.要注意的是並不是這個列組中的所有列都必須出現在sql語句中只要列組中的這些列的子集出現在sql語句中優化器就會使用擴充套件的統計資料.

表示式統計資料

也可以對一個表示式(包括函式)建立擴充套件的統計資料,來幫助優化器來對where子句中嵌有表示式的列進行基數評估.例如,常見的是一個where子句對一個customers表的last name使用upper函式,upper(cust_last_name)=:B1,那麼對於這個表示式upper(cust_last_name)建立一個擴充套件的統計資料是有益的.

與列組一樣,在表示式統計資料被定義後需要重新對這個表收集統計資料.在統計資料被收集後可能通過user_tab_col_statistics檢視來檢視通過系統生成的列名,它代表表示式統計資料.就象列組一樣關於表示式統計資料可以在user_stat_extensions檢視中可以找到.

擴充套件的統計資料的限制

擴充套件的統計資料只有當where子句中是等號謂詞或者in列表時才會被使用.如果在底層的列存在直方圖且列組上不存在直方圖那麼擴充套件的統計資料將不會被使用.

索引統計資料

索引統計資料提供了索引中不同值的數量(distinct keys),索引的深度(blevel),索引的葉子塊數量(leaf_blocks)和集族因子.優化器使用這些資訊與其它的統計資料一起來判斷一個索引訪問的成本.例如優化器使用blevel,leaf_blocks和表統計資料的num_rows來判斷一個索引範圍掃描的成本

收集統計資料

對於不斷改變的資料物件統計資料也必須定期的收集才能精確的描述資料庫物件.dbms_stats包是oracle推薦收集統計資料的方法用它來替代過時的analyze命令.dbms_stats包包含了超過50個不同的收集和管理統計資料的過程.但最重要的是這些過程gather_*_stats過程.這些過程被用來收集表,列和索引統計資料.執行這些過程需要需要是物件的所有者或者有analyze any的系統許可權或者是dba角色.這些過程使用的引數幾乎是相同的.所以這裡重點介紹gather_table_stats過程.

Gather_table_stats

Dbms_stats.gather_table_stats過程可以用來收集表,分割槽,索引和列統計資料.雖然這個過程有15個不同的引數,但是執行這個過程時只需要指定前兩個或前三個就能滿足大多數使用者的需求.

包含這個表的方案名

表名

如果是分割槽表且想要對這個特定的分割槽收集統計資料就是一個特定的分割槽名

其它的引數在大多數情況下可以保留其預設值.

Estimate_percent引數

這個estimate_percent引數判斷在計算統計資料是使用的行記錄的百分比.當表中所有的行(100%sample)被處理時收集的統計資料最精確,通常也稱作計算統計資料.在oracle database11g中引入了一種新的抽樣演算法是基於雜湊的且提供了確定的統計資料.這種新的演算法精確度與100%抽樣很接近但是成本只有100%抽樣的10%.當任何dbms_stats.gather_*_stats過程中的estimate_percent設定為auto_sample_size(預設值)時就會使用這種新的演算法.以前使用者將estimate_percent引數設定為一個較低的值來確保統計資料能被快速的收集完成.然而沒有經過詳細的測試是很難知道使用什麼樣的抽樣大小可以得到精確的統計資料.強烈建議從oracle database11g開始讓estimate_percent引數使用其預設值(而不是顯式的設定).

Method_opt引數

這個method_opt引數控制著在收集統計資料時是否建立直方圖.直方圖是當列中資料不是均勻分佈時建立的一種特定型別的列統計資料.使用預設值for all column size auto,oracle會基於列的使用資訊(dbms_stats.report_col_usage)和列中不同值的數量來自動判斷哪個列和將要使用的bucket的數量.列的使用資訊反映了資料庫對一個指定物件所處理的所有sql操作的一種分析.列使用跟蹤預設情況下是啟用的.

如果列在where子句中以等號謂詞,範圍,like等形式出現那麼這個列是建立直方圖的一個備選者.如果在建立直方圖之前列中的資料存在傾斜oracle也會驗證.例如列只以等號謂詞出現

且是唯一性列將不會對這個列建立直方圖.

Degree引數

這個degree引數控制著用於收集統計資料的並行伺服器程式的個數.通常oracle使用並行伺服器程式的個數與資料字典中表的degree屬性指定的值相同(並行度).預設情況下在oracle database中所有表的這個屬性被設定為1,如果對一個大表收集統計資料為了加快收集的速度這個引數是用的.當引數degree設定為auto_degree,oracle將基於物件的大小來自動判斷收集統計資料時的並行伺服器程式的個數.這個值的範圍介於小物件的1(序列執行)到大物件的

Default_degree(parallel_threads_per_cpu * cpu_count).

Granularity引數

這個granularity引數指示了對分割槽表收集統計資料的級別.可能的級別是表(全域性),分割槽或子分割槽.預設情況下基於表的分割槽策略oracle將會判斷需要使用的級別.統計資料總是在第一級分割槽進行收集不管分割槽的型別.當子分割槽型別是list或range時會收集子分割槽統計資料.如果表不是分割槽表這個引數會被忽略.

Cascade引數

這個cascade引數控制著是否對錶中的索引收集統計資料.預設值auto_cascade,oracle將只會對哪些統計資料過期的表重新收集索引統計資料.當一個大量資料直接載入時並且索引是禁用時節cascade通常是設定為false.在資料載入完成後,索引要被重建統計資料也會自動建立,當收集表統計資料時不需要收集索引統計資料

No_invalidate引數

這個no_invalidate引數決定在統計資料收集後遊標(當收集統計資料時訪問表的遊標)是否立即失效.預設值是dbms_stats.auto_invalidate,遊標(已經被解析的語句)不會立即失效.它們將使用之前統計資料所建立的執行計劃直到oracle基於內部啟示決定依賴的遊標失效為止.為了確保在共享池中沒有效能問題或者如果有大量的依賴遊標而且它們都是硬解析隨著時間的推移失效將會發生.

改變dbms_stats.gather_*_stats中引數的預設值

可以對一個單獨的dbms_stats.gather_*_stats命令指定一個非預設值或者對於資料庫覆蓋其預設值.可以使用dbms_stats.set_*_prefs

過程來覆蓋dbms_stats.gather_*_stats過程中的預設引數值.下面是可以修改的引數列表:

AUTOSTATS_TARGET (SET_GLOBAL_PREFS only as it relates to the auto stats job) CONCURRENT (SET_GLOBAL_PREFS only)

CASCADE DEGREE ESTIMATE_PERCENT METHOD_OPT NO_INVALIDATE GRANULARITY PUBLISH INCREMENTAL STALE_PERCENT

可以使用下面的dbms_stats.set_*_prefs過程來在表,方案,資料庫或全域性級別來覆蓋每一個引數的預設值.

SET_TABLE_PREFS

SET_SCHEMA_PREFS

SET_DATABASE_PREFS

SET_GLOBAL_PREFS

Set_table_prefs過程允許修改用於一個特定表統計收集dbms_stats.gather_*_stats過程的預設引數值.

Set_schema_prefs過程允許修改用於一個特定方案中所有表統計收集dbms_stats.gather_*_stats過程的預設引數值.這個過程實際上是對一個特定方案中的每一個表呼叫一次set_table_prefs過程,因為它是呼叫set_table_prefs所以呼叫這個過程在其執行後不會對新建立的物件有影響.新物件將對所有引數使用golable引用值.

Set_database_prefs過程允許修改用於資料庫所有使用者方案統計收集dbms_stata.gather_*_stats過程的預設引數值.這個過程實際上是對每一個使用者方案中的每一個表呼叫set_table_prefs過程. 因為它是呼叫set_table_prefs所以呼叫這個過程在其執行後不會對新建立的物件有影響.新物件將對所有引數使用golable引用值.如果將引數add_sys設定為true那麼它也有可能包含oracle所擁有的方案(sys,system等).

Set_global_prefs過程允許修改用於資料庫中任何物件統計收集dbms_stats.gather_*_stats過程的預設引數值.所有引數預設為全域性設定除非有表進行了優先設定或者這個引數通過gahter_*_stats命令被顯式的設定.通過這個過程修改的引數將會影響修改之後所有新建立的物件.新物件將會使用所有引數的global_prefs.

使用set_global_prefs也可以對兩個額外的引數autostat_target和concurrent.autostat_target設定預設值來控制什麼物件將會被自動統計收集job來進行統計收集.這個引數可能的值有all,oracle和auto.預設值是auto.

Concurrent引數控制著在一個使用者(或資料庫)是否併發對多個表和對一個表的多個分割槽收集統計資料.這是一個布林型的引數,預設值為false.concurrent引數值不會影響自動統計收集job.

Dbms_stats.gather_*_stats過程和自動統計收集job遵循以下層次的引數值,通過命令顯式設定的引數值將會對其它設定進行覆蓋.如果引數沒有通過命令進行設定,就會檢查表級的引數引用.如果沒有表級引用設定,就使用global引用.

                         

如果你不能確保什麼級別的引數引用被設定可以使用dbms_stats.get_prefs函式來檢查.這個函式有三個引數,引數名,方案名和表名.在下面的例子中我們首先檢查sh.sales表上的stale_percent的值.然後設定表級引數並且檢查使用dbms_stats.get_prefs的影響.

自動統計收集job

通過在一個預定義的維護視窗中執行一個oracle自動任務來收丟失統計或統計過期的所有資料庫物件自動收集統計資料(工作日的上午10點到零晨2點和週末的6點到零晨2點).

自動收集統計資料是通過呼叫內部過程

Dbms_stats.gather_database_stats_job_proc來實現的.這個過程操作非常類似如使用gahter auto選項的dbms_stats.gather_database_stats過程.主要區別是oracle內部非常重視需要統計資料的資料庫物件，所以這些物件那個最需要更新統計資料誰就會被優先處理.可以通過查詢dba_autotask_client_job檢視或EM來驗證.也可以通過EM來改變這個job的維護視窗.

一個表中被修改的行數超過stale_percent(預設值10%)時就會認為這個表的統計資料過時了.oracle會監控所有表的DML活動並在SGA中進行記錄.監控的資訊會定時的重新整理到磁碟且可以通過*_tab_modifications檢視來檢視.

也可以呼叫dbms_stats.flush_database_monitoring_info過程來手動重新整理這些資料.如果想在查詢時得到最新資訊(在所有統計資料收集之前內部監控資料會被重新整理).可以通過查詢user_tab_statistics檢視中的stale_stats列來檢視哪個表的統計資料過時了.

表的stale_stats被設定為NO,統計資料是最新的.表的stale_stats被設定為YES,統計資料是過時的.表的stale_stats沒有被設定說明丟失統計資料.

如果你有一套完善的統計資料收集過程或者因為某些原因想對主應用程式方案禁用自動統計收集,保留對資料字典表的收集.可以將dbms_stats.set_global_prefs過程的autostats_target從auto設定為oracle.

BEGIN

DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','ORACLE');

END;

/

同時一起禁用自動收集任務:

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'auto optimizer stats collection',

operation => NULL,

window_name => NULL);

END;

/

提高收集統計資料的效率

當你確定了對哪些統計資料感興趣你可能想要及時的收集這些統計資料.傳統上人們希望通過使用並行來提高統計資料收集的速度.然而,如果方案中所有的物件很小並不能使用並行執行那要怎樣提高統計資料收集的速度呢

並行統計資料收集

在oracle database11grelease 2(11.2.0.2)中,並行統計資料收集模式被引入來同時併發的收集一個方案中的多個表和一個表中的多個分(子分)區.對多個表和多個分(子分)區同時並行收集統計資料能充分利用多CPU的資源來減少收集的時間.

併發統計資料收集是通過全域性引數來控制的,concurrent可以設定為true或false.預設值為false.當concurrent設定為true時,oracle將會使用oracle job排程和高階佇列元件來建立和管理多個併發統計資料收集job.

當concurrent設定為true時,對一個分割槽表呼叫dbms_stats.gather_table_stats使用oracle對這個分割槽表的每一個分割槽建立一個竭的統計資料收集job.這些job中有多少將會同時併發執行,這些可能job佇列過程中有多少將會排隊(初始化引數job_queue_processes.在rac中每一個節點的job_queue_processes)和有多不可用的系統資源.噹噹前執行的job完成後,更多的job會被排隊和執行直到所有的分割槽都完成統計資料收集為止.

如果使用dbms_stats.gather_database_stats,dbms_stats.gather_schema_stats或者dbms_stats.gather_dictionary_stats收集統計資料,那麼oracle將會對每一個非分割槽表和對分割槽表的每一個分割槽建立一個單獨的統計資料收集job.每一個分割槽表都有一個排程job來管理它的分割槽job.資料庫會盡可能的同時多執行幾個job和餘下的job將會排隊直到執行的job完成.然而為了防止死鎖的發生多個分割槽表不能同時被處理.因此如果對一個分割槽表執行了幾個job那麼其它的分割槽表將會進行排隊等待直到當前的收集job完成為止.對於非分割槽表沒有限制.

下面的資料說明了在sh方案中使用dbms_stats.gather_schema_stats命令在不同級別建立job.

Oracle將會對每一個非分割槽表建立一個統計資料收集job.

CHANNELS,

COUNTRIES,

CUSTOMERS,

PRODUCTS,

PROMOTIONS,

TIMES

每一個分割槽表一個排程job.比如,sales和costs.且它將對sales和costs中的每一個分割槽建立一個統計資料收集job.

假設job_queue_processes引數設定為32,oracle job排程器允許執行32個統計資料收集job.假設costs表的第29個job開始執行,那麼三個非分割槽表統計資料收集job也會開始執行,sales的統計資料收集job將會自動排隊,因為一次只能處理一個分割槽表.當每一個job完成後,另外的job將會進行排隊和開始執行直到所有64個job全部完成為止.每一個單獨的統計資料收集job都能使用並行執行.

配置和設定

在oracle database11.2.0.2中,統計資料收集的並行設定預設情況下是關閉的.可以使用下面的命令將其開啟.

BEGIN

DBMS_STATS.SET_GLOBAL_PREFS(‘CONCURRENT’,’TRUE’);

END;

/

為了收集統計資料可能也會需要一些額外的許可權.使用者必須要有job scheduler和AQ許可權:

CREATE JOB

MANAGE SCHEDULER

MANAGE ANY QUEUE

當job scheduler在sysaux表空間中儲存它的內部表和檢視時sysaux表空間應該是聯機狀態.最後對於統計資料收集過程來說job_queue_processes引數的設定應該要能完全利用可用的系統資源.如果不計劃使用並行執行應該將job_queue_processes設定為cpu核數的2倍(在rac中需要對每一個節點進行設定).要確保在系統級別設定這個引數(alter system..或者通過init.ora檔案)而不是在會話級別進行設定(alter session).

如果將使用並行執行作為併發統計資料收集的一部分應該禁用parallel_adaptive_multi_user初始化引數.

ALTER SYSTEM SET parallel_adaptive_multi_user=false;

也建議啟用並行語句佇列.這要求資源管理器是啟用的且建立一個臨時資源計劃組資源消耗組”OTHER_GROUPS”將啟用排隊.通常資源管理器只在維護視窗期間是啟用的.下面的指令碼將建立一個臨時資源計劃並對這個計劃使用資源管理器.

你會注意到自動統計資料收集job現在不會使用併發.將concurrent設定為true對自動統計資料收集job不會有影響.

分割槽表的統計資料收集

分割槽表的統計資料收集它是由表級別和分割槽級別的統計資料收集組成.在oracle database11g之前,新增加一個分割槽或修改一個分割槽都要求掃描整個表來重新重新整理表級別的統計資料.如果跳過全域性層面的統計資料收集那麼優化器會基於已經存在的分割槽級別的統計資料來推斷全域性層面的統計資料.這種方法對於簡單表的統計資料來說是精確的比如象行數可以通過聚合每一個分割槽的行數來統計—但是其它的統計資料是不能精確的判斷的.比如,不能基於所有分割槽的單個分割槽來精確的判斷一個列中的不同值的數量(優化器使用的最關鍵的統計資料).

在oracle database11g中通過引入增量全域性統計資料增加了對分割槽表的統計資料收集.如果一個分割槽的incremental引數設定為true,dbms_stats.gather_*_stats引數granularity為global,estimate_percent設定為auto_sample_size. Oracle將會對新的分割槽收集統計資料並通過掃描新增加的或修改的分割槽來更新所有全域性層面的統計資料但不是掃描整個表.

增量全域性統計資料是通過為表中每一個分割槽儲存的概要來計算出來的.一個概要是分割槽和分割槽中列的統計資料的後設資料.每一個概要儲存在sysaux表空間中.全域性統計資料是通過聚合分割槽級別的統計資料和每一個分割槽的概要生成的,因此消除了為了收集表級別統計資料而要掃描整個表.當一個新的分割槽被增加到表中,僅需要對新增加的分割槽收集統計資料.全域性統計資料會自動使用新的分割槽概要和已經存在的分割槽概要來精確的更新.

下面是使用增量全域性統計資料的步驟

在表級或全域性層面啟用增量統計資料

BEGIN

DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE');

END;

/

為了檢查一個指定表的incremental的當前設定可以使用dbms_stats.get_prefs;

SELECT DBMS_STATS.GET_PREFS('INCREMENTAL','SH','SALES') FROM dual;

注意incremental不會應用到子分割槽上.子分割槽和分割槽的統計資料會正常的被收集.只是分割槽統計資料將會用來確定全域性或表級別的統計資料.

管理統計資料

為了收到適當的統計資料,提供一種全面框架來管理它們也很重要.oracle提供了一系列的方法來做這件事包括將統計資料還原到之前的版本,將統計資料從一個系統傳輸到另一個系統或者甚至手動設定統計資料.這些選項在特定情況下是非常有用的,但是不建議替換標準的收集統計資料的方法dbms_stats.

還原統計資料

從oracle database10g開始,當你使用dbms_stats收集統計資料時原始的統計資料會自動在資料字典表中進行備份, 如果新收集的統計資料導致任何的問題通過執行dbms_stats.restore_table_stats就能很容易的還原.檢視dba_tab_stats_history包含了每一個表統計資料儲存的時間戳.

下面的例子將sales表的統計資料還原成昨天收集的且在shared_pool中引用sales表的所有遊標會自動變為無效.想讓所有的遊標變為無效因為將統計資料還原成昨天的想讓它們立即影響所有遊標.no_invalidate引數值會判斷引用該表的遊標是否會變為無效.

BEGIN

DBMS_STATS.RESTORE_TABLE_STATS(ownname => 'SH',

tabname => 'SALES',

as_of_timestamp => SYSTIMESTAMP-1

force => FALSE,

no_invalidate => FALSE);

END;

/

Pending統計資料

通常情況下當收集統計資料時,收集到的統計資料會被立即釋出(寫)到適當的資料字典表並提供給優化器使用.在oracle database11g中可以將收集到的統計資料不立即釋出,而是將它們儲存為一個沒有釋出的pending狀態.它們不是儲存在常用的資料字典表中而是儲存在pending表所以在釋出它們之前可以對它們進行測試.這些pending統計資料可以以一種受控的方式對單個會話啟用,這樣就可以在釋出之前對這些統計資料進行驗證.為了啟用pending統計資料收集需要使用dbms_stats.set_*_prefs這樣的一個過程對你想建立pending統計資料的物件將引數publish從true改變false.

BEGIN

DBMS_STATS.SET_TABLE_PREFS('SH','SALES',‘PUBLISH','FALSE');

END;

/

收集統計資料

BEGIN

DBMS_STATS.GATHER_TABLE_STATS('SH','SALES');

END;

/

對這些物件收集的統計資料可以通過user_*_pending_stats來顯示.可以通過一個alter session命令來設定初始化引數optimizer_use_pending_stats為true來告訴優化器使用pending統計資料並運一個sql,對於訪問的表沒有pending統計資料優化器將會使用標準資料字典表中的當前統計資料.當對pending統計資料進行驗證後可以使用下面的過程將其釋出.

DBMS_STATS.PUBLISH_PENDING_STATS.

BEGIN

DBMS_STATS.PUBLISH_PENDING_STATS('SH','SALES');

END;

/

匯出/匯入統計資料

當開發一個新應用程式或一個新模組想要測試時,理想情況下想要測試系統與生產系統的硬體平臺和資料大小是相同的.這種情況並不總是可以滿足的.最常見的問題是生產系統的大小.通過將一個產生資料庫中的優化統計資料複製到另一個相同資料庫版本的系統中,這樣就可以模仿生產環境中的行為.生產資料庫中的統計資料可以使用dbms_stats.export_*_stats和dbms_stats.import_*_stats過程複製到測試系統中.

在匯出統計資料之前需要使用dbms_stats.create_stat_table過程來建立一個表來儲存這些統計資料.在這個表建立之後可以使用dbms_stats.export_*_stats過程從資料字典中匯出統計資料.當統計資料填充到統計資料表中,可以使用datadump從生產資料庫中將統計資料表中的資料匯出並匯入到測試資料庫中.當統計資料表完全匯入到測試資料庫中,可以使用dbms_stats.import_*_stats過程匯入到資料字典表.下面的例子將建立一個統計資料表MYSTATS並從sh方案中將統計資料匯出到MYSTATS統計資料表中.

複製分割槽統計資料

當處理分割槽表時優化器依賴整個表(全域性統計資料)統計資料和單個分割槽統計資料來給一個sql語句選擇一個好的執行計劃.如果查詢需要訪問的只有單個分割槽優化器只會使用要訪問的這個分割槽的統計資料.如果查詢要訪問多個分割槽優化器將會使用全域性統計資料和分割槽統計資料.

常見的一個範圍分割槽表有一個新的分割槽增加到一個存在的表中並向這個增加的分割槽插入資料.如果終端使用者在統計資料收集之前要查詢新插入的資料,由於過時的統計資料可能會選擇一個次優的執行計劃.最常見的一種情況是where子句中謂詞使用值超出了列統計資料中[最小值,最大值]的範圍.這就是一個’out-of-range’的錯誤.

超出範圍的條件可以使用dbms_stats.copy_table_stats過程(從oracle database10.2.0.4開始可以使用)來預防.這個過程複製一個代表源(子)分割槽的統計資料到一個新建立的空的(子)分割槽中.它也會複製依賴物件的統計資料:列,本地(分割槽)索引等等.按下面的方式來調整分割槽列的最小值和最大值.

如果分割槽型別是hash分割槽那麼目標分割槽的最小值和最大值與源分割槽相同.

如果分割槽型別是list分割槽且目標分割槽是一個not default分割槽,那麼目標分割槽的最小值將被設定為描述目標分割槽列表值中的最小值.最大值將被設定為描述目標分割槽列表值中的最大值.

如果分割槽炻是list分割槽且目標分割槽是一個default分割槽,那麼目標分割槽的最小值被設定為源分割槽中的最小值,目標分割槽的最大值被設定為源分割槽中的最大值.

如果分割槽型別是range那麼目標分割槽的最小值設定為之前分割槽的上限值,目標分割槽的最大值設定為目標分割槽的上限值除非目標分割槽的上限值是maxvalue,在這種情況下目標分割槽的最大值設定為之前分割槽的上限值.

它也能基於指定的比例來複制統計資料(比如塊數或行數).下面的命令將sales_q3_2011範圍分割槽的統計資料複製到sales表的sales_q4_2011分割槽中.比例因子為2.

BEGIN

DBMS_STATS.COPY_TABLE_STATS('SH','SALES','SALES_Q3_2002','SALES_Q4_2002', 2);

END;

/

如果索引分割槽名與表分割槽一樣只會複製索引統計資料.全域性或表級統計資料預設情況下不會被更新.全域性層面的統計資料只有呼叫dbms_stats.copy_table_stats時沒有全域性統計資料存在時通過聚合生成全域性統計資料時才會被影響.

比較統計資料

一個系統中sql語句的執行計劃與另一個系統中的執行計劃不同一個關鍵的原因變是每個系統中的統計資料不同,例如測試系統中的資料不是100%的與生成系統的中相同.為了識別不同的統計資料

Dbms_stats.diff_table_stats_*函式可以用來比較兩個不同資料來源的表統計資料.統計資料來源可以是:

一個使用者統計資料表和資料字典中的當前統計數

單個使用者統計資料表包含兩組統計資料可以通過statids來識別

兩個不同使用者統計資料表

歷史中的兩個時間點的統計資料

當前統計數與厙上的一個時間點的統計資料

Pending統計資料與資料字典中當前的統計資料

Pending統計資料與使用者統計資料表

這個函式也可以比較兩個單獨物件的統計資料(索引,列,分割槽)而且如果統計資料之間的差別超過了指定的閾值也俑顯示兩個來源物件的所有統計資料.這個閾值可以通過一個函式的引數來指定預設值是10%.第一個來源的統計資料將會用作計算不同百分比的基數.

下面的例子中將用當前資料字典中emp表的統計資料與統計資料表TAB1中emp表的統計資料進行比較,下面的sql語句將會生成了一個報告:

SELECT report, maxdiffpct

FROM table(DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB(‘SCOTT’,’EMP’,’TAB1’));

鎖定統計資料

在有些情況下,可以通過鎖定統計資料來阻止對錶或方案收集新的統計資料.當統計資料被鎖定後這些統計資料直到這些統計資料被解鎖或者使用gather_*_stats過程時將引數force設定為true否則是不會被修改的.

在oracle database11g中dbms_stats包被擴充套件到允許統計資料在分割槽級別被鎖定和解鎖.這些額外的過程允許更細粒度的來控制.

BEGIN

DBMS_STATS.LOCK_PARTITION_STATS(‘SH’,’SALES’, ‘SALES_Q3_2000’);

END;

注意這裡有一種層次結構的鎖定統計資料.例如,如果對一個分割槽表鎖定統計資料,然後為了對一個分割槽重新收集統計資料需要對這一個分割槽解鎖統計資料這時就會收到ora-20005錯誤.出現這個錯誤是因為分割槽雖然已經解鎖但表級仍然被鎖定.這時要對分割槽重新收集統計資料只在將force引數設定為true才能成功.

手動設定統計資料

在某些特定的情況下手動設定儲存在資料字典中的優化器統計資料是非常有效的.比如一個高度不穩定的全域性臨時表(注意雖然這裡裡討論手動設定統計資料,但是不建議這麼做.因為不精確或不一致的統計資料可能導致選擇效能極差的執行計劃).統計資料可能呼叫

Dbms_stats.set_*_stats過程來手動設定.

其它型別的統計資料

除了基本表,列和索引統計資料之外優化器還使用額外的資訊來判斷一個語句的執行計劃.額外的資訊包含動態抽樣和系統統計資料.

動態抽樣

動態抽樣是在oracle database 9i release 2中引入用來在優化一個sql語句時收集額外的語句特定物件的統計資料.最常見的誤解是動態抽樣可以用來替優化器統計資料.動態抽樣的目的是當常規的統計資料不能滿足良好的基數評估時來擴大現有的統計資料.

所以怎樣和何時使用動態抽樣呢.在編譯一個sql語句時優化器通過考慮可用的統計資料是否能生成一個良好的執行計劃來決定是否使用動態抽樣.如果可用的統計資料不滿足就會使用動態抽樣.它通常是用來彌補由於丟失或無效的統計資料導致一個糟糕的執行計劃的情況.對於一個查詢中的一個或多個表沒有統計資料,優化器在優化語句之前將會使用動態抽樣來對這些表生成基本的統計資料.動態抽樣收集的統計資料質量不高或者沒有使用dbms_stats收集的完整.

第二種使用動態抽樣的情況是當語句包含一個複雜的謂詞表示式且擴充套件統計資料不可以使用或者不能使用的時候.例如,一個查詢在兩個相關列之間使用的不是等號謂詞.在這種情況下標準的統計資料不能滿足判斷,優化器假設每一個謂詞將會減少查詢所要返回的行數.根據標準的統計資料判斷的基數是20197但實際上返回的行數是210420.

SELECT count(*)

FROM sh.Sales

WHERE cust_id < 2222

AND prod_id > 5;

使用標準的統計資料優化器不能識別sales表中cust_id與prod_id之間的關聯.通過設定

Optimizer_dynamic_sampling為級別6,優化器將使用動態抽樣對複雜謂詞表示式收集額外的資訊.通過動態抽樣提供的額外資訊允許優化器生成一個更精確的基數評估因此會選擇一個更好的執行計劃.

正如你所看到的一樣動態抽樣是由引數optimizer_dynamic_sampling來控制的,它可能設定為不同的級別(0-10).這些級別控制著兩件不同的事;當動態抽樣開始時使用多大的抽樣大小來生成統計資料.太大的抽樣大小,動態抽樣對編譯查詢的時間的影響就會越大.

從oracle database11grelease 2開始,優化器將會自動決定動態抽樣是否有用和對於並行執行的sql語句使用什麼樣的動態抽樣級別.這個決定是基於語句中表的大小和謂詞的複雜程度.然而如果optimizer_dynamic_sampling引數被顯式的設定為一個非預設值,那麼使用者指定的值將會被使用.可以檢視執行計劃的note部分來了解是否使用了動態抽樣.例如,如果對sales表啟用了並行執行,執行下面的查詢,優化器將會自動使用級別為4的動態抽樣

對於序列執行的sql語句,動態抽樣的級別將根據optimizer_dynamic_sampling引數來決定.將不是由優化器自動觸發.原因是序列語句通常執行時間短且在編譯時的任何開銷都可能影響它的效能.而並行語句會使用更多的資源,所以在編譯時產生的額外開銷對獲得更好的執行計劃來說是值得的.

系統統計資料

在oracle database 9i中,系統統計資料引入是為了讓優化器通過使用關於執行語句的真實系統硬體資訊比如cpu速度和IO效能來更精確的計算出執行計劃中每一步的成本.

系統統計資料預設情況下是啟用且預設值是自動初始化的,這些值代表了大多數系統.當收集系統統計資料時它們將會覆蓋這些初始值.為了收集系統統計資料可以在一個有代表性的工作負載時間視窗使用dbms_stats.gather_system_stats,理想的情況是在負載高峰期間收集.

系統統計資料只需收集一次.系統統計資料不是作為自動統計資料收集job的一部分也不會自動被收集.必須使用gahter_system_statistics來更新系統統計資料.

字典表統計資料

因為現在優化器只支援基於成本的優化器,資料庫中所有的表都需要有統計資料包括所有字典表(sys,system使用者所擁有的表它們儲存在system和sysaux表空間中).字典表的統計資料是由在晚上維護視窗執行的自動統計資料收集job來維護的.如果想對應用程式使用者關閉自動統計資料收集job任務但是對字典表保留.可以呼叫dbms_stats.set_global_prefs將autostats_target引數從auto改為oracle.

BEGIN

DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','ORACLE');

END;

/

字典表的統計資料通過使用dbms_stats.gather_dictionary_stats過程來手動設定.使用者必須要有analyze any dictionary和analyze any系統許可權或者dba角色來更新字典表統計資料.建議對字典表的統計資料維護與常規使用者方案統計資料維護方式保持一致.

固定表的統計資料

需要對動態效能表和它們的索引(固定物件)收集統計資料.它們有x$表和v$檢視.因為v$檢視可能會象其它的使用者表或檢視一樣出現在sql語句中.對這些表收集統計資料來讓優化器生成一個更好的執行計劃來說是很重要的.然而不象其它的資料庫表,當優化統計資料丟失時不會對語句呼叫的x$表使用動態抽樣.優化器會使用預先定義的預設統計資料.這些預設值可能沒有代表性且可能會導致選擇一個次優的執行計劃.在系統中可能會造成一些效能問題.因為這個原因強烈建議收集固定物件的統計資料.

自動統計資料收集job不能對固定物件的統計資料進行收集或維護.可以使用dbms_stats.gather_fixed_objects_stats過程來收集固定物件的統計資料

BEGIN

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

END;

/

這個dbms_stats.gather_fixed_objects_stats過程除了資料塊的數量之外與dbms_stats.gather_table_stats過程收集的統計資料是一樣的.因為x$表只是在記憶體結構中不儲存到磁碟所在塊總是為0.因為x$表的瞬態性很重要當系統中有一個有代表性的工作負載就是對固定物件收集統計資料的時候.使用者必須要有analyze any dictionary系統許可權或者dba角色來更新固定物件統計資料.當你對資料庫或程式進行升級後強烈建議重新收集固定物件的統計資料.

小結

為了讓基於成本的優化器精確的判斷一個執行計劃的成本,它必須有sql語句所訪問的所有物件的資訊和關於執行這個語句的系統方面的資訊.這些必要的資訊通常被稱作優化器統計資料.理解和管理優化器統計資料是優化sql執行的關鍵.知道何時以及怎樣及時的收集統計資料是維護良好效能的關鍵.

通過使用自動統計資料收集job和dbms_stats包.dba可以對系統維護一組精確的統計資料來確保優化器將有最好的資源資訊來確定語句的執行計劃.

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