Need advice on ORA-04030 and pga_aggregate_target parameter
harvi...@gmail.com
Users are getting following errors on 1 of the database machine:
"Oracle.DataAccess.Client.OracleException ORA-04030: out of process
memory when trying to allocate 16396 bytes (koh-kghu call ,pmuccst:
adt/record)"
Configuration: Oracle 10.2.0.1 on RHAT Linux 4, 4GB RAM, SGA-1.2GB,
PGA -600MB, 10 Shared servers
Following is the info from the dynamic views:
SELECT * FROM V$PGASTAT;
NAME VALUE UNIT
aggregate PGA target parameter 629145600 bytes
aggregate PGA auto target 509561856 bytes
global memory bound 104857600 bytes
total PGA inuse 63959040 bytes
total PGA allocated 119723008 bytes
maximum PGA allocated 3512438784 bytes
total freeable PGA memory 14090240 bytes
process count 26
max processes count 33
PGA memory freed back to OS 1027295150080 bytes
total PGA used for auto workareas 1264640 bytes
maximum PGA used for auto workareas 13465600 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 536576 bytes
over allocation count 2173
bytes processed 400043052032 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
recompute count (total) 491550
select
max(pga_used_mem) max_pga_used_mem
, max(pga_alloc_mem) max_pga_alloc_mem
, max(pga_max_mem) max_pga_max_mem
from v$process
/
19164289 40582953 3386319473
SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM,
PGA_MAX_MEM
FROM V$PROCESS;
PROGRAM PGA_USED_MEM PGA_ALLOC_MEM
PGA_FREEABLE_MEM PGA_MAX_MEM
PSEUDO 0 0 0 0
oracle@qalin1 (PMON) 213725 366221 0 366221
oracle@qalin1 (PSP0) 212937 366221 0 366221
oracle@qalin1 (MMAN) 220937 366221 0 366221
oracle@qalin1 (DBW0) 19164289 40582953 131072
44318505
oracle@qalin1 (LGWR) 10902229 23106685 196608
23565437
oracle@qalin1 (CKPT) 308505 1657617 1114112
2771729
oracle@qalin1 (SMON) 1319161 3118733
1507328 3577485
oracle@qalin1 (RECO) 433237 1087117 65536 1087117
oracle@qalin1 (CJQ0) 616505 1676941 786432 2070157
oracle@qalin1 (MMON) 1220449 3249753
1638400 3446361
oracle@qalin1 (MMNL) 218349 431757 0 431757
oracle@qalin1 (D000) 651201 664177 0 1218189
oracle@qalin1 (S000) 5319113 6496881 393216
739582577
oracle@qalin1 (S001) 5378853 7217777
1048576 3380945521
oracle@qalin1 (S002) 8128853 10035825 131072
3381273201
oracle@qalin1 (S003) 4899257 6496881
1048576 3381600881
oracle@qalin1 (S004) 615721 1909361 1179648
3386188401
oracle@qalin1 (S005) 611497 1581681 851968 3386253937
oracle@qalin1 (S006) 616837 1712753 851968 3386122865
oracle@qalin1 (S007) 555533 1516145 851968 3386319473
oracle@qalin1 (S008) 610401 1778289 1048576
3386253937
oracle@qalin1 (S009) 609297 1974897 1114112
3386122865
oracle@qalin1 (J000) 254085 1087117 0
1087117
oracle@qalin1 (QMNC) 222077 366221 0 366221
oracle@qalin1 (q000) 613877 1414797 262144 1414797
oracle@qalin1 (q001) 307561 562829 0 562829
PGA_MAX_MEM for shared servers is ~3GB sometimes, not sure what is
causing that big consumption but the current allocated memory looks
reasonable
SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0;
LOW_KB HIGH_KB OPTIMAL_EXECUTIONS
ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
2 4 101146391 0 0
4 128 43179 0 0
128 256 50124 0 0
256 512 43867 0 0
512 1024 170969 0 0
1024 2048 10837 0 0
2048 4096 148 0 0
4096 8192 55 0 0
8192 16384 16 0 0
16384 32768 4 0 0
No processing caused anything but optimal executions, there are no one
pass or multi pass executions.
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
75 98 4
150 98 4
300 98 4
450 98 4
600 100 4
720 100 4
840 100 4
960 100 4
1080 100 4
1200 100 3
1800 100 0
2400 100 0
3600 100 0
4800 100 0
PGA hit is 100% at 600MB but overalloc count is not 0 until 1800MB.
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total))
percentage
FROM (SELECT name, value cnt, (sum(value) over ()) total
FROM V$SYSSTAT
WHERE name like 'workarea exec%');
PROFILE CNT PERCENTAGE
workarea executions - optimal 101481578 100
workarea executions - onepass 0 0
workarea executions - multipass 0 0
What can be the possible reason for this error and how we determine
what may have caused the maximum memory usage for process to spike to
3GB and since we don't have multiple pass executions what can be the
other reasons?
Thanks
--Harvinder
Ronny
For ORA-04030, increase your large_pool and java_pool_size.
1.2 GB SGA seems quite small for 4 GB RAM.
For your system, you should start with a RAM config like this:
2.5 GB for SGA ("Automatic Shared Memory Management" should work fine
with 10gR2. Please read the docs.)
0.7 GB for PGA
0.8 GB for OS
After that you can start fine tuning (if needed at all).
Regards,
NitelyJoy
DA Morgan
> Your problem is not PGA, but SGA:
> For ORA-04030, increase your large_pool and java_pool_size.
>
> 1.2 GB SGA seems quite small for 4 GB RAM.
Why would you say that? The Oracle rule is 40% of RAM.
The largest recommended would be 1.6GB.
I'd be fascinated to know how you made your recommendation with
respect to Java Pool Size with no evidence than any Java is
involved.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
harvi...@gmail.com
great if you can send me some link that can show how this error can
happen due to incorrect size of SGA (normally we get ora-04031 for SGA
sizing errors)
Mladen Gogala
> Users are getting following errors on 1 of the database machine:
> "Oracle.DataAccess.Client.OracleException ORA-04030: out of process
> memory when trying to allocate 16396 bytes (koh-kghu call ,pmuccst:
> adt/record)"
The error here is: "out of process memory", which means that you
have problems with the address space of the process. On Linux, you
can increase the limits in /etc/security/limits.conf, if there are any.
You can play with data segment size and address space size, both limits
are in KB. You should first check whether there are any limits for user
oracle bu logging into oracle and executing ulimit -a. Also, you should
set overcommit_memory to 1, which will allow processes to allocate memory
without having an adequate space on the swap partition first.
The error "ORA-04030: out of process memory when trying to allocate 16396
bytes (koh-kghu call ,pmuccst: adt/record)" simply means that a malloc
call has failed. Also, check /var/log/messages on the server side, as well
as $ORACLE_BASE/admin/$DB_NAME/bdump/alert_$ORACLE_SID.log
harvi...@gmail.com
log.
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 3145728
max memory size (kbytes, -m) unlimited
open files (-n) 63536
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 557055
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
On May 15, 9:24 am, Mladen Gogala <mgogala.SPAM_ME....@verizon.net>
wrote:
Ronny
> All the listings searched points to PGA for this error, it will be
> great if you can send me some link that can show how this error can
> happen due to incorrect size of SGA (normally we get ora-04031 for SGA
> sizing errors)
OK, you are right, sorry. ORA-04030 points to PGA, not SGA.
Metalink Note:233869.1 seems to be useful here.
Obviously, OS limits are too restrictive in our case (as Mladen Gogala
mentioned, too).
Regards,
NitelyJoy
Ronny
>
> The largest recommended would be 1.6GB.
Example for OLTP systems: "... leaving 80% for the SGA."
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i47856
Mladen Gogala
> Thanks for the info, ulimit looks good and nothing is in the alert
How about the swap partition? What do free or swapon -s say?
--
http://www.mladen-gogala.com
hast...@hotmail.com
> > Why would you say that? The Oracle rule is 40% of RAM.
>
> > The largest recommended would be 1.6GB.
>
Daniel and Ronny.. How could percentage of total RAM be a useful
figure ?
On one hand, assume two GB of ram, and 500 MB of overhead
for OS etc... Then maximum memory available to Oracle is 1.5 GB,
or 75% of total RAM. Now add 2GB. Then memory available is
3.5 GB, or 87% of total RAM. Therefore, memory available to
Oracle is not a fixed percentage of total RAM.
On the other hand, the memory required for a given performance
level depends on a lot of factors, such as block working set, number
of sessions, etc. These factors are mostly uncorrelated to total
memory...
Therefore, neither available memory nor required
memory is a fixed percentage of total memory..
--- Raoul
Niall Litchfield
Here's a thought, you seem to be using .Net technology to access an
Oracle database via Shared Servers. Normally the use of shared servers
indicates that you expect large numbers of clients. Quite often the
use of the .Net clients indicates a 3 (or more) tier setup with some
IIS application servers in the middle.
If this is the case I'd be thinking about doing my connection pooling
on the apps tier (ODAC can do this for you in a nice configurable way)
and not at the database level. I'd also be quite lary of shared
servers in general, but that's another story.
cheers
harvi...@gmail.com
total used free shared buffers
cached
Mem: 4147184 1474236 2672948 0 22936
1295784
-/+ buffers/cache: 155516 3991668
Swap: 4620280 0 4620280
On May 15, 3:06 pm, Mladen Gogala <mgogala.SPAM...@not-at-verizon.net>
wrote:
Valentin Minzatu
Is the above calculation implying the use of MTS or you forgot to
factor in the memory for Oracle shaddow process(es)?
Ronny
> figure ?
>
> On one hand, assume two GB of ram, and 500 MB of overhead
> for OS etc... Then maximum memory available to Oracle is 1.5 GB,
> or 75% of total RAM. Now add 2GB. Then memory available is
> 3.5 GB, or 87% of total RAM. Therefore, memory available to
> Oracle is not a fixed percentage of total RAM.
>
> On the other hand, the memory required for a given performance
> level depends on a lot of factors, such as block working set, number
> of sessions, etc. These factors are mostly uncorrelated to total
> memory...
>
> Therefore, neither available memory nor required
> memory is a fixed percentage of total memory..
>
> --- Raoul
Raoul,
these percentage values are just imprecise suggestions from Oracle
when you initially setup a database. But they are good to know when
you don't have much expierience with Oracle. And very different values
to your configuration can help reveal misconfigurations.
Fine tuning is only possible when your production database is running
some time and you can evaluate the v$*advise dict views, etc.
Regards,
NitelyJoy
DA Morgan
> Daniel and Ronny.. How could percentage of total RAM be a useful
> figure ?
You might want to put that question to the people who wrote the code
for DBCA. <g>
joel garry
> > Why would you say that? The Oracle rule is 40% of RAM.
>
> > The largest recommended would be 1.6GB.
>
I think that reference in particular is just plain wrong (quite
overestimating things for a ballpark initial installation, and not
taking multiple instances into account at all, and probably enlarging
bug effects in some versions).
But google Jonathan Lewis' site for interesting ways to look at PGA.
jg
--
@home.com is bogus.
Are you older than videogames? http://www.1up.com/do/feature?cId=3159462
Mladen Gogala
Harvinder, it's hard to know what's going on without debugging.
One way of finding out what is going on would be to connect to a
dedicated server turn on strace like this:
strace -o /tmp/proc.out -p <pid> -etrace=brk
That will leave output in /tmp/proc.out. Here is an example
of a procedure, but without the error:
strace -o /tmp/proc.out -p 3650 -etrace=open
This has produced a trace file, tracing all calls to "open".
The output file looks like this:
open("/oradata/file_dir/oracle/system01.dbf", O_RDWR|O_SYNC|O_DIRECT|
O_LARGEFILE) = 9
open("/oradata/file_dir/oracle/users01.dbf", O_RDWR|O_SYNC|O_DIRECT|
O_LARGEFILE) = 10
open("/oracle/product/10g/admin/oracle/udump/10g_ora_3650.trc", O_WRONLY|
O_CREAT|O_TRUNC|O_LARGEFILE, 0660) = 5
open("/dev/null", O_RDONLY) = 5
open("/oracle/product/10g/admin/oracle/udump/10g_ora_3650_TEST.trc",
O_WRONLY|O_CREAT|O_TRUNC|O_LARGEFILE, 0660) = 5
open("/dev/shm/ora_10G_1540109", O_RDONLY|O_LARGEFILE) = -1 ENOENT (No
such file or directory)
You don't have a problem with the "open" call, you have a problem with
the "brk" call. You can get the list of system calls by through yelp
(Manuals->Developemnt->System Calls) I tried with the "brk" but the
process didn't use it, even once.
Jonathan Lewis
<harvi...@gmail.com> wrote in message
news:1179174227.4...@u30g2000hsc.googlegroups.com...
> PGA_MAX_MEM
> FROM V$PROCESS;
> PROGRAM PGA_USED_MEM PGA_ALLOC_MEM
> PGA_FREEABLE_MEM PGA_MAX_MEM
>
> oracle@qalin1 (S006) 616837 1712753 851968 3386122865
> oracle@qalin1 (S007) 555533 1516145 851968 3386319473
>
The pga_aggregate_target is a target for the sum
of all PGA memory usage - tunable, and non-tunable.
Start up a database, connect 50 sessions to it, and
watch the PGA stats as you do so. As each session
connects (and creates a process) watch things like:
> aggregate PGA auto target 509561856 bytes
> total PGA inuse 63959040 bytes
The AUTO target will go down, the PGA inuse will go up.
PGA memory is split into tunable (sort, hash, merge bitmap)
and untunable (cursor, local data, pl/sql, etc.) You (probably)
have a problem with your untunable memory.
> maximum PGA allocated 3512438784 bytes
> maximum PGA used for auto workareas 13465600 bytes
> maximum PGA used for manual workareas 536576 bytes
You don't seem to be doing anything terribly exciting
with workareas - 13MB worst case - but you've still
managed to allocated at some moment in time 3.5GB,
which is probably the moment at which you got the
ORA-04030, since you have 4GB of RAM and a
1.2GB SGA.
Taking a wild guess at the output:
(koh-kghu call ,pmuccst: adt/record)"
ADT is usually Abstract Data Type - I would guess
that someone has a pl/sql loop that populates an
array of objects; or used a select with table cast
to do something similar. PL/SQL tables are not
in the tunable memory, and Oracle can't stop them
from crashing the session and jamming your machine.
The fact that multiple shared_servers show the same
extreme probably relates to the way the the 'worst
case' session probably migrates around the servers
and only tips the session over from time to time.
The behaviour, and associated oddities with the stats
from v$process, may be related to the changes in code
for PGA handling in the upgrade from 9i to 10g - which
probably moved some session heaps outside the UGA
heap. In 9i, you would probably have found yourself
getting ORA-04031 (SGA full) rather than ORA-04030
(machine exhausted) when a session ran away like this.
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html