Oracle的優化器(Optimizer)-Analysis

Oracle的優化器(Optimizer)-Analysis

標籤: 
 
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
———————————————————————-
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
———————————————————————-
|   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