RE: Why is Parallel Query Running after upgrading to 8.1.7.2

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Tim Gorman
> Sent: Saturday, June 08, 2002 4:53 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Why is Parallel Query Running after upgrading to 8.1.7.2
>
>
> Here's my thinking -- it could be way off the mark...
>
> As observed in the output from the 10053 trace, the CBO always
> considers not
> only serial full table scans but also parallel full table scans. The big
> question is exactly what "degree of parallelism" is it using in its
> cogitations; I haven't had a chance to research that (at least I don't
> remember). Logically, with no "PARALLEL" hint in the SQL
> statement and with
> the table DEGREE set to "1", then the "degree of parallelism"
> considered by
> the CBO should be "1". But apparently not.

I don't know, your original thoughts make sense. A quick 10053 trace indicates with a value of 1, the serial and parallel costs are the same. Here is the section of a 10053 trace showing the tablescan (tsc) costs for serial (Resc) and parallel (Resp) with a degree of 1 on the table:

  Access path: tsc Resc: 234 Resp: 234   <snip>
  BEST_CST: 234.00 PATH: 2 Degree: 1

Changed the degree to 4, and here is how the section changes, note the drop for Resp and the reported value for Degree:

  Access path: tsc Resc: 234 Resp: 59   <snip>
  BEST_CST: 234.00 PATH: 2 Degree: 4

Yeah I know that *you* know what those abbreviations mean, but I included the explanation for those not familiar with 10053 traces. So yeah, a 10053 shows the comparison between serial and parallel, but with a degree of 1, one would *think* they would always be the same. I, like you, wouldn't expect parallel to jump into the mix, or at least I've never run across such a situation (except as noted below).

> Are you sure that it is the
> value "1" and not "DEFAULT" in the DEGREE column?

And along the "default" train of thought, and a wild stab in the dark. If someone or something had issued an "alter session force parallel query", and the degree on the tables/indexes were 1, and, no hints specifying parallel were used, it would still use a default value which is based on the number of CPU's. For example, I just bounced my single CPU box here at home setting the CPU Count to 4. After issuing the "alter session force parallel query" and doing a select on a table with a degree of 1, the 10053 trace indicated a degree of 8:

  Access path: tsc Resc: 234 Resp: 30 <snip>
  BEST_CST: 234.00 PATH: 2 Degree: 8

Something like that *could* have happened. But it is a wild stab in the dark.

Cherie, have you come up with anything from the Metalink research you were going to do?

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).