Re: Re: parallel index creation again:in which case, can we use 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 parallel execution 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)

• 2003-02-12 18:40:00 ,you wrote£º=======

>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).