Shulou(Shulou.com)06/01 Report--

In a failure case, the error message is as follows

ORA-04030: the process ran out of memory when trying to allocate 16328 bytes (koh-kghu call, kollrsz)

Oerr checks the error message, but process does not get enough memory. Server process consumes PGA, not SGA.

[oracle@febdb ~] $oerr ora 04030

04030, 00000, "out of process memory when trying to allocate% s bytes (% s)"

/ / * Cause: Operating system process private memory was exhausted.

/ / * Action:

PGA is insufficient. The idea is mainly to check the parameter values of PGA and workarea and the statistical values since the instance was launched, which can be dealt with in four steps. The first step is to query the parameter values, the second step is to query the implicit parameter values, the third step is to query the v$pgastat statistical values, and the fourth step is to analyze the items in the first three steps.

The first step

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

The second step of 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)

The maximum PGA value of a process, which in this case is 1717985280B (the unit of _ pga_max_size is B)

In automatic mode, the maximum work area value of a process's PGA, which in this case is 838860KB (the unit of _ smm_max_size is KB, which is usually 50% of _ pga_max_size)

In automatic mode, the maximum total PGA work area of all processes. In this case, it is the third step (the unit of _ smm_px_max_size is KB, which is usually 50% of the PGA_AGGREGATE_TARGET parameter).

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

-- the parameter value of the current PGA. In this case, it is 8589934592bytes, which is the same as the parameter 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.

-- less than 100% means that sorting and other operations that consume work areas are not necessarily done in PGA, but that work areas takes away the disk and uses temporary table space. In this case, it is 97%.

Maximum PGA allocated

Maximum number of bytes of PGA memory allocated at one time since instance startup

-- the maximum value that PGA has reached, in this case, 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

-- the maximum work areas ever reached in automatic mode, in this case, 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.

-- the maximum work areas ever reached in manual mode, in this case, 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.

-- the real value of the current PGA, which in this case is 15749543936 bytes

The Oracle database attempts to keep this number below the value of the PGA_AGGREGATE_TARGET initialization parameter. However, PGA can be allocated beyond this value in a small percentage in a short period of time, which is possible when the workload in the work area is growing very fast, or when PGA_AGGREGATE_TARGET is set to a small value.

Total PGA inuse

Indicates how much PGA memory is currently consumed by work areas

-- the real value of the current work areas. This is the fourth step of 12480521216bytes in this case

Based on the data of 1, 2, 3 above, analyze whether the PGA is insufficient or whether the workarea_size_policy is set to AUTO.

If the PGA is insufficient, reset the parameter pga_aggregate_target to use a higher value

If the error is guaranteed after the PGA setting is very large, set the parameter value workarea_size_policy to MANUAL, and then set the values of these parameters * area_size according to the business.

-- this case is obviously caused by insufficient PGA. Set the PGA parameter value to 20g.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.