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:
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?
