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:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).