Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Processes Waiting for Semaphores

Paddy_1
Valued Contributor

Oracle Processes Waiting for Semaphores

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
The sufficiency of my merit is to know that my merit is NOT sufficient
11 REPLIES
RAC_1
Honored Contributor

Re: Oracle Processes Waiting for Semaphores

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.
There is no substitute to HARDWORK
Steven E. Protter
Exalted Contributor

Re: Oracle Processes Waiting for Semaphores

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
A. Clay Stephenson
Acclaimed Contributor

Re: Oracle Processes Waiting for Semaphores

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.
If it ain't broke, I can fix that.
Paddy_1
Valued Contributor

Re: Oracle Processes Waiting for Semaphores

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
The sufficiency of my merit is to know that my merit is NOT sufficient
Michael Schulte zur Sur
Honored Contributor

Re: Oracle Processes Waiting for Semaphores

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.
Esteemed Contributor

Re: Oracle Processes Waiting for Semaphores

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
What kind of a name is 'Wolverine'?
Paddy_1
Valued Contributor

Re: Oracle Processes Waiting for Semaphores

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.
The sufficiency of my merit is to know that my merit is NOT sufficient
Mic V.
Esteemed Contributor

Re: Oracle Processes Waiting for Semaphores

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

What kind of a name is 'Wolverine'?
zhuchao
Advisor

Re: Oracle Processes Waiting for Semaphores

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.
www.happyit.net
Hein van den Heuvel
Honored Contributor

Re: Oracle Processes Waiting for Semaphores

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.
Paddy_1
Valued Contributor

Re: Oracle Processes Waiting for Semaphores

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
The sufficiency of my merit is to know that my merit is NOT sufficient