Re: System Statistics and the CBO

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: Patty.Charlebois@xxxxxxxxxxxxxx
  • Date: Fri, 17 Jun 2005 08:11:24 -0600

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@xxxxxxxxxxxxxx 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
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2005