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 220.127.116.11 non-RAC 64-bit on RHEL5.4. By lots I mean we have 478,587 indexes. We're trying to export from a 18.104.22.168 and import into an 22.214.171.124, 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.
- Re: name-service call wait
- From: Scott Sibert
- Re: name-service call wait