Oracle Processes Waiting for Semaphores

[Paddy_1]

I use measureware to collect data from our servers and one of a recent web applications that uses a particular database suffered a degradation of response times.

I have looked at the usual suspects and everything seemed okay so far.

The unusual part is that for this Oracle server Oracle I have seen that more than 90% of its time is spent in waiting for semaphores.

I have attached the sysdef parms and ipcs output.I am guessing that some semaphore related parm in the kernel has to be changes but I am reluctant to go ahead without understanding what this means.

Can someone help me understand what is that it means to spend so much time waiting for semaphores.

Thanks
Paddy

[RAC_1]

where is the attachement?

What is your kernel setting for system wide semaphores?

Compare it with real time usage.

You can check realtime usage by executing ipcs -s periodically.

[Steven E. Protter]

A lack of shared memory is usually from one or two kernel parameters

shmmax
shmseg

shmmax can be cranked up to a maximum of 25% total memory, which is defined as ram plus swap

shmseg usually doesn't need to be pushed up much farther than say 100 on all but the most heavily used systems.

shmmax is a dynamic kernel parameter on 11i systems.

SEP

[A. Clay Stephenson]

Your attachment seems to be missing. Generally when not enough semaphores are available the Oracle instance will not start. However, waiting on semaphores is quite common and that means they are doing exactly what they are intended to do. Think of semaphores as flags for communicating between processes. For example, Process A might send a request to Process B to do a query and let me know when data is available. The "let me know" part is the job of a sema4. In the meantime, while Process B is as busy as a beaver fetching your rows, Process A is, guess what, waiting on a semaphore and this is perfectly normal. I suspect that improving your SQL (possibly adding an index or two) will resolve your sema4 problem.

The one thing that I would look at is your timeslice setting. If it is small (i.e 1) sema4 behavior becomes very bizarre. Timeslice should be set at 10.

[Paddy_1]

Sorry for the missing attachment.I have some problem uploading it and hence here is the output.
------------------
NAME VALUE BOOT MIN-MAX UNITS FLAGS
acctresume 4 - -100-100 -
acctsuspend 2 - -100-100 -
allocate_fs_swapmap 0 - - -
bufpages 314572 - 0- Pages -
create_fastlinks 0 - - -
dbc_max_pct 10 - - -
dbc_min_pct 5 - - -
default_disk_ir 0 - - -
dskless_node 0 - 0-1 -
eisa_io_estimate 768 - - -
eqmemsize 63 - - -
file_pad 10 - 0- -
fs_async 0 - 0-1 -
hpux_aes_override 0 - - -
maxdsiz 262144 - 0-655360 Pages -
maxdsiz_64bit 262144 - 256-1048576 Pages -
maxfiles 1024 - 30-2048 -
maxfiles_lim 1024 - 30-2048 -
maxssiz 2048 - 0-655360 Pages -
maxssiz_64bit 2048 - 256-1048576 Pages -
maxswapchunks 10240 - 1-16384 -
maxtsiz 65536 - 0-655360 Pages -
maxtsiz_64bit 262144 - 256-1048576 Pages -
maxuprc 800 - 3- -
maxvgs 14 - - -
msgmap 2555904 - 3- -
nbuf 266868 - 0- -
ncallout 2016 - 6- -
ncdnode 150 - - -
ndilbuffers 30 - 1- -
netisr_priority -1 - -1-127 -
netmemmax 0 - - -
nfile 8010 - 14- -
nflocks 800 - 2- -
ninode 4096 - 14- -
no_lvm_disks 0 - - -
nproc 2000 - 10- -
npty 60 - 1- -
nstrpty 60 - - -
nswapdev 10 - 1-25 -
nswapfs 10 - 1-25 -
public_shlibs 1 - - -
remote_nfs_swap 0 - - -
rtsched_numpri 32 - - -
sema 0 - 0-1 -
semmap 8323072 - 4- -
shmem 0 - 0-1 -
shmmni 200 - 3-1024 -
streampipes 0 - 0- -
swapmem_on 1 - - -
swchunk 2048 - 2048-16384 kBytes -
timeslice 10 - -1-2147483648 Ticks -
unlockable_mem 49152 - 0- Pages -


$ ipcs -as
IPC status from /dev/kmem as of Wed Dec 31 11:58:21 2003
T ID KEY MODE OWNER GROUP CREATOR CGROUP NSEMS OTIME CTIME
Semaphores:
s 0 0x411c3a7a --ra-ra-ra- root root root root 1 6:25:08 6:25:01
s 1 0x4e0c0002 --ra-ra-ra- root root root root 2 6:25:04 6:25:01
s 2 0x412075ea --ra-ra-ra- root root root root 2 no-entry 6:25:01
s 131 0x00446f6e --ra-r--r-- root sys root sys 1 no-entry 13:51:29
s 260 0x00446f6d --ra-r--r-- root sys root sys 1 no-entry 13:51:29
s 5 0x01090522 --ra-r--r-- root root root root 1 no-entry 6:25:24
s 6 0x411c4ba0 --ra-ra-ra- root root root root 1 6:25:34 6:25:34
s 7 0x612074bd --ra-ra-ra- root root root root 1 6:29:20 6:25:40
s 8 0x732074bd --ra-ra-ra- root root root root 1 4:56:06 6:25:40
s 9 0x702074bd --ra-ra-ra- root root root root 1 4:56:06 6:25:40
s 10 0x692074bd --ra-ra-ra- root root root root 1 6:29:20 6:25:40
s 11 0x752074bd --ra-ra-ra- root root root root 1 6:25:40 6:25:40
s 12 0x632074bd --ra-ra-ra- root root root root 1 6:25:40 6:25:40
s 13 0x642074bd --ra-ra-ra- root root root root 1 17:11:34 6:25:40
s 14 0x662074bd --ra-ra-ra- root root root root 1 no-entry 6:25:40
s 15 0x6c2074bd --ra-ra-ra- root root root root 1 6:29:20 6:25:40
s 16 0x6d2074bd --ra-ra-ra- root root root root 1 6:34:42 6:25:40
s 17 0x6f2074bd --ra-ra-ra- root root root root 1 no-entry 6:25:40
s 18 0xffffffff --ra-r--r-- root root root root 1 6:25:50 6:25:50
s 19 0x410c03d1 --ra-ra-ra- root root root root 1 6:25:53 6:25:53
s 74132 0x4b95e0e0 --ra-r----- oracle dba oracle dba 654 11:58:21 4:41:48
s 21 0x522074bd --ra-ra-ra- root root root root 1 6:34:42 6:29:21
s 22 0x451c2a43 --ra-ra-ra- root sys root sys 1 7:21:50 4:30:02
s 23 0x451c2aac --ra-ra-ra- root sys root sys 3 7:21:50 4:30:03
s 24 0x0000cace --ra-ra-ra- root sys root sys 1 7:21:50 4:30:03
s 25 0x450c080a --ra-ra-ra- root sys root sys 1 7:21:50 4:30:03

[Michael Schulte zur Sur]

Hi,

if you have contact to metalink look here and create a tar:
http://forums1.itrc.hp.com/service/forums/postanswer.do?threadid=340427&forumId=1
if not check on semmns. If it is still on the default of 128, that might be not enough.

greetings,

Michael

[Mic V.]

Paddy,

Your situation kind of sounds like ours. I don't have any certain answers, but can share some data.

We are trying to diagnose a performance problem with our Oracle 7/HP-UX 11 ERP system.

You wrote that "more than 90% of its time is spent in waiting for semaphores." What method did you use to come to this conclusion?

When I've looked at our system, nothing really seems to be wrong with it (as you say, I've looked at the usual suspects) except that it reports a high count on the semaphore queue. None of us really know what this means and whether it's significant or normal. Unfortuately, we don't have a long-term baseline for this data.

If you have access to MeasureWare and PerfView (I can't remember what they call them these days), I'm looking at the system Global Wait Queue graph. It's the second or third graph from the top in the main PerfView window. The semaphore queue is sky-high compared to everything else.

We *have* seen some performance improvement by rewriting SQL, but this is hard due to the nature of the application we run.

One suggestion for your kernel parameters is to check them against the Oracle Installation Guide's required settings.

Good luck,
Mic

[Paddy_1]

Mic,

Thank you for your corroboration.

Coming to the measurement I can outline how we baseline things.

We have Measureware agents installed on each of the HP-Servers whose data is transferred nightly into our DataWarehouse(we use SAS/ITSV which is called SAS/ITRM these days)

I use the data from the warehouse to do this kind of analysis.I can ask my development guys to have a look at the SQL code.

In my googling I found this thread to be pretty close and says pretty much what you have said :
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=877362755.26206%40dejanews.com&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DOracle%2B%2Bsemaphores%2Bunix

Let me try changing the parms and get back to you.

[Mic V.]

Paddy,

Thanks. I thought of a couple of other tools that could be helpful. Unfortunately, they aren't open source.

sarcheck from Aptitune http://www.aptitune.com (sar-based tool, writes a "report" in plain English). I like this a lot but for this one, it's been more helpful in terms of what the problem isn't rather than what it is (probably due to inherent inability to pry into proprietary disk arrays)

Lund Performance Systems http://www.lund.com I haven't looked at them in several years, but they used to have an HP-UX performance analysis tool. They specialized in HP3000.

tt,
Mic

[zhuchao]

What is your version of oracle?
If oracle 7-8.0, please post a utlb/estat report.
If oracle 8i+, please post a statspack report.

Since you are tuning your oracle database, you must provide oracle tuning statistics. not only os statistics.

In general, semaphore lack will cause oracle won't start, and will not cause performance problems.

Or maybe it is oracle internal latch/enqueue wait.