Oracle數據庫優化思路和9個典型問題分別是什么

Oracle數據庫優化思路和9個典型問題分別是什么，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

       系統上線前，對于基礎架構的技術審核可能需要伴隨一系列整改和優化，其中數據庫層面的整改和優化是最重要的一項。

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，他們的日志是定期循環還是持續不斷增長等等？

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

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。