Re: CBO influences

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 10 Sep 2005 16:43:12 -0700
Message-ID: <1126395746.244667@yasure>

Jonathan Lewis wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1126374364.66827_at_yasure...
>

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

>
>
> Odd.
>
> It's interesting to note that your mreadtim is less
> than your sreadtim, and your MBRC looks
> suspiciously as if it might be your db_file_multiblock_read_count,
> which would tend to suggest (in a busy production
> system at least) that nearly everything you were
> doing was at least a full scan, and possibly
> a parallel scan.

SHUTDOWN IMMEDIATE STARTUP statistics in sys.aux_stats$ unchanged

col name format a30
col value format a30

SELECT name, value
FROM v$parameter
WHERE name LIKE '%file_multiblock%';

NAME                           VALUE
------------------------------ ------------------------------

db_file_multiblock_read_count 16

So, no, MBRC is not the multiblock read count.

> If you access x$kcfio - which is where the
> read stats come from, and check the columns
> (names I can't remember, but they'll be obvious)
> for sblk reads and mblk reads and mblk read
> counts, and sblk read times and mblk read times
> just before the start, and just after the stop,
> this should tell you the figures that the code
> is using to calculate the mbrc, sreadtim and
> mreadtim - check if what's captured in x$kcfio
> makes sense.

Not sure I understand precisely what to look at. All entries in x$kcfio are 0 except as follows:

   1* SELECT DISTINCT addr, kcfiosbr, kcfiombr FROM x$kcfio SQL> / ADDR KCFIOSBR KCFIOMBR
-------- ---------- ----------

206034FC       2828        106
2060361C         26          0
2060373C        105         15
2060385C          1          0
2060397C          1          0
20603A9C          5          2
20603BBC          1          0
20603DFC          1          0

I presume these are the columns to which you refer.

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 - 18:43:12 CDT