From: Jonathan Lewis on




"DA Morgan" <damorgan(a)psoug.org> wrote in message
news:1126374364.66827(a)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.

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.

To make the stats happen (if they can) I would
create a single large table (larger than the cache)
with one index, and run three separate attempts:

a) Design the index to access every 4th block,
and do a forced index range scan across
the whole table.

b) Do a serial full tablescan

c) Do a parallel sort group by, but on a
column which doesn't aggregate very
well so that you have to send a lot of
traffic from slave to slave. And try
to query v$px_sesstat whilst it's
going on to capture stats about the
volume of data moving across the
layers of slaves.



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




From: DA Morgan on
Jonathan Lewis wrote:
> "DA Morgan" <damorgan(a)psoug.org> wrote in message
> news:1126374364.66827(a)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(a)x.washington.edu
(replace x with u to respond)
From: Jonathan Lewis on
"DA Morgan" <damorgan(a)psoug.org> wrote in message
news:1126395746.244667(a)yasure...
> Jonathan Lewis wrote:
>> "DA Morgan" <damorgan(a)psoug.org> wrote in message
>> news:1126374364.66827(a)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(a)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






From: DA Morgan on
Jonathan Lewis wrote:

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

Thanks.

From where I'm sitting you completing your book is a far
higher priority. Any chance of getting a copy in November
at UKOUG?
--
Daniel A. Morgan
http://www.psoug.org
damorgan(a)x.washington.edu
(replace x with u to respond)
From: Jonathan Lewis on

The plan is to have a few boxes
couriered in from the printer for
the event. The schedule is pretty
tight, though. (See the URL in the
sig)

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



"DA Morgan" <damorgan(a)psoug.org> wrote in message
news:1126483366.219427(a)yasure...
>
> From where I'm sitting you completing your book is a far
> higher priority. Any chance of getting a copy in November
> at UKOUG?
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan(a)x.washington.edu
> (replace x with u to respond)