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

>From MetaLink:

Database Parameter Setup for Parallel Execution PARALLEL_MAX_SERVERS
Maximum slaves possible per instance. A value of 0 means no parallel query.

Hope that helps,
Mark Stahlke

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Janine A Sisk
Sent: Tuesday, September 14, 2004 1:58 PM To: oracle-l_at_freelists.org
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 far it has eluded me; I'm hoping someone out there will know the answer.

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

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

SQL> show parameters para

NAME                                 TYPE    VALUE
------------------------------------ -------
------------------------------
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
------------------------------------ -------
------------------------------
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 = 1767463;

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

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

Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=1 Bytes=31)     1 0 SORT (AGGREGATE)
    2 1 SORT* (AGGREGATE)
:Q428002

    3 2 NESTED LOOPS* (Cost=64 Card=65 Bytes=2015)
:Q428002

    4 3 HASH JOIN* (Cost=64 Card=65 Bytes=1690)
:Q428002

    5 4 TABLE ACCESS* (BY INDEX ROWID) OF 'ACS_RELS' (Cost
:Q428000

           =17 Card=65 Bytes=780)

    6    5             INDEX (RANGE SCAN) OF 'ACS_RELS_OBJECT_ID_ONE_ID
           X' (NON-UNIQUE) (Cost=1 Card=65)

    7    4           TABLE ACCESS* (FULL) OF 'MEMBERSHIP_RELS' (Cost=46 


:Q428001

            Card=34577 Bytes=484078)

    8    3         INDEX* (UNIQUE SCAN) OF 'DOTLRN_MEMBER_RELS_REL_ID_P 


:Q428002

           K' (UNIQUE)



    2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ 
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 
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 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 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, 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 returned no rows. But now it does. I assume that executing the query caused the parallel facility to "wake up" since it was needed, but I'm only guessing. This is the development server and it's fairly lightly used (compared to the production server), so it's not implausible that the query doesn't get executed very often.

One last detail: fast_start_parallel_rollback was originally set to LOW, which I believe is the default. We set it to FALSE via an "alter system" command but it doesn't seem to have changed anything. This may 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 production server and a fair number of waits associated with parallel query are showing up in the statspack report. Since parallel query is not supposed to be turned on there either, I started looking into it and found that both systems are exhibiting this bizarre (to me, anyway) behavior.

Can anyone a) explain what the heck is going on here and b) tell me how 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