Swap, memory, kernal config? - ORA-12500 error

[Volker Borowski]

Hi again,

I have some questions about the init.ora.
Is it still used, since you attached an spfile (which unfortunately my browser was not able to open or to download). May be a spooled output from "select * from V$PARAMETER" suits us more ?

If init.ora is used, could you please attach the included file "/u01/app/oracle/admin/PTWB/pfile/configPTWB.ora" as well (or the above mentioned output from the select statement will be ok).

Best bet still the sessions/processes parameters or may be 24M for PGA_AGGREGATE_TARGET is too low (try 250M) (recommendation for SAP i.e is 10%-20% of phys RAM, but it depends on the application.

Do you run Oracle AND the application (which one) on the same box ?

Volker

[Surya Narayana]

I would strongly recommend reading up on the
'chatr' command. That changes the way
memory chunks are allocated.
Oracle Metalink has an article on that too.
This is particularly relevant for 11i/Oracle 9i.

[TWBSupport]

Here's the other file you requested. Thank you. I did find out that the information about the 32 bit application was wrong. OS, Oracle & the application are all 64 bit. Thanks again for your help..

[Indira Aramandla]

Hi Belynda,

The reply that I gave was pertaining to ORA-1200. Yes I agree hitting SWAP is poor performance. Increasing swap is only a temporary option for the above error. I am sorry I was not clear enough.

Your server rp2470 is having only 2 GB RAM and your are using oracle 9i RDBMS. And you said by increasing your swap more users can be connected, which clearly says it is a memory issue.

Generally the error ORA-12500 may be an indication that the RAM amount may be reaching its limit. Or is caused as a result of lack of memory resources. In order to prevent one has to:

1. Increase the amount of memory available to the system either by removing unnecessary processes or increasing the amount of physical memory.

2. Increasing the amount of swap space will also temporarily help relieve the symptoms.

Swap space is used when the amount of physical memory (RAM) is full. If the system needs more memory resources and the physical memory is full, inactive pages in memory are moved to the swap space.

While swap space can help machines with a small amount of RAM, it should not be considered a replacement for more RAM. Swap space is located on hard drives, which have a slower access time than physical memory.

Oracle way :
When an Oracle instance is started, the OS is being asked to give some Key resources according to the parameters specified in init.ora. Oracle uses shared memory for efficiency. The OS does not have to load up the same address page(s) into the memory each time a process needs to reference it. Instead the process can just reference the one memory location, read/write to it, and then leave. The number of semaphores allocated is directly dependent on the PROCESSES parameter in the init.ora file. The total amount of semaphores required is derived from the 'processes' parameter inside that Oracle instance init.ora file
For optimal database performance, SGA should fit into real memory, and avoid Swapping.

UNIX way :
One of the most common problems when running large numbers of concurrent users on UNIX machines is lack of memory. A UNIX machine has virtual memory: the total addressable memory range. Virtual memory is composed of RAM, DISK and SWAP space. Generally, you will want to have the available SWAP space equal to 2 to 3 times the RAM. UNIX uses two memory management policies: swapping and paging. Swapping occurs when UNIX transfers an entire process from RAM to a SWAP device. This frees up a large amount of RAM. Paging occurs when UNIX only transfers a "PAGE" of memory to the SWAP device. Only a tiny portion of a process might actually be "paged out" to a SWAP device. While swapping frees up memory - it is slower than paging. Paging generally is more efficient but does not allow for large amounts of memory to be freed simultaneously.

When analysing your UNIX machine, make sure that the machine is not swapping at all and at worst paging lightly. This indicates a system with a healthy amount of memory available

I hope this clears your doubt.

[TWBSupport]

Hi All!
I'm baaaaackkkk. :o) I upgraded our memory to the max available 8G. Our primary swap is at 4G & priority 1, the secondary swap is 4G & priority 0. No longer getting the user connection errors, but any time a large query is run in the database, CPU goes to 100% & slows the database down to a crawl. The other thing that was done was the large table spaces have been partitioned to separate disks to alleviate any disk i/o contengency, which helped... some... :o)

Here's our swapinfo:
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 4096 0 4096 0% 0 - 1 /dev/vg00/lvol2
localfs 3906 0 3906 0% none 0 0 /swap1/paging
reserve - 4906 -4906
memory 6307 4536 1771 72%
total 14309 9442 4867 66% - 0 -

Got anything else that I can check on the hardware side? I really would like to put this problem to bed, but everytime we fix one thing, another issue pops up. Thanks a ton!

[Brian Crabtree]

Have your DBA tune the query. Most likely, it is doing something wrong, or needs to have some work done on it if it is eating up that much of the system.

Brian

[Yogeeraj_1]

hi,

If you have good broad knowledge of the database, you can start to use STATSPACK to identify possible 'bottlenecks'.

Note that you will also have wait events:
a. foreground (client) waits for latches (probably not using binds) and log syncs.
b. background (oracle background processes) waits.

Which you will have to analyse at the application level.

regards
Yogeeraj

[T G Manikandan]

YOur buffer cache is pulling out 50% of memory on the machine.

check the dbc_pct_max is 50 which is half the RAM.Only half the RAM is taken for the remaining tasks.

Bring down the buffer cache value to around 300Mb from 1GB i.e. that would be the value of 15.
Bring down your dbc_pct_max kernel parameter to 15

That should give some improvements.

[T G Manikandan]

Hey!
Are you joking!!
your kernel value for shmmax shows 67MB.Is that what you have defined.

Come on man you do not have the shmmax properly set and how will the Oracle SGA allocate space in memory.

First bump your shmmax to 1.5GB that would be
really solve hell lot of problems.

Just assume that your SGA does not fit in the memory which is causing hell lot of problems.

I am expecting your answers to the comments.

Revert back!

[Hein van den Heuvel]

Witht the increased physical memory, did you retune Oracle to exploit some of it.
You are running 64 bit now? What Oracle version? Did you clean some version 7 crud from the init.ora files?

The init.ora you published back in September showed a relatively small number of db_block_buffers. It is not uncommon to dedicated 1/4 of a machine to this (now 2Gb) and you have given it 100MB. I'd recommend at least 1GB (250,000 buffers of 4KB for an 8GB box). The log_buffer of 160KB might as well be bumped to 1MB just in case you have significant activity there.
And you may want to review PGA_AGGREGATE_TARGET = 24000000
LARGE_POOL_SIZE = 8000000
for appropriate values with the new RAM.

One set up values was relatively large and possibly contributed to your original problem: sort_area_retained_size=3072000 / sort_area_size=6144000
Times 300 users, that is 1GB !
Are you sure you need that? Can retained be reduced? Or can the pga_target be used to control this some?


Of course this all would be an 'ready, fire, aim' approach. You really should get statspack going to get an appreciation of what your main wait events really are (as remarked by others).

> everytime we fix one thing, another issue pops up

Ah yes, that's Oracle tuning for you. Think of it as Job Security. Supposedly the overall result gets better and better and eventually the new issue will be small enough (and hard enough!) to just let it be.

For better help yo may want to re-publish your current init.ora and at least teh first page of a relevant statspack output.

hth,
Hein.