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

2003-02-13 Thread Mercadante, Thomas F
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

2003-02-13 Thread STEVE OLLIG
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

2003-02-13 Thread John Kanagaraj
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

2003-02-13 Thread Jared . Still
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

2003-02-13 Thread Tim Gorman



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

2003-02-13 Thread Mercadante, Thomas F



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

2003-02-13 Thread chao_ping
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

2003-02-13 Thread Stephen Evans

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

2003-02-12 Thread Toepke, Kevin M
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

2003-02-12 Thread Mercadante, Thomas F
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

2003-02-12 Thread Toepke, Kevin M
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