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.