Tune index parallel create in 817

From: chao_ping <member327_at_dbforums.com>
Date: Thu, 23 Jan 2003 16:07:25 +0000
Message-ID: <2425018.1043338045_at_dbforums.com>

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.

--
Posted via http://dbforums.com

Received on Thu Jan 23 2003 - 17:07:25 CET