Oracle and swap space

[1FJ]

We have several Oracle Servers, mostly on 11.23 and while they are remarkably simillar in configuration some applications seem to drive the supporting database server to use huge amounts of paging space where as others seem to make do with a swap volunme a fraction of RAM. All the Oracle installations are linked to use lazy paging. Would anybody know of a paper that explains this ?

[avizen9]

Hello,

you need to set SGA parameter accordingly, this will help to improving performance
how much is physical RAM for this server?
thanks,

[Hein van den Heuvel]

Right. You want to check the PGA and SGA settings for each database, and possibly get an indication of how many connections. Be sure to engage your DBA (or is that you?!) to help explain how much memory Oracle is expected to use. If you need further help, then please be sure to indicate the exact Oracle version/patch level, and the HPUX platform.
You may also want to quantify 'huge'.
How about some actual swapinfo numbers?

fwiw, Running swap at a fraction of ram is possible in lazy/deferred swap mode, but can get scary. I used to run (Tru64) Unix servers with Oracle in 64GB or more of memory with just 8 or 16 GB of swap... but I knew _exactly_ what the SGA and PAG settings would be and how many connections to expect from my SAP middle tier and at what memory footprint those kicked in.

As for a paper... have you checked out the regular Oracle documentation like:
http://download.oracle.com/docs/cd/B19306_01/server.102/b15658/tuning.htm
If that does not cover it, then please provide concrete questions with factual details.


Good luck!
Hein.

[1FJ]

Mostly I'm just looking to understand what drives oracle paging needs since they baffle me with seemingly different needs for what, essentially, is the same load.

@ avizen, not sure what you mean by "set the SGA accordingly". Are you suggesting that excessive paging is a function of an undersized SGA?

@Hein

yeah, fractional paging space can be interesting. Not as interesting as it used to be though.

[Hein van den Heuvel]

paging suggest an over-sided sum of all the SGA's.

Why don't you start to really work this problem and provided pertinent numbers instead of handwaving.
We are not good mind reading here.
Words like 'several' and 'Oracle Server' and 'fraction' and 'huge' are essentially meaningless.
That does not leave much information so far to try to help you.

There are _tons_ (another meaningless word) of papers and books out out there describing memory management and usage an many of them in an Oracle context.
Google: +oracle +pdf +paging +swap will find 'several'

For both a 'good' and a 'bad' system please enumerate (for yourself!)
- physical ram
- number of databases instances
- for each database find (version), SGA mb, PGA mb, sessions
- sum of all SGA and all PGA and all sessions over concurrently active database instances.

If that does not lead you to the problem, then please share here (in a text file attachment) and we'll try to help better.

Regards,
Hein.
HvdH Performance Consulting.

[1FJ]

Hein,

I apologize for perhaps being a little vague but I wasn't really looking for a direct technical "how-to"

Then again, your suggestions are valid. I'll try run it down from a pure numbers angle and see where that leads me.

[Shailendran V Naidu]

1FJ,
You must also see some settings like nfile, maxuproc, nproc parameters and their usages.

I have seen ppl keeping very high values for the heck of it and end up utilizing more memory and hence leading to swapping.

Also number of users logging in and the kind of query running on a database also effects over all memory.

[1FJ]

Shailendran,

I agree that keeping kernel parms at too high settings will cause overuse of resources. In this particular case they are set as per oracle recomendations (keeps the peace).

Also, the system isn't actually paging just reserving paging space , ie. no high values of PI and PO in fact they show at 0. Physical Ram is 32 GB and 150 GIG of paging space gets reserved in no-time. I have not had to use this ratio of paging space to RAM since the mid 90's .

[Don Morris_1]

If it is swap reservation, you should be able to track it down.

Look for processes with large virtual size, then use Glance or pstat backed utilities to see where the large size stems from and act accordingly. (This can't all be kernel -- since the kernel will rarely reserve _actual_ disk swap, only memory swap [since it doesn't get paged except for a few bits of metadata] -- so it has to be user, and if it is user -- there have to be virtual objects you can locate.)

If the process VSZ doesn't add up, orphaned SysV shmem segments (like the SGAs of badly terminated prior Oracle instances?) leap to mind -- have 4 or 5 copies of the same SGA in RAM [because Oracle tried to remove the segment (shmctl with IPC_RMID) but there's one (or more) attached process that won't finish exiting, etc.] and it would add up quickly in reservation costs, etc.

[Hein van den Heuvel]

Ok, the story is becoming a little more clear, despite the lack of real numbers.

If I were a betting man (which I am 1 Friday evening each month :-), then I would put my money on a DBA having gotten exited about SGA_MAX_SIZE, and turned it way up 'just in case'. Oracle will then tell teh OS to reserve that VM, but will not actually touch it untill the SGA components are dynamically resized (SGA_TARGET, SHARED_POOL_SIZE,...)

Again, check the SGA settings, and kindly indicate the Oracle version. It may not be important, but it is likely to be more important than the HPUX version.

In the running system, please be sure to check with ipcs -m.
And like Don write. Check the process VSZ numbers with teh tool of your preference (ps, glance, top, whatever works for you).

Besides the tuning manual mentioned earlier check out the concepts manual, and have a look at:

http://blog.tanelpoder.com/2007/08/28/operating-systems-are-lazy-allocating-memory/

http://forums.oracle.com/forums/thread.jspa?threadID=535400&tstart=0

Hein.

[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...

[Alzhy]

You had 900+ connections to that big OLTP DB based on your ipcs output.

Now go figure total Mem usage of Oracle -- SGA, VM, RSS...

[Hein van den Heuvel]

Good think this it was not my gambling day.
That is, it does not look like the SGA(s) were the problem.
So the focus has to shift back to the per-process memory.
If we simplistically take 129GB and divide it by 900, that would be 136 MB/process. That's not impossible, but unlikely.
Unless something add is happening like the Oracle executable chattered to 8kb pages.


Anyway, time to start poking with TOP, GLANCE or
UNIX95= ps -ef -o pid,sz,vsz,args | sort -nr -k 2,3

http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=99729

hth,
Hein.