[20190821]關於CPU成本計算.txt--//有人問鏈接http://blog.itpub.net/267265/viewspace-2653964/中CPU成本如何計算的,實際上一般在優化中考慮這個細節很少,--//因為CPU COST占整個COST的比例很少,至於如何計算說複雜很複雜,說 ...
[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');
