【建立3000行测试表】
DROP TABLE t1 PURGE;
CREATE TABLE t1(v1,n1,n2)
AS
SELECT to_char(MOD(rownum, 20)), rownum, MOD(rownum, 20)
FROM dual
CONNECT BY LEVEL <= 3000;
BEGIN
dbms_stats.gather_table_stats(ownname => USER,
tabname => 't1',
estimate_percent => 100,
method_opt => 'for all columns size 1');
END;
/
【成本估算】
这里使用ordered_predicates强制进行谓词排序：
DELETE FROM plan_table;
EXPLAIN PLAN SET STATEMENT_ID='v1' FOR
SELECT /*+ ordered_predicates*/
*
FROM t1
WHERE v1 = 1 AND
n2 = 18 AND
n1 = 998;
EXPLAIN PLAN SET STATEMENT_ID='n1' FOR
SELECT /*+ ordered_predicates*/
*
FROM t1
WHERE n1 = 998 AND
n2 = 18 AND
v1 = 1;
EXPLAIN PLAN SET STATEMENT_ID='v1c' FOR
SELECT /*+ ordered_predicates*/
*
FROM t1
WHERE v1 = '1' AND
n2 = 18 AND
n1 = 998;
####################################################################
PS:记得看过donald的一本性能调优的书，貌似8i的谓词排序hint是加在
WHERE后的，但是9i之后也直接放到select后中，据说ordered_predicates
还受_pred_move_around隐式参数的影响：
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND
a.ksppinm = '_pred_move_around';
--_pred_move_around TRUE enables predicate move-around
不过我在10.2.0.1中会话级别将_pred_move_around设置为false后，ORDER
ed_predicates依旧起作用，感兴趣的可以将兼容版本放到9.2.0测试下，这里
不再追究。
####################################################################
【查看成本估算值】
SELECT statement_id, io_cost, cpu_cost, cost FROM plan_table WHERE id = 0;
/*
v1 3 1091968 3
n1 3 784150 3
v1c 3 791968 3
*/
我们主要关注cpu_cost部分。
【查看执行计划】
SELECT * FROM TABLE(dbms_xplan.display(NULL, 'n1', 'typical', NULL));
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (25)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 4 (25)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)
SELECT * FROM TABLE(dbms_xplan.display(NULL, 'v1', 'typical', NULL));
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (25)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 4 (25)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998)
SELECT * FROM TABLE(dbms_xplan.display(NULL, 'v1c', 'typical', NULL));
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (25)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 4 (25)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V1"='1' AND "N2"=18 AND "N1"=998)
【cpu消耗操作项目解释】
这里与上面执行计划顺序对应：
#####################################
n1
产生cpu开销的操作：
3000次数值比较，产生1行
这一行进行一次n2数值比较
这一行强制转换一次并比较一次
综合：
1次转换
3002次数值比较
cpu开销：784150
#####################################
v1
产生cpu开销的操作：
3000次v1转换，并应用过滤条件，产生150行
150次n2比较，产生8行(对于为什么cbo会认为返回8行，后面将解释)
8次n1比较
综合：
3000次转换
3158次数值比较
cpu开销：1091968
#####################################
v1c
产生cpu开销的操作：
3000次数值比较，产生150行
这150行进行150次n2数值比较,产生8行
这8行与n1进行数值比较
综合：
3158次数值比较
cpu开销：791968
【基准测试数值观察结论】
通过观察v1与v1c，我们发现v1相比v1c只是多出了3000次to_number操作，所以
我们很容易的得出一次to_number操作消耗的cpu调用是100次：
SELECT (1091968 - 791968) / 3000 to_number_cpucosting FROM dual;
【总结】
cbo估算cpu成本是不同原子级别操作的cpu call总合，而这些“原子级”
的对象实在太多，它可能是一个函数，一个过滤操作等，而我们知道就
算是访问同一行的不同列，cpu开销也不一样；
而且不同版本相同的单个原子操作可能成本开销也不一样，这和oracle
内核代码息息相关，比如上面的to_number，你可以假想它的内部算法是永
恒不变的，但是这种内稳态的东西实在太少。
总之一句话，对于cpu成本估算细节方面的东西我们没必要纠结，而上面
的例子只是帮助你在cbo估算cpu成本方面时有一个直观的理解。
