理解谓词对cpu cost的影响
这个实验脚本来自cost-based oracle,动手实验一次加深印象
SQL> create table t1(
2 v1,
3 n1,
4 n2
5 )
6 as
7 select
8 to_char(mod(rownum,20)),
9 rownum,
10 mod(rownum,20)
11 from
12 all_objects
13 where
14 rownum <= 3000
15 ;
表已创建。
SQL> select count(distinct v1) from t1;
COUNT(DISTINCTV1)
-----------------
20~~~~~~~~~~~~~~~~~~~~V1 有20个不同值 char类 ,每个数对应150行
SQL> select count(distinct n1) from t1;
COUNT(DISTINCTN1)
-----------------
3000~~~~~~~~~~~~~~~~~~~~~N1 有3000个不同值 NUMBER类 表示 是唯一的
SQL> select count(distinct n2) from t1;
COUNT(DISTINCTN2)
-----------------
20~~~~~~~~~~~~~~~~~~~~N2 有20个不同值 NUMBER类 ,每个数对应150行
总共3000 rows
下面4个查询中 前3个使用+ ordered_predicates HINTS 强制按所写的predication顺序 从左到右
最后一个 让CBO优化器自己考虑 注意
_pred_move_around = true (predication 移动 oracle自动排序predication)
_optimizer_cost_model = choose(基本使用CPU_COSTING 而不使用8I 的传统计算(只算IO))
SQL> exec dbms_stats.gather_table_stats('SYS','T1');
PL/SQL 过程已成功完成。
SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';
会话已更改。
SQL> ed
已写入 file afiedt.buf
1 select
2 /*+ ordered_predicates */
3 v1, n2, n1
4 from
5 t1
6 where
7 v1 = 1
8 and n2 = 18
9* and n1 = 998
10 /
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998)
SQL> ed
已写入 file afiedt.buf
1 select
2 /*+ordered_predicates */
3 v1, n2, n1
4 from
5 t1
6 where
7 n1 = 998
8 and n2 = 18
9* and v1 = 1
SQL> /
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)
SQL> ed
已写入 file afiedt.buf
1 select
2 /*+ ordered_predicates */
3 v1, n2, n1
4 from
5 t1
6 where
7 v1 = '1'
8 and n2 = 18
9* and n1 = 998
SQL> /
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V1"='1' AND "N2"=18 AND "N1"=998)
SQL> ed
已写入 file afiedt.buf
1 select
2 v1, n2, n1
3 from
4 t1
5 where
6 v1 = 1
7 and n2 = 18
8* and n1 = 998
SQL> /
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)
看TRACE
使用
predication 顺序- filter(TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998)
select
/*+ ordered_predicates */
v1, n2, n1
from
t1
where
v1 = 1
and n2 = 18
and n1 = 998
Access Path: TableScan
Cost: 3.25 Resp: 3.25 Degree: 0
Cost_io: 3.00 Cost_cpu: 1070604****************
Resp_io: 3.00 Resp_cpu: 1070604
Best:: AccessPath: TableScan
Cost: 3.25 Degree: 1 Resp: 3.25 Card: 0.00 Bytes: 0
分析:总共3000行中oracle首先对v1 = 1 进行了3000次转换(to_number) 又在表里3000行中 让 V1与一个数值进行比较产生了 150行(3000/20) CPU进行了3000次比较操作
又在这150行中 n2与一个数值比较 产生8行(150/20) CPU进行150次比较操作, 最后在这8行中 N1与一个数值比较(N1 唯一) CPU进行8次比较操作,CPU一共进行了 3158次数值比较
+3000次转换操作
****************
使用
predication 顺序- filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)
select
/*+ordered_predicates */
v1, n2, n1
from
t1
where
n1 = 998
and n2 = 18
and v1 = 1
Access Path: TableScan
Cost: 3.18 Resp: 3.18 Degree: 0
Cost_io: 3.00 Cost_cpu: 762786************
Resp_io: 3.00 Resp_cpu: 762786
Best:: AccessPath: TableScan
Cost: 3.18 Degree: 1 Resp: 3.18 Card: 0.00 Bytes: 0
分析:总共3000行中oracle 让N1与一个数值 进行了3000次比较 产生 1行(3000/3000) CPU进行3000次比较操作 ,对这一行 N2与一个数比较一次 产生1行 CPU进行了1次比较操作,
对这一行 V1与一个数比较一次CPU进行1次比较操作, 且V1转换一次
所以为3002次比较操作+1次转换
这个是最节约CPU成本的
******************************
使用
predication 顺序- filter("V1"='1' AND "N2"=18 AND "N1"=998)
select
/*+ ordered_predicates */
v1, n2, n1
from
t1
where
v1 = '1'
and n2 = 18
and n1 = 998
Access Path: TableScan
Cost: 3.18 Resp: 3.18 Degree: 0
Cost_io: 3.00 Cost_cpu: 770604**********************
Resp_io: 3.00 Resp_cpu: 770604
Best:: AccessPath: TableScan
Cost: 3.18 Degree: 1 Resp: 3.18 Card: 0.00 Bytes: 0
分析:总共3000行中 oracle 让V1与一个值比较 产生150行 CPU进行了3000次比较操作,150行中 让N2与一个值比较cpu进行比较操作150次产生8行,又在这8行中 让N1与一个数值比
较 CPU比较8次产生1行
注意没有进行转换3000次对V1
所以ORACLE CPU进行了3158次比较操作
SQL> select 1070604-770604 from dual;
1070604-770604
--------------
300000
3000次转换 CPU COST 多了 300000 这样可以得出 一次to_number隐式转换 需要100次 CPU操作(300000/3000=100)
**************************
oracle optimizer自动选 顺序- filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)
select
v1, n2, n1
from
t1
where
v1 = 1
and n2 = 18
and n1 = 998
Access Path: TableScan
Cost: 3.18 Resp: 3.18 Degree: 0
Cost_io: 3.00 Cost_cpu: 762786*********************
Resp_io: 3.00 Resp_cpu: 762786
Best:: AccessPath: TableScan
Cost: 3.18 Degree: 1 Resp: 3.18 Card: 0.00 Bytes: 0
分析:最后这个ORACLE 采用了第2个SELECT 所使用的PREDICATION 顺序 没有按照 指定的顺序 oracle进行COST 评估后采用了这个顺序
_pred_move_around = true 由于这个参数 让ORACLE自动对PREDICATION 选择合适的顺序
SQL> alter system set "_pred_move_around"=false;************实验改下
系统已更改。
SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';
会话已更改。
SQL> select
2 v1, n2, n1
3 from
4 t1
5 where
6 v1 = 1
7 and n2 = 18
8 and n1 = 998
9 /
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)~~~~~~~~~~~~~~~~雪特还是自己变化了
_pred_move_around FALSE FALSE FALSE FALSE
enables predicate move-around
10GR2中好象没起作用
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-613681/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-613681/
2475
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
