pga_aggregate_target和_pga_max_size都不能絕對限制實際PGA的使用--ora04030

531968912發表於2017-07-10

 和sga_target不同，pga_aggregate_target並不能限制PGA的大小。 另一個差別是，SGA是資料庫啟動時按照sga_max_size預先分配的，而PGA則是“按需分配”的。


       以下詳細解釋下，為什麼pga_aggregate_target並不能限制PGA的大小。一般的文件會把PGA分為stack space和UGA兩個部分，但這種提法太籠統，不利於我們理解這個問題。我們把它分為work area和work area之外的部分。

       所謂work area，就是session要執行SQL，需要在資料庫內部分配的，為了存放中間結果的記憶體。 比如sort area，為了排序用的記憶體，比如hash area，為了hash join用的記憶體，這一部分直接和SQL執行相關，影響SQL執行的效率，比如更大hash area會讓hash join更快。pga_aggregate_target實際上只限制work area的大小。當work area達到pga_aggregate_target的限制，則會產生4030錯誤。隱含引數_pga_max_size只是限制單個程式使用的work area大小，也是在pga_aggregate_target的限制之內的更小限制。而work area之外的記憶體，不被pga_aggregate_target和_pga_max_size所限制。所以你經常會看到PGA的大小超過了pga_aggregate_target。這通常是因為PL/SQL中的變數和陣列中裝入了巨大的資料造成的。通常的例子是bulk collect，imp/exp，sql loader等工具也會產生類似的問題。

      我下面給出一個具體的示例：
alter system set "_pga_max_size"=10m scope=both;
alter system set pga_aggregate_target=100m scope=both;
SQL> shutdown immediate
startup
SQL> select ksppinm as "hidden parameter", ksppstvl as "value" from x$ksppi join x$ksppcv  using (indx) where ksppinm like '%_pga_max_size%' order by ksppinm;
hidden parameter
--------------------------------------------------------------------------------
value
--------------------------------------------------------------------------------
_pga_max_size
10485760
<=========限制單個程式使用PGA 100M(其實是work area)

執行一個PL/SQL，使用bulk collect使其記憶體使用大大超過pga_aggregate_target和_pga_max_size：
DECLARE
  CURSOR c1 IS
    SELECT *
    FROM oracle.employees;
  TYPE emp_row IS TABLE OF c1%ROWTYPE;
  table_set  emp_row;
BEGIN
  -- Assign values to nested table of records:
  SELECT *
    BULK COLLECT INTO table_set
    FROM oracle.employees;
  -- Print nested table of records:

    FOR i IN table_set.FIRST .. table_set.LAST LOOP
      DBMS_OUTPUT.PUT_LINE (
        table_set(i).email || ' ' ||
        table_set(i).last_name  || ', ' ||
        table_set(i).first_name
      );
    END LOOP;END;
/

select pid,spid,program,pga_used_mem/1024/1024,pga_alloc_mem/1024/1024 from v$process
where spid=3735758

PID SPID         PROGRAM                                          PGA_USED_MEM/1024/1024 PGA_ALLOC_MEM/1024/1024
--- ------------ ------------------------------------------------ ---------------------- -----------------------
19    3735758    oracle@nascds5 (TNS V1-V3)    616.22734165191650390625    616.63962650299072265625
《=========PGA使用了600M

select a.name,to_char(b.value,'999,999,999') value
from v$statname a,v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga memory%';

NAME                                                             VALUE
---------------------------------------------------------------- ------------
session uga memory                                                  1,896,024
session uga memory max                                              1,896,024
session pga memory                                                  2,330,120
session pga memory max                                            647,400,968  <==========Peak PGA size for the session. 647,400,968


12c新追加了一個引數PGA_AGGREGATE_LIMIT來限制PGA的大小：
Limiting process size with database parameter PGA_AGGREGATE_LIMIT (Doc ID 1520324.1) 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2141853/，如需轉載，請註明出處，否則將追究法律責任。

相關文章