Re: Parallel query on when it's not supposed to be (?)

From: Janine A Sisk <janine_at_furfly.net>
Date: Tue, 14 Sep 2004 21:03:02 -0400
Message-Id: <FE7DA575-06B2-11D9-B6F5-000393AED29A@furfly.net>

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

Received on Tue Sep 14 2004 - 19:58:35 CDT