講解Oracle優化器的優化方式和優化模式

　　Oracle在執行一個SQL之前首先要分析一下語句的執行計劃然後再按執行計劃去執行分析語句的執行計劃的工作是由優化器(Optimizer)來完成的不同的情況一條SQL可能有多種執行計劃但在某一時點一定只有一種執行計劃是最優的花費時間是最少的相信你一定會用Pl/sql DeveloperToad等工具去看一個語句的執行計劃不過你可能對RuleChooseFirst rowsAll rows這幾項有疑問因為我當初也是這樣的那時我也疑惑為什麼選了以上的不同的項執行計劃就變了?

　　優化器的優化方式

　　

　　Oracle的優化器共有兩種的優化方式即基於規則的優化方式(RuleBased Optimization簡稱為RBO)和基於代價的優化方式(CostBased Optimization簡稱為CBO)

　　

　　ARBO方式優化器在分析SQL語句時所遵循的是Oracle內部預定的一些規則比如我們常見的當一個where子句中的一列有索引時去走索引

　　

　　BCBO方式依詞義可知它是看語句的代價(Cost)了這裡的代價主要指Cpu和內存優化器在判斷是否用這種方式時主要參照的是表及索引的統計信息統計信息給出表的大小 有少行每行的長度等信息這些統計信息起初在庫內是沒有的是你在做analyze後才出現的很多的時侯過期統計信息會令優化器做出一個錯誤的執行計劃因些我們應及時更新這些信息在Oracle及以後的版本Oracle列推薦用CBO的方式

　　

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

　　

　　優化器的優化模式(Optermizer Mode)

　　

　　優化模式包括RuleChooseFirst rowsAll rows這四種方式也就是我們以上所提及的如下我解釋一下

　　

　　Rule:不用多說即走基於規則的方式

　　

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

　　

　　First Rows:它與Choose方式是類似的所不同的是當一個表有統計信息時它將是以最快的方式返回查詢的最先的幾行從總體上減少了響應時間

　　

　　All Rows:也就是我們所說的Cost的方式當一個表有統計信息時它將以最快的方式返回表的所有的行從總體上提高查詢的吞吐量沒有統計信息則走基於規則的方式

　　

　　如何設定選用哪種優化模式

　　◆AInstance級別

　　

　　我們可以通過在initora文件中設定OPTIMIZER_MODE=RULEOPTIMIZER_MODE=CHOOSEOPTIMIZER_MODE=FIRST_ROWSOPTIMIZER_MODE=ALL_ROWS去選用所提的四種方式如果你沒設定OPTIMIZER_MODE參數則默認用的是Choose這種方式

　　

　　◆BSessions級別

　　

　　通過SQL> ALTER SESSION SET OPTIMIZER_MODE=;來設定

　　

　　◆C語句級別

　　

　　這些需要用到Hint比如:

　　SQL> SELECT /*+ RULE */ auserid bname bdepart_name FROM tf_f_yhda a tf_f_depart b WHERE auserid=buserid;

 為什麼有時一個表的某個字段明明有索引當觀察一些語的執行計劃確不走索引呢？如何解決呢？

　　

　　◆A不走索引大體有以下幾個原因

　　

　　你在Instance級別所用的是all_rows的方式

　　

　　你的表的統計信息(最可能的原因)

　　

　　你的表很小上文提到過的Oracle的優化器認為不值得走索引

　　

　　◆B解決方法

　　

　　可以修改initora中的OPTIMIZER_MODE這個參數把它改為Rule或Choose重起數據庫也可以使用中所提的Hint

　　

　　刪除統計信息

　　

　　SQL>analyze table table_name delete statistics;

　　表小不走索引是對的不用調的

　　

　　其它相關

　　

　　◆A如何看一個表或索引是否是統計信息

　　

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

　　◆B假如我們先用CBO的方式就應當及時去更新表和索引的統計信息以免生形不切合實的執行計劃

　　

　　SQL> ANALYZE TABLE table_name COMPUTE STATISTICS; SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;

From:http://tw.wingwit.com/Article/program/Oracle/201311/18622.html