Re: Re: How to parallel index scan.

Hi,
  After your words, I did a single thread of index creation and i was so surprised to find that the time spent on index creation is less: SQL> CREATE INDEX IDX_PRO ON PRODUCTS(SELLER_ID) NOLOGGING ; Index created.

Elapsed: 00:01:12.65
God!
But I still think there is something we can tune.I noticed that when one thread creating index, cpu usage is high, while multiple thread index create, cpu usage is low. They are waiting for the wait event like those i listed in the statspack report. Thanks for your experience.:)

> I do a lot of this and have found that there isn't much you can do. I don't
> use degree anymore as I've found it hasn't been worth the effort of trying
> to figure it all out. ie. timings didn't change much. I have read where you
> might want a smaller sort_area_size. This way some slaves will be reading,
> others sorting etc. I don't do this myself, my sort_area_size is 5Mb and I
> do notice them all bunch up. I don't think adjusting the sort_area_size will
> have that much of a difference anyways.
>
> Mike
> ganstadba_at_hotmail.com
> ----- Original Message -----
> From: "chao_ping" <chao_ping_at_vip.163.com>
> To: "LazyDBA.com Discussion" <oracledba_at_lazydba.com>
> Sent: Thursday, January 23, 2003 11:01 AM
> Subject: How to parallel index scan.
>
>
> > Hi, friends:
> > Soon there will be a database reorgnization in my system and downtime is
> limited, so i am doing some test in parallel operation.
> > My test system has 8CPU/8G memory/8disk Raid5(raid pretty old).And I
> tested create index in parallel.
> > First I enlarge sort_area_size to 30MB. and do create index parallel
> degree 2-12(all tested), with nologging option. But it does not help when i
> enlarge the parallel clause:
> > 2 parallel thread: 1minute and 44 second.
> > 12 parallel thread: 1 minute and 30 second.
> > I did a statspack between the time I create the index, and find the top
> wait event like:
> >
> > Top 5 Wait Events
> > ~~~~~~~~~~~~~~~~~ Wait %
> Total
> > Event Waits Time (cs) Wt
> Time
> > -------------------------------------------- ------------ ------------ ---
> ----
> > direct path read 64,594 179,134
> 41.04
> > PX Deq: Table Q Normal 88,100 174,969
> 40.09
> > PX Deq: Execute Reply 666 61,336
> 14.05
> > PX Deq: Execution Msg 731 16,122
> 3.69
> > control file parallel write 249 2,139
> .49
> >
> > Disk is already 100% busy and system load profile:
> > 23:04:05 15 1 8 75
> > 23:04:15 16 1 2 80
> > 23:04:25 18 1 3 78
> > 23:04:35 16 1 3 80
> > 23:04:45 17 1 2 80
> > 23:04:55 18 1 1 80
> > 23:05:05 17 1 1 80
> > 23:05:15 16 1 2 81
> > 23:05:25 17 1 1 81
> > 23:05:35 16 1 2 81
> > 23:05:45 32 3 0 65
> > 23:05:55 15 2 13 69
> > 23:06:05 17 2 5 77
> > 23:06:15 17 2 7 74
> > 23:06:25 15 1 4 79
> > 23:06:35 15 1 3 81
> > 23:06:45 17 2 3 78
> > 23:06:55 17 1 2 80
> > 23:07:05 17 2 2 79
> > 23:07:15 15 1 5 79
> > 23:07:25 21 2 2 75
> > 23:07:35 29 1 11 59
> > 23:07:45 17 2 6 76
> > 23:07:55 17 1 6 75
> > 23:08:05 17 1 5 76
> > 23:08:15 17 1 3 79
> > 23:08:25 15 1 2 81
> > 23:08:35 15 1 2 82
> > Can someone share your experience of tuning parallel operation like
> parallel index create and parallel ctas? Is the disk the really bottlenect
> and is there still space for tuning?
> > Thanks.
> >
> > zhu chao.
> > www.cnoug.org.
> >
> >
> > --------
> > Oracle documentation is here:
> http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> > To unsubscribe: send a blank email to oracledba-unsubscribe_at_LAZYDBA.com
> > To subscribe: send a blank email to oracledba-subscribe_at_LAZYDBA.com
> > Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> > Tell yer mates about http://www.farAwayJobs.com
> > By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
> >
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: chao_ping
  INET: chao_ping_at_vip.163.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).