Oracle and swap space

[Alzhy]

Post "ipcs -a" of your DB servers that are exhibiting - "some applications seem to drive the supporting database server to use huge amounts of paging space" - and that'll conform Hein's assertions.

[1FJ]

@ Don
I'm thinking you are on to something with the remnant shared memory segments, we had a maintenance window yesterday morning and bounced the dabatase server. Looking a lot different this morning. Looks like a lot of user processes' just get left hanging on the database server. Sunday morning with no users on the applications it still showed 1600 plus active oracle connections.

@Hein,

I'll talk to the DBA about oracle parms.

I've been staring at this so long I'm going blind. Attaching the ipcs -a output

[1FJ]

oracle version:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

[Alzhy]

Looks like you have 4 (or more?) DB instances on the server you got "ipcs -a" from. One has an SGA of about 4.6GB and with LOTs of dedicated connections.

How much RAM do you have on this particular server and how much SWAP?

It may bery well be your RAM is not sufficient for the Oracle load and SGA your running.

IF you have glance, you can check VM starvation. You can also do vmstat paying particular attention to "sr".

[1FJ]

There are two instances, one ASM instance and on "regular" OLTP instance.


Right now the there's 120 GIG of paging space defined with 35 GIG used at the time of this writing.

[Alzhy]

But how much RAM (Physical Memory) do you have? And can you confirm your OLTP instance's SGA is indeed ~ 4.5GB (probably not multi-threaded and all dedicated right?)?

Also I wonder what those 2 or 3 other large chunkks of Shared Memory are though.

[1FJ]

Should have attached the vmstat snippet to the previous post. While this is a short snippet, it is fairly representative. As far as I can tell we're not having a lot of pressure on RAM.

[1FJ]

32 GIG RAM
123 GIG swap

OLTP instance is set with 6 GIG SGA
and a 2 GIG PGA

QL> show sga

Total System Global Area 6442450944 bytes
Fixed Size 2164096 bytes
Variable Size 4777212544 bytes
Database Buffers 1660944384 bytes
Redo Buffers 2129920 byte

[1FJ]

Ok I think we have the contours of an explanation here. Had
discussion with one of the developers this morning.

If and when a user session disconnects improperly from the application server, i.e., an RF terminal looses power or a terminal session on PC gets "x'd out" rather than closed with the application exit menu option the session server does not clean up so the connection(s) will persist for the lifetime on the application server process.

We've yet to prove this but it makes sense in that we slowly but surely accumulate database connections over and above what we believe should be there. We should be able to graph this.

[James R. Ferguson]

Hi:

> If and when a user session disconnects improperly from the application server, i.e., an RF terminal looses power or a terminal session on PC gets "x'd out" rather than closed with the application exit menu option the session server does not clean up so the connection(s) will persist for the lifetime on the application server process.

Yes, that is a very BAD THING. Not only connections, but temporary files and shared memory segments could be left lying around!

Regards!

...JRF...