Re: System Statistics and the CBO

There are no default values for system statistics. If you want to enable cpu costing with - theoretically - zero impact on IO costing you can modify your gathered system statistics and set MBRC => <what you have now for db_file_multiblock_read_count> SREADTIM => 1
MREADTIM => <depends on what you set MBRC to. see table below)

MBRC	MREADTIM
8	1.21
16	1.54
32	1.95
64	2.48

For any MBRC value not in the table you can do one of three things: a) do a linear extrapolation
b) plunk the numbers from the table above into excel, draw a chart, draw a power trendline, get the formula for the trendline and calculate your value. The number you get from this method likely will differ from that of a linear extrapolation only in the 3rd or 4th decimal. c) e-mail me and I look it up in my chart (obtained with method b from a few more than 4 measured values)
Which reminds me that there is a 4th option - Do tests to discover what the number should be.

Patty.Charlebois_at_greenshield.ca wrote:
> I gathered system statistics over during a typical day and stored them in a table as I do not want them 'turned on' until I have done some testing and can ensure that they will not negatively impact our production OLTP system.
>
> I then imported the system statistics that I gathered from production into our test system and compared plans before and after the system stats were imported. The production and test server are the exact same hardware and database.
>
> Where would I find the default values for our system?
>
> The difference in the plans is choice of single index:
>
>
> INDEX (RANGE SCAN) OF 'X04CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=1)
>
> vs.
>
> INDEX (RANGE SCAN) OF 'X01CLAIM_FORM' (NON-UNIQUE) (Cost=2 Card=153)
>
>
> To have any idea why is that, I should know the default values for your system, which I don't.
> Because of larger cardinality, I somehow think that the latter has larger MBRC, which makes
> multiblock reads cheap enough rot the CBO to choose the latter plan. Have you ever tried gathering system statistics? Importing it doesn't seem like a very good idea to me, because system stats
> are supposed to calibrate your system. If you import system stats, you are using a calibration
> from another system.
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l