Oracle Database Discussions

Hi All,

hopely you can help me. The following has just occured to us:
we have a linux server:
Linux i18802 2.6.18-164.6.1.el5 #1 SMP Tue Oct 27 11:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

and one 11.1.0.7 database

currrent memory utilisation is:

top - 08:47:46 up 244 days, 15:52, 2 users, load average: 1.78, 1.71, 1.90
Tasks: 1034 total, 1 running, 1033 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.1%us, 0.2%sy, 0.0%ni, 96.3%id, 2.3%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 66006404k total, 65101092k used, 905312k free, 355440k buffers
Swap: 8388600k total, 5446320k used, 2942280k free, 53562368k cached


relevant db parameters are following:
memory_max_target big integer 30G
memory_target big integer 20G

sga_max_size big integer 30G
sga_target big integer 0
pga_aggregate_target big integer 0


Our linux guys are thinking, that oracle consumes most of the memory, but I don't think, because yesterday we stopped the database and after stopping it, physical memory sonsumtion was still 40GByte of 64 Gbyte. They saw that it was filesystem cache. Filesystem cache has been cleaned up. Then, physical memory consumption has been reduced uptp 1Gbyte. When we restarted the database, physical memory consumtion went upto 20Gbyte (around memory target value). Slowly physical memory had been growing and growing, and now we have the situation where almost all physical memory is consumed and the system is swapping and swapping causing performance problem.
Oracle think that it it a bug: Bug 13680405 - PGA consumption keeps growing in DIA0 process
but we don't think so.
we really see that DIA Process consumes the most memory, not not as much as can be seen:

SQL> l
1 SELECT p.spid,
2 p.pid,
3 s.sid,
4 s.serial#,
5 s.status,
6 p.pga_alloc_mem,
7 p.pga_used_mem,
8 s.username,
9 s.osuser,
10 s.program
11 FROM v$process p, v$session s
12 WHERE s.paddr( + ) = p.addr
13* ORDER BY p.pga_alloc_mem DESC
SQL> /

SPID PID SID SERIAL# STATUS PGA_ALLOC_MEM PGA_USED_MEM USERNAME OSUSER PROGRAM
------------------------ ---------- ---------- ---------- -------- ------------- ------------ --------------- --------------- ------------------------------------------------
4046 7 5499 1 ACTIVE 27726570 11892722 oracle oracle@i18802 (DIA0)
4085 22 5483 1 ACTIVE 24597898 11220098 oracle oracle@i18802 (ARC3)
4079 19 5487 5 ACTIVE 24597898 11220098 oracle oracle@i18802 (ARC0)
4083 21 5484 1 ACTIVE 24597898 11220098 oracle oracle@i18802 (ARC2)
4056 12 5494 1 ACTIVE 23859946 11550274 oracle oracle@i18802 (LGWR)
4081 20 5485 2 ACTIVE 23352714 11262434 oracle oracle@i18802 (ARC1)
4054 11 5495 1 ACTIVE 16167930 4260762 oracle oracle@i18802 (DBW2)
4052 10 5496 1 ACTIVE 15447034 3997290 oracle oracle@i18802 (DBW1)
4050 9 5497 1 ACTIVE 14726138 4013362 oracle oracle@i18802 (DBW0)

don't know. How would it be possible the prove if it is the orracle bug, or wrong os configuration?

thanks for all