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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Parallelism in oracle8i

Re: Parallelism in oracle8i

From: <Riyaj_Shamsudeen_at_i2.com>
Date: Thu, 30 Aug 2001 08:02:23 -0700
Message-ID: <F001.0037D0CC.20010830074243@fatcity.com>


Hi
        What does your explain plan say with and without parallelism ? Analyzing the explain plan will be a good start..
        Also, You will benefit from parallelism only if the PQ slave processes operate on different disks /different controllers etc. The performance (of this particular query, at least ) depends upon how the table partitions and their indexes are laid out physically. Do you have any indexes on this column ? What kind of indexes are they( local, global etc)? Are those index partitions on separate disks ?
        If these partitions are not physically separated then due to the overhead associated with the message transfer between the PQ slave processes, the performance could be worser than "noparallel" query.
HTH
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com

Soman Manoj <manojdsoman_at_yahoo.com>
Sent by: root_at_fatcity.com
08/30/01 06:15 AM
Please respond to ORACLE-L

       
        To:        Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc:        
        Subject:        Parallelism in oracle8i

Hi,

We are doing testing of oracle 8i parallelism features on unix HP 11 .
We have done 2 partitions on the table ib020mb_3 on a field adt_date with the following details (partition p1 values less than('22-apr-2000') tablespace CONTRACT,
 partition p2 values less
than('01-jUn-2000')tablespace CONTRACT1).

Each of these partitions are in different tablespace. Each of this tablespace are on different disks(data files on different
disk ) in unix environment. We have 4 processors and 4 controllers
(like disk drives) to write on disk.

With this details we are running the following query with and without
degree of parallelism specified.

select  adt_date from ib020mb_3
where adt_date <'01-jun-2000'

But we are taking more time with parallelism. We have tried the query with
different degrees of parallelism. Currently we are fetching approx. 15 lac records.
We have tested with a different table and different set of records but still we
are taking more time with degree of parallelism specified.

Could you please suggest why we are taking more time .

The following are the tkprof generated for this queries:


  1. Without parallelism:

select  adt_date from ib020mb_3
where adt_date <'01-jun-2000'

call     count       cpu    elapsed   disk  query current   rows
------- ------  -------- ---------- ------ ------ ------ -------
Parse        1      0.12       0.31     21    204     5       0
Execute      1      0.00       0.00      0      0     0       0
Fetch   101340     11.37      16.48   7107 107826     0 1520078
------- ------  -------- ---------- ------- ------



total   101342     11.49      16.79   7128 108030     5 1520078

Total time taken : 28.28
2)With parallelism:

select  /*+parallel(ib020mb_3,2)*/ adt_date from ib020mb_3
where adt_date <'01-jun-2000'

call     count       cpu    elapsed   disk  query current    rows
------- ------  -------- ---------- ------ ------- ------  ------
Parse        1      0.13       0.61     21    243      6       0
Execute      1      0.00       0.00      0      0      0       0
Fetch   101340     11.72      16.79   7107 107826      0 1520078
------- ------  -------- ----------



total   101342     11.85      17.40   7128 108069      6 1520078
                      

Total time taken :29.25                    




                          

Thanks In Advance



Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Soman Manoj
  INET: manojdsoman_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Aug 30 2001 - 10:02:23 CDT

Original text of this message

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