Re: MAXTHR and SLAVETHR in sys.aux_stats$
- From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx, Oracle List <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 15 May 2008 03:49:41 -0700 (PDT)
Ah, that makes sense then, thanks Jonathan.
It did seem that maxthr and slavethr ought to be integral to influencing the
default degree of parallelism, much more than the cpu-based methods normally
quoted. If the optimizer is using them to determine the point beyond which
there is no read performance improvement in increasing DOP above something in
the order of maxthr/slavethr (though I can think of many cases where you'd want
to be higher or lower than that) then its an important piece of my mental
puzzle on how to use default DOP properly -- ie. without ending up with a
ridiculously high default degree on a 40 CPU machine.
I haven't done anything close to a formal test but some of the numbers I've
gathered on system statistics on our production machines have led me to doubt
whether direct path reads were being considered in calculating the mreadtim --
the time just seemed way to high in comparison to the weighted average of db
file scattered read and direct path read from v$event_histogram. Just a
thought, based on observation rather than rigourous test -- I'll have to
synchronise my system stats time with statspack snapshots to be sure.
I'm actually running tests on a new production storgae config at the moment,
and for now I'm inclined to go with benchmarking read performance at varying
DOP to find the maxthr and slavethr values (around 1.2GBytes/sec and
950MBytes/sec respectively), setting the system statistics manually, and making
sure that the mreadtim and mbrc are not inconsistent with slavethr. I'll see
how that works out.
Thanks again.
----- Original Message ----
From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
To: Oracle List <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, May 15, 2008 3:20:49 AM
Subject: Re: MAXTHR and SLAVETHR in sys.aux_stats$
If you generate a 10053 trace file in 10.2, the file will contain the
following section (the numbers are something I set for a demo):
Using WORKLOAD Stats
CPUSPEED: 800 millions instructions/sec
SREADTIM: 12 milliseconds
MREADTIM: 26 millisecons
MBRC: 8.000000 blocks
MAXTHR: 12000 bytes/sec
SLAVETHR: 2000 bytes/sec
So I think you have to assume it's bytes per second.
Setting them manually -
The effect I see from playing with them is that the
CBO uses maxthr/slavethr as an upper limit on the
notional degree of parallelism when doing the arithmetic.
However, there is some sort of sanity check between
the absolute values for these stats and (I think, but haven't
yet investigated) the implied throughput from MBRC and
MREADTIM, so if you want to set them manually and have
them work predictably you need to work out how the sanity
check goes and get all the figures self-consistent.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "David Aldridge" <david@xxxxxxxxxxxxxxxxxx>
To: "Oracle List" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, May 14, 2008 8:02 PM
Subject: MAXTHR and SLAVETHR in sys.aux_stats$
> Does anyone know what units these are supposed to be in, or have any caveats
> about setting them manually?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
