系統投產前，Oracle資料庫最佳化思路和9個典型問題

       轉自 talkwithtrend
 
       系統上線前，對於基礎架構的技術稽核可能需要伴隨一系列整改和最佳化，其中資料庫層面的整改和最佳化是最重要的一項。以下來自社群會員的經驗和心得可供參考（以Oracle為例）。由社群專家趙海整理總結。

1.安裝時（文件 ID 1525820.1）

其實Oracle官方對於Oracle的通用最佳實踐提供的非常詳細，針對不同平臺、針對不同版本、針對不同用途等都會有相應一套實施的最佳實踐。

例如：

1）RAC 和 Oracle Clusterware 最佳實踐和初學者指南（平臺無關部分）
Document 810394.1 

RAC and Oracle Clusterware Best Practices and Starter Kit (Platform Independent)

2）特定平臺的詳細最佳實踐

Document 811306.1 

RAC and Oracle Clusterware Best Practices and Starter Kit (Linux)

3）作業系統配置注意事項

4）虛擬化注意事項

5）儲存注意事項

6）網路注意事項

7）特定硬體注意事項

2.測試及系統上線之前

這個過程當中，根據特定的應用場合及測試結果以及我們對資料庫理解的不同可能會產生一些以行業背景為區分的行業經驗及行業實踐。

典型問題：

1）關於重做日誌的配置最佳化應該做哪些點？應該如何做？

首先、接觸過資料庫的人相信對這個概念都不陌生。資料庫在做SQL更新的時候，首先要將事務執行過程記入重做日誌當中，然後才會把日誌刷入磁碟，將資料更新持久化。一條資料提交之後成功的標準時日誌落到磁碟，而不是真正的資料落盤。因此日誌的配置（大小、數量）直接決定著資料庫讀寫的效能，如果日誌大小非常大，那麼會造成歸檔切換時間非常長，一旦這時候發生了不可恢復的DB災難，那麼透過備份恢復的資料流失量或者說RPO就會較大。日誌大小非常小的話，勢必會造成日誌頻繁切換，AWR裡面有大量的日誌切換事件，這樣對資料庫的效能會有較大影響。因此根據效能測試的AWR報告中日誌切換的等待事件、和切換頻度來決定其資料量和大小是否需要調整。一般的OLTP建議（10組、500M）。

接著，我們還需要考慮與其相關的引數設定。

比如說“_use_adaptive_log_file_sync”，它直接決定了日誌落盤的方式，對於日誌緩衝區的資料落盤的方式，11g增加一種新的方式就是polling的方式，傳統方式是post/wait方式。oracle底層自動判斷何時用何種方法來完成lgwr程式的寫任務。對於post/wait方式來講，客戶端做了commit之後，需要等待事件完成。oracle一旦完成會通知使用者程式，使用者程式立刻感知。但是這一通知post，會耗費大量CPU資源。polling是oracle前臺程式啟動檢查任務，自動檢查後臺lgwr寫入情況，耗費CPU資源比較少，但是使用者程式並不一定能立刻感知。所以兩種方法各有千秋。但是關鍵是後臺實現兩種方法切換的時候要耗費系統效能，尤其在繁忙的時候頻繁切換的話反而會導致資料庫效能下降。awr出現大量‘Log file sync’。Bug 13707904。

比如說“archive_lag_target”，它決定了我們是否開啟日誌強制切換功能，為了減少故障時資料損失，可以設定ARCHIVE_LAG_TARGET引數，強制進行日誌切換。這個引數的預設值是0，即為不啟用該引數。建議設定值為1800。

2）關於ORACLE的記憶體管理應該關注那些點？應該如何配置？

首先，ORACLE通用的兩種記憶體管理方式AMM&ASMM，從Oracle 11g開始，ORACLE預設使用AMM(自動記憶體管理)，即讓資料庫完全管理SGA、PGA的大小，而對於管理員只需要設定一個總的大小（memory_target），資料庫會動態的調整SGA、PGA的大小以及其中包含的各個元件大小，如Database buffer cache、Shared pool等。這個特性設計的初衷是好的，它希望避免不正確的SGA和PGA設定導致的記憶體使用不平衡的效能問題。但是在實際應用過程中，這個特性是不是一定非常出色呢？AMM中在資料庫啟動是會有一個固定比例來分配SGA/PGA 大小：sga_target =memory_target *60% 
pga_aggregate_target=memory_target *40%。

但是在併發較高，資料庫非常繁忙的場合下，自動記憶體調整的速度很可能趕不上大量會話對記憶體的請求的速度。另外當PGA隨著會話不斷增加而需求量猛增的情況下，它會首先搶佔SGA，導致資料庫效能故障。在高併發的資料庫場景中並不建議使用AMM。採用10g更為成熟的自動共享記憶體管理(ASMM)和自動PGA管理。手動調整記憶體引數，具體可以參照以下：

//關閉記憶體自動管理

memory_target=0

memory_max_target=0

//設定SGA為固定值，可以根據效能測試中的AWR報告中的建議

sga_max_size=XG

sga_target=XG

//設定PGA等引數

pga_aggregate_target=XG

large_pool_size=256M

另外很重要的一個引數，“_shared_pool_reserved_pct”，如果這個引數設定小了，很可能導致ORA04031，TROUBLESHOOTING ORA-4031 - Simple Guide and Basic Approach to Solve the issue (文件 ID 1416817.1)

3）關於Linux系統下的大頁配置？

在 Linux 環境中實施 HugePage 能夠極大地提高核心效能。對於記憶體較大的系統，效果尤其明顯。一般而言，系統中的 RAM 越大，系統啟用 Hugepage 後獲得的好處也越大。這是因為核心為對映和維護記憶體頁表所要做的工作量會隨著系統記憶體的增大而增加。啟用 Hugepage 能夠顯著地降低核心要管理的頁面數，而且能提高系統的效率。經驗表明，如果未啟用 Hugepage，核心擠佔關鍵的 Oracle Clusterware 或 Real Application Clusters 守護程式的情況會很常見，而這會導致例項或節點驅逐出現。具體配置方法可以參照：HugePages on Linux: What It Is... and What It Is Not... (文件 ID 361323.1)

4）關於SQL解析相關的引數最佳化？

首先、在Oracle中每條SQL語在執行之前都需要經過解析，這裡面又分為軟解析和硬解析。在Oracle中存在兩種型別的SQL語句，一類為 DDL語句(資料定義語言)，他們是從來不會共享使用的，也就是每次執行都需要進行硬解析。還有一類就是DML語句(資料操縱語言)，他們會根據情況選擇要麼進行硬解析，要麼進行軟解析。

一般我們希望我們的AWR報告中硬解析偏少，而軟解析偏多。因為硬解析的代價會非常高。為了減少帶繫結變數的sql的解析時間，oracle 9i引入的繫結變數窺測的功能。也就是在同一個SQL的變數被賦於不同值時採用同一個遊標，這樣雖然節省了sql的解析時間。大家有沒有透過功能的開啟或者關閉實際觀察過AWR中的軟硬解析數目的實際狀況呢？其實對於繫結變數窺測這個特性以及後來的自適應遊標等特性，都是oracle為了找到最優執行計劃而啟用的一些新特性，但是在實際應用過程中，對於不同量級不同特性的業務場景也曾經因此出現了很多bug。

understanding and Diagnosing ORA-00600 [12333] / ORA-3137 [12333] Errors (ID 389713.1)

根據自己的業務系統特點，做大量的效能測試和業務測試，根據引數的關閉開啟對比awr報告當中顯示出的軟硬解析比率以及執行計劃資料決定是否開啟或者關係相應功能特性。如下引數：

"_optim_peek_user_binds"

"_optimizer_adaptive_cursor_sharing"

"_optimizer_extended_cursor_sharing"

"_optimizer_extended_cursor_sharing_rel"

"_optimizer_use_feedback"

接著，與之相關的幾個引數：open_cursors、session_cached_cursors 這兩個引數決定著應用會話可以控制開啟以及快取的遊標數量，如果數量不足，就會引起SQL解析的效能問題。這兩個引數要根據v$resource_limit檢視中的值的情況進行調整，避免資源設定不合理導致的效能問題。

還有，與執行解析執行計劃相關的幾個引數，_b_tree_bitmap_plans、有時將B-Tree索引進行BITMAP轉換來進行SQL執行，往往會生成極其惡劣的執行計劃，導致CPU100%。

Select Fails With ORA-600 [20022] (文件 ID 1202646.1)

建議可以關掉。

5）如何避免資料庫叢集節點之間的激烈競爭？

資料庫節點之間的競爭有很多，包括鎖（各種粒度鎖）的競爭以及資料的傳輸等。完全避免競爭那就失去了RAC的意義了，RAC本身就是希望能在兩個節點並行執行任務。

如果特別極致的並行一定引起嚴重的效能問題，如果完全禁止，既無法做到又失去了叢集本來的意義。所以我們只能在一定程度上去平衡：

首先、關於DRM，oracle的DRM特性從理論上來看，它是為了避免節點間的資料量傳輸，避免節點間的鎖等待事件頻繁發生。DRM的極致是做到請求節點和Master節點統一化。但是實踐中，這個特性引起了很多的BUG、反而導致了節點間的競爭出現了效能故障。Bug 6018125 - Instance crash during dynamic remastering or instance reconfiguration (Doc ID 6018125.8)。所以建議關閉。

接著、關於引數“parallel_force_local”，ORACLE RAC為了實現多節點並行處理是花費了很大代價的，假設一個叢集當中有三個節點，對於某一個資料塊兒讀寫，有一個Master、有一個請求者、有一個擁有者，請求者向Master請求資料塊兒的最新版本，Master把請求轉發給擁有者，擁有者按照請求資訊把資料塊兒傳送給申請者，然後加鎖進行讀寫。這一過程是需要有大量的資料傳輸和競爭存在的，一旦這個事情成為多數，那麼勢必造成節點間的通訊負載過大，造成大量的鎖等待時間，嚴重影響資料庫整體效能。尤其是在做跨資料中心高可用的場合下。因此我們只要做到業務級別的併發處理，而不要追求一個SQL級別的絕對併發。物極必反的道理就在於此。因此把引數開啟，使得程式級別併發實現本地化處理，不要跨節點處理。在官方文件 ID 1536272.1當中，必須最佳化的引數就包括這個。

6）關於資料庫的自動任務？

Oracle 11g 資料庫有三個預定義自動維護任務：

Automatic Optimizer Statistics Collection（自動最佳化器統計資訊收集）：

收集資料庫中所有無統計資訊或僅有過時統計資訊的 Schema 物件的 Optimizer（最佳化器）統計資訊。QL query optimizer（SQL 查詢最佳化器）使用此任務收集的統計資訊提高 SQL 執行的效能。

Automatic Segment Advisor（自動段指導）：

識別有可用回收空間的段，並提出如何消除這些段中的碎片的建議。您也可以手動執行 Segment Advisor 獲取更多最新建議，或獲取 Automatic Segment Advisor 沒有檢查到的那些有可能做空間回收的段的建議。

Automatic SQL Tuning Advisor（自動 SQL 最佳化指導）：檢查高負載 SQL 語句的效能，並提出如何最佳化這些語句的建議。您可以配置此指導，自動應用建議的SQL profile。

關於統計資訊收集，資料庫是有其自己的預設啟動時間，11g是在22:00-2:00之間，假設這個時間跟我們的跑批時間有衝突的話，我們可以修改器具體執行時間。但是這個任務必須保留。

關於其他的兩個最佳化指導，其實要看我們實際工作中用到的機率是否很高，是否有價值留著給我們提供一些最佳化的理論指導。一般感覺用不好的話意義不大，還不如不用。

7）關於安全方面的幾個配置最佳化？

首先，是資料庫要不要保留審計？如何保留。假設不開啟，那麼將來出來安全問題，我們無法尋找線索；假設開啟，那麼很可能因為使得審計日誌佔用大量的儲存空間，甚至影響資料庫IO效能。一般情況下還是需要對一些基本登入行為的審計，但是我們可以把日誌位置修改制定到作業系統層面減少資料庫層因此的效能壓力，而且應該定期轉儲，減少碎檔案太多而把檔案系統i節點用光的極端情況。可以透過對引數"AUDIT_TRAIL"以及adump引數的調整來實現此項最佳化。

接著，alert日誌和trace檔案的控制引數。

“MAX_DUMP_FILE_SIZE”，它決定了這些檔案的大小限制，預設情況下是unlimited，如果生成了很大的檔案，就會達到OS對檔案上限的要求，導致寫入失敗。

最後，所有這些重定給OS或者本來就依靠OS的日誌檔案也好、審計檔案也好。一定得注意其對OS的i節點資源使用情況的一個把握，不要出現df -h正常但是df -i 不正常的情況。這個往往是非常容易忽視的一點。無論是從監控上還是從OS對使用者資源引數的限定上都要有一個明確的把握。

8）關於ADG的關注點？

ADG本身作為容災的一個手段，那麼其本身會有很多點需要我們監控。比如說主備庫的狀態、日誌的切換狀況、資料之間有沒有GAP等等。但是我想說的是我們非常容易忽略的地方。

首先，關於備庫的RMAN引數設定，

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

這個引數設定是保護沒有被應用的日誌不被刪除，在11g的高版本實際上已經不需要再設定了，但是低版本的就需要注意了。具體可以參照文件 ID 1577382.1

9）其他在管理資料庫時應該注意的點？

例如：

表空間的資料檔案是否採用了自動擴充套件的方式？

表空間的資料檔案是否都用了ASM的方式？

ASM的冗餘方式是否一致？

應用使用者的預設密碼策略是不是已經取消了180天的限制等等。

資料庫的監控指標是否覆蓋了（叢集、服務、監聽、ASM、表空間、效能等所有應該涵蓋的方面）？

OS層面的監控是否已經啟用？尤其是私網之間的通訊、CPU、記憶體的監控等？是Nmon還是osw，他們的日誌是定期迴圈還是持續不斷增長等等？

資料庫巡檢的體系是否完善？日巡檢月度巡檢的內容是否經過精心設計？是否已經實現了自動化等等？強烈建議日巡檢工作實現指令碼自動化，任務定時執行，日誌統一整合到共享檔案系統上，有條件的可以進行整合入庫，按照自己的巡檢機制和體系實現按需調入調出。