[20190823] about CPU cost calculation 2.txt

Time:2019-11-10

[20190823] about CPU cost calculation 2.txt

–//A few days ago, when I explored the problems encountered in CPU cost, my test query results jumped when I got the row cost. I don’t know why. It’s a little strange. Let’s analyze it.
–//The original link of itpub no longer exists, and my diary still has records. Now I think the record thinking at that time was very disordered, but these are all guessing processes, and it was normal that the thinking was disordered before.
–//By the way, make some necessary additions.

1. environment:
[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. test:
[email protected]> create table t as select rownum a1 , rownum a2 ,rownum a3 from dual connect by level<=100 ;
Table created.

–//Analysis omitted
select ‘explain plan set statement_id=”’||lpad(rownum,3,’0′)||””||’ for select 1 from t where rownum<=’||rownum||’;’ c80 from t;
–//Save the above output to a file for execution, and then execute as follows:

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.
–//For the part larger than 7271, I have parsed it before
–//In statement_id = 025050705, N2 is 7272717272, respectively. It indicates that one more block is accessed in statement_id = 026051076.
–//It can be understood that table t accounts for 4 blocks, with 100 rows in total, with an average of 25 rows per block. In this way, when the query is equal to rownum < = 26,51,76, one more access block will occur.

–//The rest is why the CPU cost is the same when the query criteria rownum < = 55, rownum < = 56, and rownum < = 57, which is not easy to understand. What about N2 jumping?

3. Continue to explore:
[email protected]> set feedback only
[email protected]> select 1 from t where rownum<=55 ;
         1
———-

55 rows selected.

[email protected]> set feedback 6
[email protected]> @ 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 / [email protected]$1
Predicate Information (identified by operation id):
—————————————————
   1 – filter(ROWNUM<=55)
–//Pay attention to e-rows = 57. Oh! That’s why jumping occurs. It’s Oracle’s algorithm to estimate the selection rate that causes such a situation
–//It’s a bit strange that Oracle is such a conditional algorithm. What’s the possible query condition rownum < = 55, e-rows = 57?

–//The execution plan of select 1 from t where rownum < = 7 can also be verified:
[email protected]> @ 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. That’s why the cost of each row of my algorithm jumped at that time. Others can verify by themselves

4. How to calculate the selection rate?
–//Rownun < = n, I’ve seen this query, and I don’t have any content about it
–//I’m trying to get the interval algorithm wrong
–// Selectivity =  (limit – low_value) / (high_value – low_value) + 1/num_distinct
–//(55-1) / (100-1) + 1 / 100 =. 555454545454545454, no, rownum virtual column, how to determine the selection rate of such query conditions is unknown
[email protected]> set feedback only
[email protected]> select 1 from t where a1<=55 ;
         1
———-

55 rows selected.

[email protected]> set feedback 6
[email protected]> @ 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 / [email protected]$1
Predicate Information (identified by operation id):
—————————————————
   1 – filter(“A1″<=55)

–//If you want to query select 1 from t where a1 < = 55; e-rows = 56, select activity = (limit – low value) / (high value – low value) + 1 / num – distinct according to the above formula
–//The result of calculation is correct
–//Just know why the above situation occurs, do not know the selection rate calculation formula of the condition rownum < = n
–//If you increase NumRows = > 1000, the previous situation will not occur

[email protected]> exec dbms_stats.set_table_stats(ownname=> NULL,TABNAME=>’T’,NUMROWS=> 1000);
PL/SQL procedure successfully completed.

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

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

Recommended Today

The method of obtaining the resolution of display by pyqt5

The code is as follows import sys from PyQt5.QtWidgets import QApplication, QWidget class Example(QWidget): def __init__(self): super().__init__() self.initUI() #Interface drawing to initui method def initUI(self): self.desktop = QApplication.desktop() #Get display resolution size self.screenRect = self.desktop.screenGeometry() self.height = self.screenRect.height() self.width = self.screenRect.width() print(self.height) print(self.width) #Show window self.show() if __name__ == ‘__main__’: #Create applications and objects app […]