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 <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 08/30/01 06:15 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
