Parallel query on when it 's not supposed to be (?) 2004-09-14 - By Janine A Sisk
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
--
http://www.freelists.org/webpage/oracle-l
|
|
