[20190821]关于CPU成本计算.txt
--//有人问链接http://blog.itpub.net/267265/viewspace-2653964/中CPU成本如何计算的,实际上一般在优化中考虑这个细节很少,
--//因为CPU COST占整个COST的比例很少,至于如何计算说复杂很复杂,说简单也很简单.
--//如果你看onathan Lewis的<基于成本的Oracle优化法则>,里面提到P51:
Finding out exactly where the original count of 72,914,400 operations came from is much
harder. If you care to run through a set of extremely tedious experiments, you could probably
track it down—approximately—to details like these:
. Cost of acquiring a block = X
. Cost of locating a row in a block = Y
. Cost of acquiring the Nth (in our case the 2nd) column in a row = (N - 1) * Z
. Cost of comparing a numeric column with a numeric constant = A
--//通过值计算这些成本比较困难,实际上反推可以很容易的,我以前做过系列blog,可惜现在找不到了.
--//大概做一遍,实际的情况也许很复杂..^_^.
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2.测试:
SCOTT@test01p> create table t as select rownum a1 , rownum a2 ,rownum a3 from dual connect by level<=100 ;
Table created.
--//分析略.
column STATEMENT_ID format a10
column OPTIONS format a10
explain plan set statement_id='a1' for select a1 from t where rownum<=1;
explain plan set statement_id='a2' for select a2 from t where rownum<=1;
explain plan set statement_id='a3' for select a3 from t where rownum<=1;
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ------------ ------- ---- -------- ------- ----
a1 TABLE ACCESS FULL 2 7271 2 1
a2 TABLE ACCESS FULL 2 7291 2 1
a3 TABLE ACCESS FULL 2 7311 2 1
--//从这里就可以看出7271,7291,7311正好相差20 ,也就是 20 CPU Cycles for Column Skip. 也就是上面提到Z=20.
3.测试,分别取同一的字段a1,并且仅仅取1行,2行,3行的情况:
--//退出上面的测试,因为plan_table是临时表,退出后自动清空.
--//我建立的表很小,数据自然在1个块中.
--//select 'explain plan set statement_id='''||lpad(rownum,3,'0')||''''||' for select a1 from t where rownum<='||rownum||';' c80 from t;
select 'explain plan set statement_id='''||lpad(rownum,3,'0')||''''||' for select 1 from t where rownum<='||rownum||';' c80 from t;
--//把以上的输出保存一个文件执行:
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ------------ ------- ---- -------- ------- ----
001 TABLE ACCESS FULL 2 7271 2 1
002 TABLE ACCESS FULL 2 7421 2 1
003 TABLE ACCESS FULL 2 7571 2 1
004 TABLE ACCESS FULL 2 7721 2 1
005 TABLE ACCESS FULL 2 7871 2 1
006 TABLE ACCESS FULL 2 8021 2 1
007 TABLE ACCESS FULL 2 8321 2 1
008 TABLE ACCESS FULL 2 8321 2 1
009 TABLE ACCESS FULL 2 8471 2 1
010 TABLE ACCESS FULL 2 8621 2 1
011 TABLE ACCESS FULL 2 8771 2 1
012 TABLE ACCESS FULL 2 8921 2 1
...
092 TABLE ACCESS FULL 3 42286 3 1
093 TABLE ACCESS FULL 3 42436 3 1
094 TABLE ACCESS FULL 3 42586 3 1
095 TABLE ACCESS FULL 3 42736 3 1
096 TABLE ACCESS FULL 3 42886 3 1
097 TABLE ACCESS FULL 3 43036 3 1
098 TABLE ACCESS FULL 3 43186 3 1
099 TABLE ACCESS FULL 3 43486 3 1
100 TABLE ACCESS FULL 3 43486 3 1
100 rows selected.
select STATEMENT_ID,CPU_COST,lead(cpu_cost ) over ( order by STATEMENT_ID ) N1,lead(cpu_cost ) over ( order by STATEMENT_ID )- cpu_cost N2 from (
select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL');
STATEMENT_ CPU_COST N1 N2
---------- -------- ----- ----
001 7271 7421 150
002 7421 7571 150
003 7571 7721 150
004 7721 7871 150
005 7871 8021 150
006 8021 8321 300
007 8321 8321 0
008 8321 8471 150
009 8471 8621 150
010 8621 8771 150
011 8771 8921 150
012 8921 9071 150
013 9071 9371 300
014 9371 9371 0
015 9371 9521 150
016 9521 9671 150
017 9671 9821 150
018 9821 9971 150
019 9971 10121 150
020 10121 10271 150
021 10271 10421 150
022 10421 10571 150
023 10571 10721 150
024 10721 10871 150
025 10871 18143 7272
026 18143 18293 150
027 18293 18593 300
028 18593 18593 0
029 18593 18743 150
030 18743 18893 150
031 18893 19043 150
032 19043 19193 150
033 19193 19343 150
034 19343 19493 150
035 19493 19643 150
036 19643 19793 150
037 19793 19943 150
038 19943 20093 150
039 20093 20243 150
040 20243 20393 150
041 20393 20543 150
042 20543 20693 150
043 20693 20843 150
044 20843 20993 150
045 20993 21143 150
046 21143 21293 150
047 21293 21443 150
048 21443 21593 150
049 21593 21743 150
050 21743 29014 7271
051 29014 29164 150
052 29164 29314 150
053 29314 29464 150
054 29464 29914 450
055 29914 29914 0
056 29914 29914 0
057 29914 30064 150
058 30064 30214 150
059 30214 30364 150
060 30364 30514 150
061 30514 30664 150
062 30664 30814 150
063 30814 30964 150
064 30964 31114 150
065 31114 31264 150
066 31264 31414 150
067 31414 31564 150
068 31564 31714 150
069 31714 31864 150
070 31864 32014 150
071 32014 32164 150
072 32164 32314 150
073 32314 32464 150
074 32464 32614 150
075 32614 39886 7272
076 39886 40036 150
077 40036 40186 150
078 40186 40336 150
079 40336 40486 150
080 40486 40636 150
081 40636 40786 150
082 40786 40936 150
083 40936 41086 150
084 41086 41236 150
085 41236 41386 150
086 41386 41536 150
087 41536 41686 150
088 41686 41836 150
089 41836 41986 150
090 41986 42136 150
091 42136 42286 150
092 42286 42436 150
093 42436 42586 150
094 42586 42736 150
095 42736 42886 150
096 42886 43036 150
097 43036 43186 150
098 43186 43486 300
099 43486 43486 0
100 43486
100 rows selected.
--//我一直不理解这里为什么出现跳跃.不过还是基本可以定位Cost of locating a row in a block = Y = 150.
--//也许是后面where条件的影响.
--//后记：
--//在STATEMENT_ID=025,050,075,N2分别是7272,7271,7272.说明在statement_id=026,051,076多访问1块。
--//可以这么理解表T占4blocks,共100行，平均下来每块25行。这样当查询等于rownum<=26,51,76时出现多访问1块的情况。
--//当然实际的情况不是这样^_^。
SCOTT@test01p> select blocks,num_rows from user_tables where table_name='T';
BLOCKS NUM_ROWS
---------- ----------
4 100
--//但是N2=300,450的跳跃情况我实在无法解析，不知道那位能解析看看。
4.继续分析:
--//多个字段在select的情况呢？
column STATEMENT_ID format a10
column OPTIONS format a10
explain plan set statement_id='a0' for select 1 from t where rownum<=1;
explain plan set statement_id='ax' for select rowid from t where rownum<=1;
explain plan set statement_id='a1' for select a1 from t where rownum<=1;
explain plan set statement_id='a2' for select a2 from t where rownum<=1;
explain plan set statement_id='a3' for select a3 from t where rownum<=1;
explain plan set statement_id='a12' for select a1,a2 from t where rownum<=1;
explain plan set statement_id='a13' for select a1,a3 from t where rownum<=1;
explain plan set statement_id='a23' for select a2,a3 from t where rownum<=1;
explain plan set statement_id='a123' for select a1,a2,a3 from t where rownum<=1;
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ------------ ------- ---- -------- ------- ----
a0 TABLE ACCESS FULL 2 7271 2 1
ax TABLE ACCESS FULL 2 7271 2 1
a1 TABLE ACCESS FULL 2 7271 2 1
a2 TABLE ACCESS FULL 2 7291 2 1
a3 TABLE ACCESS FULL 2 7311 2 1
a12 TABLE ACCESS FULL 2 7291 2 1
a13 TABLE ACCESS FULL 2 7311 2 1
a23 TABLE ACCESS FULL 2 7311 2 1
a123 TABLE ACCESS FULL 2 7311 2 1
9 rows selected.
--//看statement_id=a0,ax,a1可以发现CPU_COST都是一样,也就是取表中第一个字段不计cpu cost.
--//看statement_id= a2,a12 ,CPU_COST=7291也说明取表中第一个字段不计cpu cost.
--//也就是前面的 Cost of acquiring the Nth (in our case the 2nd) column in a row = (N - 1) * Z
--//另外你可以发现看statement_id= a3,a13,a23,a123 中 CPU_COST=7311,也就是select中列的成本以最大列的成本计算.
--//剩下的就是上面X(Cost of acquiring a block）如何计算。实际上只要反推就可以知道X等于多少,X= 7271-150 = 7121.
--//实际上后面还有小数点的,为了后面的测试需要准确的知道小数点后的数值是多少，继续测试。
5.hack统计信息看看.
--//为了准确确定X(Cost of acquiring a block),hack统计信息,增加表T块的数量。
SCOTT@test01p> exec dbms_stats.SET_TABLE_STATS(user,'T',NUMBLKS=>1000000);
PL/SQL procedure successfully completed.
SCOTT@test01p> alter system flush shared_pool;
System altered.
SCOTT@test01p> explain plan set statement_id='block' for select a1 from t ;
Explained.
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ------------ ------- ------ ---------- ------- ----
block TABLE ACCESS FULL 271400 7121455000 270835 11
--//注：没有修改记录数量还是100.这样计算如下:
--//(7121455000-100*150)/1000000 = 7121.44,也就是前面X=7121.44，这样就获得X的精确值。
--//当做到这里时，我一直想oracle内部如何定下X=7121.44,后面居然还有小数点...
--//这样就知道select部分的计算公式;
X*blocks +( Y+(N - 1) * Z )*numrows
7121.44 * blocks + 150*rows + 20*effect_rows* (Highest_column_id - Lowest_column_id)
--//注：Lowest_column_id许多情况下等于1，我之所以改动公式，继续看后面的的测试就知道了。实际上Lowest_column_id=1是没有
--//where查询条件的特例.
--//而且许多情况下字段成本要单独计算.我这里定义为effect_rows,如果没有where条件实际上等于前面rows.
--//简单验证看看:
SCOTT@test01p> explain plan set statement_id='dept' for select * from dept;
Explained.
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where STATEMENT_ID='dept';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ---------------- ------- ---- -------- ------- ----
dept SELECT STATEMENT 3 36367 3 1
dept TABLE ACCESS FULL 3 36367 3 1
SCOTT@test01p> select blocks,num_rows from user_tables where table_name='DEPT';
BLOCKS NUM_ROWS
------ --------
5 4
7121.44 * blocks + 150*rows + 20*rows* (Highest_column_id - Lowest_column_id)
= 7121.44 * 5 + 150*4 + 20*4*(3-1) = 36367.20
--//基本吻合.
6.最后看看谓词部分A:
--//如果有where查询条件呢?
--//. Cost of comparing a numeric column with a numeric constant = A
--//这部分我认为相对难一些.因为查询条件可能不止一个.可能and也可能是or .而且比较也有先后次序.
--//复杂的是谓词可能与select存在一些关联.
--//我仅仅简单分析:
--//重新分析表T,取消前面hack的设置.
SCOTT@test01p> select blocks,num_rows from user_tables where table_name='T';
BLOCKS NUM_ROWS
------ --------
4 100
column STATEMENT_ID format a10
column OPTIONS format a10
explain plan set statement_id='a1' for select 1 from t where a1=100;
explain plan set statement_id='a2' for select 1 from t where a2=100;
explain plan set statement_id='a3' for select 1 from t where a3=100;
explain plan set statement_id='ax' for select 1 from t where a1=:N1;
explain plan set statement_id='ax' for select 1 from t where a2=:N1;
explain plan set statement_id='ax' for select 1 from t where a3=:N1;
--//注：select部分没有查询字段,这样可以先排除select中涉及字段查询的cpu cost的干扰。
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ------------ ------- ---- -------- ------- ----
a1 TABLE ACCESS FULL 3 48486 3 1
a2 TABLE ACCESS FULL 3 50486 3 1
a3 TABLE ACCESS FULL 3 52486 3 1
ax TABLE ACCESS FULL 3 58486 3 1
ax TABLE ACCESS FULL 3 60486 3 1
ax TABLE ACCESS FULL 3 62486 3 1
6 rows selected.
--//注意使用绑定变量与不使用绑定变量CPU_COST差距很大.
--//select部分:
7121.44 * blocks + 150*rows + 20*rows* ( Highest_column_id - Lowest_column_id)
= 7121.44 * 4 + 150*100 = 43485.76 = 43486
--//可以发现where中cost对比如下:
--//使用非绑定变量的情况:
a1=100 48486-43486 = 5000
a2=100 50486-43486 = 7000
a3=100 52486-43486 = 9000
--//使用绑定变量的情况:
a1=:N1 58486-43486 = 15000
a2=:N1 60486-43486 = 17000
a3=:N1 62486-43486 = 19000
--//使用非绑定变量的情况:
--//对应a1=100条件,表T共用100条记录,对比100次,这样每次5000/100 = 50.
--//以此类推a2=100,每次7000/100 = 70.a3=100,每次9000/100 = 90.
--//结合前面的z=20(20 CPU Cycles for Column Skip).where的cost实际上的计算公式如下:
rows*50+rows*(column_id-1)*20).
--//实际上可以理解每次比较的cpu cost是50.
--//补充实际上字段成本在select部分计算(看后面的例子就明白了).我这里仅仅推导出每次比较的cpu cost是50.
--//使用绑定变量的情况:
--//细节不在说了,可以推导出每次比较的cpu cost是150.为什么使用绑定变量比非绑定变量这么多?不理解.
--//先放弃分析绑定变量的情况...
--//看看多个条件的情况呢.
column STATEMENT_ID format a10
column OPTIONS format a10
explain plan set statement_id='a12' for select 1 from t where a1=100 and a2=100;
explain plan set statement_id='a21' for select 1 from t where a2=100 and a1=100;
explain plan set statement_id='a23' for select 1 from t where a2=100 and a3=100;
explain plan set statement_id='a13' for select 1 from t where a1=100 and a3=100;
explain plan set statement_id='a123' for select 1 from t where a1=100 and a2=100 and a3=100;
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ------------ ------- ---- -------- ------- ----
a12 TABLE ACCESS FULL 3 50536 3 1
a21 TABLE ACCESS FULL 3 50536 3 1
a23 TABLE ACCESS FULL 3 52536 3 1
a13 TABLE ACCESS FULL 3 52536 3 1
a123 TABLE ACCESS FULL 3 52536 3 1
--//STATEMENT_ID=a12的情况, 50536-43486 = 7050
--//我的理解:where a1=100 and a2=100. 因为要取a2字段
--//rows*(Highest_column_id - 1)*20 = 100*20 = 2000.
--//(7050-2000)/50 = 101, 说明比较101次.
--//可以这样理解 a1=100比较100次,仅仅1条记录过滤出来,这样a2=100仅仅需要比较1次.总共101次.
--//补充我认为正常情况下应该比较选择性好的字段先比较,这样减少后续比较的次数.
--//STATEMENT_ID=a123的情况, 52536-43486 = 9050
--//因为要取a3字段
--//rows*(Highest_column_id - 1)*20 = 100*2*20 = 4000.
--//(9050-4000)/50 = 101,说明比较101次.我的理解应该是101.01次(102?)，小数点后面的忽略了。
--//where部分cost, 比较成本.每次比较cost=50,实际上情况可能更复杂,
--//当我查询select 1 from t... ,select部分没有查询字段.
--//如果查询 explain plan set statement_id='bb' for select a3 from t where a2=100 ;呢?
--//前面的select部分已经查询a3字段.看看情况如何?我在这里迷糊一段时间.....
SCOTT@test01p> explain plan set statement_id='bb' for select a3 from t where a2=100 ;
Explained.
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where STATEMENT_ID='bb';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ---------------- ------- ---- -------- ------- ----
bb SELECT STATEMENT 3 50506 3 1
bb TABLE ACCESS FULL 3 50506 3 1
--//我先说说我的理解然后计算:
如果查询时第1条记录是a2=1,这样根本不用取a3字段直接跳过这条记录,这样select部分仅仅取a2后然后比较
如果a2=100符合查询条件再取字段a3.有了这样理解cpu cost计算就简单了.
--//select部分,开始仅仅需要取到a1字段(即使select部分包含a3):
7121.44 * blocks + 150*rows + 20*rows* (Highest_column_id - Lowest_column_id)
7121.44 * 4 + 150*100 + 20*rows* ( 2 - 1) = 43485.76 + 2000 = 45486
--//where部分:
比较100次 100*50=5000
仅仅1条符合a2=100.取a3字段成本 :
20*rows* (Highest_column_id - Lowest_column_id)
20*1*(3-2) = 20
--//cpu cost
45486 + 5000+ 20 = 50506
--//完全符合.
--//补充说明:字段成本分2次计算
--//第一次是 20*rows* ( Highest_column - Lowest_column) ,这里的Highest_column=2,Lowest_column=1.rows=100.
--//第二次是 20*rows* ( Highest_column - Lowest_column) ,这里的Highest_column=3,Lowest_column=2.rows=1.
--//总之字段成本要看select以及查询条件结合起来判断,出现的情况可能比较复杂.
--//补充or的测试：
SCOTT@test01p> explain plan set statement_id='cc' for select a3 from t where a1=100 or a2= 100;
Explained.
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where STATEMENT_ID='cc';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ---------------- ------- ---- -------- ------- ----
cc SELECT STATEMENT 3 55476 3 1
cc TABLE ACCESS FULL 3 55476 3 1
--//select部分,开始仅仅需要取到a2字段(即使select部分包含a3):
7121.44 * blocks + 150*rows + 20*rows* (Highest_column_id - Lowest_column_id)
7121.44 * 4 + 150*100 + 20*100* ( 2 - 1) = 45485.76 = = 45486
--//where部分:
比较100+99次 ,199*50 = 9950
--//注我开始认为200次.这里应该考虑重合部分.你可以这么理解假设a1=100已经符合条件,a2=100就不需要比较了.
仅仅1条符合a1=100.1条符合a2=100.共计2条(这里不知道是否考虑重合部分,估计太小也忽略了)。取a3字段成本 :
20*rows* (Highest_column_id - Lowest_column_id)
20*1*(3-2)*2=40
--//cpu cost
45486 + 9950 + 40 = 55476
--//完全符合.
7.最后看看谓词条件存在函数的情况.
column STATEMENT_ID format a10
column OPTIONS format a10
explain plan set statement_id='a1' for select 1 from t where to_char(a1)='100';
explain plan set statement_id='a2' for select 1 from t where to_char(a2)='100';
explain plan set statement_id='a3' for select 1 from t where to_char(a3)='100';
explain plan set statement_id='a3' for select 1 from t where to_char(a3)=:C1;
select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL';
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ------------ ------- ---- -------- ------- ----
a1 TABLE ACCESS FULL 3 58486 3 1
a2 TABLE ACCESS FULL 3 60486 3 1
a3 TABLE ACCESS FULL 3 62486 3 1
a3 TABLE ACCESS FULL 3 62486 3 1
--//select部分:
7121.44 * blocks + 150*rows + 20*rows* ( Highest_column_id - 1)
= 7121.44 * 4 + 150*100 = 43485.76 = 43486
--//可以发现where中cost对比如下:
to_char(a1)='100' 58486-43486 = 15000
to_char(a2)='100' 60486-43486 = 17000
to_char(a3)='100' 62486-43486 = 19000
--//对应a1=100条件,表T共用100条记录,对比100次,这样每次 15000/100 = 150.
--//以此类推a2=100,每次17000/100 = 170.a3=100,每次19000/100 = 190.
--//根据前面的测试,排除字段的成本(20),比较每次50,可以推断函数部分占100,感觉这部分也太少了!!
--//字段= 常量的比较每次50,而引入函数仅仅占100。我个人感觉oracle设置太小了,应该设置500.
8.总结:
--//cup cost的计算是 每块7121.44,每行150,字段看位置(N-1)*20(注仅仅取最考后的字段计算),比较50以及次数,函数100.
--//使用绑定变量比较成本150,不知道为什么这样.这个我不再探究.
--//许多情况很复杂,最后做一个例子验证看看.
SCOTT@test01p> explain plan set statement_id='x1' for select to_char(a1),to_char(a2),to_char(a3) from t where to_char(a2)='100';
Explained.
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where STATEMENT_ID='x1';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ---------------- ------- ---- -------- ------- ----
x1 SELECT STATEMENT 3 60506 3 1
x1 TABLE ACCESS FULL 3 60506 3 1
--//select部分,查询到a2
7121.44 * blocks + 150*rows + 20*rows* ( Highest_column_id - 1)
=7121.44 * 4 + 150*100 +20*100*1 = 45485.76 = 45486
--//注意3个函数to_char.注仅仅1条返回.好像这部分oracle没有考虑.
100*rows*3
=1*100*3 = 300
--//where部分:
--//比较100次, 函数占100,比较占50
100*(100+50) = 15000
--//字段成本,仅仅1条返回,也就是取a3字段仅仅1次.注意前面select部分已经查询到a2字段，这样Lowest_column_id=2
20*rows* ( Highest_column_id - Lowest_column_id)
= 20*1*(3-2) = 20
--//累计:
45486 + 15000+ 20 = 60506
SCOTT@test01p> explain plan set statement_id='x2' for select a1,a2,a3 from t where to_char(a2)='100';
Explained.
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where STATEMENT_ID='x2';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ---------------- ------- ---- -------- ------- ----
x2 SELECT STATEMENT 3 60506 3 1
x2 TABLE ACCESS FULL 3 60506 3 1
--//可以看出select部分包含函数,cpu cost不计算的.最后回到前面别人问的问题:
--//http://blog.itpub.net/267265/viewspace-2653964/
SCOTT@test01p> explain plan for select /*+ index_ffs(t1) */ count(*) from t1 where val > 100;
Explained.
SCOTT@test01p> select cpu_cost from plan_table;
CPU_COST
--------
72914400
72914400
--//select部分,查询到val
7121.44 * blocks + 150*rows + 20*rows* ( Highest_column_id - 1)
7121.44* 10000 + 150*10000 + 20*10000* ( 2- 1) = 72914400
--//已经吻合.
--//为什么没有比较的cpu cost消耗,我的理解这是由索引的特性决定的块间有序,块内无序.通过行目录排序键值,这样扫描叶子时比较
--//的次数是有限的。
--//显然oracle忽略了索引叶子块中的比较测试。
--//在看看索引的情况:
SCOTT@test01p> explain plan set statement_id='x1' for select * from dept where deptno=10;
Explained.
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where STATEMENT_ID='x1';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ---------------- -------------- ---- -------- ------- ----
x1 SELECT STATEMENT 1 8361 1 1
x1 TABLE ACCESS BY INDEX ROWID 1 8361 1 1
x1 INDEX UNIQUE SCAN 0 1050 0 1
--//select部分:
7121.44*1+1*150+20*1*(3-1) = 7311.44
8361-1050 = 7311
--//select部分是吻合的.
--//where部分(索引部分)呢?也就是UNIQUE SCAN 的cpu cost 1050如何确定的呢?
SCOTT@test01p> explain plan set statement_id='x2' for select 1 from dept where deptno=10;
Explained.
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where STATEMENT_ID='x2';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ---------------- ----------- ---- -------- ------- ----
x2 SELECT STATEMENT 0 1050 0 1
x2 INDEX UNIQUE SCAN 0 1050 0 1
SCOTT@test01p> create index i_dept_dname on dept(dname);
Index created.
SCOTT@test01p> explain plan set statement_id='x3' for select * from dept where dname='ACCOUNTING';
Explained.
SCOTT@test01p> column OPTIONS format a30
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where STATEMENT_ID='x3';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ---------------- ---------------------- ---- -------- ------- ----
x3 SELECT STATEMENT 2 14633 2 1
x3 TABLE ACCESS BY INDEX ROWID BATCHED 2 14633 2 1
x3 INDEX RANGE SCAN 1 7321 1 1
--//RANGE SCAN 访问块按 7121.44计算.
--//7321-7121 = 200,每行150,比较1次 50 .猜测不知道是否正确?
--//这样反推 UNIQUE SCAN block的cpu cost = 1050 - 200 = 850.
--//绑定变量的情况为什么比较150.不理解? 感觉有点高.是否绑定变量有一个替换的过程.
column STATEMENT_ID format a10
column OPTIONS format a10
explain plan set statement_id='a12' for select 1 from t where a1=100 and a2=100;
explain plan set statement_id='x12' for select 1 from t where a1=100 and a2=:N2;
explain plan set statement_id='x21' for select 1 from t where a1=:N1 and a2=100;
explain plan set statement_id='y12' for select 1 from t where a1=:N1 and a2=:N2;
explain plan set statement_id='z123' for select 1 from t where a1=:N1 and a2=:N2 and a3=:N3;
SCOTT@test01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ------------ ------- ---- -------- ------- ----
a12 TABLE ACCESS FULL 3 50536 3 1
x12 TABLE ACCESS FULL 3 50636 3 1
x21 TABLE ACCESS FULL 3 50636 3 1
y12 TABLE ACCESS FULL 3 60636 3 1
z123 TABLE ACCESS FULL 3 62637 3 1
--//x12, select部分:
7121.44 * blocks + 150*rows + 20*rows* ( Highest_column_id - Lowest_column_id)
7121.44*4 + 150*100 = 43485.76 = 43486
--//where:
--//比较100+1
100*50 = 5000
1*150 = 150
--//字段成本:
20*rows* ( Highest_column_id - Lowest_column_id)
20*100*(2-1) = 2000
--//43486+5000+150+2000 = 50636 ,OK!!
--//y12 ,select部分:
7121.44 * blocks + 150*rows + 20*rows* ( Highest_column_id - Lowest_column_id)
7121.44*4 + 150*100 = 43485.76 = 43486
--//where:
--//比较100+1,2个都是绑定变量
101*150 = 15150
--//字段成本:
20*rows* ( Highest_column_id - Lowest_column_id)
20*100*(2-1) = 2000
--//43486+15150+2000 = 60636,OK!!
--//z123, select部分:
7121.44 * blocks + 150*rows + 20*rows* ( Highest_column_id - Lowest_column_id)
7121.44*4 + 150*100 = 43485.76 = 43486
--//where:
--//比较100+1+0.01
101*150 = 15150
101.01*150 = 15151.50
--//字段成本:
20*rows* ( Highest_column_id - Lowest_column_id)
20*100*(3-1) = 4000
--//43486+15150+4000 = 62636. 差1.我估计如果101.01*150 = 15151.50计算基本符合..
--//43485.76+15151.50+4000 = 62637.26
--//又有点专牛角尖了.这些细节不重要.不过看到计算结果与测试一致,还是蛮有成就感的.
select STATEMENT_ID,CPU_COST,lead(cpu_cost ) over ( order by STATEMENT_ID ) N1,lead(cpu_cost ) over ( order by STATEMENT_ID )- cpu_cost N2 from (
select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL');