LIUBINGLIN

很多时候不在于你技术有多高,更重要的是你够不够细心、耐心和静心。

  • 博客访问: 5091387
  • 博文数量: 493
  • 用 户 组: 普通用户
  • 注册时间: 2010-01-05 23:06
  • 认证徽章:
个人简介

Oracle数据库管理员,Oracle数据库系统构架员;2012年7月出版《构建最高可用Oracle数据库系统:Oracle 11gR2 RAC管理、维护与性能优化》一书;Oracle 10g OCM。

文章分类

全部博文(493)

分类: Oracle

2015-08-12 12:04:11


    客户的一套Oracle 11.2.0.3 for AIX 6.1的RAC环境每天一直有ORA-04030的报错,能够确定的是物理内存充足,操作系统oracle用户ulimit没有任何限制,PGA的大小跟此报错没有关系(增大PGA或者缩小PGA的值都会报这个错)

一.告警日志文件。
数据库ALERT日志报错如下:

......
2015-08-10 22:29:16.438000 +08:00
Errors in file /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/incident/incdir_133659/ynsyn13_ora_14352442_i133659.trc:
ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)
Errors in file /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/trace/ynsyn13_ora_14352442.trc  (incident=133661):
ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)
Incident details in: /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/incident/incdir_133661/ynsyn13_ora_14352442_i133661.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Archived Log entry 139978 added for thread 1 sequence 161599 ID 0x515a23a dest 1:
Dumping diagnostic data in directory=[cdmp_20150810222917], requested by (instance=1, osid=14352442), summary=[incident=133660].
2015-08-10 22:29:18.455000 +08:00
Errors in file /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/incident/incdir_133659/ynsyn13_ora_14352442_i133659.trc:
ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)
......

二.trace文件。
trace文件的内容如下:
Dump file /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/incident/incdir_145646/ynsyn13_ora_11075592_i145646.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: AIX
Node name: lpar2
Release: 1
Version: 6
Machine: 00F94D394C00
Instance name: ynsyn13
Redo thread mounted by this instance: 1
Oracle process number: 38
Unix process pid: 11075592, image: oracle@lpar2


*** 2015-08-11 02:37:33.105
*** SESSION ID:(434.341) 2015-08-11 02:37:33.105
*** CLIENT ID:() 2015-08-11 02:37:33.105
*** SERVICE NAME:(ynsyn13) 2015-08-11 02:37:33.105
*** MODULE NAME:(SQL*Plus) 2015-08-11 02:37:33.105
*** ACTION NAME:() 2015-08-11 02:37:33.105
 
Dump continued from file: /u01/app/oracle/diag/rdbms/ynsyn13/ynsyn13/trace/ynsyn13_ora_11075592.trc
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)


========= Dump for incident 145646 (ORA 4030) ========
----- Beginning of Customized Incident Dump(s) -----
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
39%   43 MB, 907 chunks: "kllcqas:kllsltba          "  SQL
         QERHJ hash-joi  ds=1110841a0  dsprt=110bb1268
29%   33 MB, 562 chunks: "permanent memory          "  SQL
         sort subheap    ds=111083eb8  dsprt=110bb1268
12%   13 MB, 907 chunks: "free memory               "  SQL
         QERHJ hash-joi  ds=1110841a0  dsprt=110bb1268
 7% 8160 KB,   9 chunks: "QERHJ list array          "  SQL
         QERHJ hash-joi  ds=1110841a0  dsprt=110bb1268
 4% 4096 KB,   1 chunk : "HT buckets                "  SQL
         QERHJ hash-joi  ds=1110841a0  dsprt=110bb1268
 3% 2931 KB,  29 chunks: "kllcqc:kllcqslt           "  SQL
         QERHJ hash-joi  ds=1110841a0  dsprt=110bb1268
 2% 2048 KB,  16 chunks: "QERHJ Bit vector          "  SQL
         QERHJ hash-joi  ds=1110841a0  dsprt=110bb1268
 1% 1184 KB, 213 chunks: "free memory               "  
         pga heap        ds=110004c20  dsprt=0
 1%  836 KB,  25 chunks: "permanent memory          "  
         pga heap        ds=110004c20  dsprt=0
 1%  660 KB, 1442 chunks: "free memory               "  
         session heap    ds=110954758  dsprt=110101b80

<<<<这个地方显示的是该进程(服务器进程)使用的PGA内存情况,可以看出该进程的PGA内存分配已经接近100MB。

......


三.数据库参数配置。

数据库实例的配置参数如下:
SELECT a.ksppinm Param,
         b.ksppstvl SessionVal,
         c.ksppstvl InstanceVal,
         a.ksppdesc Descr
    FROM x$ksppi a, x$ksppcv b, x$ksppsv c
   WHERE     a.indx = b.indx
         AND a.indx = c.indx
         AND a.ksppinm LIKE '/_%' ESCAPE '/' and a.ksppinm='_smm_max_size'
ORDER BY 1
/

PARAM
--------------------------------------------------------------------------------
SESSIONVAL
--------------------------------------------------------------------------------
INSTANCEVAL
--------------------------------------------------------------------------------
DESCR
--------------------------------------------------------------------------------
_smm_max_size
51200
51200
maximum work area size in auto mode (serial)

<<<<自动模式下,单个进程最大的work area大小。

SQL> SELECT a.ksppinm Param,
  2           b.ksppstvl SessionVal,
  3           c.ksppstvl InstanceVal,
  4           a.ksppdesc Descr
  5      FROM x$ksppi a, x$ksppcv b, x$ksppsv c
  6     WHERE     a.indx = b.indx
  7           AND a.indx = c.indx
  8           AND a.ksppinm LIKE '/_%' ESCAPE '/' and a.ksppinm='_pga_max_size'
  9  ORDER BY 1
 10  /


PARAM
--------------------------------------------------------------------------------
SESSIONVAL
--------------------------------------------------------------------------------
INSTANCEVAL
--------------------------------------------------------------------------------
DESCR
--------------------------------------------------------------------------------
_pga_max_size
104857600
104857600
Maximum size of the PGA memory for one process

<<<< 一个进程最大的PGA内存为100MB。

SQL> show parameter pga


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size                        big integer 100M    <<<< 初始化参数中限制了_pga_max_size大小。
pga_aggregate_target                 big integer 2G
SQL> 
SQL> show parameter workarea


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO
SQL> 
SQL> show parameter target


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 0
memory_target                        big integer 0
pga_aggregate_target                 big integer 2G
sga_target                           big integer 15G
SQL> 

四.pga_aggregate_target和WORKAREA_SIZE_POLICY参数的含义。

>PGA_AGGREGATE_TARGET

Property Description
Parameter type Big integer
Syntax PGA_AGGREGATE_TARGET = integer [K | M | G]
Default value 10 MB or 20% of the size of the SGA, whichever is greater
Modifiable ALTER SYSTEM
Range of values Minimum: 10 MB

Maximum: 4096 GB - 1

Basic Yes

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZE parameters.

Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.

When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory toPGA_AGGREGATE_TARGET.


>WORKAREA_SIZE_POLICY

Property Description
Parameter type String
Syntax WORKAREA_SIZE_POLICY = { AUTO | MANUAL }
Default value AUTO
Modifiable ALTER SESSIONALTER SYSTEM

WORKAREA_SIZE_POLICY specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned.

Values:

  • AUTO

    Work areas used by memory-intensive operators are sized automatically, based on the PGA memory used by the system, the target PGA memory set in PGA_AGGREGATE_TARGET, and the requirement of each individual operator.

  • MANUAL

    The sizing of work areas is manual and based on the values of the *_AREA_SIZE parameter corresponding to the operation (for example, a sort uses SORT_AREA_SIZE). Specifying MANUAL may result in sub-optimal performance and poor PGA memory utilization.

实践证明,可以将PGA_AGGREGATE_TARGET设置为非0值,而将WORKAREA_SIZE_POLICY设置为MANUAL;

五.How To Super-Size Work Area Memory Size Used By Sessions? (Doc ID 453540.1)

In this Document

Goal
Solution
References


Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 08-Jul-2015***

Goal

How to super-size the work area memory size that can be used by any session in the database?

Solution

With the automatic PGA management mode, you can control the size for the work area sizes, to be able perform big sort operations or hash joins in memory and avoid messages like:

kxhfWrite: hash-join is spilling to disk


You have to set the following parameters to proper values:

1. PGA_AGGREGATE_TARGET

  • should be set to five times the desired work area size


2. _PGA_MAX_SIZE

  • should be set in minimum of twice the desired work area size. The default value is 200Mb.


3. _SMM_MAX_SIZE

  • normally this parameter is not needed but maybe under certain circumstances
  • if set it should be equal to the desired work area size (in kb !)


Example:
If you like to use a sort area size of 2GB for a special operation (e.g for the creating of an index on a large table) you could set the values as follows:

PGA_AGGREGATE TARGET = 10G
"_PGA_MAX_SIZE" = 2G
"_SMM_MAX_SIZE" = 2097152


Dynamically, the values must be changed for the full instance using the "alter system" command. It is not allowed changes the above parameters for one session.

Please note that there is an internal limitation that a single workarea size is limited to 2GB.  But you can manually set _pga_max_size above 2gb if you have the fix for bug 17951233.

_PGA_MAX_SIZE is set up to 2GB (1GB limit for 32-bit) per bug 3946308

Also please be careful that the above changes are very dangerous because the values affect all sessions in the database. If you need to have granular control of the work area for a single session, then it is better to set at the session level WORKAREA_SIZE_POLICY=MANUAL and *_AREA_SIZE as high as needed.

NOTE:
PGA_AGGREGATE_TARGET can only be used when using ASMM with Oracle 10g and higher.
When using AMM with Oracle 11g and higher, the PGA size is also automatically managed.

MOS文章写得很清楚,到此应该明白该问题的原因,以及处理该问题的方法了。

   之前的一个案例《
WORKAREA_SIZE_POLICY参数引起的ORA-04030错误http://blog.itpub.net/23135684/viewspace-712772/

--end--
阅读(7192) | 评论(1) | 转发(1) |
给主人留下些什么吧!~~

lusklusklusk2018-03-22 10:11:13

评论热议
请登录后评论。

登录 注册