RE: Re: parallel index creation again:in which case, can we use p
Steve,
I appreciate the beer-related example. A man after my best interests.
But if the customers are on the far-ends of the bar, then the bartender is
wasting time
walking back and forth!
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Thursday, February 13, 2003 12:45 PM
To: Multiple recipients of list ORACLE-L
p
Tom - think of it like a bartender serving beers. the bartender is the CPU,
the beer is the data, the glass is the disk. give him 2 glasses, turn on
parallelism (let him use both hands), and he can get your beer to you
faster...
-Original Message-
Sent: Thursday, February 13, 2003 9:40 AM
To: Multiple recipients of list ORACLE-L
Steve,
I believe what you say, but it seems counter-intuitive.
If you only have one cpu, and you start two jobs, then it follows that the
cpu needs to split itself to do the work.
So, what are we gaining? The CPU can only go so fast and do so much work.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Thursday, February 13, 2003 9:39 AM
To: Multiple recipients of list ORACLE-L
tom,
if the process is IO bound (ie consumes little cpu) then you can achieve a
lot.
thanks,
steve
"Mercadante, Thomas F" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/12/2003 02:23 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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 = 1;
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
RE: Re: parallel index creation again:in which case, can we use p
Tom - think of it like a bartender serving beers. the bartender is the CPU,
the beer is the data, the glass is the disk. give him 2 glasses, turn on
parallelism (let him use both hands), and he can get your beer to you
faster...
-Original Message-
Sent: Thursday, February 13, 2003 9:40 AM
To: Multiple recipients of list ORACLE-L
Steve,
I believe what you say, but it seems counter-intuitive.
If you only have one cpu, and you start two jobs, then it follows that the
cpu needs to split itself to do the work.
So, what are we gaining? The CPU can only go so fast and do so much work.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Thursday, February 13, 2003 9:39 AM
To: Multiple recipients of list ORACLE-L
tom,
if the process is IO bound (ie consumes little cpu) then you can achieve a
lot.
thanks,
steve
"Mercadante, Thomas F" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/12/2003 02:23 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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 = 1;
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 2200 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
>
RE: Re: parallel index creation again:in which case, can we use p
Stephen/Chao-ping,
I will side with Tom on this. In a single CPU situation, you want to avoid
context switches by _reducing_ the number of processes in a single CPU box.
Depending on other stuff such as processor/L2 cache flushing, this can be
quite a significant overhead. So PQ on a a single CPU box is essentially a
bad idea.
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
What would you see if you were allowed to look back at your life at the end
of your journey in this earth?
** The opinions and statements above are entirely my own and not those of my
employer or clients **
-Original Message-
Sent: Thursday, February 13, 2003 7:40 AM
To: Multiple recipients of list ORACLE-L
Steve,
I believe what you say, but it seems counter-intuitive.
If you only have one cpu, and you start two jobs, then it follows that the
cpu needs to split itself to do the work.
So, what are we gaining? The CPU can only go so fast and do so much work.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Thursday, February 13, 2003 9:39 AM
To: Multiple recipients of list ORACLE-L
tom,
if the process is IO bound (ie consumes little cpu) then you can achieve a
lot.
thanks,
steve
"Mercadante, Thomas F" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/12/2003 02:23 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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 = 1;
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.
Re: Re: parallel index creation again:in which case, can we use p
Oh, this is just *begging* for me to do some testing.
Anything that isn't real work. ;)
Jared
"Tim Gorman" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/13/2003 08:34 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: Re: parallel index creation again:in which case, can we
use p
Simply put, CPU is swifter than I/O (though not as much as some would have
us believe!).
7-8 years ago, we benchmarked Oracle 7.1 PQ on single-processor IBM RS6000
nodes and found that the "sweet spot" was DOP=4 (where "DOP" means "degree
of parallelism"). In testing on those 60Mhz PowerPC RISC processors, one
CPU on 1RPM SSA drives (using RAID1+0) would drive four PQ slave
processes best. Adding more or removing any tended to reduce overall
performance. One CPU can easily drive several PQ processes, because the
bottleneck there is I/O...
I suspect that todays multi-Ghz processors, if supported by the right
backplane, bus, and RAM, can do a little better... :-)
- Original Message -
To: Multiple recipients of list ORACLE-L
Sent: Thursday, February 13, 2003 8:39 AM
Steve,
I believe what you say, but it seems counter-intuitive.
If you only have one cpu, and you start two jobs, then it follows that the
cpu needs to split itself to do the work.
So, what are we gaining? The CPU can only go so fast and do so much work.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Thursday, February 13, 2003 9:39 AM
To: Multiple recipients of list ORACLE-L
tom,
if the process is IO bound (ie consumes little cpu) then you can achieve a
lot.
thanks,
steve
"Mercadante, Thomas F" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/12/2003 02:23 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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 = 1;
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:
Re: Re: parallel index creation again:in which case, can we use p
Simply put, CPU is swifter than I/O (though not as much as some would have us believe!). 7-8 years ago, we benchmarked Oracle 7.1 PQ on single-processor IBM RS6000 nodes and found that the "sweet spot" was DOP=4 (where "DOP" means "degree of parallelism"). In testing on those 60Mhz PowerPC RISC processors, one CPU on 1RPM SSA drives (using RAID1+0) would drive four PQ slave processes best. Adding more or removing any tended to reduce overall performance. One CPU can easily drive several PQ processes, because the bottleneck there is I/O... I suspect that todays multi-Ghz processors, if supported by the right backplane, bus, and RAM, can do a little better... :-) - Original Message - From: Mercadante, Thomas F To: Multiple recipients of list ORACLE-L Sent: Thursday, February 13, 2003 8:39 AM Subject: RE: Re: parallel index creation again:in which case, can we use p Steve, I believe what you say, but it seems counter-intuitive. If you only have one cpu, and you start two jobs, then it follows that the cpu needs to split itself to do the work. So, what are we gaining? The CPU can only go so fast and do so much work. Tom Mercadante Oracle Certified Professional -Original Message-From: Stephen Evans [mailto:[EMAIL PROTECTED]]Sent: Thursday, February 13, 2003 9:39 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Re: parallel index creation again:in which case, can we use ptom, if the process is IO bound (ie consumes little cpu) then you can achieve a lot. thanks, steve "Mercadante, Thomas F" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 02/12/2003 02:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: Re: parallel index creation again:in which case, can we use pIf you only have one CPU, then is parallel either not supported, or simply awaste of time?I actually thought it was not supported. If you only have one CPU, what doyou expect to gain?Tom MercadanteOracle Certified Professional-Original Message-Sent: Wednesday, February 12, 2003 12:54 PMTo: Multiple recipients of list ORACLE-LpMy experience shows that a parallel degree of less than 4 is nearly alwaysslower than serial. I would recommend tring parallel degree of 4.-Original Message-Sent: Wednesday, February 12, 2003 10:59 AMTo: Multiple recipients of list ORACLE-Lparallel with single cpu env?Michael Ivanov, Hi, Thanks for your reply. In fact, I builded the index several times like, and theresult 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 onSQL> alter session set sort_area_size = 1;Session altered.Elapsed: 00:00:00.01SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel(degree 2) tablespace pqind;Index created.Elapsed: 00:18:01.36SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.16SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging tablespacepqind;Index created.Elapsed: 00:06:48.04SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.06SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel(degree 2) tablespace pqind;Index created.Elapsed: 00:14:51.92SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.13SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging;Index created.Elapsed: 00:06:26.23SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.06SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel(degree 2) tablespace pqind;Index created.Elapsed: 00:14:44.58SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.13SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging tablespacepqind;Index created.Elapsed: 00:06:49.09SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.07SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel(degree 2) tablespace pqind;Index created.Elapsed: 00:14:46.79SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.14SQL> SQL> create index idx_serial on
RE: Re: parallel index creation again:in which case, can we use p
Steve, I believe what you say, but it seems counter-intuitive. If you only have one cpu, and you start two jobs, then it follows that the cpu needs to split itself to do the work. So, what are we gaining? The CPU can only go so fast and do so much work. Tom Mercadante Oracle Certified Professional -Original Message-From: Stephen Evans [mailto:[EMAIL PROTECTED]]Sent: Thursday, February 13, 2003 9:39 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Re: parallel index creation again:in which case, can we use ptom, if the process is IO bound (ie consumes little cpu) then you can achieve a lot. thanks, steve "Mercadante, Thomas F" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 02/12/2003 02:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: Re: parallel index creation again:in which case, can we use pIf you only have one CPU, then is parallel either not supported, or simply awaste of time?I actually thought it was not supported. If you only have one CPU, what doyou expect to gain?Tom MercadanteOracle Certified Professional-Original Message-Sent: Wednesday, February 12, 2003 12:54 PMTo: Multiple recipients of list ORACLE-LpMy experience shows that a parallel degree of less than 4 is nearly alwaysslower than serial. I would recommend tring parallel degree of 4.-Original Message-Sent: Wednesday, February 12, 2003 10:59 AMTo: Multiple recipients of list ORACLE-Lparallel with single cpu env?Michael Ivanov, Hi, Thanks for your reply. In fact, I builded the index several times like, and theresult 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 onSQL> alter session set sort_area_size = 1;Session altered.Elapsed: 00:00:00.01SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel(degree 2) tablespace pqind;Index created.Elapsed: 00:18:01.36SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.16SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging tablespacepqind;Index created.Elapsed: 00:06:48.04SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.06SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel(degree 2) tablespace pqind;Index created.Elapsed: 00:14:51.92SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.13SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging;Index created.Elapsed: 00:06:26.23SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.06SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel(degree 2) tablespace pqind;Index created.Elapsed: 00:14:44.58SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.13SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging tablespacepqind;Index created.Elapsed: 00:06:49.09SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.07SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel(degree 2) tablespace pqind;Index created.Elapsed: 00:14:46.79SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.14SQL> SQL> create index idx_serial on viewcount( SID_LIST) nologging tablespacepqind;Index created.Elapsed: 00:06:44.51SQL> drop index idx_serial;Index dropped.Elapsed: 00:00:00.07Regardszhu chaomsn:[EMAIL PROTECTED]www.happyit.netwww.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 withparallel. I think you will look other results.>>> hi, dba friends:>> some paper said, pqo should only be used in SMP machines, whileothers>> say, We can also use pqo in uniprocessor machines in some case. I amtrying>> 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 2200 records,1.2GB>> Table tablespace contains 3 datafiles , 400M, 400M and 600M, on seperate3>> 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 = 1;>> Session altered.>> Elapsed: 00:00:00.01>> SQL> create index idx_serial on viewcount( SID_LIST) nologging parallel>> (degree 2) tablespace pqind;
Re: RE: Re: parallel index creation again:in which case, can we use p
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.
2003-02-13 06:38:00 ÄúÔÚÀ´ÐÅÖÐдµÀ£º
tom,
if the process is IO bound (ie consumes little cpu) then you can achieve a lot.
thanks,
steve
"Mercadante, Thomas F" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/12/2003 02:23 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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 = 1;
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 2200 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 = 1;
>> 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>
RE: Re: parallel index creation again:in which case, can we use p
tom, if the process is IO bound (ie consumes little cpu) then you can achieve a lot. thanks, steve "Mercadante, Thomas F" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 02/12/2003 02:23 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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 = 1; 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 2200 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 = 1; >> 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 mac
RE: Re: parallel index creation again:in which case, can we use p
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 = 1; 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 2200 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 = 1; >> 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 WaitsTime (s) >> Ela Time -
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 = 1; 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 2200 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 = 1; >> 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 WaitsTime (s) >> Ela Time >> --- PX qref latch >> 48,371 41540.94 PX Deq: Execute Reply >> 176 34033.54 PX Deq Credit: send blkd >> 47,704 24824.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 >>
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 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 = 1; 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 2200 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 = 1; >> 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 WaitsTime (s) >> Ela Time >> --- PX qref latch >> 48,371 41540.94 PX Deq: Execute Reply >> 176 34033.54 PX Deq Credit: send blkd >> 47,704 24824.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
