[20190823]关于CPU成本计算2.txt

[20190823]关于CPU成本计算2.txt --//前几天探究CPU cost时遇到的问题,获取行成本时我的测试查询结果出现跳跃，不知道为什么，感觉有点奇怪，分析看看。 --//ITPUB原始链接已经不存在，我的日记本还有记录，现在想想当时的记录思路很乱，不过这些都是猜测的过程，以前思路混乱也是正常的。 --//顺便做一些必要补充。 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. --//分析略. select 'explain plan set statement_id='''||lpad(rownum,3,'0')||''''||' for select 1 from t where rownum<='||rownum||';' c80 from t; --//把以上的输出保存一个文件执行,然后执行如下: 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. --//大于7271的部分,我前面已经解析. --//在STATEMENT_ID=025,050,075,N2分别是7272,7271,7272.说明在statement_id=026,051,076多访问1块。 --//可以这么理解表T占4blocks,共100行，平均下来每块25行。这样当查询等于rownum<=26,51,76时出现多访问1块的情况。 --//剩下就是为什么查询条件rownum<=55,rownum<=56,rownum<=57时CPU_COST一样的,不好理解。N2出现跳跃的情况呢? 3.继续探究: SCOTT@test01p> set feedback only SCOTT@test01p> select 1 from t where rownum<=55 ;          1 ---------- 55 rows selected. SCOTT@test01p> set feedback 6 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  g2r21fyyf3y90, child number 1 ------------------------------------- select 1 from t where rownum<=55 Plan hash value: 508354683 ------------------------------------------------------------------------------------------------------------- | Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |      1 |        |     3 (100)|          |     55 |00:00:00.01 |       6 | |*  1 |  COUNT STOPKEY     |      |      1 |        |            |          |     55 |00:00:00.01 |       6 | |   2 |   TABLE ACCESS FULL| T    |      1 |     57 |     3   (0)| 00:00:01 |     55 |00:00:00.01 |       6 | ------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / T@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(ROWNUM<=55) --//注意看E-Rows = 57.噢!这样明白为什么出现跳跃.是oracle估计选择率的算法非常特别造成这样的情况. --//感觉oracle这样条件算法有点奇怪,什么可能查询条件rownum<=55,E-Rows = 57呢? --//看看select 1 from t where rownum<=7的执行计划也可以验证: SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  a8yj08mysamg1, child number 0 ------------------------------------- select 1 from t where rownum<=7 Plan hash value: 508354683 ------------------------------------------------------------------------------------------------------------- | Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |      1 |        |     2 (100)|          |      7 |00:00:00.01 |       6 | |*  1 |  COUNT STOPKEY     |      |      1 |        |            |          |      7 |00:00:00.01 |       6 | |   2 |   TABLE ACCESS FULL| T    |      1 |      8 |     2   (0)| 00:00:01 |      7 |00:00:00.01 |       6 | ------------------------------------------------------------------------------------------------------------- --//E-ROWS=8.这样就明白为什么我当时的算法每行成本出现跳跃的情况.其它大家可以自行验证. 4.选择率如何计算呢? --//rownun<=N,这样的查询我看了<基于成本的Oracle优化法则>,也没有这方面的内容. --//我试图按照区间的算法不对. --// Selectivity =  (limit – low_value) / (high_value – low_value) + 1/num_distinct --//(55-1)/(100-1)+1/100 = .55545454545454545454 , 不对,rownum虚拟列,这样的查询条件选择率如何确定不知道. SCOTT@test01p> set feedback only SCOTT@test01p> select 1 from t where a1<=55 ;          1 ---------- 55 rows selected. SCOTT@test01p> set feedback 6 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  4vmjyzbu16y74, child number 0 ------------------------------------- select 1 from t where a1<=55 Plan hash value: 1601196873 -------------------------------------------------------------------------------------------------------------------- | Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |     55 |00:00:00.01 |      10 | |*  1 |  TABLE ACCESS FULL| T    |      1 |     56 |   168 |     3   (0)| 00:00:01 |     55 |00:00:00.01 |      10 | -------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("A1"<=55) --//如果查询select 1 from t where a1<=55;E-Rows=56,按照上面公式Selectivity =  (limit – low_value) / (high_value – low_value) + 1/num_distinct --//计算的结果是正确的. --//仅仅知道为什么出现上面的情况,不知道条件rownum<=N的选择率计算公式. --//如果加大NUMROWS=> 1000,就不会出现前面的情况. SCOTT@test01p> exec dbms_stats.set_table_stats(ownname=> NULL,TABNAME=>'T',NUMROWS=> 1000); PL/SQL procedure successfully completed. SCOTT@test01p> alter system flush shared_pool; System altered. --//select 'explain plan set statement_id='''||lpad(rownum,3,'0')||''''||' for select 1 from t where rownum<='||rownum||';' c80 from t; SCOTT@test01p> SELECT *   FROM (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') )  WHERE N2 <> 150; no rows selected