| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Janine A Sisk
Sent: Tuesday, September 14, 2004 3: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=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
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
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=20the 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-lReceived on Tue Sep 14 2004 - 15:05:41 CDT
 |
 |