Oracle 最佳化器__Oracle

一、最佳化器基本知識

Oracle在執行一個SQL之前,首先要分析一下語句的執行計畫,然後再按執行計畫去執行。分析語句的執行計畫的工作是由最佳化器(Optimizer)來完成的。不同的情況,一條SQL可能有多種執行計畫,但在某一時點,一定只有一種執行計畫是最優的,花費時間是最少的。

相信你一定會用Pl/sql Developer、Toad等工具去看一個語句的執行計畫,不過你可能對Rule、Choose、First rows、All rows這幾項有疑問,因為我當初也是這樣的,那時我也疑惑為什麼選了以上的不同的項,執行計畫就變了?

1、最佳化器的最佳化方式

Oracle的最佳化器最佳化方式：

RBO方式
即基於規則的最佳化方式(Rule-Based Optimization,簡稱為RBO)。最佳化器在分析SQL語句時,所遵循的是Oracle內部預定的一些規則。比如我們常見的,當一個where子句中的一列有索引時去走索引。

CBO方式
基於代價的最佳化方式(Cost-Based Optimization,簡稱為CBO)。依詞義可知,它是看語句的代價(Cost)了,這裡的代價主要指Cpu和記憶體。最佳化器在判斷是否用這種方式時,主要參照的是表及索引的統計資訊。統計資訊給出表的大小、有少行、每行的長度等資訊。這些統計資訊起初在庫內是沒有的,是你在做analyze後才出現的,很多的時侯到期統計資訊會令最佳化器做出一個錯誤的執行計畫,因些我們應及時更新這些資訊。在Oracle8及以後的版本,Oracle列推薦用CBO的方式。

我們要明了,不一定走索引就是優的 ,比如一個表只有兩行資料,一次IO就可以完成全表的檢索,而此時走索引時則需要兩次IO,這時對這個表做全表掃描(full table scan)是最好的。

新版本的oracle逐漸拋棄對Rule方式的支援,即使是Rule方式,最後sql執行效率的衡量標準都是,sql執行消耗了多少資源?對代價(COST)的最佳化方式,需要表,索引的統計資訊,需要每天多表和索引進行定時的分析,但是統計資訊也是曆史的,有時候也不一定是最優的,統計資訊等於就是一個人的經驗,根據以前的經驗來判斷sql該怎麼執行(得到最佳化的sql執行路徑),所以具體最佳化執行的時候,先手工分析sql,看是用RBO方式消耗大,還是CBO消耗大;DBA的工作就是要根據當前oracle的作業記錄,進行各種調整,使當前的oracle運行效率盡量達到最優.可以在運行期間,採用hint靈活地採用最佳化方式.

CHOOSE (選擇性)
設定預設的Oracle最佳化器，可以通過對init.ora檔案中OPTIMIZER_MODE參數的各種聲明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS . 你當然也在SQL句級或是會話(session)級對其進行覆蓋。
為了使用基於成本的最佳化器(CBO， Cost-Based Optimizer) ，你必須經常運行analyze 命令，以增加資料庫中的對象統計資訊(object statistics)的準確性。

如果資料庫的最佳化器模式設定為選擇性(CHOOSE)，那麼實際的Oracle最佳化器模式將和是否運行過analyze命令有關。如果table已經被analyze過， 最佳化器模式將自動成為CBO ， 反之，資料庫將採用RULE形式的最佳化器。
在預設情況下，Oracle採用CHOOSE最佳化器，為了避免那些不必要的全表掃描(full table scan) ，你必須盡量避免使用CHOOSE最佳化器，而直接採用基於規則或者基於成本的Oracle最佳化器。

2、最佳化器的最佳化模式(Optermizer Mode)

最佳化模式包括Rule,Choose,First rows,All rows這四種方式,也就是我們以上所提及的。如下我解釋一下：

Rule:不用多說,即走基於規則的方式。 (RBO最佳化方式)

Choolse:這是我們應觀注的,預設的情況下Oracle用的便是這種方式。指的是當一個表或或索引有統計資訊,則走CBO的方式,如果表或索引沒統計資訊,表又不是特別的小,而且相應的列有索引時,那麼就走索引,走RBO的方式。

在預設情況下，ORACLE採用CHOOSE最佳化器，為了避免那些不必要的全表掃描（full table scan），你必須盡量避免使用CHOOSE最佳化器，而直接採用基於規則或者基於成本的最佳化器。

First Rows:它與Choose方式是類似的,所不同的是當一個表有統計資訊時,它將是以最快的方式返回查詢的最先的幾行,從總體上減少了回應時間。 (CBO最佳化方式,提供一個最快的反應時間,根據系統的需求,使用方式)

All Rows:也就是我們所說的Cost的方式,當一個表有統計資訊時,它將以最快的方式返回表的所有的行,從總體上提高查詢的輸送量。沒有統計資訊則走基於規則的方式。 (CBO最佳化方式,提供最大的輸送量,就是使執行總量達到最大) [Page]

First Rows和All Rows是有衝突的.如果想最快第返回給使用者,就不可能傳遞更多的結果,這就是First Rows返回最先檢索到的行(或記錄);而All Rows是為了盡量將所有的結果返回給使用者,由於量大,使用者就不會很快得到返回結果.就象空車能跑得很快,重裝車只能慢慢地跑;

3、如何設定選用哪種最佳化模式

A、Instance層級

我們可以通過在init<SID>.ora檔案中設定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去選用3所提的四種方式,如果你沒設定OPTIMIZER_MODE參數則預設用的是Choose這種方式。
init.ora和init<SID>.ora都在$ORACLE_HOME/dbs目錄下,可以用find $ORACLE_HOME -name init*.ora查看該目錄下的init檔案.
init.ora是對全體執行個體有效;init<SID>.ora只對指定的執行個體有效.

B、Sessions層級

通過SQL> ALTER SESSION SET OPTIMIZER_MODE=<Mode>;來設定。將覆蓋init.ora,init<sid>.ora設定的最佳化模式,也可以在sql語句中採用hint強制選定最佳化模式.如下:

C、語句層級

這些需要用到Hint,比如:
SQL> SELECT a.userid,
2 b.name,
3 b.depart_name
4 FROM tf_f_yhda a,
5 tf_f_depart b
6 WHERE a.userid=b.userid;
在這兒採用hint,強制採用基於規則(rule)的最佳化模式;
hint文法,結尾,中間填寫強制採用的最佳化模式.

4、為什麼有時一個表的某個欄位明明有索引,當觀察一些語的執行計畫確不走索引呢。如何解決呢。

A、不走索引大體有以下幾個原因
♀你在Instance層級所用的是all_rows的方式
♀你的表的統計資訊(最可能的原因)
♀你的表很小,上文提到過的,Oracle的最佳化器認為不值得走索引。

B、解決方案
♀可以修改init<SID>.ora中的OPTIMIZER_MODE這個參數,把它改為Rule或Choose,重起資料庫。也可以使用4中所提的Hint.
♀刪除統計資訊
SQL>analyze table table_name delete statistics;
♀表小不走索引是對的,不用調的。

不走索引的其它原因：

1、建立複合式索引，但查詢謂詞並未使用複合式索引的第一列，此處有一個INDEX SKIP SCAN概念。

2、在包含有null值的table列上建立索引，當時使用select count(*) from table時不會使用索引。

3、在索引列上使用函數時不會使用索引，如果一定要使用索引只能建立函數索引。

4、當被索引的列進行隱式的類型轉換時不會使用索引。如:select * from t where indexed_column = 5，而indexed_column列建立索引但類型是字元型，這時Oracle會產生隱式的類型轉換，轉換後的語句類似於select * from t where to_number(indexed_column) = 5，此時不走索引的情況類似於case3。日期轉換也有類似問題，如: select * from t where trunc(date_col) = trunc(sysdate)其中date_col為索引列，這樣寫不會走索引，可改寫成select * from t where date_col >= trunc(sysdate) and date_col < trunc(sysdate+1)，此查詢會走索引。

5、並不是所有情況使用索引都會加快查詢速度，full scan table 有時會更快，尤其是當查詢的資料量占整個表的比重較大時，因為full scan table採用的是多塊讀，當Oracle最佳化器沒有選擇使用索引時不要立即強制使用，要充分證明使用索引確實查詢更快時再使用強制索引。

6、<>

7、like’�’百分比符號在前。

5、其它相關

A、如何看一個表或索引是否是統計資訊

SQL>SELECT * FROM user_tables 2 WHERE table_name=<table_name>
3 AND num_rows is not null;

SQL>SELECT * FROM user_indexes
2 WHERE table_name=<table_name>
3 AND num_rows is not null;

b、如果我們先用CBO的方式,我們應及時去更新表和索引的統計資訊,以免生形不切合實的執行計畫。
SQL> ANALYZE TABLE table_name COMPUTE STATISTICS;
SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;

二、最佳化器的二十六個參數

影響系統效能類可變參數

(1)CHECKPOINT_PROCESS該參數根據是否要求檢查點而設定成TRUE或者FALSE。當所有緩衝區的資訊寫到磁碟時，檢查點進程（CHPT）建立一個靜態點。在歸檔記錄檔中做一個記號表示有一個檢查點發生。檢查點發生在歸檔日誌轉換的時候或當達到log_checkpoint_interval定義的塊數的時候。當設定此參數為TRUE時，後台進程CHPT便可工作。在檢查點期間內，若日誌寫進程（LGWR）的效能減低，則可用CHPT進程加以改善。

(2)DB_BLOCK_CHECKPOINT_BATCH該參數的值設定得較大時，可加速檢查點的完成。當指定的值比參數DB_BLOCK_CHECKPOINT_BATCH大時，其效果和指定最大值相同。

(3)DB_BLOCK_BUFFERS該參數是在SGA中可作緩衝用的資料庫塊數。該參數決定SGA的大小，對資料庫效能具有決定性因素。若取較大的值，則可減少I/O次數，但要求記憶體空間較大。每個緩衝區的大小由參數DB_BLOCK_SIZE決定。

(4)DB_BLOCK_SIZE該參數表示Oracle資料庫塊的大小，以位元組為單位，典型值為2048或4096。該值一旦設定則不能改變。它影響表及索引的FREELISTS參數的最大值。

(5)DB_FILES該參數為資料庫運行時可開啟的資料檔案最大數目。

(6)DB_FILE_MULTIBLOCK_READ_COUNT該參數表示在順序掃描時一次I／O操作可讀的最大塊數，該最大塊數取決於作業系統，其值在4至16或者32是比較好。

(7)D1SCRETE_TRANSACTION_ENABLED該參數實現一個更簡單、更快的復原機制，以改進某些事務類型的效能。當設定為TRUE時，可改善某些類型的事務效能。

(8)LOG_ARCHIVE_BUFFER_SIZE此參數的值依賴於作業系統，它與LOG_ARCHIVE_BUFFER 參數一起用於調整有歸檔日誌的運行，使其運行速度盡量加快，但不能快到降低效能。僅當直接歸檔到磁帶裝置時才需要增加這些參數的值，重做日誌緩衝區要等待歸檔日誌緩衝區變得可用。

(9)LOG_ARCHIVE_BUFFER該參數指定用于歸檔的日誌時的緩衝區數。

(10)LOG_BUFFER該參數指明分配給SGA中的日誌緩衝區的位元組數，該參數值較大時，可減少日誌I／O的次數。對於繁忙的系統不宜採用大於或等於64K的值。預設值-般為資料庫塊的4倍。

(11)LOG_CHECKPOINT_TIMEOUT該參數指明兩個檢查點之間的時間間隔，若指定為0時，則說明不允許進行基於時間的檢查點。

(12)LOG_CHECKPOINT_INTERVAL該參數用來確定檢查點進程的執行頻率。這個數值設定成取檢查點之前處理的重做緩衝區塊的數量。

(13)LOG_FILES該參數指定運行期間資料庫可開啟的記錄檔數。若需要較大的SGA空間，而不需多個記錄檔，則可減少該值。

(14)LOG_SIMULTANEOUS_COPIES該參數是日誌緩衝區副本閂鎖的最大數，為同時寫日誌項所用。為提高效能，可設定此參數為兩倍的CPU數，對單進程系統，該值多數設定為0，此時斷開閂鎖。

(15)LOG_SMALL_ENTRY_MAX_SIZE該參數與LOG_SIMULTANEOUS_COPIES參數配合使用。若日誌項大於此項，則在給緩衝區分配空間並獲得日誌複製閂鎖之後，使用者進程釋放日誌複製閂鎖。

(16)OPTIMIZRER_MODE若該參數的值為RULE，則Oracle最佳化器選擇基於規則的最佳化；若設定為COST，並且在資料字典中存在有統計資訊，則Oracle最佳化器選擇基於代價的最佳化方法進行最佳化。

(17)SEQUENCE_CACHE_ENTRIES該參數指明在SGA中可進行快取的序列數，用於直接存取。該快取區是基於最近最少使用(LRU)的演算法進行管理的。若此值設定得較高，則可達到較高的並發性。

(18)SEQUENCE_CACHE_HASH_BUCKETS該參數用於加速查看高速緩衝區最近請求的最新序列的桶式地址數，每個桶式地址佔8個位元組。高速緩衝區以散列表排列，該參數應為質數。

(19)SERIALIZEABLE此參數用於保證重複讀的一致性。當它設定為TRUE時，查詢可保證表級讀一致，以防止在此查詢提交之前的修改。

(20)SHARED_POOL_SIZE該參數指定共用池的大小，其中包括共用游標及預存程序。在多使用者系統中，較大的SHARED_POOL_SIZE值可改善SQL語句的執行效能，但較小的值可節省記憶體。

(21)SMALL_TABLE_THRESHOLD該參數決定SGA中用於掃描的緩衝區的數目，若表的數目小於該值，則該表可整個地讀入快取區。若表大於該值，則立即重用該緩衝區。一般用預設值可使效能最好。

(22)SORT_AREA_TETAINED_SIZE這是會話記憶體的最大數量,用於記憶體排序。當從排序空間提出最後-行時，便釋放該記憶體。若排序要較大的記憶體，則分配一臨時段，排序便可在盤上進行。用於排序的最大總量可由SORT_AREA_SIZE指定，而不用此參數。可以分配同樣大小的多個排序空間，不過一般對於複雜的查詢才需要。

(23)SORT_AREA_SIZE該參數用於指定進行外排序(磁碟)時所需PGA記憶體的最大數量，以位元組為單位。當排序行寫入磁碟時，該記憶體被釋放。增大該參數的值，可改進排序效率。一般不調整該參數，除非排序量很大時才調整。

(24)SORT_SPACEMP_SIZE該參數僅在排序量很大時才調整該參數。可用下式設定該參數，使排序能最佳地使用盤空間。

(25)SQLTRACE該參數設定為TRUE時，便可跟蹤，以獲得改善效能的資訊。因為跟蹤會增加開銷，所以一般僅在收集資訊時才置為TRUE。在實際使用時，可用ALTER SESSION命令覆蓋它。

(26)TRANSACTION該參數設定並發事務的最大數。若此值較大，則需增加SGA空間和分配的復原段數量。預設值大於PROCESS時，可允許遞迴事務。

 

 

Oracle最佳化器存在兩種最佳化方式：基於規則的最佳化方式RBO(Rule-Based Optimization)和基於代價的最佳化方式CBO（Cost-Based Optimization），其中RBO方式在oracle10g中已經摒棄。

    RBO是一種基於規則的最佳化方式，最佳化器在分析SQL語句時遵循oracle內部預定的一些規則，如，當一個where子句中的一列有索引時就走索引。RBO根據可用的訪問路徑和訪問路徑的等級來選擇訪問計劃，等級越高的訪問路徑運行SQL速度越慢，如果有多個路徑，則選用等級較低的路徑。

    CBO是一個基於代價或成本的最佳化方式，代價或成本即記憶體和CPU 的消耗。最佳化只要是根據可用的訪問路徑、表或索引的統計資訊等來選擇一個成本最低的訪問路徑。所以要及時更新統計資料，避免到期的統計資訊讓最佳化器選擇一個錯誤的執行計畫。

       CBO主要包含查詢轉化器（Query Transformer）、評估器（Estimator）和計劃產生器（Plan Generator）。

    一、查詢最佳化工具，由於查詢SQL語句的形式可能會影響所產生的執行計畫，查詢轉化器就是改變查詢語句的形式以獲得更為高效的執行計畫，Oracle主要提供四種轉化技術：

  1.視圖合并（View Mergin）:查詢語句含有視圖時，會產生獨立的“視圖查詢塊”，影響語句的整體性，會產生不良的執行計畫。視圖合并會去掉“視圖查詢塊”，將視圖合并至一個整體的查詢塊中，使執行計畫的整體性得到提升。

  2.謂詞推進（Predicate Pushing）:這項技術是將不能進行合并的視圖相應的謂詞推進到視圖查詢塊中。這些謂詞通常是可索引或有較強過濾性的。

   3.非嵌套子查詢（Subquery Unnesting）:子查詢和視圖一樣，會被放進獨立的查詢塊，查詢轉換器會將大多數子查詢轉為串連放進同一個查詢塊，不能合并的按照一個高效的方式排列。

   4.物化視圖的查詢重寫：當query_rewrite_enabled=true時，轉化器會尋找與該語句相關聯的物化視圖，並將該語句改寫。

   二、評估器，評估器通過計算三個值來評估計劃的總成本：選擇性（Selectivity）、基數（Cardinality）、成本（Cost）。

    1.選擇性：是一個大於0小於1的數，0表示沒有記錄被選定，1表示所有記錄都被選定。統計資訊和長條圖關係到選擇性值的準確性。如：name=’Davis’，如果不存在統計資訊評估器將根據所用的謂詞來指定一個預設的選擇性值，此時評估器會始終認為等式謂詞的選擇性比不等式謂詞小；如果存在統計資訊而不存在長條圖，此時選擇性值為1/count(distinct name)；如果存在統計資訊也存在長條圖，選擇性值則為count（name）where name=’Davis’/ count（name）where name is not null。

    2.基數：通常表中的行數稱為“基礎基數”（Base cardinality）；當用WHERE中的條件過濾後剩下的行數稱為“有效基數”（Effective cardinality）；串連操作之後產生的結果集行數稱為“串連基數”（Join cardinality）；一個欄位DISTINCT之後的行數稱為“DISTINCT基數”等等。

    3.成本：就是度量資源消耗的單位。可以理解為執行表掃描、索引掃描、串連、排序等操作所的消耗I/O、CPU、記憶體。

    三、計劃產生器，最後是計劃產生器的作用就是產生大量的執行計畫，然後選擇其中總體成本最低的一個，如果它發現當前執行計畫的成本已經很低了，它將停止實驗，相反當前計劃的成本如果很高，它將繼續實驗其他執行計畫。