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

2024-08-29 13:39:23

字體：大中小

來源：轉載

供稿：網友

隨著數據庫治理員在自調整工作方面變得更加成熟，許多 Oracle 規格可能變為自調整。在 Oracle Database 10g 中，我們將看到比以前更多的自調整功能。例如，Oracle Database 10g 的動態內存分配特性使得創建一個自調整的 Oracle SGA 成為可能。通過演示，在本文中我將說明如何檢查 Oracle 9i Database 中的 Oracle 例程，以及根據服務器上和數據庫內的處理需求來調整 sort_area_size 或 pga_aggregate_target、large_pool_size、sga_max_size 和 db_cache_size 的內存區域。這里討論的技巧的基礎是使用 Statspack 來隨時監控內存區域并顯示系統資源利用率的信號圖。我還將討論如何創建一種智能機制，以根據當前的處理需求來自動地重新配置 Oracle9i Database，并提供了示例代碼，這些示例代碼將使您能夠開始編寫自己的能夠有效地仿效 Oracle Database 10g 功能的自動化腳本；例如，我將提供一個腳本，它將自動識別小型、常用的程序段，并將它們分配給 KEEP 池，以全部進行緩存。（重要注重事項：這種仿效僅考慮外部的行為，但不反映新版本的內部實施。）因為每個數據庫都各不相同，所以為了清楚起見，這些腳本特意進行了省略和簡化。因此，您將需要擴展這些示例并編寫適合您的環境的自定義的自動化腳本。具有以下特性的商店將從自動化的自調整中最大程度的獲益：雙模式系統 — 在在線事務處理 (OLTP) 和數據倉庫處理模式之間轉換的系統尤其將從自調整 RAM 區域中獲益。 32 位的商店 — 運行 32 位服務器的商店受其 RAM 區域大小（最大約為 1.7GB）的限制。對于這些商店，最有效地使用 RAM 資源尤為重要。記住擁有一個非常大的 db_cache_size 的趨勢正在下降也很重要。雖然對數據的直接訪問是利用散列法來完成的，但有時數據庫必須檢查 RAM 緩存中的所有內存塊：高失效率的系統 — 無論何時當程序產生一個截斷表、使用臨時表或運行一次大型的數據清除時，Oracle 必須清除 db_cache_size 中的所有內存塊，以除去已被使用的內存塊。對于擁有大于 10gB 的 db_cache_size 的系統，這種方法可能造成過多的開銷。更新率高的系統 — 當執行一次異步寫操作時，數據庫寫入器 (DBWR) 過程必須清除 db_cache_size 中的所有內存塊。擁有一個巨大的 db_cache_size 可能給數據庫寫入器造成過重的負擔。首先，讓我們回顧一下創建自調整數據庫背后的準則。 自調整背后的準則 重新配置一個 Oracle 例程的最常用的技巧是使用一個腳本，并通過 dbms_job 或一個外部調度程序（如 UNIX cron）來調用這個腳本。為了說明一個簡單的例子，考慮一個白天在 OLTP 模式下運行，而晚上在數據倉庫模式下運行的數據庫。對于這種類型的數據庫，您可以安排一項作業來將例程 SGA 內存重新配置成最適合于在該 Oracle 例程中執行的處理類型的配置。列表 1 包含一個 UNIX 腳本，該腳本用來重新配置 Oracle，以便進行決策支持處理。注重為了適應數據倉庫行為，對 shared_pool、db_cache_size 和 pga_aggregate_target 中的配置作了重要的修改。該腳本被安排在每晚 6:00pm 通過 dbms_job 來調用。在列表 1 中，我們看到了建立了自調整 Oracle Database 10g 的基礎的 alter system 命令。記住 RAM 是一種昂貴的 Oracle 服務器資源，DBA 有責任在服務器上充分地分配 RAM 資源。未得到利用的 RAM 將浪費昂貴的硬件資源。即使得到了充分的分配，分配過度的 RAM 也是一種浪費。例如，當您只需要 200m 時，分配一個 shared_pool_size=400m 是效率低下的，因為 RAM 可以被 SGA 的另一個區域（如 db_cache_size）使用。為了說明 RAM 重新配置的概念，考慮下面這個例子，一個分配不足且數據緩沖命中率很低的 16K 數據緩沖區，和一個分配過度且數據緩沖命中率很高的 32K 數據緩沖區（參見圖 1）。自動調整 Oracle9i Database：Oracle SGA(1)（圖一）

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

圖 1：分配過度和分配不足的 RAM 區域使用 alter system 命令，我們可以在數據緩沖區之間調整 RAM，以將 RAM 重新分配給最需要的地方（參見圖 2）。自動調整 Oracle9i Database：Oracle SGA(1)（圖二）

    圖 2：RAM 的動態重分配
    您可以在很多種 Oracle 腳本（包括動態 SQL、dbms_job 和 shell 腳本）中使用 alter system 命令。列表 2 是調整 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: 1048576    alter system set shared_pool_size = 49283072;
    System altered.    alter system set db_cache_size = 17825792;    System altered.    現在我們看到了在 Oracle Database 10g 中，如何輕易地改變 RAM 區域，下面讓我們研究一下調用 RAM 區域自動調整的一些規則。    何時觸發動態重配置    無論何時當監控腳本的例程指示有一個負擔過重的 RAM 區域時，您必須選擇從哪一個區域中“竊取”內存。表 1 顯示了閾值條件的一個簡單的示例，該閾值條件觸發 SGA 的三個主要區域的動態內存修改。當然，每個系統都各不相同，您將要根據您的需求來調整這些閾值。例如，許多商店實施了多個 blocksize，并分離了 db_32k_cache_size （用于索引表空間）、db_keep_cache_size（用于小型、引用頻繁的對象）等的 RAM 區域。    記住數據庫的需求將根據正在執行的 SQL 不斷變化是很重要的；在 9:00am 最優的一個 SGA 可能在 3:00pm 就不是最優了。為了了解處理特性的變化，您可以運行 Statspack 報表來查明 Oracle 改變 RAM 存儲需求的那些時間。您還可以運行 v$db_cache_advice、v$pga_target_advice、v$java_pool_advice 和 v$db_shared_pool_advice 實用程序來查看 RAM 區域大小的變化帶來的邊際效益。    一種使動態 SGA 重新配置自動化的流行的方法是識別趨勢。您可以使用 Statspack 來猜測那些處理特性變化的時間，并使用 dbms_job 程序包或動態 SQL 來執行特定的 SGA 修改。讓我們具體了解一下基于趨勢的方法。    顯示系統信號圖    基于趨勢的重新配置的一種常見的方法是使用 Statspack 歷史數據來顯示可猜測的趨勢，并根據信號圖用這些趨勢來修改數據庫。    這種方法與零庫存生產很相似，其中零部件正好在需要組裝時才出現在工廠車間里。Oracle Database 10g 使 DBA 能夠預見處理需求并定期地安排適當的干預操作，從而確保為處理需求的變化即時地提供 SGA 資源。    自調整 Oracle 的內存區域涉及到改變幾個 Oracle 參數的值。雖然存在 250 多個 Oracle Database 10g 參數來治理數據庫的各方面配置，但只有少數幾個參數對自動的 Oracle SGA 調整很重要：    db_cache_size — db_cache_size 確定 Oracle SGA 中的數據庫塊緩沖的數量，并且代表著 Oracle 內存最重要的一個參數。    db_keep_cache_size — 這個數據緩沖池是 Oracle8i 中 db_block_buffers 的一個子緩沖池，但從 Oracle9i Database 開始成為一個單獨的 RAM 區域。    db_nn_cache_size — Oracle Database 10g 有單獨的數據緩沖池，您可以使用這些數據緩沖池來分離數據并分離具有不同 I/O 特性的對象。    shared_pool_size — shared_pool_size 定義系統中由所有用戶共享的池，包括 SQL 區域和數據字典緩存。    pga_aggregate_target — pga_aggregate_target 定義為系統范圍的排序和散列連接保留的 RAM 區域。    您可以看到，甚至不需要對您的 Oracle 數據庫狀況的最重要的量度進行歸零校正。讓我們從檢查庫緩存中的趨勢開始，并確定如何自動調整 shared_pool_size。    使用 Oracle Database 10g 顧問實用程序    Oracle Database 10g 擁有完整的顧問實用程序，它們將準確地猜測改變任意的 RAM 區域大小將帶來的變化。Oracle Database 10g 中的顧問實用程序包括：    共享池建議 — v$shared_pool_advice    PGA 目標建議 — v$pga_target_advice
    數據緩存建議 — v$db_cache_advice    Java 池建議 — v$java_pool_advice    這些實用程序是確保自調整變化正確合理的一種極好的方式。以下內容將顯示如何調用和解釋這些顧問實用程序；當您能夠輕松地解釋它們的輸出時，您就可以編寫自動化的腳本來生成建議、解釋輸出，并自動改變 RAM 區域的大小。    共享池建議實用程序    這一顧問功能在 Oracle9i Database Release 2 中得到了擴展，包含了一個稱為 v$shared_pool_advice 的新的建議實用程序，在將來的版本中它可能最終將被擴展至所有的 SGA RAM。    從 Oracle9i Database Release 2 開始，當共享池的大小從當前值的 10% 變為當前值的 200% 時，v$shared_pool_advice 視圖將顯示 SQL 分析的邊際差異。    共享池建議實用程序非常易于配置：安裝后，您可以運行一個簡單的腳本來查詢 v$shared_pool_advice 視圖，并查看不同 shared_pool 大小的 SQL 分析的邊際變化。以下腳本的輸出將告訴您動態增加或減少 shared_pool_size 參數帶來的變化。-- ************************************************
-- Display shared pool advice
-- ************************************************set lines 100
set pages 999column c1 heading 'Pool Size(M)'
column c2 heading 'SizeFactor'
column c3 heading 'EstLC(M) '
column c4 heading 'Est LCMem. Obj.'column c5 heading 'EstTimeSaved(sec)'
column c6 heading 'EstParseSavedFactor'
column c7 heading 'EstObject Hits'   format 999,999,999SELECT
   shared_pool_size_for_estimate c1,
   shared_pool_size_factor  c2,
   estd_lc_size   c3,
   estd_lc_memory_objects  c4,
   estd_lc_time_saved  c5,
   estd_lc_time_saved_factor c6,
   estd_lc_memory_object_hits c7
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
---------- ---------- ---------- ---------- ---------- ---------- ------------
        48         .5         48      20839    1459645          1 135,756,032
        64      .6667         63      28140    1459645          1 135,756,101
        80      .8333         78      35447    1459645          1 135,756,149
        96          1         93      43028    1459645          1 135,756,253
       112     1.1667        100      46755    1459646          1 135,756,842
       128     1.3333        100      46755    1459646          1 135,756,842
       144        1.5        100      46755    1459646          1 135,756,842
       160     1.6667        100      46755    1459646          1 135,756,842
       176     1.8333        100      46755    1459646          1 135,756,842
       192          2        100      46755    1459646          1 135,756,842    下面我們看一下共享池在當前大小的 50% 到當前大小的 200% 的變化范圍內的統計數字。這些統計數字可以使您很好地了解 shared_pool_size 的真正大小。假如您使用自動化的 alter system 命令來自動調整 SGA 區域的大小，那么創建這種輸出并編寫一個程序來解釋結果是確保共享池和庫緩存始終有足夠的 RAM 的一種極好的方式。接下來，讓我們看看我們如何能夠隨時跟蹤共享池的使用并顯示“信號圖”，從而答應我們猜測那些需要共享池調整的時間。    我們可以使用 Statspack 來創建一個列表來顯示那些庫緩存丟失率低于指定水平的時間，如列表 3 中所示。以下輸出顯示，在天天上午 9:00 到 10:00 之間共享池重復出現 RAM 不足。                        Cache   Misses    Library Cache
Yr. Mo Dy Hr. execs   While Executing   Miss Ratio
--------------- ------- --------------- -------------
2001-12-11 10    10,338          6,443        .64
2001-12-12 10   182,477         88,136        .43
2001-12-14 10   190,707        101,832        .56
2001-12-16 10    72,803         45,932        .62    在這個例子中，DBA 需要在內存不足的時間段內為 shared_pool_size 安排額外的 RAM。    顯示 pga_aggregate_target 的信號圖    Oracle Database 10g 中的 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 10g 連接提供一個極好的總體 PGA 使用統計表：    check_pga.sql-- *************************************************************
-- Display detailed PGA statistics
--
-- *************************************************************
column name format a30
column value format 999,999,999select
   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 的顯示中，我們看到了下面這些重要的統計量：    用于自動工作區的全部 PGA — 這個統計量監控運行在自動內存模式下的所有連接的 RAM 使用。記住，不是所有的內部過程都被 Oracle 答應使用自動內存特性。例如，Java 和 PL/SQL 將分配 RAM 內存，而它不會計入總體的 PGA 統計。因此，您應當從分配的總體 PGA 中減去這個值，以查看連接使用的內存和 Java 及 PL/SQL 使用的 RAM 內存。    用于最優/一次通過的估計 PGA 內存 — 這個統計量估計在最優模式下執行所有的任務連接 RAM 需求需要多少內存。記住，當 Oracle Database 10g 碰到內存不足時，DBA 將調用多次通過操作來試圖找到當前釋放的 RAM 內存。這個統計量對于監控 Oracle Database 10g 中的 RAM 消費至關重要，大多數 Oracle DBA 將把 pga_aggregate_target 增加到這個值。    在 Oracle Database 10g 中，您可以使用名稱為 v$pga_target_advice 的新的顧問實用程序。這個實用程序將顯示不同大小的 pga_aggregate_target （范圍從當前值的 10% 到 200%）在最優、一次通過和多次通過 PGA 執行中的邊際變化。    列表 4 顯示了使用這個新的實用程序的一個示例查詢，下面是一個輸出示例。這里我們看到對于當前的處理，我們已經為 pga_aggregate_target 分配了過多的內存，從這個區域取出內存并將其分配到其它地方是安全的：             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 使用率。

上一篇：oracle中SGA的設置

下一篇：Oracle9i 性能調整與優化(1)