Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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-lReceived on Fri Jun 17 2005 - 10:16:36 CDT
![]() |
![]() |