configuration

Oracle的優化器(Optimizer)-Analysis

標籤： Oracle

Oracle的優化器(Optimizer)有兩種優化方式, Hint也不例外，除了/*+rule*/其他的都是CBO優化方式

基於規則的優化方式(Rule-Based Optimization，簡稱為RBO)

基於成本的優化方式(Cost-Based Optimization，簡稱為CBO)

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

CBO(Cost-Based Optimization)方式：它是看語句的成本(Cost),這裡的成本主要指CPU和記憶體RAM。

優化器在判斷是否用這種方式時,主要參照的是表及索引的統計資訊。統計資訊給出表的大小、有多少行、每行的長度等資訊。這些統計資訊起初在庫內是沒有的，是做 Analyze 後才出現的，很多的時侯過期統計資訊會令優化器做出一個錯誤的執行計畫, 因些應及時更新這些資訊。

什麼是全表掃描?

全表掃描就是掃表表中所有的行，實際上是掃描表中所有的數據塊，因為Oracle中最小的存儲單位是Oracle block。

掃描所有的數據塊就包括高水位線以內的數據塊，即使是空數據塊在沒有被釋放的情形下也會被掃描而導致I/O增加。

在全表掃描期間，通常情況下，表上這些相鄰的數據塊被按順序(sequentially)的方式訪問以使得一次I/O可以讀取多個數據塊。

一次讀取更多的數據塊有助於全表掃描使用更少的I/O，對於可讀取的數據塊被限制於參數DB_FILE_MULTIBLOCK_READ_COUNT。

何時發生全表掃描?

a、表上的索引失效或無法被使用的情形(如對謂詞使用函數、計算、NULL值、不等運算符、類型轉換)

b、查詢條件返回了整個表的大部分數據

c、使用了並行方式訪問表

d、使用full 提示

e、統計信息缺失時使得Oracle認為全表掃描比索引掃描更有效

f、表上的數據塊小於DB_FILE_MULTIBLOCK_READ_COUNT值的情形可能產生全表掃描

查看Execution Plan:

scott@ORA11G> set autot trace exp; –>轉換成execution Plan

scott@ORA11G> select count(*) from t; —>count(*)的時候使用了索引快速掃描

Execution Plan

———————————————————-

Plan hash value: 454320086

———————————————————————-

———————————————————————-

| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| T_PK | 1000 | 2 (0)| 00:00:01 |

———————————————————————-

scott@ORA11G> set autot off; –>關閉execution Plan

假使明明一個 table 有建 index，而且你知道你的 sql script 使用 index 一定會比full table scan 快可以用下面的 sql 寫法

SELECT /*+INDEX (TableA Index_on_COL1) */ Col1, Col2, Col3

FROM TableA

WHERE COL1 LIKE '123%'

這樣就會強迫 oracle 用 Index_on_COL1 去抓 TableA 的資料，如果看 explain plan 還是沒有用 index 那就把 table 跟 index 都 analyze 一次應該就可以得到想要的結果了

Analyze Table語法：

SELECT 'ANALYZE TABLE '||OWNER||'.'||TABLE_NAME||' COMPUTE STATISTICS;'FROM ALL_TABLES WHERE OWNER='STGC5';

參考資料：

Oracle 全表掃描及其執行計劃(full table scan)及演練：

http://rritw.com/a/bianchengyuyan/C__/20130524/358606.html

Oracle SQL Optimizer Hints Concept 優化概念與範例

http://mistech.pixnet.net/blog/post/190247519-oracle-sql-optimizer-hints-concept-%E5%84%AA%E5%8C%96%E6%A6%82%E5%BF%B5%E8%88%87%E7%AF%84%E4%BE%8B