-none- 2004-09-14 - By -not available-
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@(protected) [mailto:oracle-l-bounce@(protected)]
On Behalf Of Janine A Sisk
Sent: Tuesday, September 14, 2004 1: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 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
|
|
