一個故障案例，報錯資訊如下
ORA-04030: 在嘗試分配 16328 位元組 (koh-kghu call ,kollrsz) 時程式記憶體不足

oerr檢視報錯資訊，是process獲取不到足夠的記憶體，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的引數值和例項啟動以來的統計值，可以分4步來處理，第一步查詢引數值、第二步查詢隱含引數值、第三步查詢v$pgastat統計值、第四步分析前面三步的各項值得出結論

第一步
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引數的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的引數值，本案例中為8589934592bytes，和引數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曾經達到的最大值，本案例中為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曾經達到的最大值，本案例中為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曾經達到的最大值，本案例中為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資料庫試圖將這個數字保持在PGA_AGGREGATE_TARGET初始化引數的值以下。然而，PGA可以在短時間內以較小的百分比分配超過該值，當工作區域的工作負載增長非常快，或者當PGA_AGGREGATE_TARGET被設定為一個小的值時，這是可能的。
total PGA inuse
Indicates how much PGA memory is currently consumed by work areas
--當前work areas的真實值。本案例中為12480521216bytes

第四步
根據以上1、2、3的資料，分析是PGA不足還是workarea_size_policy設定為AUTO的問題。
如果PGA不足，重新設定引數pga_aggregate_target，使用更大值
如果PGA設定很大後，還是保報錯，則設定引數值workarea_size_policy為MANUAL，再根據業務設定*area_size這些引數的值。
--本案例明顯是因為PGA不足引發，設定PGA引數值為20G解決。

PGA引發的ORA-04030報錯的處理思路

相關文章