customer had reorged this table before, but in aix 4.3.3 (now aix 5.2), oracle 8.1.7 (now 9.2) and space manager v4. suspected problem using livereorg with a table with longs:
Script Statement 34: CREATE INDEX ERROR: ORA-04030: out of process memory when trying to allocate 1052696 bytes (callheap,kllcqas:kllsltba)
table size 25 Gb.
index size 200M
customer upgraded memory parameters for SGA but not for PGA.
Actually WORKAREA_SIZE_POLICE is MAUAL and PGA_AGGREGATE_TARGET is 0.
session parameters:
db_file_multiblock_read_count = 1024;
sort_area_size = 67108864;
system memory 12 Gb
Total System Global Area 5370782176 bytes
Fixed Size 750048 bytes
Variable Size 1073741824 bytes
Database Buffers 4294967296 bytes
Redo Buffers 1323008 bytes
total PGA allocated:2277095424 bytes
maximum PGA allocated:4358743040 bytes
ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) unlimited
memory(kbytes) unlimited
coredump(blocks) 2097151
nofiles(descriptors) 4000
For your information this is information of the ORA-04030 error message:
ORA-04030 out of process memory when trying to allocate string bytes (string,string)
Cause: Operating system process private memory has been exhausted.
Action: See the database administrator or operating system administrator to increase process memory quota. There may be a bug in the application that causes excessive allocations of process memory space.
PGA_AGGREGATE_TARGET was set to 0 i.e dynamically set by Oracle (which was not sufficient) - solution is to manually set this value (see procedure below).
To determine if PGA_AGGREGATE_TARGET is set correctly, you can use the V$PGASTAT view.
An example of the output of a query against V$PGASTAT might look like this:
NAME VALUE UNIT
--------------------------------------- ------------------- -------
aggregate PGA target parameter 524288000 bytes
aggregate PGA auto target 163435776 bytes
global memory bound 25600 bytes
total PGA inuse 9353216 bytes
total PGA allocated 73516032 bytes
maximum PGA allocated 698371072 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 560744448 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0
total bytes processed 3.0072E+10 bytes
total extra bytes read/written 2.1517E+10 bytes
cache hit percentage 65.97 percent
In particular the following statistics are of interest. The aggregate PGA target parameter displays the current setting of the PGA_AGGREGATE_TARGET parameter. The aggregate PGA auto target value gives the amount of PGA memory that Oracle can use for work areas. This is a derived value and low values indicate that there is little available memory for sort areas. If this number is low, you should consider setting the
PGA_AGGREGATE_TARGET parameter higher.
The global memory bound statistics is another work area that is dynamically set by Oracle, and increases or decreases as workload changes. If this value falls below 1MB then you should increase the PGA_AGGREGATE_TARGET parameter.
If the total PGA allocated parameter exceeds PGA_AGGREGATE_TARGET frequently, then this means that Oracle has to actually allocate more memory to the private work areas than was expected. If this is the case then PGA_AGGREGATE_TARGET should be increased. Further if the over allocation count is large, then this indicates that the PGA_AGGREGATE_TARGET is too small.
You can determine the amount of memory allocated and used, as well as the maximum amount of memory allocated to a given process via columns of the V$PROCESS view. You can query the PGA_USED_MEM,
PGA_ALLOC_MEM and PGA_MAX_MEM columns of V$PROCESS to determine the PGA memory usages for each process. The PGA_USED_MEM column tells you how much memory the process is actually using. The PGA_ALLOC_MEM column indicates how much memory has been allocated to the process (some of which may not be in use). Finally the PGA_MAX_MEMORY indicates the maximum amount of memory that has been allocated to that session during it's lifetime.
Linked Case is 238998
