edit by scottn: moved ps output (1558 lines) to .txt attachment
Could it be that there are Solaris zones on the machine? Or even an unfinished zone creation (no final reboot)?
No its just one physical server, we are not using any LDOMS or other virtualization techniques
Do you have any update on this issue?
Hi radulov,
Sorry for the late reply. Thanks for your help. Was very much useful and I learnt quite a lot of Solaris commands.
The issue was not fixed but when the instance was restarted the java processes were cleared which did free up some memory, so users were not complaining but still there was a difference in actual memory used by Oracle database and the oracle memory usage that Solaris reported.
I was busy with some other work so thought I'll have a look at this one when I get spare time. Today I was looking at this server once again and I don't see any additional java processes running which is good.
This is the current memory usage:
SGA - 5G
POOL SUM(BYTES)
------------ ----------
large pool 50331648
java pool 167772160
streams pool 201326592
shared pool 1795912720
2113929168
PGA Usage - 8G
select sum(value) from v$sesstat ss, v$statname sn where sn.statistic# = ss.statistic# AND sn.name = 'session pga memory'
/ 2
SUM(VALUE)
----------
8097824112
Clusterware Memory Usage - 850M
ps -eorss,args | nawk 'END { print s/1024, "MB" } /cluster/$1 }'
853.023 MB
Oracle listener memory usage - 69M
ps -eorss,args | nawk 'END { print s/1024, "MB" } /db_1/ { }'
69.0156 MB
Agent memory usage - 165M
ps -eorss,args | nawk 'END { print s/1024, "MB" } /agent10g $1 }'
164.414 MB
Free Memory - 1G appr
vmstat 5 5
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr m0 m1 m3 m4 in sy cs us sy id
0 0 0 6919392 5236208 600 1949 1929291843905 575 567 0 4 2 0 0 0 7137 30982 6894 4 2 94
0 0 0 4796696 1082888 368 1557 40607348150 27 27 0 0 0 0 0 0 8370 26135 8119 6 2 92
0 1 0 4806656 1090088 342 1073 40 41 41 0 0 0 1 2 0 7976 18484 7493 6 2 92
swap usage
swap -s
total: 27390952k bytes allocated + 7552552k reserved = 34943504k used, 4792424k available
When I run prstat -a -s rss to see oracle user memory usage
NPROC USERNAME SWAP RSS MEMORY TIME CPU
4018 oracle 33G 25G 78% 354:40:07 6.9%
68 root 296M 286M 0.9% 87:05:26 0.1%
1 daemon 2632K 6376K 0.0% 6:35:27 0.0%
1 smmsp 1352K 4968K 0.0% 0:00:22 0.0%
I know you said that this o/p might be wrong but still I am confused as the free memory is very slow and there is huge amount of swapping happening.
Using Oracle provided script(
How to Check the Host Memory Usage on Solaris via NMUPM Utility [ID 741004.1])
./nmupm_mem.sh
/reuters/oracle/product/10.2/db_1/bin/nmupm osLoad
Calulates average memory (interval 300 (s)) usage on Solaris using nmupm
Memory 33423360.000000 [kB] Freemem 1499683.86 [kB] %Free 4.00
Memory 33423360.000000 [kB] Freemem 1514275.89 [kB] %Free 4.00
Memory 33423360.000000 [kB] Freemem 1867766.98 [kB] %Free 5.00
Memory 33423360.000000 [kB] Freemem 1505032.69 [kB] %Free 4.00
sar -r
freemem freeswap
164676 10233251
pagesize
8192
So around 1.3G free memory reported.
So it seems like around 10G of memory has vanished. I am not able to trace this memory.
Any suggestions?
I observe similar situations in out environment (Oracle/Solaris).
Often (especially with multiple concurrent connections) the memory
drill-down using standard Unix utilities doesn't seem to match
the physical memory of the servers.
I have raised this with Oracle now .. Will update you when I get some information.
Thanks!
I believe it's somehow related to the high connection count.
what gives :
at OS level
ipc -m | grep ora
at DB level (by the way, i'm not sure if Oracle RAC have these views but if so, let's have a look):
show sga
show parameter sga
show db_cache
select * from gv$resource_limit
select * from v$pgastat
select * from v$db_cache_advice
select * from v$pga_target_advice
Do you use directio ?
Do you use automatic SGA and PGA management ?
What type of file system you have ?
Are you in dedicated server mode or shared server mode ?
(it will determines whether the UGA will be located in PGA or SGA ... at least for standard Oracle DB, i'm not sure how Oracle RAC differs from the "standars" oracle database)
By the way, at the end of last year, we encountered some problem with Solaris memory leak that week after week made the solaris kernel use up to 20% of the RAM (::memstat in crontab logged the evolution ... and .... after reboot : decrease to 2% of the RAM).
Updating to last kernel patch level fixed that issue.
Maybe it doesn't have anything to do with your problem, but it still can give some ideas.
By the way, the ORA- error you got demonstrate an obvious memory undersizing just as Radoulov suggest.
---------- Post updated at 07:24 PM ---------- Previous update was at 07:14 PM ----------
I add bonus technical information that may be of interest for Oracle DBA guys (many people talk about directio without knowing what is behind):
Extract from : Re: direct reads and writes on Solaris - oracle-l - FreeLists
A couple of explanations. First, the main reason to do directio is to get
around the Posix single-writer lock. This lock is a mechanism that prevents
2 processes from writing to the same file at the same time, mainly to prevent
them from both trying to write the same block and having unpredictable results.
Since Oracle is already handling the coordination of writes, this lock is not
needed. But the filesystems and OS enforce it automatically. On larger systems
this can cause contention, since many processes may want to write to the same
Oracle datafile at the same time and will be forced to be single-threaded(*).
So methods were introduced to get around those semantics. The 4 that work on
Solaris are using raw devices directly, UFS with directio (either through
filesystemio_options = setall or the forcedirect mount option), VxFS(**) with QIO
or ODM and QFS with samaio. Note that VxFS with mincache=direct is NOT included
here because it does NOT eliminate the single-writer lock. You have to have QIO
or ODM with VxFS to avoid the lock.
I hope you enjoy it
---------- Post updated at 07:44 PM ---------- Previous update was at 07:24 PM ----------
Yes, its sga_max_size is 5G :
ipcs -a
IPC status from <running system> as of Thursday September 30 12:48:07 GMT 2010
T ID KEY MODE OWNER GROUP CREATOR CGROUP CBYTES QNUM QBYTES LSPID LRPID STIME RTIME CTIME
Message Queues:
q 0 0x61037083 --rw------- root root root root 0 0 65536 1013 1144 12:48:06 12:48:07 5:37:46
T ID KEY MODE OWNER GROUP CREATOR CGROUP NATTCH SEGSZ CPID LPID ATIME DTIME CTIME
Shared Memory:
m 16 0xb2d23620 --rw-r----- oracle oinstall oracle oinstall 2493 5251284992 14099 25818 12:47:53 12:47:53 5:48:42
m 1 0x2fe9b7f0 --rw-r----- oracle oinstall oracle oinstall 18 130031616 3446 24816 12:47:14 12:47:15 5:39:26
T ID KEY MODE OWNER GROUP CREATOR CGROUP NSEMS OTIME CTIME
Semaphores:
Yep, correct.
Thanks!