topic Re: Oracle Processes Waiting for Semaphores in Operating System - HP-UX

Oracle Processes Waiting for Semaphores

Paddy_1 — Wed, 31 Dec 2003 13:59:29 GMT

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

Re: Oracle Processes Waiting for Semaphores

RAC_1 — Wed, 31 Dec 2003 14:19:34 GMT

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.

Re: Oracle Processes Waiting for Semaphores

Steven E. Protter — Wed, 31 Dec 2003 14:24:24 GMT

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

Re: Oracle Processes Waiting for Semaphores

A. Clay Stephenson — Wed, 31 Dec 2003 14:26:02 GMT

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.

Re: Oracle Processes Waiting for Semaphores

Paddy_1 — Wed, 31 Dec 2003 15:15:22 GMT

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

Re: Oracle Processes Waiting for Semaphores

Michael Schulte zur Sur — Wed, 31 Dec 2003 15:48:07 GMT

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

Re: Oracle Processes Waiting for Semaphores

Mic V. — Thu, 01 Jan 2004 20:05:59 GMT

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

Re: Oracle Processes Waiting for Semaphores

Paddy_1 — Fri, 02 Jan 2004 09:53:44 GMT

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.

Re: Oracle Processes Waiting for Semaphores

Mic V. — Fri, 02 Jan 2004 21:21:46 GMT

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

Re: Oracle Processes Waiting for Semaphores

zhuchao — Mon, 05 Jan 2004 04:12:50 GMT

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.

Re: Oracle Processes Waiting for Semaphores

Hein van den Heuvel — Mon, 12 Jan 2004 00:23:47 GMT

zhuchao, Finally a sensible reply in this stream! (IMHO of course!)

Best I know, 'Waiting for semaphore' does not mean waiting for one to become available, but means that the system is using one to synchronize access. For example an Oracle slave being blocked for the logwriter.

You can not tune semaphores other then making 'enough' available. Any less and you'll get a hard failure. Any more will not speed up anything, but you'd better have some slack. (Just like NPROC, FILES,

Check out for example: http://www.ixora.com.au/q+a/0105/24161647.htm
Be Sure to get Steve Adams 'Oracle8i Internal Services" booklette as referenced there. No DBA should be without that.

You want to use statspack (or utlXstat) to learn why Oracle is waiting. It's right there in the first statspack page: "Top 5 Timed Events" followed by the complete table for "Wait Events for". You are likely to be very insterested also in the table for "Enqueue activity"

Forget system tuning & tools, focus on Oracle! System tuning is 99% about making enough recourse availabel to allow it to work at all. After that it is up to Oracle (through your help!) to make it work fast.

hth,
Hein.

Re: Oracle Processes Waiting for Semaphores

Paddy_1 — Mon, 12 Jan 2004 09:20:06 GMT

Two excellent answers.I am using Oracle 8.1.7
I am going to use STATSPACK as mentioned and I have to mention I also use "dbtuner" as a perf tool.
Either ways my ignorance of Oracle performance tuning is whats coming in way of solution here.
Thanks
Paddy