Re: Parallel query on when it's not supposed to be (?)
- From: Janine A Sisk <janine@xxxxxxxxxx>
- To: Mark.Bobak@xxxxxxxxxxxxxxx
- Date: Tue, 14 Sep 2004 21:03:02 -0400
On Sep 14, 2004, at 4:28 PM, Bobak, Mark wrote:
> Janine, yes, for that table, do:
> alter table table_name parallel (degree 1);
> and also set parallel_max_servers to 0, as the other Mark suggested.
I have now done both of these things, and am intrigued by the results.
tkprof output with parallel query on:
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.01 0.01 0 0 0
0
Execute 1 0.00 0.06 0 0 3
0
Fetch 2 0.01 0.12 0 73 0
1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.02 0.19 0 73 3
1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 38
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)
tkprof output with parallel query off:
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.16 0.16 0 657 6
1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.16 0.16 0 657 6
1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 38
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
108 NESTED LOOPS
109 HASH JOIN
108 TABLE ACCESS BY INDEX ROWID ACS_RELS
109 INDEX RANGE SCAN (object id 26428)
170140 TABLE ACCESS FULL MEMBERSHIP_RELS
108 INDEX UNIQUE SCAN (object id 26694)
So the query ran marginally faster, but was less efficient in terms of
how many blocks/rows it processed. While not exactly a ringing
endorsement for parallel query, this surprised me. I didn't expect to
see a change like this. Is this just a matter of tkprof not accurately
reporting what the parallel slaves did, or is it really processing more
rows with them turned off?
thanks,
janine
--
//www.freelists.org/webpage/oracle-l
Other related posts:
