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

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

From: chao_ping <chao_ping_at_vip.163.com>
Date: Thu, 13 Feb 2003 08:09:02 -0800
Message-ID: <F001.0054BF99.20030213080902@fatcity.com>


Stephen Evans£¬ÄúºÃ£¡

       It seems your words are right, after i balanced my disk io and retest the index build, wait time during index parallel creation increases.
	   I will retest my creation next morning.
	Thanks.

¡¡¡¡

tom,

if the process is IO bound (ie consumes little cpu) then you can achieve a lot.

thanks,

steve

"Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us> Sent by: root_at_fatcity.com
02/12/2003 02:23 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 
        cc:         
        Subject:        RE: Re: parallel index creation again:in which case, can we use p



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





= = = = = = = = = = = = = = = = = = = = = = 
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÖÂ
Àñ£¡

¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡chao_ping
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡chao_ping_at_vip.163.com
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡2003-02-14


-- 
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).
Received on Thu Feb 13 2003 - 10:09:02 CST

Original text of this message

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