| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: parallel index creation again:in which case, can we use p
My experience shows that a parallel degree of less than 4 is nearly always
slower than serial.
I would recommend tring parallel degree of 4.
-----Original Message-----
Sent: Wednesday, February 12, 2003 10:59 AM
To: Multiple recipients of list ORACLE-L
parallel with single cpu env?
Michael Ivanov,
Hi, Thanks for your reply. In fact, I builded the index several times like, and the result is persistent across difference test case: So, I think buffer is not the cause of the parallelexecution slower. But I really do not get other parameter to tune:(
SQL> set term on timing on echo on feedback on SQL> alter session set sort_area_size = 100000000;
Session altered.
Elapsed: 00:00:00.01
SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel
(degree 2) tablespace pqind;
Index created.
Elapsed: 00:18:01.36
SQL> drop index idx_serial;
Index dropped.
Elapsed: 00:00:00.16
SQL>
SQL> create index idx_serial on viewcount( SID_LIST) nologging tablespace
pqind;
Index created.
Elapsed: 00:06:48.04
SQL> drop index idx_serial;
Index dropped.
Elapsed: 00:00:00.06
SQL>
SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel
(degree 2) tablespace pqind;
Index created.
Elapsed: 00:14:51.92
SQL> drop index idx_serial;
Index dropped.
Elapsed: 00:00:00.13
SQL>
SQL> create index idx_serial on viewcount( SID_LIST) nologging;
Index created.
Elapsed: 00:06:26.23
SQL> drop index idx_serial;
Index dropped.
Elapsed: 00:00:00.06
SQL>
SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel
(degree 2) tablespace pqind;
Index created.
Elapsed: 00:14:44.58
SQL> drop index idx_serial;
Index dropped.
Elapsed: 00:00:00.13
SQL>
SQL> create index idx_serial on viewcount( SID_LIST) nologging tablespace
pqind;
Index created.
Elapsed: 00:06:49.09
SQL> drop index idx_serial;
Index dropped.
Elapsed: 00:00:00.07
SQL>
SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel
(degree 2) tablespace pqind;
Index created.
Elapsed: 00:14:46.79
SQL> drop index idx_serial;
Index dropped.
Elapsed: 00:00:00.14
SQL>
SQL> create index idx_serial on viewcount( SID_LIST) nologging tablespace
pqind;
Index created.
Elapsed: 00:06:44.51
SQL> drop index idx_serial;
Index dropped.
Elapsed: 00:00:00.07
Regards
zhu chao
msn:chao_ping_at_163.com
www.happyit.net
www.cnoug.org(China Oracle User Group)
>Dear Chao.
>Did you try change order of index's creating- first noparallel, second with
parallel. I think you will look other results.
>
>> hi, dba friends:
>> some paper said, pqo should only be used in SMP machines, while
others
>> say, We can also use pqo in uniprocessor machines in some case. I am
trying
>> to use parallel index creation in the following env:
>>
>> Dell 1650 with 3 scsi160 disks and 1 CPU and 2G memory.
>> Oracle 9.2
>> Table contains 22000000 records,1.2GB
>> Table tablespace contains 3 datafiles , 400M, 400M and 600M, on seperate
3
>> disks. Index tablespace contains 3 datafiles, 200M, 200M and 200M on
>> seperate 3 disks.
>>
>>
>> SQL> set term on timing on echo on feedback on
>> SQL> alter session set sort_area_size = 100000000;
>> Session altered.
>> Elapsed: 00:00:00.01
>> SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel
>> (degree 2) tablespace pqind; Index created.
>> Elapsed: 00:18:01.36
>> SQL> drop index idx_serial;
>> Elapsed: 00:00:00.16
>> SQL> create index idx_serial on viewcount( SID_LIST) nologging
tablespace
>> pqind; Elapsed: 00:06:48.04
>> This machine is exclusived used my me and It seems that PQO is
rather
>> slower than single thread. So is it still possible to use PQO on single
>> processor machines? Please share your experience and idear.
>> Thanks.
>>
>> Wait event like:
>>
>> Top 5 Timed Events
>> ~~~~~~~~~~~~~~~~~~
>> Total Event Waits Time
(s)
>> Ela Time -------------------------------------------- ------------
>> ----------- -------- PX qref latch
>> 48,371 415 40.94 PX Deq: Execute Reply
>> 176 340 33.54 PX Deq Credit: send blkd
>> 47,704 248 24.47 control file parallel write
>> 112 5 .48 PX Deq Credit: need buffer
>> 1,835 4 .38
>> ------------------------------------------------------------- ^LWait
Events
>> for DB: ORA9 Instance: ora9 Snaps: 19 -20
>> -> s - second
>> -> cs - centisecond - 100th of a second
>> -> ms - millisecond - 1000th of a second
>>
>>
>--
>Best regards
>Michael Ivanov, TD "ERA"
-- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Toepke, Kevin M INET: ktoepke_at_trilegiant.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).Received on Wed Feb 12 2003 - 11:54:19 CST
 |
 |