oracle基礎知識-oracle11g資料庫核心參數優化

#頭條創作挑戰賽#

根據我多年的資料庫運維經驗，提供一套資料庫核心參數的調整。提供給大家。該版本參數為oracle11g。

一、執行個體記憶體參數

Oracle執行個體記憶體，由SGA（System global area系統全局區）和PGA（Program global area程式全局區）構成。SGA是一組共享記憶體結構，稱為SGA元件，包含一個oracle資料庫執行個體的資料和控制檔案資訊。所有的伺服器程序和背景程序共享SGA記憶體區，SGA中存儲的資料包括緩存的資料塊和SQL共享區域。PGA是一個非共享記憶體區域，它隻包含用于Oracle程序使用的資料和控制資訊。當Oracle程序啟動時，Oracle資料庫建立PGA。

Oracle資料庫建庫初始化完畢以後，需要我們設定SGA，PGA，以及SGA各記憶體元件的大小。雖然Oracle提供自動記憶體管理，根據最佳實踐在部分特定場景，Oracle自動管理記憶體會導緻某些元件記憶體無限增大，進而影響其他元件的非正常運作。是以，我們推薦采用手動設定管理記憶體的方式。

Oracle資料庫執行個體運作一般情況下，提供給Oracle使用的總記憶體（SGA+PGA）占主機作業系統記憶體的60%左右。比如資料庫記憶體為128G，則Oracle使用的總記憶體建議不超過75G。其中SGA設定為60G，PGA設定為15G。SGA的60G又需要進一步細化，SGA由db_cache_size、shared_pool_size、large_pool_size、java_pool_size、streams_pool_size等構成。db_cache_size建議占用SGA總大小的80%（48G），shared_pool_size建議占用SGA總大小的10%（6G），剩餘的large_pool_size、java_pool_size、streams_pool_size占用總大小的5%，最後預留5%。

根據以上建議，以128G實體記憶體,48Core CPU為例，需要設定的參數如下：

alter system set sga_max_size=60G scope=spfile sid=’*’;

alter system set sga_target=60G scope=spfile sid=’*’;

alter system set pga_aggregate_target=15G scope=spfile sid=’*’;

alter system set db_cache_size=48G scope=spfile sid=’*’;

alter system set shared_pool_size=6G scope=spfile sid=’*’;

alter system set large_pool_size=1G scope=spfile sid=’*’;

alter system set java_pool_size =1G scope=spfile sid=’*’;

alter system set streams _pool_size=1G scope=spfile sid=’*’;

二、Oracle資料庫檔案，程序，遊标

processes: 指定了一個執行個體在作業系統級别能同時運作的程序數，包括背景程序與伺服器程序。

db_files：指定資料庫的最大檔案數。

open_cursors：每個session（會話）最多能同時打開多少個cursor（遊标）。

session_cached_cursors：每個session（會話）最多可以緩存多少個關閉掉的cursor。

以128G實體記憶體,48Core CPU為例，需要設定的參數如下：

alter system set processes =6000 scope=spfile sid=’*’;

alter system set db_files =3000 scope=spfile sid=’*’;

alter system set open_cursors =6000 scope=spfile sid=’*’;

alter system set session_cached_cursors=400 scope=spfile sid=’*’;

三、UNDO，事件，段建立相關參數

undo_retention：設定Oracle Undo過期資料的儲存期限，機關是秒。如果參數設定為900秒，那麼Undo段資料在非Active狀态之後，會保留900秒。調整為10800秒。

Event:需要解決在11gr2中由于密碼錯誤導緻資料庫出現大量的row cache lock問題。

deferred_segment_creation：關閉segment延遲建立，減少exp\expdp導出錯誤。

以下為通用修改參數：

alter system set undo_retention= 10800 scope=spfile sid=’*’;

alter system set event=’ 28401 trace name context forever,level 1’ scope=spfile sid=’*’;

alter system set deferred_segment_creation = false scope=spfile sid=’*’;

四、RAC環境parallel_force_local

11g 的新增參數PARALLEL_FORCE_LOCAL，如果指定為TRUE， 并行操作隻會在目前instance裡面并行，而不會跨多節點，在11g R2 預設為FALSE,如果想跨多個節點運作并行操作，可以設定為 false。建議設定為TRUE，用于将并行的 slave 程序限制在發起并行 SQL 的會話所在的節點，即避免跨節點并行産生大量的節點間資料交換和引起性能問題。

參數值	預設值	一般建議值	參考标準
parallel_force_local	FALSE	TRUE

修改方式：

alter system set parallel_force_local=true scope=spfile sid='*';

五、隐含參數設定

參數	目前值	建議值	備注
_PX_use_large_pool	FALSE	TRUE	使用large pool作為px buffer源,需要增加large pool的大小，大于128Mb
_clusterwide_global_transactions	TRUE	FALSE	叢集全局事務是在11g上才出現的新功能。該功能允許事務跑在RAC多個執行個體上。該功能存在bug 13605839 .[文檔 ID 13605839.8]
_gc_defer_time	1	3	用于确定将buffer的資料寫入磁盤之前要等待的時間長度，這樣可減少執行個體的gc征用，優化實力對塊的通路
_resource_manager_always_off	False	TRUE	關閉resource manager
_resource_manager_always_on	TRUE	FALSE	關閉resource manager
_serial_direct_read	auto	never	在11g中，全表掃描可能使用direct path read方式，繞過buffer cache，這樣的全表掃描就是實體讀了.設定該參數可以顯著減少直接路徑讀
_cleanup_rollback_entries	100	400	SMON一次性恢複事務入口數量，在SMON Tx recovery使用，也被死事務恢複使用。預設值為100. 增量到400，能夠加快恢複的速度
_optimizer_use_feedback	TRUE	FALSE	關閉該特性，目前該特性存在無Patch的bug (文檔 ID 8729064.8)
_dbms_sql_security_level	1	關閉DBMS_SQL增加檢查的安全特性
_bloom_pruning_enabled	TRUE	FALSE	關閉 bloom filter裁剪 目前存在無patch bugBug 12637294 - Deadlock of PS and BF locks during parallel query operations (Doc ID 12637294.8)
_gc_policy_time	10	關閉drm
_bloom_filter_enabled	Ture	FALSE	禁用bloom filter功能，該功能存在Bug 14233851
_gc_read_mostly_locking	TRUE	FALSE	禁用read mostly 。該功能存在較多bug 。詳見 ID 1549191.1
_gc_undo_affinity	TRUE	FALSE	關閉DRM
_smu_debug_mode	134217728	禁用min active scn功能，該功能在11g中引入，禁用後可解決BUG 9272671。詳見(文檔 ID 1519781.1)
_undo_autotune	TRUE	FALSE	關閉undo retention自動調整特性
deferred_segment_creation	TRUE	FALSE	關閉segment延遲建立，減少exp\expdp導出錯誤
Audit_trail	Db	None	關閉審計
sec_case_sensitive_logon	TRUE	false	啟用/關閉使用者密碼大小寫區分
event	28401 trace name context forever,level 1'	解決在11gr2中由于密碼錯誤導緻資料庫出現大量的row cache lock問題