隨著數據庫管理員在自調整工作方面變得更加成熟許多 Oracle 規格可能變為自調整在 Oracle Database g 中我們將看到比以前更多的自調整功能
例如Oracle Database g 的動態內存分配特性使得創建一個自調整的 Oracle SGA 成為可能通過演示在本文中我將說明如何檢查 Oracle i Database 中的 Oracle 例程以及根據服務器上和數據庫內的處理需求來調整 sort_area_size 或 pga_aggregate_targetlarge_pool_sizesga_max_size 和 db_cache_size 的內存區域這裡討論的技巧的基礎是使用 Statspack 來隨時監控內存區域並顯示系統資源利用率的信號圖
我還將討論如何創建一種智能機制以根據當前的處理需求來自動地重新配置 Oraclei Database並提供了示例代碼這些示例代碼將使您能夠開始編寫自己的能夠有效地仿效 Oracle Database g 功能的自動化腳本例如我將提供一個腳本它將自動識別小型常用的程序段並將它們分配給 KEEP 池以全部進行緩存（重要注意事項這種仿效僅考慮外部的行為但不反映新版本的內部實施）因為每個數據庫都各不相同所以為了清楚起見這些腳本特意進行了省略和簡化因此您將需要擴展這些示例並編寫適合您的環境的自定義的自動化腳本
具有以下特性的商店將從自動化的自調整中最大程度的獲益
雙模式系統 — 在在線事務處理 (OLTP) 和數據倉庫處理模式之間轉換的系統尤其將從自調整 RAM 區域中獲益
位的商店 — 運行 位服務器的商店受其 RAM 區域大小（最大約為 GB）的限制對於這些商店最有效地使用 RAM 資源尤為重要
記住擁有一個非常大的 db_cache_size 的趨勢正在下降也很重要雖然對數據的直接訪問是利用散列法來完成的但有時數據庫必須檢查 RAM 緩存中的所有內存塊
高失效率的系統 — 無論何時當程序產生一個截斷表使用臨時表或運行一次大型的數據清除時Oracle 必須清除 db_cache_size 中的所有內存塊以除去已被使用的內存塊對於擁有大於 gB 的 db_cache_size 的系統這種方法可能造成過多的開銷
更新率高的系統 — 當執行一次異步寫操作時數據庫寫入器 (DBWR) 過程必須清除 db_cache_size 中的所有內存塊擁有一個巨大的 db_cache_size 可能給數據庫寫入器造成過重的負擔
首先讓我們回顧一下創建自調整數據庫背後的准則
自調整背後的准則
重新配置一個 Oracle 例程的最常用的技巧是使用一個腳本並通過 dbms_job 或一個外部調度程序（如 UNIX cron）來調用這個腳本為了說明一個簡單的例子考慮一個白天在 OLTP 模式下運行而晚上在數據倉庫模式下運行的數據庫對於這種類型的數據庫您可以安排一項作業來將例程 SGA 內存重新配置成最適合於在該 Oracle 例程中執行的處理類型的配置
列表 包含一個 UNIX 腳本該腳本用來重新配置 Oracle以便進行決策支持處理注意為了適應數據倉庫行為對 shared_pooldb_cache_size 和 pga_aggregate_target 中的配置作了重要的修改該腳本被安排在每晚 :pm 通過 dbms_job 來調用
在列表 中我們看到了建立了自調整 Oracle Database g 的基礎的 alter system 命令記住 RAM 是一種昂貴的 Oracle 服務器資源DBA 有責任在服務器上充分地分配 RAM 資源未得到利用的 RAM 將浪費昂貴的硬件資源
即使得到了充分的分配分配過度的 RAM 也是一種浪費例如當您只需要 m 時分配一個 shared_pool_size=m 是效率低下的因為 RAM 可以被 SGA 的另一個區域（如 db_cache_size）使用
為了說明 RAM 重新配置的概念考慮下面這個例子一個分配不足且數據緩沖命中率很低的 K 數據緩沖區和一個分配過度且數據緩沖命中率很高的 K 數據緩沖區（參見圖 ）
圖 分配過度和分配不足的 RAM 區域
使用 alter system 命令我們可以在數據緩沖區之間調整 RAM以將 RAM 重新分配給最需要的地方（參見圖 ）
圖 RAM 的動態重分配
您可以在很多種 Oracle 腳本（包括動態 SQLdbms_job 和 shell 腳本）中使用 alter system 命令列表 是調整 RAM 緩存大小的一個簡單的 SQL*Plus 腳本這個腳本向您提示緩存的名稱和大小並發出適當的 alter system 命令來調整 RAM 區域的大小下面是輸出的內容
SQL> @dyn_sga
Enter cache to decrease:shared_pool_size
Enter cache to increase:db_cache_size
Enter amount to change:
alter system set shared_pool_size = ;
System altered
alter system set db_cache_size = ;
System altered
現在我們看到了在 Oracle Database g 中如何輕易地改變 RAM 區域下面讓我們研究一下調用 RAM 區域自動調整的一些規則
何時觸發動態重配置
無論何時當監控腳本的例程指示有一個負擔過重的 RAM 區域時您必須選擇從哪一個區域中竊取內存表 顯示了阈值條件的一個簡單的示例該阈值條件觸發 SGA 的三個主要區域的動態內存修改當然每個系統都各不相同您將要根據您的需求來調整這些阈值例如許多商店實施了多個 blocksize並分離了 db_k_cache_size （用於索引表空間）db_keep_cache_size（用於小型引用頻繁的對象）等的 RAM 區域
記住數據庫的需求將根據正在執行的 SQL 不斷變化是很重要的在 :am 最優的一個 SGA 可能在 :pm 就不是最優了為了了解處理特性的變化您可以運行 Statspack 報表來查明 Oracle 改變 RAM 存儲需求的那些時間您還可以運行 v$db_cache_advicev$pga_target_advicev$java_pool_advice 和 v$db_shared_pool_advice 實用程序來查看 RAM 區域大小的變化帶來的邊際效益
一種使動態 SGA 重新配置自動化的流行的方法是識別趨勢您可以使用 Statspack 來預測那些處理特性變化的時間並使用 dbms_job 程序包或動態 SQL 來執行特定的 SGA 修改讓我們詳細了解一下基於趨勢的方法
顯示系統信號圖
基於趨勢的重新配置的一種常見的方法是使用 Statspack 歷史數據來顯示可預測的趨勢並根據信號圖用這些趨勢來修改數據庫
這種方法與零庫存生產很相似其中零部件正好在需要組裝時才出現在工廠車間裡Oracle Database g 使 DBA 能夠預見處理需求並定期地安排適當的干預操作從而確保為處理需求的變化即時地提供 SGA 資源
自調整 Oracle 的內存區域涉及到改變幾個 Oracle 參數的值雖然存在 多個 Oracle Database g 參數來管理數據庫的各方面配置但只有少數幾個參數對自動的 Oracle SGA 調整很重要
db_cache_size — db_cache_size 確定 Oracle SGA 中的數據庫塊緩沖的數量並且代表著 Oracle 內存最重要的一個參數
db_keep_cache_size — 這個數據緩沖池是 Oraclei 中 db_block_buffers 的一個子緩沖池但從 Oraclei Database 開始成為一個單獨的 RAM 區域
db_nn_cache_size — Oracle Database g 有單獨的數據緩沖池您可以使用這些數據緩沖池來分離數據並分離具有不同 I/O 特性的對象
shared_pool_size — shared_pool_size 定義系統中由所有用戶共享的池包括 SQL 區域和數據字典緩存
pga_aggregate_target — pga_aggregate_target 定義為系統范圍的排序和散列連接保留的 RAM 區域
您可以看到甚至不需要對您的 Oracle 數據庫狀況的最重要的量度進行歸零校正讓我們從檢查庫緩存中的趨勢開始並確定如何自動調整 shared_pool_size
使用 Oracle Database g 顧問實用程序
Oracle Database g 擁有完整的顧問實用程序它們將准確地預測改變任意的 RAM 區域大小將帶來的變化Oracle Database g 中的顧問實用程序包括
共享池建議 — v$shared_pool_advice
PGA 目標建議 — v$pga_target_advice
數據緩存建議 — v$db_cache_advice
Java 池建議 — v$java_pool_advice
這些實用程序是確保自調整變化正確合理的一種極好的方式以下內容將顯示如何調用和解釋這些顧問實用程序當您能夠輕松地解釋它們的輸出時您就可以編寫自動化的腳本來生成建議解釋輸出並自動改變 RAM 區域的大小
共享池建議實用程序
這一顧問功能在 Oraclei Database Release 中得到了擴展包含了一個稱為 v$shared_pool_advice 的新的建議實用程序在將來的版本中它可能最終將被擴展至所有的 SGA RAM
從 Oraclei Database Release 開始當共享池的大小從當前值的 % 變為當前值的 % 時v$shared_pool_advice 視圖將顯示 SQL 分析的邊際差異
共享池建議實用程序非常易於配置安裝後您可以運行一個簡單的腳本來查詢 v$shared_pool_advice 視圖並查看不同 shared_pool 大小的 SQL 分析的邊際變化以下腳本的輸出將告訴您動態增加或減少 shared_pool_size 參數帶來的變化
************************************************
Display shared pool advice
************************************************
set lines
set pages
column c heading Pool |Size(M)
column c heading Size|Factor
column c heading Est|LC(M)
column c heading Est LC|Mem Obj
column c heading Est|Time|Saved|(sec)
column c heading Est|Parse|Saved|Factor
column c heading Est|Object Hits format
SELECT
shared_pool_size_for_estimate c
shared_pool_size_factor c
estd_lc_size c
estd_lc_memory_objects c
estd_lc_time_saved c
estd_lc_time_saved_factor c
estd_lc_memory_object_hits c
FROM
v$shared_pool_advice;
Est Est
Time Parse
Pool Size Est Est LC Saved Saved Est
Size(M) Factor LC(M) Mem Obj (sec) Factor Object Hits
下面我們看一下共享池在當前大小的 % 到當前大小的 % 的變化范圍內的統計數字這些統計數字可以使您很好地了解 shared_pool_size 的真正大小如果您使用自動化的 alter system 命令來自動調整 SGA 區域的大小那麼創建這種輸出並編寫一個程序來解釋結果是確保共享池和庫緩存始終有足夠的 RAM 的一種極好的方式接下來讓我們看看我們如何能夠隨時跟蹤共享池的使用並顯示信號圖從而允許我們預測那些需要共享池調整的時間
我們可以使用 Statspack 來創建一個列表來顯示那些庫緩存丟失率低於指定水平的時間如列表 中所示以下輸出顯示在每天上午 : 到 : 之間共享池重復出現 RAM 不足
Cache Misses Library Cache
Yr Mo Dy Hr execs While Executing Miss Ratio
在這個例子中DBA 需要在內存不足的時間段內為 shared_pool_size 安排額外的 RAM
顯示 pga_aggregate_target 的信號圖
Oracle Database g 中的 PGA 區域非常重要因為它控制排序操作和 SQL 散列連接的速度當以下條件的任何一個為真時您可能需要動態地修改 pga_aggregate_target 參數
無論何時當 v$sysstat 統計的用於一次通過的估計 PGA 內存的值超過 pga_aggregate_target 時您需要增加 pga_aggregate_target
無論何時當 v$sysstat 統計的工作區執行 — 多次通過的值大於百分之一時數據庫就可以從額外的 RAM 內存中獲益
有可能 PGA 內存分配過度無論何時當 v$sysstat 行工作區執行 — 最優的值始終測量為百分之百時您可以考慮減少 pga_aggregate_target 的值
v$pgastat 視圖提供了例程級的 PGA 使用率匯總統計和自動的內存管理器要獲得一個快速的概覽一次簡單的查詢可以為所有 Oracle Database g 連接提供一個極好的總體 PGA 使用統計表
check_pgasql
*************************************************************
Display detailed PGA statistics
*************************************************************
column name format a
column value format
select
name
value
from
v$pgastat
;
該查詢的輸出可能如下所示
NAME VALUE
aggregate PGA auto target
global memory bound
total expected memory
total PGA inuse
total PGA allocated
maximum PGA allocated
total PGA used for auto workareas
maximum PGA used for auto workareas
total PGA used for manual workareas
maximum PGA used for manual workareas
estimated PGA memory for optimal
maximum PGA memory for optimal
estimated PGA memory for onepass
maximum PGA memory for onepass
在之前 v$pgastat 的顯示中我們看到了下面這些重要的統計量
用於自動工作區的全部 PGA — 這個統計量監控運行在自動內存模式下的所有連接的 RAM 使用記住不是所有的內部過程都被 Oracle 允許使用自動內存特性例如Java 和 PL/SQL 將分配 RAM 內存而它不會計入總體的 PGA 統計因此您應當從分配的總體 PGA 中減去這個值以查看連接使用的內存和 Java 及 PL/SQL 使用的 RAM 內存
用於最優/一次通過的估計 PGA 內存 — 這個統計量估計在最優模式下執行所有的任務連接 RAM 需求需要多少內存記住當 Oracle Database g 遇到內存不足時DBA 將調用多次通過操作來試圖找到當前釋放的 RAM 內存這個統計量對於監控 Oracle Database g 中的 RAM 消費至關重要大多數 Oracle DBA 將把 pga_aggregate_target 增加到這個值
在 Oracle Database g 中您可以使用名稱為 v$pga_target_advice 的新的顧問實用程序這個實用程序將顯示不同大小的 pga_aggregate_target （范圍從當前值的 % 到 %）在最優一次通過和多次通過 PGA 執行中的邊際變化
列表 顯示了使用這個新的實用程序的一個示例查詢下面是一個輸出示例這裡我們看到對於當前的處理我們已經為 pga_aggregate_target 分配了過多的內存從這個區域取出內存並將其分配到其它地方是安全的
Estimated Estimated
Target(M) Cache Hit % OverAlloc
<= current size
您可以看到您可以容易地創建自動化的方法來檢測 PGA 內存不足（利用 Statspack）並編寫作業來動態改變 pga_aggregate_target以便為排序和散列連接確保最優的 RAM 使用率
From:http://tw.wingwit.com/Article/program/Oracle/201311/16688.html
