Parallel query on when it 's not supposed to be (?) 2004-09-14 - By Janine A Sisk
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
|
|
