Re: 10g System statistics - single and multi

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 17 May 2005 17:28:29 -0600
Message-Id: <6.2.1.2.2.20050517171232.102022e0@pop.centrexcc.com>

Don't forget the cpu cost. What is your cpuspeed?

Using your data and a cpuspeed of 1000000000 to marginalize/eliminate the cpu cost factor I get:

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- 
------------------------------
SYSSTATS_INFO                  STATUS 
COMPLETED
SYSSTATS_INFO                  DSTART 
05-17-2005 17:15
SYSSTATS_INFO                  DSTOP 
05-17-2005 17:15
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                     400.897016
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM                                2
SYSSTATS_MAIN                  MREADTIM                               16
SYSSTATS_MAIN                  CPUSPEED                       1000000000
SYSSTATS_MAIN                  MBRC                                  128
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

17:15:53 ora101.scott> set autot traceonly explain
17:16:03 ora101.scott> select * from newkutro
17:16:16   2  /

Execution Plan

0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6251 Card=2000 Bytes=26000)

           1                  0

TABLE ACCESS (FULL) OF 'NEWKUTRO' (TABLE) (Cost=6251 Card=2000 Bytes=26000)

exactly what is expected (because of _table_scan_cost_plus_one=TRUE).

If I lower the cpuspeed to 1200, I get

17:21:07 ora101.scott> select * from newkutro;

Execution Plan

0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6548 Card=2000 Bytes=26000)

           1                  0

TABLE ACCESS (FULL) OF 'NEWKUTRO' (TABLE) (Cost=6548 Card=2000 Bytes=26000)

Practically the same as you

At 04:16 PM 5/17/2005, Christo Kutrovsky wrote:

>100 000 / 128 * 16/2 =3D 6250
>
>select * from newkutro
>
>reports a cost of 6549.
>
>Also by halfing MBRC almost doubles the cost.
>By doubling SREADTIM it almost halves the cost, but by a different amount.

Don't ignore the CPU cost "factor" - mathematically speaking not a factor but a summand. And it may not be fixed either, even for a fixed cpu speed. The estimated cpu cycles may be different for different # of estimated (physical) IO.

>To me it doesnt make sense to check for mread > sread . opinions ?

Whether it makes sense or not, that's the current rules. There are many more "sanity checks" and assumptions in the CBO, some of which I would question much more than this one.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 17 2005 - 19:33:08 CDT