Re: CBO influences

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 11 Sep 2005 06:44:01 +0000 (UTC)
Message-ID: <dg0jnh$h2t$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com>

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1126395746.244667_at_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)

My snapshot code on filestats (9i onwards) uses these columns for timing

   KCFIOPYR phyrds,
   KCFIOPBR phyblkrd,
   KCFIOPRT readtim,

KCFIOSBR singleblkrds,
KCFIOSBT singleblkrdtim,

KCFIOMBR multiblkrds,
KCFIOMBT multiblkrdtim,

You will probably find that phyrds does not quite match singleblkrds + multiblkrds

My initial checks indicated that:

MBRC = (approx) (phyblkrd - singleblkrds )/multiblkrds

(oracle records only an integer for MBRC) mreadtim and sreadtim are the obvious divisions.

NB Just because a restart says a parameter has value X, that doesn't mean that the session had that value when you ran the test. But I'll trust you ;)

I'll take a look at the oddity when I have a little time. At present I'm working on checking the final layout of the book.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005

Received on Sun Sep 11 2005 - 01:44:01 CDT