Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.freenet.de!news.osn.de!diablo1-ffm.news.osn.de!news.tele.dk!news.tele.dk!small.news.tele.dk!sn-xit-03!sn-xit-08!sn-xit-05!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: DA Morgan Newsgroups: comp.databases.oracle.server Subject: Re: CBO influences Date: Sat, 10 Sep 2005 10:46:51 -0700 Organization: Ye 'Ol Disorganized NNTPCache groupie Message-ID: <1126374364.66827@yasure> User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 References: <1126283179.991124.138480@z14g2000cwz.googlegroups.com> <1126284892.947680.217710@g44g2000cwa.googlegroups.com> <1126294364.813025.287860@o13g2000cwo.googlegroups.com> <1126306293.634107@yasure> <1126329859.958172@yasure> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Cache-Post-Path: yasure!unknown@oracle.advtechserv.com X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/) X-Complaints-To: abuse@supernews.com Lines: 84 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:251253 Jonathan Lewis wrote: > "DA Morgan" wrote in message > news:1126329859.958172@yasure... > >>One more, slightly related question, if I may. >> >>Gathering system statistics I do not seem to be able to force >>a demo database to gather statistics for MBRC, MREADTIM, and >>SLAVETHR. >> >>I understand the last might be a bit difficult on a notebook >>used for classroom demo. But any help on any of this would >>be appreciated. >> >>Thanks. >>-- >>Daniel A. Morgan >>http://www.psoug.org >>damorgan@x.washington.edu >>(replace x with u to respond) > > > > > Perhaps your sample data sets and > are too small, and your db_cache_size > too big to generate any physical > multiblock reads. I just create a table > that I know is larger than the cache, > then do a few count(*) on it. > > (Or if the cache is small, I do a few > scans of source$ and a few forced > indexed accesses of source$) > > The SLAVETHR is a bit of a puzzle, > since you haven't mentioned MAXTHR > as not being set - and these both apply > to parallel queries: MAXTHR is the > observation of throughput the coordinator > can take, SLAVETHR is the throughput > the slaves can supply - I wouldn't have > thought you could get one set without > the other. (Guess - maybe the gather > goes wrong if you have had only one > ongoing parallel query that didn't terminate > when you are trying to gather system stats). Thanks. What I ended up doing was running some ROLLUP and CUBE queries on the demo Sales History schema ... worked like a charm. But here's what I get (10gR1). Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production With the Partitioning, OLAP and Data Mining options SQL> SELECT pname, pval1 2 FROM aux_stats$ 3 WHERE sname = 'SYSSTATS_MAIN'; PNAME PVAL1 ------------------------------ ---------- CPUSPEED 413 CPUSPEEDNW 450.609 IOSEEKTIM 10 IOTFRSPEED 4096 MAXTHR 43008 MBRC 8 MREADTIM 5.121 SLAVETHR SREADTIM 6.636 9 rows selected. So it is possible. Again ... thanks. -- Daniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond)