Re: MAXTHR and SLAVETHR in sys.aux_stats$
Date: Thu, 15 May 2008 03:49:41 -0700 (PDT)
Message-ID: <396618.65757.qm@web801.biz.mail.mud.yahoo.com>
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_at_jlcomp.demon.co.uk> To: Oracle List <oracle-l_at_freelists.org> 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_at_david-aldridge.com> To: "Oracle List" <oracle-l_at_freelists.org> 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?
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu May 15 2008 - 05:49:41 CDT