Parallelism in oracle8i

From: Soman Manoj <manojdsoman_at_yahoo.com>
Date: Thu, 30 Aug 2001 03:29:11 -0700
Message-ID: <F001.0037CA4E.20010830031521@fatcity.com>

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:

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 - 05:29:11 CDT