RE: Parallel query on when it's not supposed to be (?)
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <janine@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Sep 2004 16:10:01 -0400
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
