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

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, May 28, 2002 1:18 PM

> Hey all,
>
> I'm using DBMS_STATS (despite it's bugs) on 8.1.7.2.0 / HPUX 11.0. When I
> specify "degree => 2" in the parms for either GATHER_SCHEMA_STATS or
> GATHER_TABLE_STATS, I notice that 4 "p" processes are kicked off (e.g.
> "ora_p001_sid"). Since this is on a test system, there is only one
> processor. Two of the stats processes have higher CPU and little or no
> I/Os, while the other two are almost all physical I/O and some CPU
(probably
> for the I/O requests).
>
> So, why are there four processes? I ASSuMEd that there'd only be two.
And
> I can't find the FM to R, nor anything suitable on MetaClink.
>
> Anyone?
>
> TIA!
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI
USA
> --
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.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).