Re: 10g System statistics - single and multi
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: kutrovsky.oracle@xxxxxxxxx
- Date: Tue, 17 May 2005 17:28:29 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
