在看cbo优化法则这本书，做cpu车本计算实验是发现autotrace的cpu cost是一样的
SQL> begin
2 dbms_stats.set_system_stats('MBRC',12);
3 dbms_stats.set_system_stats('MREADTIM',30);
4 dbms_stats.set_system_stats('SREADTIM',5);
5 dbms_stats.set_system_stats('CPUSPEED',500);
6end;
7/
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> drop table t1;
create table t1(
v1,
n1,
n2
)
as
select
to_char(mod(rownum,20)),
rownum,
mod(rownum,20)
from
all_objects
where
rownumtrue
);
end;
/
Table dropped.
PL/SQL procedure successfully completed.
SQL> explain plan for
select /*+ cpu_costing ordered_predicates */
23*
4from t1
5where
6 v1 = 1
7andn2 = 18
8andn1 = 998
9;
Explained.
SQL> set autotrace traceonly on;
SP2-0158: unknown SET option "on"
SQL> set autotrace traceonly explain;
SQL> select /*+ cpu_costing ordered_predicates */
*
23from t1
4where
5
n1 = 998
6andn2 = 18
7andv1 = 1
8;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT|| 1 | 9 | 5 (0)| 00:00:01 |
|*1 |TABLE ACCESS FULL| T1 | 1 | 9 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)
SQL> select /*+ cpu_costing ordered_predicates */
2 *
3from t1
4where
5
v1 = 1
6andn1 = 998
7andn2 = 18
8;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT|| 1 | 9 | 5 (0)| 00:00:01 |
|*1 |TABLE ACCESS FULL| T1 | 1 | 9 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("V1")=1 AND "N1"=998 AND "N2"=18)
SQL> select /*+ cpu_costing ordered_predicates */
2 *
3from t1
4where
5
n1 = 998
6andv1 = 1
7andn2 = 18
8;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT|| 1 | 9 | 5 (0)| 00:00:01 |
|*1 |TABLE ACCESS FULL| T1 | 1 | 9 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"=998 AND TO_NUMBER("V1")=1 AND "N2"=18)
SQL> select /*+ cpu_costing ordered_predicates */
2 *
3from t1
4where
5 v1 = '1'
6andn2 = 18
7andn1 = 998
8;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT|| 1 | 9 | 5 (0)| 00:00:01 |
|*1 |TABLE ACCESS FULL| T1 | 1 | 9 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V1"='1' AND "N2"=18 AND "N1"=998)
SQL> set autotrace off;
SQL> column cpu heading "CPU cost"
format 999,999,999
SQL> column filterheading "Filter Predicate"
SQL> break on row skip 3
查看plan_table时发现是有区别的。
SQL> explain plan for
2select /*+ cpu_costing ordered_predicates */
3 *
4from t1
5where
6 v1 = 1
7andn2 = 18
8andn1 = 998
9;
Explained.
SQL> column filterheading "Filter Predicate" format a50
SQL> /
Filter Predicate
CPU cost
-------------------------------------------------- ------------
TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998
1,070,604
SQL> rollback;
Rollback complete.
SQL> explain plan for
2select /*+ cpu_costing ordered_predicates */
3 *
4from t1
5where
6 v1 = 1
7andn1 = 998
8andn2 = 18
9;
Explained.
SQL> select substr(filter_predicates,1,60) Filter, cpu_cost CPU from plan_table where id = 1;
Filter Predicate
CPU cost
-------------------------------------------------- ------------
TO_NUMBER("V1")=1 AND "N1"=998 AND "N2"=18
1,070,231
SQL> rollback;
SQL> select substr(filter_predicates,1,60) Filter, cpu_cost CPU from plan_table where id = 1;
no rows selected
SQL> explain plan for
2select /*+ cpu_costing ordered_predicates */
3 *
4from t1
5where
6 v1 = '1'
7andn2 = 18
8andn1 = 998
9;
Explained.
SQL> select substr(filter_predicates,1,60) Filter, cpu_cost CPU from plan_table where id = 1;
Filter Predicate
CPU cost
-------------------------------------------------- ------------
"V1"='1' AND "N2"=18 AND "N1"=998
770,604
|
