Parallel query on when it 's not supposed to be (?) 2004-09-14 - By Bobak, Mark
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@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of Janine A Sisk
Sent: Tuesday, September 14, 2004 3:58 PM
To: oracle-l@(protected)
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
|
|
