|
|
 |
 |
 |
 |
Need advice on ORA-04030 and pga_aggregate_target parameter
Hi, 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
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.
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
-----------------------------------------------Reply-----------------------------------------------
Ronny wrote: > 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
damor@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
-----------------------------------------------Reply-----------------------------------------------
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) On May 14, 5:51 pm, Ronny <nitely@ist-einmalig.de> wrote:
> 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.
> 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
On Mon, 14 May 2007 13:23:47 -0700, harvinder76 wrote:
> 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 --
http://www.mladen-gogala.com
-----------------------------------------------Reply-----------------------------------------------
Thanks for the info, ulimit looks good and nothing is in the alert 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:
> On Mon, 14 May 2007 13:23:47 -0700, harvinder76 wrote: > > 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
> --http://www.mladen-gogala.com
On 15 Mai, 03:37, harvinde @gmail.com wrote: > 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
-----------------------------------------------Reply-----------------------------------------------
> Why would you say that? The Oracle rule is 40% of RAM. > 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/mem... -----------------------------------------------Reply-----------------------------------------------
On Tue, 15 May 2007 11:35:42 -0700, harvinder76 wrote:
> 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 -----------------------------------------------Reply-----------------------------------------------
On 15 mai, 20:52, Ronny <nitely @ist-einmalig.de> wrote: 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
-----------------------------------------------Reply-----------------------------------------------
On May 14, 9:23 pm, harvinde @gmail.com wrote:
> Hi, > 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
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
-----------------------------------------------Reply-----------------------------------------------
free 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:
> On Tue, 15 May 2007 11:35:42 -0700, harvinder76 wrote: > > 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
On May 14, 5:51 pm, Ronny <nitely @ist-einmalig.de> wrote: > 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.
> 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
Is the above calculation implying the use of MTS or you forgot to factor in the memory for Oracle shaddow process(es)? -----------------------------------------------Reply-----------------------------------------------
> 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
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
-----------------------------------------------Reply-----------------------------------------------
hasta @hotmail.com wrote: > 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> -- Daniel A. Morgan University of Washington damor @x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org -----------------------------------------------Reply-----------------------------------------------
On May 15, 11:52 am, Ronny <nitely @ist-einmalig.de> wrote: 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
-----------------------------------------------Reply-----------------------------------------------
On Tue, 15 May 2007 12:25:45 -0700, harvinder76 wrote: > free > 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:
>> On Tue, 15 May 2007 11:35:42 -0700, harvinder76 wrote:
>> > 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
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.
--
http://www.mladen-gogala.com
|
 |
 |
 |
 |
|
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc