NUMA導致的Oracle性能問題 - leohahah－IT工程師數位筆記本

文章出處

背景簡介：

Oracle版本:11.2.0.4 　　OS 版本：OEL5.8

在一次Oracle的Dataguard正常switchover過程中，遇到了一個極其詭異的問題，一條主業務的SQL語句在新主庫的執行時間由之前的毫秒級別完成變成了20-60秒不等，為避免高峰業務超時必須盡快進行優化，否則只能走回退方案。

優化過程：

其實這個語句在之前將備庫切換為snapshot備庫做測試時表現是非常良好的，但是切換之后立馬出了問題。在備庫實際執行后獲取到的執行計劃與在主庫一模一樣，如下：

獲取執行計劃的語句如下：（語句出自ITPUB大神版主lfree）

select * 
from 
table(dbms_xplan.display_cursor(NVL('&1',NULL),
      NULL,
      'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline &2'));

這里的參數1和2全部設置為空即可，此語句可以查出當前會話中上一個執行過語句的真實執行計劃。

此SQL中不涉及視圖，所以這個執行計劃是非常好的，在主庫執行也是毫秒級別的，因此同樣的執行計劃在備庫卻非常慢就很值得思考了。

接下來我利用set autot工具得到了執行此SQL后的統計信息，發現存在大量物理讀。這里就很搞笑了，真實執行計劃中不存在表掃描，所以出現這么多的物理讀一定是回表操作特別多，那么為什么回表？顯然內存不夠。

于是我將SGA加大至80GB（比主庫還大20GB），重啟數據庫再查，問題依舊。

我依然堅信是緩存的問題，那么必須要搞清為何數據未被緩存至內存，對Oracle數據庫來說大多有2個原因：

1、數據太多，內存太小。

2、不是熱點數據，被LRU刷出內存。

首先排除第二條，原主庫60G的SGA都可以，現在80G的SGA沒理由不可以。

此外注意到一個現象，v$sgainfo中的buffer pool在接近30GB時有一個很長時間的停頓，然后才慢慢增長至接近70G（剩余部分屬于sharedpool等）。

于是突然想到NUMA的問題，果然：

numactl --hardware的運行結果：

這就尷尬了，在/etc/grub.conf的kernel一行后添加了numa=off，重啟服務器后果然問題被解決。

事后查看數據庫日志找到了如下信息：

因此可以確認是操作系統未關閉NUMA特性引起的（只設置數據庫禁用NUMA的隱含參數是無用的，Oracle在11GR2之后已經默認禁用NUMA，但只是數據庫級別）。

關于Oracle NUMA的相關信息，參考官網文檔：Oracle NUMA Usage Recommendation (文檔 ID 759565.1)

名詞解釋：

什么是NUMA：

NUMA模式是一種分布式存儲器訪問方式，處理器可以同時訪問不同的存儲器地址，大幅度提高并行性。 NUMA模式下，處理器被劃分成多個"節點"（node），每個節點被分配有的本地存儲器空間。所有節點中的處理器都可以訪問全部的系統物理存儲器，但是訪問本節點內的存儲器所需要的時間，比訪問某些遠程節點內的存儲器所花的時間要少得多。

--OK，注意這幾個字：大幅提高并行性。Oracle數據庫絕大多數時候進程都是串行的，除非特意設置并行度，而SQL Server也只有超過cost閾值才會并行，因此數據庫服務器應該禁用NUMA。

關于NUMA更加詳細的信息參考：

https://www.ibm.com/developerworks/cn/linux/l-numa/index.html

https://technet.microsoft.com/zh-cn/library/ms178144(v=sql.105).aspx

http://www.cnblogs.com/yubo/archive/2010/04/23/1718810.html