RE: name-service call wait
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <ssibert@xxxxxxxxx>, "'Oracle-L Freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 1 Apr 2010 01:44:43 -0400
Since you have plenty parallel job threads (13), why are you also running
parallel 4 on the individual actions? In general parallel options on
individual commands incur some overhead in order to put more of a machine to
work on an individual command. That is a good trade off when it is a
priority to get some individual task to complete in as little elapsed time
as possible. If you cut back to parallel 1 and you have available headroom
on i/o capacity and/or cpu cycles, run more job threads.
Setting up your threads so that all the indexes on a given table or
partition are created at the same time will take advantage of any possible
caching at any layer of the read complex. For index creation table blocks
may not go into the buffer cache, but getting the underlying blocks into the
cache of your storage array from one index creation *may* help the others.
Setting up your threads so that some threads are all the small stuff will
minimize the chances of more threads having the sort phase of index
construction spill to disk.
As for name-service call wait, there is a thread in the archives about that
from K Gopalakrishnan. Dec. 7, 2004 if I recall correctly.
I thought that was a RAC instance startup problem, but you state you're
non-RAC. And that was 10g, not 11g.
Ah. You're using clusterware and the ASM is RAC, right?
Someone may be able to point you to the exact reference on this wait (KGogal
being a likely suspect), but I would look in your clusterware logs on the
theory that the wait is coming from clustered ASM access. Are the other
nodes also pounding on ASM? The same diskgroups?
I'm speculating a bit here. Quite possibly you're just beating on ASM too
hard. Up to the diminishing return limit on getting additional indexes to
avoid sort spilling to disk you might look into whether an increase to the
pga aggregate size would help reduce total i/o.
Good luck,
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Scott Sibert
Sent: Wednesday, March 31, 2010 4:58 PM
To: Oracle-L Freelists
Subject: name-service call wait
We've tried looking around but we've had trouble finding any information on
this. It doesn't show up in the Reference guide either.
We're doing lots of index creations in 11.1.0.7 non-RAC 64-bit on RHEL5.4.
By lots I mean we have 478,587 indexes. We're trying to export from a
9.2.0.6 and import into an 11.1.0.7, doing lots of creative testing to
manually parallelize table creation, data importing, and index creation.
We're doing lots of creative manual parallelizing but today when we looked
in Grid Control for the database we saw lots of "other" waits during the
index creation. Clicking on that we see that "name-service call wait" is
most of "other." ADDM complains about large number of unusual "other" wait
events. ASH shows the "name-service call wait" as 25% of all waits during
the three-hour period.
The box is a Dell PE 2900iii dual quad-core X5470 at 3.33GHz with 48GB
memory plugged into an SMC DMX array. (Meaning it should be fast enough.)
This host is a member of a 4-node 11.2 clusterware cluster and this 11.1
database does use ASM.
We're not sure what this "name-service call wait" really means.
A little about the index creation: we've broken up the DDL (extracted from
the export) into 13 commands to create indexes that match a certain wildcard
criteria. We cover all the indexes this way and have them fairly evenly
distributed according to size of indexes and how long each set will run.
Each index create statement has PARALLEL 4 in the command to help with index
creation. I know we could have up to 52 threads creating indexes on an
8-core box but it does pretty well. (It is likely that when we go live it
will be on a couple newer--not-yet-ordered--R900's with dual quad-core E7440
at 2.4Ghz, 128GB memory and RAC.) And even when most of them finish and
there are only one or two creates being run we still have this "name-service
call wait" showing up.
Other related posts:
