RE: Parallel query on when it's not supposed to be (?)

Janine,

Try this:

select table_name from dba_tables where degree >1;
select index_name from dba_indexes where degree >1;

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Janine A Sisk
Sent: Tuesday, September 14, 2004 3:58 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Parallel query on when it's not supposed to be (?)


Hi all,

I have a mystery on my hands.  There must be a good explanation, but so=20
far it has eluded me;  I'm hoping someone out there will know the=20
answer.

Configuration:  Oracle 8.1.7.4 64-bit on Solaris 9.  System is a=20
SunFire V440.

As far as I can tell, this system does not have the parallel query=20
facility turned on:

SQL> show parameters para

NAME                                 TYPE    VALUE
------------------------------------ -------=20
------------------------------
fast_start_parallel_rollback         string  FALSE
optimizer_percent_parallel           integer 0
parallel_adaptive_multi_user         boolean FALSE
parallel_automatic_tuning            boolean FALSE
parallel_broadcast_enabled           boolean FALSE
parallel_execution_message_size      integer 2152
parallel_instance_group              string
parallel_max_servers                 integer 5
parallel_min_percent                 integer 0
parallel_min_servers                 integer 0
parallel_server                      boolean FALSE

NAME                                 TYPE    VALUE
------------------------------------ -------=20
------------------------------
parallel_server_instances            integer 1
parallel_threads_per_cpu             integer 2
recovery_parallelism                 integer 2

Everything is off, right?  Even parallel rollback has been disabled.

I have a very simple query:

select count(dotlrn_member_rels_approved.rel_id)
from   dotlrn_member_rels_approved
where dotlrn_member_rels_approved.community_id =3D 1767463;

dotlrn_member_rels_approved is a view, which is a subset of another=20
view, which is a simple join.  Nothing obviously tricky there.

This query, when profiled via autotrace, produces the following=20
execution plan:

Execution Plan
----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D64 Card=3D1 =
Bytes=3D31)
    1    0   SORT (AGGREGATE)
    2    1     SORT* (AGGREGATE)                                       =20
:Q428002
    3    2       NESTED LOOPS* (Cost=3D64 Card=3D65 Bytes=3D2015)        =
    =20
:Q428002
    4    3         HASH JOIN* (Cost=3D64 Card=3D65 Bytes=3D1690)         =
    =20
:Q428002
    5    4           TABLE ACCESS* (BY INDEX ROWID) OF 'ACS_RELS' (Cost=20
:Q428000
           =3D17 Card=3D65 Bytes=3D780)

    6    5             INDEX (RANGE SCAN) OF 'ACS_RELS_OBJECT_ID_ONE_ID
           X' (NON-UNIQUE) (Cost=3D1 Card=3D65)

    7    4           TABLE ACCESS* (FULL) OF 'MEMBERSHIP_RELS' =
(Cost=3D46=20
:Q428001
            Card=3D34577 Bytes=3D484078)

    8    3         INDEX* (UNIQUE SCAN) OF 'DOTLRN_MEMBER_RELS_REL_ID_P=20
:Q428002
           K' (UNIQUE)



    2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */=20
SYS_OP_MSR(COUNT(A1.C0
                                    )) FROM (SELECT /*+ ORDERED NO_EXPAN

    3 PARALLEL_COMBINED_WITH_PARENT
    4 PARALLEL_COMBINED_WITH_PARENT
    5 PARALLEL_FROM_SERIAL
    7 PARALLEL_TO_PARALLEL          SELECT /*+ Q428001 NO_EXPAND=20
ROWID(A1) */ A1
                                    ."REL_ID" C0,A1."MEMBER_STATE" C1 FR

    8 PARALLEL_COMBINED_WITH_PARENT

Huh?  How is this possible?

Furthermore, when I do a 10046 event trace and look at the tkprof=20
report, I don't see anything about parallel:

Rows     Row Source Operation
-------  ---------------------------------------------------
       1  SORT AGGREGATE
       0   SORT AGGREGATE
       0    NESTED LOOPS
       0     HASH JOIN
       0      TABLE ACCESS BY INDEX ROWID ACS_RELS
     109       INDEX RANGE SCAN (object id 26428)
       0      TABLE ACCESS FULL MEMBERSHIP_RELS
       0     INDEX UNIQUE SCAN (object id 26694)

I have even tried doing an "explain plan for" and then running=20
utlxplp.sql to look at the plan, but the parallel part is not there.

Even more perplexing, it does look like parallel query is turned on,=20
despite the parameter settings:

SQL> select * from v_$px_process;

SERV STATUS           PID SPID             SID    SERIAL#
---- --------- ---------- --------- ---------- ----------
P000 AVAILABLE         24 5175
P001 AVAILABLE         26 5177
P002 AVAILABLE         27 5179
P003 AVAILABLE         28 5181
P004 AVAILABLE         29 5183

I first started looking at this yesterday and at that time, this select=20
returned no rows.  But now it does.  I assume that executing the query=20
caused the parallel facility to "wake up" since it was needed, but I'm=20
only guessing.  This is the development server and it's fairly lightly=20
used (compared to the production server), so it's not implausible that=20
the query doesn't get executed very often.

One last detail:  fast_start_parallel_rollback was originally set to=20
LOW, which I believe is the default.  We set it to FALSE via an "alter=20
system" command but it doesn't seem to have changed anything.  This may=20
not be important but I mention it for the sake of completeness.

BTW, the reason I care about this is that I'm trying to tune the=20
production server and a fair number of waits associated with parallel=20
query are showing up in the statspack report.  Since parallel query is=20
not supposed to be turned on there either, I started looking into it=20
and found that both systems are exhibiting this bizarre (to me, anyway)=20
behavior.

Can anyone a) explain what the heck is going on here and b) tell me how=20
to drive a stake through the heart of parallel query on this system?

thanks,

janine

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Other related posts: