Soman, If you are truly scanning 100,000 records then
that could be the problem. There is overhead to do the
PQ. In your case it is probably cheaper to do a
serialized query. Try the same test with 1,000,000 or
2,000,000 records and you will probably see a
difference. I had a similar problem to this in an OPS
environment. I would do a PQ with 500,000 records on
one node and it would take about 10 seconds but the
same PQ across nodes would take 15 seconds. However
when I bumped the number of records to around
2,000,000 the PQ on 1 node would take about 25 seconds
and the PQ across nodes took about 19 seconds. There
is a cost to PQ and you have to find where PQ's start
to outperform the serialized queries. I would try this
before staring complex tuning rituals.
Scott
--- Soman Manoj <[EMAIL PROTECTED]> wrote:
> 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).
__________________________________________________
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: Scott
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).
