Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: parallel index creation again:in which case, can we use p

RE: Re: parallel index creation again:in which case, can we use p

From: Toepke, Kevin M <ktoepke_at_trilegiant.com>
Date: Wed, 12 Feb 2003 12:48:54 -0800
Message-ID: <F001.0054B2C3.20030212124854@fatcity.com> 





Better utilization of the CPU. While one process is I/O-ing (or waiting on
an I/O) the other process can use the CPU. 



Various tests I have performed on various unixes (unicies?) have shown the
parallelism usually scales to between 3 & 6 times the number of CPUs before
performance degrades.



Quite often adding another parallel thread to a process helps even when the
CPU is shown as 100% busy. 



-----Original Message-----


Sent: Wednesday, February 12, 2003 2:24 PM
To: Multiple recipients of list ORACLE-L




If you only have one CPU, then is parallel either not supported, or simply a
waste of time?



I actually thought it was not supported.  If you only have one CPU, what do
you expect to gain?



Tom Mercadante


Oracle Certified Professional




-----Original Message-----


Sent: Wednesday, February 12, 2003 12:54 PM
To: Multiple recipients of list ORACLE-L
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 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)






>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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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 - 14:48:54 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US