RE: Parallel degrees in DBMS_STATS

One bug is with GATHER_SCHEMA_STATS. I don't remember the bug# offhand, but under some circumstances (like ours) it will fail to gather stats for the "first" table in the schema. I don't know if "first" relates to the schema alphabetically or by object ID, etc. BTW, I've seen this behavior on 8.1.6.0.0. Every week, in fact.

Thanks for the perfect explanation! :)

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Tim Gorman [mailto:Tim_at_SageLogix.com]
> Sent: Tuesday, May 28, 2002 6:43 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Parallel degrees in DBMS_STATS
>
>
> I'd be interested to know of the bugs you've found on
> DBMS_STATS; most of
> the bugs I've seen logged against it were created due to
> differences with
> ANALYZE, and in the end it was determined that ANALYZE
> produced the wrong
> result, not DBMS_STATS...
>
> Anyway, the symptoms you describe match those for a
> "two-stage parallel"
> operation. One-stage parallel operations are simple SELECT
> on a table.
> Two-stage happens with queries that use GROUP BY (including
> ORDER BY and
> DISTINCT). You have requested a "degree of parallelism"
> (DOP) of 2. Oracle
> allocates 2 parallel execution slave processes to do one stage of the
> two-stage operation and 2 parallel execution slave processes to do the
> second stage; total of 4. The first 2 slave processes are considered
> "producers" and they are scanning the table or indexes. The
> second 2 slave
> processes are considered "consumers" and they are taking the
> results from
> the "producers" and grouping them for the GROUP BY. This
> explains why the
> the two sets of slave processes show different characteristics: the
> "producers" should show lots of physical I/O (which doesn't
> need a lot of
> CPU) and the "consumers" should show lots of CPU but no I/O.
> They should
> also be busy at different times, since you only have one CPU
> to "timeshare"
> between them...
>
> Hope this helps...

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

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).