CBO学习---第2章--表扫描（Tablescans）(八)
TEMENT (all_rows) Old Cost (6,1,9) New Cost (6,1091968,0)
1 0 1 1 TABLE ACCESS (analyzed) TABLE SCOTT T1 (full) Old Cost (6,1,9) New Cost (6,1091968,0) Filter (TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998)
New Cost (6,1091968,0)中的1091968与下面的对应值相等，说明该脚本打出的CPU cost仅为#CPUCycles（CPU操作数）
[sql]
SQL> @cpu_costing
会话已更改。
Predicted cost (9.2.0.6): 1070604
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998 1,091,968
Predicted cost (9.2.0.6): 762787
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
"N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1 784,150
Predicted cost (9.2.0.6): 1070232
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
TO_NUMBER("V1")=1 AND "N1"=998 AND "N2"=18 1,091,595
Predicted cost (9.2.0.6): 762882
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
"N1"=998 AND TO_NUMBER("V1")=1 AND "N2"=18 784,245
Predicted cost (9.2.0.6): 770237
Filter Predicate CPU cost
---------------------------------------------------------------------
------------
"N2"=18 AND "N1"=998 AND TO_NUMBER("V1")=1 791,600
Predicted cost (9.2.0.6): 785604
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
"N2"=18 AND TO_NUMBER("V1")=1 AND "N1"=998 806,968
Left to its own choice of predicate order
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
"N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1 784,150
And one last option where the coercion on v1 is not needed
Predicted cost (9.2.0.6): 770604
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
"V1"='1' AND "N2"=18 AND "N1"=998 791,968
尽管这几个SQL是同义的，但由于条件的顺序不同，导致了CPU的cost不同；如果去掉hint，优化器会自动选择#CPUCycles最小值的执行；当然如果v1='1'来执行，会减少类型转换上的CPU操作。
/**************************************************************************************************************************************/
2.3 BCHR(The BCHR Is Dead! Long Live the BCHR!)
_cache_stats_monitor (Default value TRUE)
_optimizer_cache_stats (Default value FALSE)
[sql]
alter system flush BUFFER_CACHE;
set autotrace traceonly explain
alter session set "_optimizer_cache_stats" = true;
select count(*) from t1;
select count(*) from t1;
alter system flush BUFFER_CACHE;
select count(*) from t1;
alter sess