[20190821]關於CPU成本計算.txt

lfree 發表於 2019-08-22
CPU

[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.環境:
[email protected]> @ 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.測試:
[email protected]> 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;

[email protected]> 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;

--//把以上的輸出儲存一個檔案執行:
[email protected]> 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.

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塊的情況。
--//當然實際的情況不是這樣^_^。
[email protected]> 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;

[email protected]> 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塊的數量。
[email protected]> exec dbms_stats.SET_TABLE_STATS(user,'T',NUMBLKS=>1000000);
PL/SQL procedure successfully completed.

[email protected]> alter system flush shared_pool;
System altered.

[email protected]> explain plan set statement_id='block' for select a1 from t ;
Explained.

[email protected]> 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.

--//簡單驗證看看:
[email protected]> explain plan set statement_id='dept' for select * from dept;
Explained.

[email protected]> 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

[email protected]> 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的設定.
[email protected]> 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的干擾。

[email protected]> 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;

[email protected]> 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欄位.看看情況如何?我在這裡迷糊一段時間.....

[email protected]> explain plan set statement_id='bb' for select a3 from t where a2=100 ;
Explained.

[email protected]> 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的測試:
[email protected]01p> explain plan set statement_id='cc' for select a3 from t where a1=100 or a2= 100;
Explained.

[email protected]> 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';

[email protected]> 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,不知道為什麼這樣.這個我不再探究.
--//許多情況很複雜,最後做一個例子驗證看看.

[email protected]> 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.

[email protected]> 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

[email protected]> explain plan set statement_id='x2' for select a1,a2,a3 from t where to_char(a2)='100';
Explained.

[email protected]> 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/

[email protected]> explain plan for select /*+ index_ffs(t1) */ count(*) from t1 where val > 100;
Explained.

[email protected]> 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忽略了索引葉子塊中的比較測試。
--//在看看索引的情況:
[email protected]> explain plan set statement_id='x1' for  select  * from dept where deptno=10;
Explained.

[email protected]> 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如何確定的呢?

[email protected]> explain plan set statement_id='x2' for  select  1 from dept where deptno=10;
Explained.

[email protected]> 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

[email protected]> create index i_dept_dname on dept(dname);
Index created.

[email protected]>  explain plan set statement_id='x3' for select  * from dept where dname='ACCOUNTING';
Explained.

[email protected]> column OPTIONS format a30
[email protected]> 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;

[email protected]> 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

--//又有點專牛角尖了.這些細節不重要.不過看到計算結果與測試一致,還是蠻有成就感的.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2654655/,如需轉載,請註明出處,否則將追究法律責任。