Oracle的優化器(Optimizer)-Analysis
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
———————————————————————-
| 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