效能優化 - Oracle Tuning 總結 3 優化統計

2.4 基於成本的優化器技術內幕
Oracle基於成本的優化器（Oracle's cost-based SQL optimizer ，簡稱CBO)，是Oracle裡面非常複雜的一個部分, 它決定了Oracle裡面每個SQL的執行路徑。

CBO是一項評價SQL語句和產生最好執行計劃的具有挑戰性的工作，所以也使它成Oracle最複雜的軟體組成部分。
眾所周知，SQL的執行計劃，幾乎是Oracle效能調整最重要的方面了。所以想要學會如何調整Oracle資料庫的效能，就要學會如何對SQL進行調整，就需要深入透

徹理解CBO。
CBO的執行路徑，取決於一些外部因素，內部的Oracle統計資料，以及資料是如何分佈的。
我們將要討論下面的話題：
CBO的引數：我們從基本的優化器引數開始學習，然後學習每個優化器引數是如何影響Oracle的優化器的執行的。

CBO的統計：這裡我們將討論，使用Analyze或者DBMS_STATS來收集正確的統計資料，對Oracle 優化器而言，是多麼的重要。我們還將學習如何把優化器的統計數

據，從一個系統拷貝到另外一個系統，這樣可以確保開發環境和產品資料庫環境下，SQL的執行路徑不會變化。

下面我們開始討論CBO優化模式以及影響CBO的Oracle引數

2.4.1 CBO的引數
CBO受一些重要引數的影響，修改這些引數後可以看到CBO效能上戲劇性的變化。首先從設定CBO的optimizer_mode引數開始，然後討論其他重要引數的設定。

在 Oracle 9i 中，optimizer_mode 引數有四種取值，決定了四種優化模式： rule, choose, all_rows, 和 first_rows，其中 rule 和 choose 兩種模式表示

目前已經過時的基於規則的優化器模式（rule-based optimizer，簡稱RBO），所以我們在此著重討論後兩種CBO模式。

優化模式的設定可以在系統級進行，也可以對某個會話（session）進行設定，或者對某個SQL語句進行設定。對應的語句如下：
alter system set optimizer_mode=first_rows_10;
alter session set optimizer_goal = all_rows;
select /*+ first_rows(100) */ from student;

我們首先需要知道對一個SQL語句來說，什麼是最好的執行計劃（the best execution plan）？是使SQL語句返回結果的速度最快，還是使SQL語句佔用系統資源

最少？顯然，這個答案取決於資料庫的處理方式。

舉一個簡單的例子，比如有下列SQL語句：
select customer_name
from
   customer
where
   region = 'south'
order by
   customer_name; 

如果最好的執行計劃是返回結果的速度最快，那麼就需要使用 region 列和 customer_name 列上的索引，從 customer 表中按照正確的順序快速讀取所有的列，

而不用管是否從物理上讀取了很多不連續的資料塊導致的大量IO操作。（見下圖）

假設這個執行計劃從開始到返回結果耗時 0.0001 秒，同時產生了 10000 個 db_block_gets ，但是如果你的目標是計算資源的最小化呢？如果這個SQL語句是在

一個批處理程式中執行，也許對返回結果的速度要求就不那麼重要了，而另一個執行計劃則可能耗費更少的系統資源。
在下圖所示的例子中，並行的全表掃描由於不需要按照排序重新讀取資料塊，所以耗系統資源較少，並且IO操作也不多。當然，由於SQL語句執行過程中沒有排序

，得到預期結果的時間就長了，而資源耗費少了。假設這個執行計劃從開始到返回結果耗時 10 秒，同時產生了 5000 個 db_block_gets

Oracle提供了幾個 optimizer_mode 的設定引數，使你能夠得到想要的最好的執行計劃。

optimizer_mode = first_rows
設定為這種CBO模式以後，SQL語句返回結果的速度會盡可能的快，而不管系統全部的查詢是否會耗時較長或者耗系統資源過多。由於利用索引會使查詢速度加快

，所以 first_rows 優化模式會在全表掃描上進行索引掃描。這種優化模式一般適合於一些OLTP系統，滿足使用者能夠在較短時間內看到較小查詢結果集的要求。

optimizer_mode = all_rows
設定為這種CBO模式以後，將保證消耗的所有計算資源最小，儘管有時查詢結束以後沒有結果返回。all_rows 的優化模式更傾向於全表掃描，而不是全索引掃描

和利用索引排序，因此這種優化模式適合於資料檢視實時性不是那麼強的資料倉儲、決策支援系統和麵向批處理的資料庫（batch-oriented databases）等。

optimizer_mode = first_rows_n
Oracle 9i 對一些預期返回結果集的資料量小的SQL語句優化模式進行了加強，增加了四個引數值：first_rows_1、first_rows_10、first_rows_100、

first_rows_1000。CBO通過 first_rows_n 中的 n 值，決定了返回結果集數量的基數，我們可能僅僅需要查詢結果集中的一部分，CBO就根據這樣的 n 值來決定

是否使用索引掃描。

optimizer_mode = rule
基於規則的優化器模式，RBO，是早期Oracle版本使用過的一種優化模式。由於RBO不支援自1994年Oracle版本的新特性，如 bitmap indexes，table partitions

，function-based indexes等，所以在以後Oracle版本中已經不再更新RBO，並且也不推薦使用者使用RBO這種優化模式了。

從上面的討論可以看出，optimizer_mode 引數的設定對CBO是非常重要的，決定了CBO的基本模式，同時還有一些其他的引數也對CBO有著極大的影響。由於CBO的

重要性，Oracle提供了一些系統級的引數來調整CBO的全域性效能，這些調整引數包括索引掃描與全部掃描的選擇、表連線方式的選擇，等等。下面簡單討論一下。

optimizer_index_cost_adj
這個引數用於調整使用索引的訪問路徑的成本演算法，引數值越小，索引訪問的成本就越低。

optimizer_index_caching
這個引數告訴Oracle在記憶體緩衝區中索引的數量。該引數的設定會影響CBO如何決定使用表連線（巢狀迴圈）的索引還是使用全表掃描。

db_file_multiblock_read_count
這個引數的值被設定較大的時候，CBO就會認為離散的、多資料塊的讀取會比順序讀取的代價更低，使得CBO更傾向於全表掃描。

parallel_automatic_tuning
這個引數值被設定為 on 的時候，表示使用並行的全表掃描，由於並行的全表掃描比較快，所以CBO認為索引的訪問是高成本的，同時就更傾向於全表掃描。

hash_area_size
如果不使用 pga_aggregate_target 引數的話，該引數有效。該引數的設定大小決定CBO是否更加傾向於 hash joins ，而不是巢狀迴圈和表連線的索引合併。

sort_area_size
如果不使用 pga_aggregate_target 引數的話，該引數有效。該引數的設定大小影響CBO決定是否進行索引訪問和結果集的排序，引數值越大，在記憶體中排序的可

能性就越大，CBO也就更加傾向於排序。

由於對這些引數值的修改會影響到系統中成千上萬的SQL語句的執行計劃，所以Oracle並不推薦修改這些引數的預設值。

在對CBO的引數有了大致的瞭解以後，下面討論如何根據提供給CBO的資料幫助CBO制定出一個好的執行計劃。

2.4.2 CBO的統計
對於CBO來說，最重要的是定義和管理好你的統計資料，為了使CBO能夠為你的SQL語句產生一個最好的執行計劃，必須要有與SQL語句相關的表和索引統計資料。

只有當CBO知道了相關的資訊，如表的大小、分佈、基數以及列值的可選性等，才能對SQL語句作出正確的判斷，從而得到最好的執行計劃。

下面討論一下如何獲得高質量的CBO統計資料，如何為你的資料庫系統建立一個適當的CBO環境。

CBO產生最好執行計劃的能力來自於統計資料的有效性，獲得統計資料的比較過時的方法是 analyze table 和 dbms_utility ，這兩種方法對SQL語句的效能有一

些危害，因為我們知道，CBO是使用物件統計資料（object statistics）來為所有的SQL語句選擇最好的執行計劃。
dbms_stats 應用功能包是產生統計資料較好的方法，特別對大型分割槽表而言。下面看一個使用 dbms_stats 的例子。
exec dbms_stats.gather_schema_stats(
ownname          => 'SCOTT',
options            => 'GATHER AUTO',
estimate_percent    => dbms_stats.auto_sample_size,
method_opt        => 'for all columns size repeat',
degree            => 34
   )

上面例子中的options引數的幾個可選值需要說明一下。
GATHER 重新分析整個schema，產生統計資料；

GATHER EMPTY 僅分析那些還沒有統計資料的表；

GATHER STALE 僅重新分析那些發生了10％變化的表（變化原因可能是 inserts, updates , deletes ）

GATHER AUTO 僅重新分析那些還沒有統計資料和發生了10％變化的表，該選項相當於 GATHER EMPTY 和 GATHER STALE 同時使用。

使用 GATHER AUTO 和 GATHER STALE 兩個選項需要進行監控，如果你執行了 ALTER TABLE XXX MONITORING 命令，Oracle利用 dba_tab_modifications 檢視跟

蹤表的變化，記錄了最近一次統計資料分析以來的 insert , update , delete 的準確記錄數。
SQL> desc dba_tab_modifications;
Name                     Type
-----------------              ---------------
TABLE_OWNER          VARCHAR2(30)
TABLE_NAME           VARCHAR2(30)
PARTITION_NAME       VARCHAR2(30)
SUBPARTITION_NAME   VARCHAR2(30)
INSERTS                 NUMBER
UPDATES                NUMBER
DELETES                NUMBER
TIMESTAMP             DATE
TRUNCATED             VARCHAR2(3)

比較有趣的一個選項是 GATHER STALE ，比如在一個資料更新頻繁的OLTP系統中，幾乎所有的統計資料都會很快的過時，而我們必須記住 GATHER STALE 選項是

在表中10％的記錄發生變化時才對該表重新分析產生統計資料，因此除了只讀表以外的所有表幾乎使用 GATHER STALE 選項重新分析產生統計資料，所以 GATHER

STALE 選項主要還是用於一些主要是隻讀表組成的系統中。

在上面使用 dbms_stats 的例子中，我們看到了一個引數 estimate_percent ，它的值是 dbms_stats.auto_sample_size, 這個引數值是 Oracle 9i 才開始使用

的，這個引數值的出現極大方便了統計資料的分析產生。
我們知道，統計資料的質量越高，CBO產生最好執行計劃的能力就越強，但是由於資料庫統計取樣大小的問題，對一個大型資料庫系統做一個完整的統計資料分析

產生將會耗時數天，最好的辦法就是在高質量的統計資料和資料庫統計取樣大小之間得到一個平衡點。
在早一些的Oracle版本中，為了得到統計資料，DBA不得不猜測一個最好的資料取樣大小百分比。但是從 Oracle 9i 開始，可以通過 dbms_stats 包來自己指定

estimate_percent 引數的值了，那就是 dbms_stats.auto_sample_size
通過這種方式設定了自動取樣大小以後，我們可以通過下列資料字典檢視的 sample_size 欄位來驗證這些自動產生的統計取樣大小。
DBA_ALL_TABLES
DBA_INDEXES
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_OBJECT_TABLES
DBA_PART_COL_STATISTICS
DBA_SUBPART_COL_STATISTICS
DBA_TABLES
DBA_TAB_COLS
DBA_TAB_COLUMNS
DBA_TAB_COL_STATISTICS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS

使用自動統計取樣以後，Oracle會根據表的大小和列值的分佈在5％到20％之間取值。記住：你的統計資料質量越高，CBO作出的決定就越對你有利。

現在我們對CBO統計資料應該有一些瞭解了，下面來看看在一個成功的Oracle系統是如何管理CBO統計資料。

2.4.3 CBO的正確環境
成功使用CBO的關鍵是穩定性，下面是一些成功使用CBO的基本事項。

●只在必需的時候才進行統計資料的重新分析
Oracle DBA們最容易犯的一個普遍錯誤就是經常性的對系統的統計資料進行重新分析。記住：做這件事的唯一目的是改變SQL語句的執行計劃，如果這個執行計劃

沒有被破壞，就不要去修復它。如果你對SQL語句的效能還滿意的話，重新分析產生統計資料以後可能會產生較大的效能問題，並給開發團隊帶來影響。實際運用

中，也是極少數的Oracle系統才會週期性的對統計資料進行重新分析。
一般來講，一個資料庫應用系統的基本架構是不會輕易改變，大資料量的表仍然是很大，索引列的分佈、基數值等等也很少變化。只有下列幾種情況的資料庫才

可能經常對整個系統的統計資料重新分析：
1、用於資料分析的資料庫
   有一些由於科學試驗資料分析的資料庫系統，經常會更換整個一套的試驗資料，那麼這種情況下當資料庫重新load了一套資料以後，可以立即重新對統計資料

進行分析。
2、高度變化的資料庫
   這是極少數的例子，表的大小或者索引列的資料在劇烈的變化，比如一張表有100條記錄，一週以後就變成10000條記錄。這種情況下也可以考慮週期性的進行

統計資料分析。

●強迫開發人員調整自己的SQL
很多開發人員錯誤的認為，他們的任務就是編寫SQL語句然後從資料庫中獲得正確的資料。但是實際上編寫出SQL語句只是開發人員一半的工作，在一個成功的

Oracle應用系統中，會要求開發人員的SQL語句採用最優化的方式訪問資料庫，並且保證SQL語句的執行計劃在新的SQL之間的可移植性。
令人驚訝的是，在許多Oracle應用系統中都不怎麼考慮具體SQL語句的執行計劃，認為CBO是很智慧的，無論如何都可以為我們提供最好的SQL語句執行計劃。
同一個查詢在SQL語句中可能有不同方式的寫法，而每一種寫法都可能有不同的執行計劃。觀察下面的例子，每一個查詢的結果都是一樣的，但是執行計劃卻相去

甚遠。

-- 使用了不正確的子查詢
select
book_title
from
book
where
book_key not in (select book_key from sales);

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=64)
1    0   FILTER
2    1     TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=64)
3    1     TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=25)

-- 使用了兩張表的外連線
select
book_title
from
book b,
sales s
where
b.book_key = s.book_key(+)
and
quantity is null;

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=100 Bytes=8200)

1 0 FILTER
2 1   FILTER
3 2     HASH JOIN (OUTER)
4 3      TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=20 Bytes=1280)
5 3      TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=100 Bytes=1800)

-- 使用了三個正確的子查詢
select
book_title
from
book
where
book_title not in (
                select
                distinct
                  book_title
                from
                  book,
                  sales
                where
                  book.book_key = sales.book_key
                and
                  quantity > 0);

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=59)
1 0 FILTER
2 1   TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=59)
3 1   FILTER
4 3     NESTED LOOPS (Cost=6 Card=1 Bytes=82)
5 4       TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=90)
6 4       TABLE ACCESS (BY INDEX ROWID) OF 'BOOK' (Cost=1 Card=1)
7 6         INDEX (UNIQUE SCAN) OF 'PK_BOOK' (UNIQUE)

我們看到，正確的SQL語句寫法產生的執行計劃是如此的不同。明智的開發人員知道如何去編寫能夠產生最好執行計劃的SQL語句，明智的Oracle應用系統也會主

動訓練開發人員去編寫最有效的SQL語句。

下面是一些幫助開發人員優化SQL語句的技巧：
1、 使用 autotrace 和 TKPROF 功能去分析SQL語句的執行計劃；
2、 保證所有生產環境中的SQL語句都是在測試環境中經過優化的；
3、 制定一個效能優化的標準，而不是隻要求開發人員編寫出最快的SQL語句。根據這種標準，好的開發人員應該能夠寫出最有效的SQL語句。

●謹慎管理CBO統計資料
成功的Oracle系統會謹慎管理他們的CBO統計資料，以保證CBO在測試環境和生產環境中以同樣的方式工作。一個聰明的DBA會在得到高質量的CBO統計資料以後，

把這些統計資料移植到測試環境中，這樣SQL語句的執行計劃在測試環境和生產環境中就是一樣的了。

對DBA來說，一個重要的工作就是收集和釋出CBO統計資料，並隨時保持一套當前執行環境的最精確的統計資料。在一些情況下，可能會有不止一套的優化統計數

據。比如，對OLTP執行的最好的統計資料可能對資料倉儲執行卻不是最好的，在這種情況下，DBA就需要保持兩套統計資料，並根據不同的執行條件匯入系統。

可以使用 dbms_stats 包中的 export_system_stats 儲存過程來完成CBO統計資料的匯出。下面的例子中，我們把當前CBO統計資料匯出到一張名叫

stats_table_oltp的表中。
dbms_stats.export_system_stats(‘stats_table_oltp’)

匯出以後，我們就可以把這張表拷貝到別的例項中，當系統的執行模式改變以後，使用 dbms_stats 包中的 import_system_stats 儲存過程來完成CBO統計資料

的匯入。
dbms_stats.import_system_stats(‘stats_table_oltp’)

●千萬不要隨便改動CBO引數的值
改動CBO相關引數的值是非常危險的，因為一個小小的改動可能就會對整個系統的執行效能帶來極大的負面影響，只有在經過嚴格的系統測試以後才能改動這些參

數的值。可能帶來極大影響的引數值包括：optimizer_mode, optimizer_index_cost_adj, and optimizer_index_caching。而其他引數，比如 hash_area_size

, sort_area_size， 引數值的改變就不是那麼危險了，可以在會話級進行改變以幫助CBO優化查詢。

●保證靜態的執行計劃
成功的CBO應用會通過謹慎管理統計資料來鎖定SQL執行計劃，同時保證儲存的優化計劃的穩定性，或者在具體的SQL語句中加入一些細節上的提示。
記住：重新分析一個系統的統計資料，可能會導致成千上萬的SQL語句改變其執行計劃。許多Oracle應用系統要求所有的SQL語句在測試環境中經過驗證，保證在

功能上和生產環境是一致的。

2.4.4 CBO的思考
儘管我們已經對CBO的不少細節有了瞭解，但是由於隨著Oracle新版本的不斷推出，CBO變得越來越強大，同時也越來越複雜，我們仍然有許多關於CBO的知識需要

學習。
下面是一些關於CBO調整的提綱性的建議，供準備進行CBO調整的DBA們思考。

●DBA可以提供一些Oracle引數的配置對CBO進行控制，但是隻能在有限的環境下謹慎的改變這些引數；

●CBO依靠統計資料來產生SQL語句的優化的執行計劃，可以通過 dbms_stats 包來分析、產生統計資料；

●DBA們的一項重要任務就是收集、管理CBO統計資料，這些資料可以被收集、儲存，也可以在相關的例項中進行移植，以保證執行計劃的連貫性。

●在沒有使用 export_system_stats 儲存過程匯出原來的統計資料以前，重新對系統的統計資料進行分析是十分危險的，因為成千上萬的SQL語句的執行計劃將

可能全部改變，而你卻不能恢復原來的SQL效能。只有在系統的資料發生巨大變化時，才可能需要對整個系統的統計資料進行重新分析。

本小節是關於CBO的一些技術討論，原文來自 Donald K. Burleson 在OTN上的一篇文章，具體URL路徑是：
http://otn.oracle.com/oramag/webcolumns/2003/techarticles/burleson_cbo_pt1.html 

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