Re: CBO influences

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 10 Sep 2005 10:46:51 -0700
Message-ID: <1126374364.66827@yasure>

Jonathan Lewis wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1126329859.958172_at_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_at_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_at_x.washington.edu
(replace x with u to respond)

Received on Sat Sep 10 2005 - 12:46:51 CDT