本文原題《ORACLE資料庫伺服器IO高的分析方案和案例探討》

目錄：

慧眼識珠——伺服器磁碟這麼繁忙，到底是誰幹的？

謹記於心——ORACLE DBA判斷IO有效能問題的標準

帶刀侍衛——處理IO問題必須掌握的一個ORACLE工具

說難不難——用幾句話來說清ORACLE資料庫

活得明白——一個例子說明ORACLE的工作過程

牢記於心——一幅圖來總結ORACLE的IO特點

怎麼破——什麼是無效IO以及解決方法

作者：黃遠邦（小y），就職於北京中亦安圖科技股份有限公司，任資料庫團隊技術總監。長期活躍於國內多家銀行總行生產資料中心,提供Oracle第三方服務，解決過無數疑難問題，在業內具有極佳的口碑。

前言

遠邦在為資料中心提供Oracle第三方服務的過程中接觸過很多系統/儲存管理員，發現很多SA對ORACLE資料庫缺乏足夠的瞭解，導致在處理綜合問題時容易各說各話，因此才有了寫這一個系列文章的想法，本意是儘可能用大白話為大家普及一些常見問題所需的理論，輔以幾個實際的案例分析，希望對大家以後的工作有所幫助。

言歸正傳，在部署了ORACLE資料庫的伺服器上,我們大家或多或少的遇到過下列情況：
1. 業務系統執行緩慢，作為系統管理員需要檢查包括IO在內的系統資源，這時系統管理員、儲存管理員可能得到DBA（資料庫管理員）的反饋說，IO的響應時間很慢，達到了30毫秒以上，要求解決。但儲存管理員檢查又不存在熱點盤的情況，系統的IO量就是很大，除了使用更多的RAID組來重新分佈資料、更換為更高階的儲存外，似乎沒有太好的辦法；
2. 我們可能透過iostat和sar -d命令觀察到磁碟的busy很高、每秒的IOPS很高、每秒的IO讀寫量很大、HBA卡的流量很高等危險的現象；
3. IO響應時間長，到底是導致業務慢的原因還是結果？
4. IOPS很高、IO讀寫量很大，到底是原因還是結果？
5. 除了硬體的擴容或升級，難道沒有別的解決方法麼？
6. 如何識別ORACLE伺服器上的IO來源，如何判斷這些IO是否是有效IO,怎麼消除無效IO？
7. 作為系統管理員和儲存管理員需要掌握哪些資料庫簡單技能才不會在出現IO問題時處於被動的局面？
8. ORACLE DBA評判IO是否有效能問題的標準是什麼？
9. ORACLE資料庫的IO有什麼特點？哪些IO是比較關鍵，是必須保障效能的？
我們將透過理論和實際案例穿插介紹的方式為大家進行講解和分享，希望對大家有所啟發。
本文是系列的第一篇。需要說明的是，由於篇幅有限，會暫時省略掉部分在過程中實際發生但與本主題不是那麼密切的內容，如UNDO、checkpoint等內容。
同時考慮到AIX專傢俱樂部可能更多的是系統/儲存管理員，因此會有部分科普的內容，水平較好的ORACLE DBA可以自行跳到案例探討環節。對於沒有真正做過DBA的同學來說，ORACLE可能稍微有點難，但只要靜下心來花點時間去主動了解了，那就不難了。

慧眼識珠--伺服器磁碟這麼繁忙，到底是誰幹的？

出一個問題時段的AWR報告，將awr報告對應的html檔案下載到PC終端
用IE等瀏覽器開啟，查詢”SQL ordered by Reads”部分,如下圖所示

可以看到：
1) 排名第一的SQL語句，佔了整個資料庫伺服器IO的99.79%
2) 一共執行了8次，每次執行發生的IO是2，129,053個BLOCK，一個BLOCK是8K，即每次執行該SQL，將發生2，129,053*8K=16.24G

知識點：
BLOCK是ORACLE資料檔案的最小分配單元，類似LV的PP,資料就儲存在BLOCK中，一個BLOCK可以儲存幾十到幾百條不等的使用者表的資料

是不是很簡單？
當然，大家也可以用作業系統的命令也可以看到程式級的IO分佈情況。
例如Linux環境下透過pidstat -d 可以監控哪個程式IO消耗較高。
當然，採用作業系統方式檢視程式級別的IO分佈的方式的缺點是很顯然的，更可怕的是，這表明你依然在把資料庫當黑盒子來看待，程式具體在做什麼？為什麼IO那麼高? 
我們需要繼續往前一步。

謹記於心--ORACLE DBA判斷IO有效能問題的標準

知識點：
一般來說，如果單個IO的響應時間在20毫秒以內，是可以接受的，較好的效能應該在10個毫秒以下，越低越好。超過20毫秒的單個IO響應時間，則可認為效能不佳，需要做調優。需要說明的是，對於IO次數只有個位數的檔案，IO超過20毫秒，也是可以接受的，因為在儲存層面不容易被cache。

透過OS和資料庫AWR報告兩個方式均可以判斷IO是否有問題，建議以OS方式為準。

1. 作業系統方式
   sar –d 2 10的輸出中，avwait和avserv兩列之和即為IO的響應時間（AIX環境），單位為毫秒。LINUX環境下有區別，IO的響應時間為AVWAIT列。
   
   可以看到：
   hdisk4上單個IO的響應時間達到4000多毫秒和2000多毫秒，遠遠大於20毫秒，IO效能到了無法忍受的地步，需要儘快分析是否儲存儲存cache被關閉，硬碟是否出現故障、鏈路是否出現問題等情況。

2. 資料庫AWR報告方式
   下圖的Av Rd(MS)表示單次讀的毫秒數，即為單個IO的響應時間。可以看到，在0.01毫秒，遠遠低於20毫秒，IO效能非常的好！（能達到整個效能，往往是在檔案系統快取中被快取了）
   
   下圖的Av Rd(MS)表示單次IO讀的毫秒數，即為單個IO的響應時間。可以看到，大部分資料檔案的IO響應時間超過40毫秒，遠遠大於20毫秒，IO效能不理想，在對儲存進行擴容或者升級前，應該先好好分析IO是否是無效IO,是否可以消除無效IO！透過SQL最佳化消除無效IO，可以有效保護儲存等硬體的投資，滿足未來多年的業務發展，而不是盲目擴容。
   

帶刀侍衛--處理IO問題必須掌握的一個ORACLE工具

上述的AWR報告是怎麼獲得的呢？什麼是AWR報告呢?容許我囉嗦一下
很多同學可能聽過AWR報告，收集AWR報告的步驟是固定的，很簡單，步驟如下：

	
	
	

		
		

			#su – oracle 
		

		

			$sqlplus “/ as sysdba” 
		

		

			SQL>exec dbms_workload_repository.create_snapshot(); 
		

		

			SQL>@?/rdbms/admin/awrrpt.sql 
		

		

			依次輸入 
		

		

			1) Html 
		

		

			2) 回車 
		

		

			3) 輸入想要抓取的時間範圍所對應的開始snap_id 
		

		

			4) 輸入想要抓取的時間範圍所對應的結束snap_id 
		

		

			5) 輸入想要儲存為報告的名稱（自己選個名字即可） 
		

		

			6) 刷屏出報告中…… 
		

	

透過AWR報告這個資料庫內建的工具，可以清楚的瞭解到某個時段，資料庫中到底執行了哪些SQL，產生了多少IO，消耗了多少CPU。

也許有人會說，AWR報告我已經會出了，但看懂AWR報告才是關鍵。
是的，你需要更深入地瞭解一些資料庫知識，不妨耐心往下看。

說難不難--用幾句話來說清ORACLE資料庫

ORACLE資料庫，簡單來說，主要就是對外提供資料儲存服務，同時可以透過內建的豐富的SQL/PLSQL介面，對外提供資料檢索、比對、關聯等計算服務。
具體一點來說，ORACLE資料庫透過伺服器上的一組程式（後臺程式與前臺程式）和記憶體結構，對儲存上的資料進行讀寫和計算。
知識點
ORACLE例項和ORACLE資料庫的區別是什麼？
做SA的很多同學一直不太清楚什麼是ORACLE例項，其實很簡單：
程式和記憶體結構加起來就稱之為ORACLE例項即instance。

因為程式和記憶體是隨著資料庫/OS重啟而消失的，因此oracle例項中不恆久的儲存資料
我們的使用者資料，最終是存放在磁碟中的，具體來說，是存在在磁碟中的資料檔案中。
資料檔案、控制檔案、線上日誌檔案，這些物理上存在的檔案組成了我們傳統所說的ORACLE資料庫。

知識點
ORACLE是一種典型的C/S架構麼？
答：是的。
我們知道，客戶端或者應用程式是透過SQL語句和資料庫互動的。
當客戶端或應用程式想連線到資料庫執行SQL語句來完成查詢和增刪改資料時，ORACLE伺服器上預設地，將為每一個客戶端對應地建立一個專有的服務程式（在作業系統上將看到LOCAL=NO的程式）來單獨的為這個客戶端服務，這個專有的服務程式幫助這個客戶端執行相關SQL，當執行完SQL時，這個程式也不能做別的，就只能等著客戶端發起的下一條SQL。這就好比，我們去一個高階的餐廳，有一個專屬的服務員為我們服務。

我們接下來用一幅圖來總結上述的內容，即ORACLE的簡單架構

可以看到：
每個客戶端要執行SQL，只需要透過網路將SQL傳到對應的服務程式，由服務程式幫忙執行即可。多個客戶端則對應多個服務程式（見上圖中的LOCAL=NO專屬前臺程式），這些服務程式我們也稱之為前臺程式。

ORACLE包含記憶體結構和後臺程式

1. SGA共享記憶體，又可細分為
   1) Buffer cache,用來快取最近訪問的資料，避免出現IO。Buffer cache中的資料可能比磁碟中的資料要新，例如讀進記憶體後再修改為新的資料。我們稱只要的資料為髒資料，髒塊。
   2) Log buffer，用一組記錄來表示對資料庫的修改過程，我們稱之為改變向量。
   3) 其他如shared pool/large pool/java pool/stream pool等，不在此介紹

2. ORACLE後臺程式，又可細分為
   1) DBWR程式，由於ORACLE定期會像word那樣暫存一下最近所做的修改（我們稱之為檢查點checkpoint觸發DBWR寫髒資料），就是將buffer cache中的髒資料寫回磁碟中的資料檔案。這個IO屬於隨機寫。
   2) LGWR程式，在提交commit命令發出時，將log buffer中的修改記錄以同步IO的形式寫到磁碟中的線上日誌檔案後返回，commit才能完成，此時雖然buffer cache記憶體中的資料比磁碟中的資料檔案中的資料要新，但是因為已經確保有一份修改過程寫到了磁碟的線上日誌檔案，這個時候即使資料庫掉電，也可以透過重新執行線上日誌檔案的修改記錄，來保證資料不出現丟失。這在任何關係型資料庫中常見的“日誌先行”策略。由於lgwr程式採用追加寫的方式把改變向量寫到線上日誌檔案後面，因此LGWR的IO屬於連續寫。
   3) 其他以ora_開頭的oracle後臺程式，如pmon/smon/ckpt,不在此介紹

知識點：
LGWR程式的IO是否支援寫到檔案系統快取就返回？
不支援，LGWR程式的IO是透寫的。如果只寫到檔案系統快取（如果資料檔案存放在檔案系統快取）就返回，則一旦系統crash,檔案系統快取來不及刷到磁碟，則會出現使用者commit後已經提示修改成功，但由於log buffer/buffer cache中的改變最終沒有落盤而出現資料丟失的情況。

LGWR程式的IO是非同步IO麼？ 
不是，因為要確保資料不丟失，lgwr必須等IO返回才會接著處理下一個IO寫請求。

ORACLE架構中最大的瓶頸在哪裡？ 
在ORACLE 12C之前，Lgw後臺程式只有1個,由於所有程式在commit前都需要通知lgwr程式幫忙把之前在log buffer中生成的修改過程記錄(改變向量)寫到磁碟中。當大量程式要同時請lgwr程式幫忙寫時，就出現排隊的情況。在高併發的聯機交易OLTP系統中，單程式的lgwr程式有可能成為一個大瓶頸，特別是在無法保證線上日誌IO寫效能的情況下，很容易出現排隊等lgwr程式的情況。這其實也是很容易引發問題的一個點，是ORACLE一個相對脆弱的地方。

知識點：
為什麼ORACLE那麼吃記憶體？伺服器一半左右的記憶體都被ORACLE吃掉了…
因為IO相比記憶體要慢非常多，因此很多關係型資料庫為了更好的效能，大量採用記憶體換IO的策略,ORACLE也不例外，具體來說，ORACLE利用SGA中的buffer cache來快取最近訪問的資料，從而避免再次訪問時需要發生IO。Buffer cache通常會佔到SGA大小的80%，即buffer cache佔到伺服器記憶體的50%*0.8=40%左右。

每秒800M IO流量--案例分享（一）

1. 問題描述：
   客戶反映，資料庫伺服器的IO量非常大，達到每秒800M，幾乎將HBA的頻寬打滿,交易出現緩慢的情況。

2. 分析過程：
   收集當前時段的Oracle AWR報告，找到Load Profile部分，如下圖所示
   
   可以看到:
   每秒的物理讀Physical Reads達到104，207個BLOCK，每個BLOCK的大小是8K
   即每秒的IO量達到104207*8K=814M！
   IO的流量太大了，幾乎打滿了HBA所支援的吞吐，影響到整體的效能是必然的！

根據上述知識點，我們知道，ORACLE採用記憶體換IO的策略，避免出現過多IO。
那麼我們不妨猜一下，是否是因為buffer cache太小導致IO無法快取呢？
進一步檢查AWR報告中的”Cache Sizes”部分
如下圖所示，buffer cache大小僅為128M！而shared pool大小為15,232M.
而該伺服器配置了60G的記憶體！
我們提到，buffer cache可以設定到伺服器記憶體的40%，即24G。

透過查詢AWR報告中的”Buffer Pool Advisory”，如下圖所示，可以看到：
當buffer cache從128M設定到256M時，IO即物理讀的個數將從3600萬下降到1900萬，就下降了一倍！

3. 原因
   難道是配置失誤？不是的。實際上，客戶透過一個memory_target引數設定了為ORACLE資料庫分配總計40G的記憶體。這是一種常見的做法，即交給ORACLE來動態分配在SGA和PGA之間，在SGA內部各個元件的記憶體大小。由於記憶體動態調整演算法的不完善，導致過多的記憶體分給了PGA而SGA不夠,又由於應用程式繫結變數的使用不夠理想，導致shared pool不斷的膨脹，一步一步地，將buffer cache壓縮到了只剩128M.

4. 解決方法
   為buffer cache設定一個基準值20G後，IO高的問題得到解決。 
   我們首先接觸了第一個IO高的案例，接下來，我們透過一個例子來進一步的學習ORACLE的工作過程，以及更多的瞭解ORACLE的IO特點。

活得明白--一個例子說明ORACLE的工作過程

當客戶端或者應用程式發起一條UPDATE語句時，到底經歷了哪些事情和那些IO？

簡化後的過程如下：
客戶端連線到資料庫後，資料庫伺服器上將建立一個LOCAL=NO的程式來專門為這個客戶端服務

1. 客戶端發起update T set id=5 where id=3的SQL語句，其中表T的大小為1G，表上不存在任何索引
2. 伺服器上的服務程式（LOCAL=NO），將判斷表T的資料BLOCK在記憶體中的buffer cache是否存在，如果不存在，則由服務程式發起IO，從磁碟先後將1G的資料讀到記憶體中。具體來說，是先讀取16個BLOCK即128K（一個BLOCK可以儲存幾十到幾百條不等的記錄），然後逐個判斷這些BLOCK中是否存在id=3的資料。這個IO屬於隨機讀，由LOCAL=NO前臺程式來發起IO。
3. 最後在記憶體中同時存在一個BLOCK，BLOCK中存在id=3這條滿足條件的記錄
4. 接下來，在前臺程式將id=3修改為id=5之前，需要先在log buffer中生成哪個BLOCK哪個位置從3修改為5的過程的對應記錄（改變向量）

5. 將記憶體buffer cache中的id=3修改為id=5，見下圖中中的步驟5
   

6. 客戶端發起commit

7. 因為一旦commit,則表示資料持久化，即表示將不會隨著資料庫crash/os 重啟而丟失，因此，此時lgwr程式需要將log buffer中id=3修改為id=5的記錄寫到磁碟中的線上日誌檔案。這個IO只能是同步IO（非非同步IO），等IO確認寫到磁碟後，步驟6的commit完成，返回客戶端的結果為修改成功。此時雖然buffer cache記憶體中的資料(id=5)比磁碟中的資料檔案中的資料(id=3)要新，但是因為已經確保有一份修改過程(id=3修改為id=5)寫到了磁碟的線上日誌檔案，這個時候即可資料庫掉電，也可以透過重演線上日誌檔案的修改記錄(id=3修改為id=5)，來保證資料最終被修改為5（當機前的狀態），即保證了資料不會出現丟失。
8. 當發生checkpoint等場景時，DBWR程式將記憶體buffer cache中的髒資料(記憶體比磁碟資料要新)寫到磁碟中的資料檔案，這個過程是非同步的。

知識點：
ORACLE為什麼不把buffer cache中的髒資料也實時地在commit提交時刷到磁碟中？
Lgw程式將log buffer中的修改記錄（改變向量）寫到磁碟中的線上日誌檔案，是採用追加寫到線上日誌檔案後面，因此LGWR的IO屬於連續寫。
而記憶體中的資料刷到磁碟中，屬於隨機寫(每個客戶端每次可能改不同物理位置的記錄)，隨機寫的效能顯然不如連續寫的效能好，因此ORACLE允許髒資料的存在，而不是實時地在commit提交時將髒資料刷到磁碟中，採用非同步的方式往下寫即可，因為lgwr的連續寫已經保證了資料不會出現丟失。

壓測TPS上不去--案例分享（二）

1. 問題描述：
   客戶新上的一個關鍵業務系統，在做上線前的壓力測試時，應用的併發無法達到上線前的併發指標和響應時間指標要求。壓測時TPS的曲線很不穩定，如下所示：
   

2. 分析過程：
   從上述知識點可以知道：
   ORACLE中LGWR程式只有一個，由於所有程式在commit前都需要通知lgwr程式幫忙把之前在log buffer中生成的修改過程記錄(改變向量)寫到磁碟中。
   當大量程式要同時請lgwr程式幫忙寫時，就出現排隊的情況。
   在高併發的聯機交易OLTP系統中，單程式的lgwr程式有可能成為一個大瓶頸，特別是在無法線上日誌IO寫效能出現問題的情況下。
   因此，我們需要檢查lgwr程式的狀態。
   透過gv$session觀察RAC兩個節點lgwr程式寫日誌的情況，結果如下圖所示：
   

可以看到：
RAC(資料庫叢集)兩個節點中，只有1個節點出現log file parallel write的等待，該等待表示lgwr程式正在對磁碟的線上日誌檔案進行寫操作。
在state是waiting的情況下，節點1 log file parallel等待的seq#都是35693，但是seconds_in_wait達到了21秒。簡單來說，就是lgwr程式寫一個IO需要21秒！

這意味著，壓測時所有併發程式必須要發生等待，等lgwr程式完成這個的IO，才可以繼續通知LGWR程式幫忙刷log buffer的改變向量，因此從壓測的TPS曲線來看，就是不穩定，出現了大幅衰減。

至此，我們可以肯定，IO子系統有問題
需要重點排查IO路徑下的光纖線、SAN交換機、儲存的報錯和效能情況。

考慮到客戶那邊管儲存的團隊/部門可能不承認資料庫的IO慢的證據，同時為了讓對方增加排查力度，遠邦讓客戶發出以下命令，檢視多路徑軟體的IO情況，結果如下圖所示：

節點1上出現明顯的IO ERROR，並且在持續增加！
繼續檢查節點2，發現節點2上沒有任何IO ERROR！
這個與gv$session僅有一個程式在等log file parallel write寫完是完全吻合的。

3. 原因
   在鐵的證據面前，客戶的儲存團隊沒有再掙扎，而是開始認認真真逐個在排查，最終在更換了光纖線後問題得到圓滿解決。以下是更換光纖線後再次壓測的等待事件！

4. 問題得到解決
   壓測的TPS曲線從原來的波浪形
   
   變成了如下的良好曲線
   

牢記於心--一幅圖來總結ORACLE的IO特點

下圖顯示了資料庫關鍵IO的特點

知識點

1. 讀資料由前臺程式各自來完成，IO的特點是隨機讀，將資料讀取到記憶體中再進行操作
2. DBWR程式的IO特點是隨機寫，DBWR程式支援多個程式同時往下刷資料，ORACLE為了避免在某一個時間點大量往下寫髒塊而導致磁碟壓力過大，會採用非同步地、慢慢地方式來刷髒塊，減少IO壓力，但當發出checkpoint和archive log current/all命令時，將啟用大量DBWR程式全力往下刷髒塊，可能對IO造成較大壓力而影響整體效能，影響LGWR程式的寫效能和響應時間。
3. Lgwr程式是連續寫，有條件的情況下儘量為Redo分配單獨的RAID組，物理上和其他檔案分開。

併發大時效能下降嚴重--案例分享（三）

1. 問題描述：

收到客戶的郵件，原來客戶在進行X86和小機的效能對比測試時出現以下問題：
一個高階pc server，記憶體32G，cpu是32核
pc server上測試8個語句並行，小機下降不明顯，但在此pc server上卻下降明顯。
執行時間由1分多，降到7分鐘。小機上始終是2分鐘。
附件是AWR報告，請分析原因。

2. 分析過程：
3.   
4. 8個並行測試的情況下，邏輯讀=物理讀， 邏輯讀表示操作記憶體中的BLOCK的個數，透過物理讀（IO）讀進記憶體後必然發生邏輯讀。 這說明ORACLE根本沒能把資料快取到共享記憶體buffer cache，以便供其他程式複用。 從AWR報告中的等待事件可以看到，排在第一位的是direct path read,這是一個IO事件 即上面說的，繞開BUFFER CACHE，直接讀到PGA私有記憶體（非共享記憶體）中時,單次IO的時間達到了42毫秒，太大，說明磁碟IO競爭嚴重，效能不佳。 但這個是原因還是結果呢？我們不妨往下看。  
5. 如下圖所示，不再是由一個程式將資料讀取到buffer cache,其他人直接複用記憶體中的資料，從而降低了對IO的請求次數，降低了磁碟的繁忙程度。 
   而是每個程式各自讀取到自己的私有記憶體PGA中，每個程式執行同一條SQL都需要各自讀取各自的，顯然大量程式同時反覆讀取同一片資料，勢必造成磁碟的繁忙和IO的效能下降。這就是從AWR報告中得到的分析結論。如下圖所示。 

那麼為什麼不是一個人讀取到共享記憶體，其他人坐享其成就好了呢？
這是11G的新特性引起的。11g下當最佳化器判斷需要較多物理IO的時候，那麼就繞開BUFFER CACHE，直接讀到PGA私有記憶體中。
這個特性的初衷是：當ORACLE預設一次讀16個BLOCK時，由於所需的部分BLOCK已經在buffer cache裡了，不連續了，因此這16個BLOCK，很可能需要拆分為多次IO，導致原來的一次多塊讀變為了多次單塊讀。
但現實中，當並行個數多的時候，由於該特性，這個時候很容易把磁碟搞得很忙，IO效能下降嚴重，也就出現了執行時間從1分鐘升至7分鐘的情況。

而原來10G下的機制是：
一個會話讀到記憶體中，其他會話坐享其成，直接讀記憶體中的資料就可以，因此讀磁碟的個數會小一些。

3. 問題得到解決： 使用下列方法臨時禁止該特性後，再次測試，問題得到解決。 alter system set event= '10949 trace name context forever, level 1' scope=spfile; --重啟資料庫 Shutdown immediate startup alter system register; 因此不難看出，IO慢實際上是結果，而不是原因，原因在於對同一片資料反覆讀取，出現太多IO了。

怎麼破--什麼是無效IO以及解決方法

我們不放來回顧“用一個例子說明ORACLE的工作過程和IO特點”這個章節，不難發現，其實那就是一個活生生無效IO的例子。
客戶端發起update T set id=5 where id=3的SQL語句
其中，表T的大小為1G，表上不存在任何索引
在執行過程中，前臺程式總計讀取了磁碟中的1G的資料，經由SAN交換機傳輸到ORACLE共享記憶體中，再進行過濾，最後只有1條資料滿足，最終從id=3被更新為id=5。
試想一下，如果這個表的大小不是1個G，而是100個G，也不是一個客戶端發起對該表的更新，而是多個會話同時更新不同的記錄，那麼整個系統的IO將會異常繁忙。

我們從字典中找一個“喜”字，如果是挨頁翻，挨頁對，那麼勢必會多做很多無用功，最簡單的方式就是從偏旁部首或者拼音來檢索，就可以快速的找到“喜”字。
同樣是找一個“喜”字，前者多翻了很多字典，即產生了很多無效IO。後者則很高效。

因此，無效IO，說到底，是SQL語句缺少一個定位資料的高效方式，導致讀取了很多資料，但是不滿足又被丟棄了，導致了很多無效的IO。如果，我們對錶T的id欄位建立索引，那麼將可以快速精確定位到id=3的資料，只需要讀取幾個BLOCK，整體的IO量可以控制在40K以內，不是之前的1個G。

應用程式的SQL語句不夠高效，是無效IO的主要原因。
SQL語句的最佳化不是索引那麼簡單，索引只是眾多單表訪問路徑的一種，SQL最佳化還涉及到表連線方式最佳化、表連線順序最佳化、SQL改寫等手段，後續將會陸續介紹這些最佳化手段。

知識點：
我們需要有這麼一個意識：
磁碟100% busy,IO響應時間很長，這很可能是因為某些不夠高效的SQL語句，產生了很多無效的IO，或者導致IOPS超過了整個磁碟（陣列）所能提供的IO能力，或者是佔用了無效的IO頻寬導致了IO的擁堵。
磁碟繁忙，IO響應時間長，可能已經是結果，而不是導致業務慢的真正原因。
透過最佳化高IO的SQL，消除無效IO，將IO控制在合理範圍內，提升整體IO效能。

不瞭解業務邏輯的情況下實現每秒IO 359M到每秒1M的最佳化-案例分享（四）

1. 問題描述：
   系統的IO的IO量很大，經常性地，IO的吞吐量達到每秒300M以上。
   透過AWR報告，找到問題集中在一條SQL上。
   問題來了，我們不是做開發的，這個業務系統的業務邏輯我們也不清楚，我們可以最佳化麼？
   答案是可以的，
   實際上我們完全在可以不懂業務邏輯的情況下完成絕大部分情況的最佳化，我們只要獲得SQL執行的過程和明細即可快速完成最佳化，以下這條最佔IO的SQL的最佳化我們在1分鐘內就完成了最佳化。
   為了說明我們不需要了解業務邏輯也可以完成最佳化，你會發現從頭到尾沒有看到過任何的SQL語句 ^_^

2. 最佳化過程：
   獲取執行計劃和執行明細(哪些步驟消耗多少時間，花費多少IO)
   
   
   可以看到：
   上述SQL的執行時間是39秒，其中id=14的步驟，佔了38秒，必須最佳化掉該瓶頸步驟。
   Id=14的步驟，reads為1750K個BLOCK，即讀了1750K*8K=13G,單次執行13G,38秒讀完，即每秒的IO達到359M，但是最後只返回了6條記錄，該步驟對A表進行全表掃描，顯然，讀取13G，應用了過濾條件後，最後只返回了6條記錄！很多資料在讀取到記憶體後基本都被丟棄了。缺少定位資料的高效方式，而索引是最適合定位少量資料的。

3. 最佳化方式
   上圖id=14的謂詞部分，即紅色加框部分，可以看到對A表掃描了13G，主要的過濾條件是c_captialmode和c_state這兩個欄位把大部分資料全濾掉了，因此建立複合索引即可，命令如下
   Create index idx_1 on A(c_captialmode,c_state) tablespace &tbs online;

4. 最佳化效果
   最佳化後，每次執行，IO從13G下降至0 ；
   最佳化後，執行時間從50秒下降至50毫秒
   最佳化後，整個系統的IO從每秒359M下降到1M以下。

知識點：
在不瞭解業務邏輯的情況下，也可以快速實現對最消耗IO的SQL語句的快速最佳化。
精通資料庫知識的DBA往往比不懂資料庫原理的開發和程式設計師更懂SQL最佳化。

寫在最後

由於時間和篇幅的原因，本次分享到此，許多知識點和未盡事宜，可加我微信進一步交流shadow-huang-bj,希望今天的分享對大家日後的工作有幫助。
在遇到ORACLE資料庫IO量高的情況時，至少有一個意識，我們的IO是無效的麼？