另一篇: ORA-04030 私有内存超出
今天遇到ORA-04030错误：
Fri May 14 22:13:10 2021
Dumping diagnostic data in directory=[cdmp_20210514221310], requested by (instance=1, osid=135158 (J003)),
summary=[incident=9881].
Errors in file /u01/app/oracle/diag/rdbms/idsrdb/idsrdb/incident/incdir_9880/idsrdb_j003_135158_i9880.trc:
ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Dumping diagnostic data in directory=[cdmp_20210514221312], requested by (instance=1, osid=135158 (J003)),
summary=[incident=9882].
下面是相关的trace文件的头部的部分信息：
*** 2021-05-14 22:13:06.740
*** SESSION ID:(10.11475) 2021-05-14 22:13:06.740
*** CLIENT ID:() 2021-05-14 22:13:06.740
*** SERVICE NAME:(SYS U S E R S ) 2021 − 05 − 1422 : 13 : 06.740 ∗ ∗ ∗ M O D U L E N A M E : ( D B M S S C H E D U L E R ) 2021 − 05 − 1422 : 13 : 06.740 ∗ ∗ ∗ A C T I O N N A M E : ( O R A USERS) 2021-05-14 22:13:06.740 *** MODULE NAME:(DBMS_SCHEDULER) 2021-05-14 22:13:06.740 *** ACTION NAME:(ORA USERS)2021−05−1422:13:06.740∗∗∗MODULENAME:(DBMSSCHEDULER)2021−05−1422:13:06.740∗∗∗ACTIONNAME:(ORAAT_SQ_SQL_SW_9114) 2021-05-14 22:13:06.740Dump continued from file:
/u01/app/oracle/diag/rdbms/idsrdb/idsrdb/trace/idsrdb_j003_135158.trc
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
通过ACTION NAME部分可用看出来，这是AUTO SQL TUNING触发的错误。下面是触发这个错误的SQL。
/* SQL Analyze(10,1) */
Merge Into TXN_IO_EXPENSE_DAYLY P
Using (Select F_GET_ID() IO_EXPENSE_DAYLY_ID,
T.ENT_ID,
T.SOURCE_BILL_TYPE,
T.AP_TYPE,
T.ORGN_ID,
T.BC_ID,
T.VENDOR_ID,
T.PROM_FUND_ID,
T.BRAND_ID,
TO_DATE(T.CHECK_DATE, 'YYYY-MM-DD') DAYLY,
T.CUSTOMER_ID,
T.SALESREP_ID,
T.SALES_CHANNEL_ID,
T.STORE_TYPE_ID,
Sum(T.AMOUNT) AMOUNT,
T.SALES_MODE
From (With RS As (Select Distinct TRUNC(T.CHECK_DATE) CHECK_DATE
From TXN_EXP T
Where T.LAST_UPDATE_DATE > :B2
And T.LAST_UPDATE_DATE <= :B1), R As (Select T.CHECK_DATE,
F_GET_DATELIST(T.CHECK_DATE) DATELIST
From RS T), DATELIST As (Select B.CHECK_DATE,
B.DATELIST
From (Select T.CHECK_DATE,
REGEXP_SUBSTR(T.DATELIST,
'[^,]+',
1,
L) DATELIST
From R T,
(Select Level L
From DUAL
Connect By Level <= 31)
Where L <=
LENGTH(T.DATELIST) -
LENGTH(Replace(T.DATELIST,
',')) + 1) B
Order By B.CHECK_DATE,
B.DATELIST)
Select T.ENT_ID,
T.SOURCE_BILL_TYPE,
T.AP_TYPE,
T.ORGN_ID,
COALESCE(T.BC_ID, 'N/A') BC_ID,
COALESCE(T.VENDOR_ID, '0') VENDOR_ID,
T.EXPENSE_ID PROM_FUND_ID,
COALESCE(T.BRAND_ID, '0') BRAND_ID,
T1.DATELIST CHECK_DATE,
COALESCE(T.CUSTOMER_ID, '0') CUSTOMER_ID,
COALESCE(T.SALESREP_ID, '0') SALESREP_ID,
COALESCE(CM.SALES_CHANNEL_ID, '0') SALES_CHANNEL_ID,
COALESCE(CM.STORE_TYPE_ID, '0') STORE_TYPE_ID,
COALESCE(T.AMOUNT, 0) AMOUNT,
COALESCE(T.SALES_MODE, 'JX') SALES_MODE
From TXN_EXP T
Left Join DATELIST T1
On TRUNC(T.CHECK_DATE) = T1.CHECK_DATE
Left Join AR_CUSTOMER_MST CM
On T.CUSTOMER_ID = CM.CUSTOMER_ID
Where T.LAST_UPDATE_DATE > :B2
And T.LAST_UPDATE_DATE <= :B1
And T.SOURCE_BILL_TYPE In ('CAP', 'VAP')) T
Group By T.ENT_ID,
T.SOURCE_BILL_TYPE,
T.AP_TYPE,
T.ORGN_ID,
T.BC_ID,
T.VENDOR_ID,
T.PROM_FUND_ID,
T.BRAND_ID,
TO_DATE(T.CHECK_DATE, 'YYYY-MM-DD'),
T.CUSTOMER_ID,
T.SALESREP_ID,
T.SALES_CHANNEL_ID,
T.STORE_TYPE_ID,
T.SALES_MODE
) NP
On (P.ENT_ID = NP.ENT_ID And P.SOURCE_BILL_TYPE = NP.SOURCE_BILL_TYPE And P.AP_TYPE = NP.AP_TYPE And P.ORGN_ID = NP.ORGN_ID And P.BC_ID = NP.BC_ID And P.VENDOR_ID = NP.VENDOR_ID And P.PROM_FUND_ID = NP.PROM_FUND_ID And P.BRAND_ID = NP.BRAND_ID And P.DAYLY = NP.DAYLY And P.CUSTOMER_ID = NP.CUSTOMER_ID And P.SALESREP_ID = NP.SALESREP_ID And P.SALES_CHANNEL_ID = NP.SALES_CHANNEL_ID And P.STORE_TYPE_ID = NP.STORE_TYPE_ID And P.SALES_MODE = NP.SALES_MODE)
When Matched Then
Update
Set P.AMOUNT = P.AMOUNT + NP.AMOUNT
Where P.ENT_ID = NP.ENT_ID
And P.SOURCE_BILL_TYPE = NP.SOURCE_BILL_TYPE
And P.AP_TYPE = NP.AP_TYPE
And P.ORGN_ID = NP.ORGN_ID
And P.BC_ID = NP.BC_ID
And P.VENDOR_ID = NP.VENDOR_ID
And P.PROM_FUND_ID = NP.PROM_FUND_ID
And P.BRAND_ID = NP.BRAND_ID
And P.DAYLY = NP.DAYLY
And P.CUSTOMER_ID = NP.CUSTOMER_ID
And P.SALESREP_ID = NP.SALESREP_ID
And P.SALES_CHANNEL_ID = NP.SALES_CHANNEL_ID
And P.STORE_TYPE_ID = NP.STORE_TYPE_ID
And P.SALES_MODE = NP.SALES_MODE
When Not Matched Then
Insert
(IO_EXPENSE_DAYLY_ID, ENT_ID, SOURCE_BILL_TYPE, AP_TYPE, ORGN_ID, BC_ID, VENDOR_ID,
PROM_FUND_ID, BRAND_ID, DAYLY, CUSTOMER_ID, SALESREP_ID, SALES_CHANNEL_ID, STORE_TYPE_ID,
AMOUNT, SALES_MODE)
Values
(NP.IO_EXPENSE_DAYLY_ID, NP.ENT_ID, NP.SOURCE_BILL_TYPE, NP.AP_TYPE, NP.ORGN_ID, NP.BC_ID,
NP.VENDOR_ID, NP.PROM_FUND_ID, NP.BRAND_ID, NP.DAYLY, NP.CUSTOMER_ID, NP.SALESREP_ID,
NP.SALES_CHANNEL_ID, NP.STORE_TYPE_ID, NP.AMOUNT, NP.SALES_MODE)
从这里也可用看出这个错误是由AUTO SQL TUNING触发的，这个错误的原因是遇到了单个进程最多只能使用PGA只有4G的限制导致，下面是trace中的相关信息。
Global SGA Info ---------------
global target: 4096 MB auto target: 621 MB max pga: 819 MB pga limit: 1638 MB pga limit known: 0 pga limit errors: 0 pga inuse: 3405 MB pga alloc: 4555 MB pga freeable: 44 MB pga freed: 37160953 MB pga to free: 0 % broker request: 0 pga auto: 0 MB pga manual: 0 MB pga alloc (max): 6438 MB pga auto (max): 5443 MB pga manual (max): 1 MB # workareas : 1 # workareas(max): 448
与原文情况不符
还有一种解决方法，当然只对本案例有效，因为本案例ORA-04030错误是由于AUTO SQL TUNING导致的，而AUTO SQL TUNING对我来讲又没啥用，完全可用通过禁用AUTO SQL TUNING来解决这个问题。
可用通过下马的方法关闭AUTO SQL TUNING。
Begin
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'sql tuning advisor',
OPERATION => Null,
WINDOW_NAME => Null);
End;
/
如果需要开启AUTO SQL TUNING，可用通过下面的方法来开启。
Begin
DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'sql tuning advisor',
OPERATION => Null,
WINDOW_NAME => Null);
End;
/
针对单个进程只能使用4G的PGA导致的ORA-04030错误的问题，具体还要看是什么原因导致的，本案例特殊，由于是AUTO SQL TUNING触发的，而AUTO SQL TUNING对我这套数据库来讲并没什么用，所以可用通过禁用AUTO SQL TUNING的方式来解决，如果是业务程序的存储过程等PL/SQL导致的这个错误，就不能这样解决了，只能从上面的修改操作系统单个进程打开内存映射条目数或者修改数据库对应的每个映射条目内存分配大小来解决了。
422
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
