Oracle如何使用Optimizer_mode參數來控制優化器

這篇文章主要介紹了Oracle如何使用Optimizer_mode參數來控制優化器，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

Oracle使用Optimizer_mode參數來控制優化器的偏好，9i常用的幾個參數有:first_rows,all_rows,first_rows_N,rule

,choose等。而10g少了rule和choose.oracle 在執行SQL語句時，有兩種優化方法：即基于規則的RBO和基于代價的CBO。 在SQL執行的時候，到底采用何種優化方法，就由Oracle參數 optimizer_mode 來決定。

Rule Based Optimizer（RBO）基于規則
Cost Based Optimizer（CBO）基于成本，或者講統計信息

SQL> show parameter optimizer_mode;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

oracle10g開始默認是all_rows

ALL_ROWS不管是不是有統計信息，全部采用基于成本的優化方法，返回最大數的量數據

FIRST_ROWS_n不管是不是有統計信息，全部采用基于成本的優化方法并以最快的速度，返回前N行記錄

FIRST_ROWS使用成本和試探法相結合的方法，查找一種可以最快返回前面少數行的方法

第一個是返回所有行的最小代價，第二個是返回前N行的最小代價，最后一個是返回前面的少量行的最好計劃

修改全局 
alter system set optimizer_mode=all_rows;

修改當前session

alter session set optimizer_mode=all_rows;

幾個參數的具體解釋：

optimizer_mode =choose
這個是Oracle的默認值。采用這個值時，Oracle即可以采用基于規則RBO，也可以采用基于代價的CBO，到底使用那個值，取決于當前SQL的被訪問的表中是不是有可以使用的統計信息。如果有多個被訪問的表，其中有一個或多個有統計信息，那么Oralce會對沒有統計信息的表進行采樣統計（即不全部采樣），統計完成后，使用基于代價的優化方法CBO。如果所有被訪問的表都沒有統計信息，Oracle就會采用基于規則的優化方法RBO。
 
Optimizer_mode=First_rows
在oracle 9i之后這一選項已經過時，出于向后兼容的目的保留了這一選項，該選項的作用在于尋找能夠在最短的時間內返回結果集的第一行的執行計劃。這一規則傾向于促使優化器使用索引訪問路徑，偶爾會出現非常不恰當的訪問路徑。

設置為這種CBO模式以后，SQL語句返回結果的速度會盡可能的快，而不管系統全部的查詢是否會耗時較長或者耗系統資源過多。由于利用索引會使查詢速度加快，所以 first_rows 優化模式會在全表掃描上進行索引掃描。這種優化模式一般適合于一些OLTP系統，滿足用戶能夠在較短時間內看到較小查詢結果集的要求。

Optimizer_mode=all_rows
優化器將尋找能夠在最短的時間內完成語句的執行計劃。
設置為這種CBO模式以后，將保證消耗的所有計算資源最小，盡管有時查詢結束以后沒有結果返回。all_rows 的優化模式更傾向于全表掃描，而不是全索引掃描和利用索引排序，因此這種優化模式適合于數據查看實時性不是那么強的數據倉庫、決策支持系統和面向批處理的數據庫（batch-oriented databases）等。

Optimizer_mode=first_rows_N
N的值可以為1，10，100，1000，優化器首先通過徹底分析第一個連接順序來估計返回行的總數目。這樣就可以知道查詢可能獲得的整個數據集的片段，并重新啟動整個優化過程，其目標在于找到能夠以最小的資源消耗返回整個數據片段的執行計劃。

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這種優化模式了。

感謝你能夠認真閱讀完這篇文章，希望小編分享的“Oracle如何使用Optimizer_mode參數來控制優化器”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!