自動調整 Oracle9i Database ：Oracle SGA(2)

2024-08-29 13:39:05

字體：大中小

來源：轉載

供稿：網友

　　為 pga_aggregate_target 開發特征碼
　　Oracle 數據庫中的 PGA 區域非常重要，因為它控制排序操作以及 SQL 散列聯接的速度。在以下的某一種情況出現時，您可能希望動態更改 pga_aggregate_target 參數：
　　
　　只要 v$sysstat 統計量 "estimated PGA memory for one-pass” 的值超過 pga_aggregate_target ，您就希望增加 pga_aggregate_target 。
　　
　　只要 v$sysstat 統計量 “workarea executions ? multipass” 的值大于百分之一，數據庫就可能得益于額外增加的 RAM 內存。
　　
　　有可能出現過量分配 PGA 內存的情況，而只要 v$sysstat 行 “workarea executions?optimal” 的值持續顯示百分之百時，您可能會考慮減少 pga_aggregate_target 的值。
　　
　　v$pgastat 視圖提供對 PGA 使用情況以及自動內存治理程序的實例級匯總統計信息。為快速獲得概要信息，有個簡單的查詢提供了關于所有 Oracle Database 10 g 連接的總體 PGA 使用情況的極佳統計信息：
　　
　　check_pga.sql
　　
　　
　　-- *************************************************************
　　
　　-- Display detailed PGA statistics
　　
　　--
　　
　　-- *************************************************************
　　
　　column name format a30
　　
　　column value format 999,999,999
　　
　　select
　　
　　name,
　　
　　value
　　
　　from
　　
　　v$pgastat
　　
　　;
　　
　　該查詢的輸出可能類似于以下信息：
　　
　　NAME VALUE
　　------------------------------------------------------ ----------
　　aggregate PGA auto target 736,052,224
　　
　　global memory bound 21,200
　　
　　total eXPected memory 141,144
　　
　　total PGA inuse 22,234,736
　　
　　total PGA allocated 55,327,872
　　
　　maximum PGA allocated 23,970,624
　　
　　total PGA used for auto workareas 262,144
　　
　　maximum PGA used for auto workareas 7,333,032
　　
　　total PGA used for manual workareas 0
　　
　　maximum PGA used for manual workareas 0
　　
　　estimated PGA memory for optimal 141,395
　　
　　maximum PGA memory for optimal 500,123,520
　　
　　estimated PGA memory for one-pass 534,144
　　
　　maximum PGA memory for one-pass 52,123,520
　　
　　在上面來自于 v$pgastat 的顯示內容中，我們看到以下重要的統計信息：
　　
　　Total PGA used for auto workareas — 該統計量監視所有以自動內存模式運行的連接的 RAM 使用情況。記住， Oracle 沒有答應所有內部進程使用自動內存特性。例如， java 和 PL/SQL 將會分配 RAM 內存，而這將不會計算在總的 PGA 統計量中。因此，您應該從分配的總 PGA 中減去該值，以便了解由連接所使用的內存量和由 Java 和 PL/SQL 所使用的 RAM 內存量。
　　Estimated PGA memory for optimal/one-pass — 該統計量估計出以最優化模式執行所有任務連接 RAM 請求時需要多少內存。記住，當 Oracle Database 10 g 碰到內存短缺情況時， DBA 將調用多步操作，試圖找到最近釋放的 RAM 內存。在 Oracle Database 10 g 中，該統計量對于監視 RAM 使用情況非常重要，大部分 Oracle DBA 會將 pga_aggregate_target 增加到此值。
　　在 Oracle Database 10 g 中可以使用稱為新顧問實用程序的 v$pga_target_advice 。該實用程序顯示從當前值的 10% 到 200% 的不同大小的 pga_aggregate_target 的最優化、一步和多步 PGA 執行的臨界差別。

　　
　　列表顯示使用這一新的實用程序的示例查詢，以下是輸出的示例。在這里我們看到，已經為當前的處理超量分配了 pga_aggregate_target ，可以安全地從這一區域提取 RAM 并將它分配到其他地方：
　　
　　Estimated Estimated
　　
　　Target(M) Cache Hit % Over-Alloc.
　　---------- ----------- -----------
　　113 　　　　73 　　　　　　0
　　
　　225 　　　　81 　　　　　　0
　　
　　450 　　　　92 　　　　　　0
　　
　　675 　　　100 　　　　　　0
　　
　　900 　　　 100 　　　　　　0
　　
　　1080 　　　 100　　　　　　 0
　　
　　1260 　　　 100 　　　　　　0 <= current size
　　
　　1440 　　　 100 　　　　　　0
　　
　　1620 　　　 100 　　　　　　0
　　
　　1800 　　　 100 　　　　　　0
　　
　　2700 　　　 100 　　　　　　0
　　
　　3600 　　　 100 　　　　　　0
　　
　　5400 　　　 100 　　　　　　0
　　
　　7200 　　　 100 　　　　　　0
　　
　　可以看到，您能夠方便地創建自動方法來檢測 PGA 內存短缺情況（使用 Statspack ）并編寫作業來動態更改 pga_aggregate_target ，以確保為排序和散列聯接進行最優化的 RAM 使用。
　　
　　為數據緩沖區開發特征碼
　　DBA 將會注重到，在實際情況中，數據緩沖區命中率 (DBHR) 的變化會隨著測量間隔的頻率增加而增加。例如， Statspack 可能在以小時為單位的間隔時報告 DBHR 為百分之九十二，但在采樣率以兩分鐘為間隔時，將顯示很大的變化，如圖3所示。
　　　自動調整 Oracle9i Database ：Oracle SGA(2)（圖一）

自動調整 Oracle9i Database ：Oracle SGA(2)（圖一）

　　圖3
　　
　　作為一般性原則，應該調整主機上的所有可用內存，并且應該為 db_cache_size 分配達到增益遞減點的 RAM 資源。（參見圖4）。在該點處增加緩沖區塊不會顯著提高緩沖區命中率。
　　　自動調整 Oracle9i Database ：Oracle SGA(2)（圖二）

　　圖4
　　
　　新的 v$db_cache_advice 視圖類似于 Oracle7 中推出的一個用于跟蹤緩沖區命中情況的舊實用程序 x$kcbrbh ；同樣， x$kcbcbh 視圖用于跟蹤緩沖區遺漏情況。數據緩沖區命中率可以提供與 v$db_cache_advice 所提供內容相類似的數據，因此多數 Oracle 調整的專業人員可以使用這兩種工具來監視其數據緩沖區的有效性。
　　
　　當 v$db_cache_advice 實用程序已經啟用，并且數據庫已經運行了足夠長的時間來提供有代表性的結果時，可以使用列表 5 中的腳本來執行高速緩存建議功能。使用這一腳本，您可以獲得對您所有緩沖區池的高速緩存建議，包括 2k、4k、8k、16k 和 32k 數據緩沖區。
　　
　　該腳本的輸出如下所示。注重，數值的范圍從 db_cache_size 當前大小的百分之十直到當前大小的兩倍。
　　
　　Estd Phys 　　Estd Phys
　　Cache Size (MB) 　　Buffers 　Read　Factor　　　　Reads
　　
　　---------------- ------------ ----------- ------------
　　30 　　　　3,802 　　　18.70 　192,317,943 <- 10% size
　　
　　60 　　　　7,604 　　　12.83 　31,949,536
　　
　　91 　　　　11,406 　　 7.38 　 75,865,861
　　
　　121 　　　 15,208 　　 4.97 　 51,111,658
　　
　　152 　　　 19,010 　　 3.64 　 37,460,786
　　
　　182 　　　 22,812 　　 2.50 　 25,668,196
　　
　　212 　　　 26,614 　　 1.74 　 17,850,847
　　
　　243 　　　 30,416 　　 1.33 　 13,720,149
　　
　　273 　　　 34,218 　　 1.13 　 11,583,180
　　
　　304 　　　 38,020 　　 1.00 　 10,282,475　Current Size
　　
　　334 　　　 41,822 　　 .93 　　 9,515,878
　　
　　364 　　　 45,624 　　 .87 　　 8,909,026
　　
　　395 　　　 49,426 　　 .83 　　 8,495,039
　　
　　424 　　　 53,228 　　 .79 　　 8,116,496
　　
　　456 　　　 57,030 　　 .76 　　 7,824,764
　　
　　486 　　　 60,832 　　 .74 　　 7,563,180
　　
　　517 　　　 64,634 　　 .71 　　 7,311,729
　　
　　547 　　　 68,436 　　 .69 　　 7,104,280
　　
　　577 　　　 72,238 　　 .67 　　 6,895,122
　　
　　608 　　　 76,040 　　 .66 　　 6,739,731 <- 2x size
　　
　　如圖4中所標注，數據緩沖區最優化設置的位置就是附加緩沖區的臨界效益開始減少的位置。
當然，該優化點將在一段時間后改變，這就是為什么我們需要預先重新配置 SGA 的原因，以便于我們能夠根據當前的處理需要來更改數據緩沖區的大小。
　　
　　對于趨勢分析，DBHR 中的變化并不重要，可以沿兩個方向生成平均數據緩沖區命中率：一周中天天的平均 DBHR 和一天中每小時的平均 DBHR。
　　
　　記住，在數據緩沖區中變化快速地發生，有時長期的分析將會提供線索，指出數據庫中的處理故障問題。幾乎每個 Oracle 數據庫都提供鏈接到常規處理計劃的模式，稱為特征碼。
　　
　　以下顯示一個 Statspack DBHR 每小時平均值腳本的輸出。報告基于六個月的數據收集，顯示天天的平均命中率。假如在電子表格中繪制該數據，則該數據庫的 DBHR 特征碼變得顯而易見。
　　
　　hr BHR -- ----- 00 .94 01 .96 02 .91 03 .82 04 .80 05 .90 06 .94 07 .93 08 .96 09 .95 10 .84 12 .91 13 .96 14 .95 17 .97 18 .97 19 .95 20 .95 21 .99 22 .93 23 .94
　　
　　該數據的繪圖如圖5所示，我們看到一些有趣的重復趨勢。
　　　