Here we have created the table partitions on
different disks. Also we have created local index with
the index residing on the same partitions as the
table. Hence even the index partitions are on
different disks.
We are finding that when the index is dropped then
there is significant increase in the performance of
the query when parallelism is specified.Can anyone
explain why this is so?
Regards.
- Riyaj_Shamsudeen_at_i2.com wrote:
> 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).
>
>
>
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 Sun Sep 02 2001 - 20:26:10 CDT
