Parallel degrees in DBMS_STATS
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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Parallel degrees in DBMS_STATS
Run following query and check whether relevent table/index has degree > 1 select index_name,degree from dba_indexes where degree > 1 / select table_name,degree from dba_tables where degree > 1 / If degree > 1 then it will use nymber of PQ process equivalent to number od degree Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Tue, 28 May 2002 11:18:30 -0800 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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). MOHAMMAD RAFIQ _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Parallel degrees in DBMS_STATS
Nope. All tables are a degree of "1". And according to the docs, specifying the "degree" parameter when calling DBMS_STATS overrides the table default. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, May 28, 2002 3:01 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Parallel degrees in DBMS_STATS > > > Run following query and check whether relevent table/index > has degree > 1 > select index_name,degree from dba_indexes where degree > 1 > / > select table_name,degree from dba_tables where degree > 1 > / > > If degree > 1 then it will use nymber of PQ process > equivalent to number od > degree > > Regards > Rafiq > > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Tue, 28 May 2002 11:18:30 -0800 > > 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 > [EMAIL PROTECTED] Quad/Tech International, > Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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" <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jesse, Rich > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: Tim Gorman [mailto:[EMAIL PROTECTED]] > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
