PGA引發(fā)的ORA-04030報錯的處理思路

一個故障案例，報錯信息如下
ORA-04030: 在嘗試分配 16328 字節(jié) (koh-kghu call ,kollrsz) 時進程內(nèi)存不足

oerr查看報錯信息，是process獲取不到足夠的內(nèi)存，server process消耗的是PGA，而非SGA
[oracle@febdb ~]$ oerr ora 04030
04030, 00000, "out of  process  memory when trying to allocate %s bytes (%s,%s)"
// *Cause:  Operating system  process private memory  was exhausted.
// *Action:
PGA不足，思路主要查看PGA、workarea的參數(shù)值和實例啟動以來的統(tǒng)計值，可以分4步來處理，第一步查詢參數(shù)值、第二步查詢隱含參數(shù)值、第三步查詢v$pgastat統(tǒng)計值、第四步分析前面三步的各項值得出結(jié)論

第一步
SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 8G

SQL> show parameter area_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size               integer     1048576
create_bitmap_area_size              integer     8388608
hash_area_size                       integer     131072
sort_area_size                       integer     65536
workarea_size_policy                 string      AUTO 第二步
SQL> col NAME format a25
SQL> col VALUE format a20
SQL> col DESCRIPTION format a55
SQL> set linesize 110
SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%_pga_max%';
NAME            VALUE           DESCRIPTION
--------------- --------------- --------------------------------------------------
_pga_max_size   1717985280      Maximum size of the PGA memory for one process SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%smm_max%';
NAME                 VALUE           DESCRIPTION
-------------------- --------------- -------------------------------------------------------
_smm_max_size_static 838860          static maximum work area size in auto mode (serial)
_smm_max_size        838860          maximum work area size in auto mode (serial)

SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%smm_px%';
NAME                      VALUE           DESCRIPTION
------------------------- --------------- -------------------------------------------------------
_smm_px_max_size_static   4194304         static maximum work area size in auto mode (global)
_smm_px_max_size          4194304         maximum work area size in auto mode (global)

一個進程最大的PGA值，本案例中是 1717985280B （_pga_max_size的單位是B）
自動模式下，一個進程的PGA的最大work area值，本案例中是838860KB（_smm_max_size的單位是 KB ，此值一般是_pga_max_size的50%）
自動模式下，所有進程的PGA最大work area總量值，本案例中是4194304KB（_smm_px_max_size的單位是 KB ，此值一般是PGA_AGGREGATE_TARGET參數(shù)的50%） 第三步
SQL> col value format 9999999999999999
SQL> col name format a40
SQL> select * from v$pgastat where name in ('aggregate PGA target parameter','maximum PGA allocated','maximum PGA used for auto workareas','maximum PGA used for manual workareas','total PGA allocated','total PGA inuse','cache hit percentage') order by 1;
NAME                                                 VALUE UNIT
---------------------------------------- ----------------- ------------
aggregate PGA target parameter                  8589934592 bytes
cache hit percentage                                    97 percent
maximum PGA allocated                          29975256064 bytes
maximum PGA used for auto workareas             3414564864 bytes
maximum PGA used for manual workareas              2713600 bytes
total PGA allocated                            15749543936 bytes
total PGA inuse                                12480521216 bytes

aggregate PGA target parameter
Current value of the PGA_AGGREGATE_TARGET initialization parameter
--當前PGA的參數(shù)值，本案例中為8589934592bytes，和參數(shù)pga_aggregate_target值一樣
cache hit percentage
A value of 100% means that all work areas executed by the system since instance startup have used an optimal amount of PGA memory.
--小于100%表示排序等消耗work areas的操作不一定都是在PGA完成，而是work areas走了磁盤使用了臨時表空間，本案例中為97%
maximum PGA allocated
Maximum number of bytes of PGA memory allocated at one time since instance startup
-- PGA曾經(jīng)達到的最大值 ，本案例中為29975256064bytes
maximum PGA used for auto workareas  
Maximum amount of PGA memory consumed at one time by work areas running under the automatic memory management mode since instance startup
--自動模式下的work areas曾經(jīng)達到的最大值，本案例中為3414564864bytes
maximum PGA used for manual workareas
Maximum amount of PGA memory consumed at one time by work areas running under the manual memory management mode since instance startup.
--手工模式下的work areas曾經(jīng)達到的最大值，本案例中為2713600bytes
total PGA allocated
Current amount of PGA memory allocated by the instance. The Oracle Database attempts to keep this number below the value of the PGA_AGGREGATE_TARGET initialization parameter. However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time when the work area workload is increasing very rapidly or when PGA_AGGREGATE_TARGET is set to a small value.
-- 當前PGA的真實值 ，本案例中為15749543936 bytes
Oracle數(shù)據(jù)庫試圖將這個數(shù)字保持在PGA_AGGREGATE_TARGET初始化參數(shù)的值以下。然而，PGA可以在短時間內(nèi)以較小的百分比分配超過該值，當工作區(qū)域的工作負載增長非?？?，或者當PGA_AGGREGATE_TARGET被設置為一個小的值時，這是可能的。
total PGA inuse  
Indicates how much PGA memory is currently consumed by work areas
-- 當前work areas的真實值 。本案例中為12480521216bytes 第四步
根據(jù)以上1、2、3的數(shù)據(jù)，分析是PGA不足還是workarea_size_policy設置為AUTO的問題。
如果PGA不足，重新設置參數(shù)pga_aggregate_target，使用更大值
如果PGA設置很大后，還是保報錯，則設置參數(shù)值workarea_size_policy為MANUAL，再根據(jù)業(yè)務設置*area_size這些參數(shù)的值。
--本案例明顯是因為PGA不足引發(fā)，設置PGA參數(shù)值為20G解決。