[20190821] about CPU cost calculation.txt

Time:2019-11-14

[20190821] about CPU cost calculation.txt

–//It was asked how to calculate the CPU cost in the link http://blog.itpub.net/267265/viewspace-2653964/. In fact, this detail is rarely considered in optimization,
–//Because CPU cost accounts for a small proportion of the whole cost, how to calculate it is complex and simple
–//If you look at Nathan Lewis’s, it says 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

–//It’s difficult to calculate these costs by value. In fact, it’s easy to reverse. I’ve done a series of blogs before, but I can’t find them now
–//About once, the actual situation may be very complicated

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
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
–//From this, we can see that the difference between 727172917311 and 727172917311 is exactly 20, that is, 20 CPU cycles for column skip

3. For the test, take the same field A1 respectively, and only take 1 line, 2 lines and 3 lines:
–//Exit the above test, because plan table is a temporary table, it will be cleared automatically after exiting
–//The table I built is very small, and the data is naturally in one block
–//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;

–//Save the above output to a file for execution:
[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.

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.
–//I still don’t understand why there is a jump here. But I can basically locate cost of locating a row in a block = y = 150
–//Maybe it’s the influence of where condition
–//Postscript:
–//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.
–//Of course, this is not the case.
[email protected]> select blocks,num_rows from user_tables where table_name=’T’;
    BLOCKS   NUM_ROWS
———- ———-
         4        100
–//But I can’t analyze the jump with N2 = 300450. I don’t know who can.

4. Continue to analyze:
–//What about multiple fields in 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.

–//Looking at statement [id = A0, ax, A1, we can see that CPU [cost] is the same, that is to say, the first field in the table does not count CPU cost
–//See statement [id = A2, A12, CPU [cost = 7291] to show that the first field in the table is not CPU cost
–//That is to say, cost of acquiring the nth (in our case the 2nd) column in a row = (n – 1) * Z
–//In addition, you can find that CPU cost = 7311 in statement [id = A3, A13, A23, A123, that is, the cost of the column in select is calculated by the cost of the largest column
–//The rest is how the X (cost of acquiring a block) above is calculated. As a matter of fact, we can know how much x equals, x = 7271-150 = 7121
–//In fact, there is a decimal point later. In order to test later, you need to know exactly what the value is after the decimal point. Continue the test.

5. Check the hack statistics.
–//In order to accurately determine x (cost of acquiring a block), hack statistics, increase the number of table t blocks.
[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

–//Note: the number of records not modified is 100. The calculation is as follows:
–//(712145500-100 * 150) / 1000000 = 7121.44, that is to say, the front x = 7121.44, thus obtaining the exact value of X.
–//When I did this, I always wanted to figure out how to set X = 7121.44 in Oracle, and there was a decimal point behind it

–//In this way, we can know the calculation formula of the select part;
X*blocks +( Y+(N – 1) * Z )*numrows
7121.44 * blocks + 150*rows + 20*effect_rows* (Highest_column_id – Lowest_column_id)
–//Note: in many cases, lowest’column’id is equal to 1. I can see why I changed the formula by looking at the later tests. In fact, lowest’column’id = 1 does not
–//Special case of where query condition
–//In many cases, the cost of a field is calculated separately. I define it as effect rows. If there is no where condition, it is actually equal to the previous rows

–//Simple verification:
[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
–//Basically the same

6. Finally, take a look at the predicate part a:
–//What if there are where query criteria?
–//. Cost of comparing a numeric column with a numeric constant = A
–//In this part, I think it’s relatively difficult, because there may be more than one query condition, and may also be or, and the comparison also has order
–//The complexity is that the predicate may have some association with select
–//I will simply analyze:
–//Re analyze table T and cancel the previous hack settings
[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;
–//Note: there is no query field in the select part, so the interference of CPU cost involved in field query in select can be eliminated first.

[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.
–//Note that there is a big gap between using bound variable and not using bound variable CPU cost

–//Select section:
7121.44 * blocks + 150*rows + 20*rows* ( Highest_column_id – Lowest_column_id)
= 7121.44 * 4 + 150*100 = 43485.76 =  43486

–//It can be found that the cost comparison in where is as follows:
–//Use unbound variables:
a1=100 48486-43486 = 5000
a2=100 50486-43486 = 7000
a3=100 52486-43486 = 9000
–//Use bound variables:
a1=:N1 58486-43486 = 15000
a2=:N1 60486-43486 = 17000
a3=:N1 62486-43486 = 19000

–//Use unbound variables:
–//Corresponding to A1 = 100 conditions, table t uses 100 records for comparison 100 times, so each time 5000 / 100 = 50
–//And so on, A2 = 100, 7000 / 100 = 70. A3 = 100, 9000 / 100 = 90
–//In combination with the previous z = 20 (20 CPU cycles for column skip). Where’s cost is actually calculated as follows:
rows*50+rows*(column_id-1)*20).
–//In fact, it can be understood that the CPU cost of each comparison is 50
–//In fact, the field cost is calculated in the select part (see the following example). I just deduce that the CPU cost of each comparison is 50

–//Use bound variables:
–//Not to mention the details, you can deduce that the CPU cost of each comparison is 150. Why do you use bound variables so much than unbound variables? I don’t understand
–//Give up analyzing bound variables first

–//Let’s look at a number of conditions
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

–//50536-43486 = 7050 for the case of statement [id = A12
–//My understanding: where a1 = 100 and A2 = 100. Because we need to take A2 field
–//rows*(Highest_column_id – 1)*20 = 100*20 = 2000.
–//(7050-2000) / 50 = 101, indicating 101 comparisons
–//It can be understood that A1 = 100 is compared 100 times, only one record is filtered out, so A2 = 100 only needs to be compared once. A total of 101 times
–//In addition, I think that under normal circumstances, the fields with good selectivity should be compared first, so as to reduce the number of subsequent comparisons

–//52536-43486 = 9050 for the case of statement ﹣ id = A123
–//Because you want to take the A3 field
–//rows*(Highest_column_id – 1)*20 = 100*2*20 = 4000.
–//(9050-4000) / 50 = 101, indicating 101 times of comparison. My understanding should be 101.01 times (102?), and the one after the decimal point is ignored.

–//Where part of cost, compare the cost. Each comparison cost = 50, in fact, the situation may be more complicated,
–//When I query select 1 from t…, there is no query field in the select section
–//If the query explain plan set statement [id =’bb ‘for select A3 from t where A2 = 100?
–//The previous select section has queried the A3 field. How about it? I’ve been confused here for a while

[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

–//Let me first talk about my understanding and then calculate:
If the first record is A2 = 1 during query, this record will be skipped without taking A3 field at all. In this way, the select part only takes A2 and then compares it
If A2 = 100 meets the query criteria, then select field A3. With this, it is easy to understand the CPU cost calculation

–//In the select part, you only need to retrieve the A1 field at first (even if the select part contains 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 section:
Compare 100 times 100 * 50 = 5000
Only one item conforms to A2 = 100. Take A3 field cost:
20*rows* (Highest_column_id – Lowest_column_id)
20*1*(3-2) = 20

–//cpu cost
45486 + 5000+ 20 = 50506
–//Exactly
–//Supplementary note: field cost is calculated in two times
–//The first time is 20 * rows * (highest column – lowest column), where highest column = 2, lowest column = 1. Rows = 100
–//The second time is 20 * rows * (highest column – lowest column), where highest column = 3, lowest column = 2. Rows = 1
–//In short, field cost depends on the combination of select and query criteria, which may be more complex
–//Test of supplementary or:
[email protected]> 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

–//In the select part, you only need to retrieve the A2 field at first (even if the select part contains 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 section:
Compare 100 + 99 times, 199 * 50 = 9950
–//Note I started to think 200 times. Here we should consider the overlap part. You can understand that if A1 = 100 has met the conditions, A2 = 100 does not need to be compared
Only one article conforms to A1 = 100.1 article conforms to A2 = 100. There are two articles in total (I don’t know whether to consider the coincidence part here, and the estimation is too small and ignored). Take A3 field cost:
20*rows* (Highest_column_id – Lowest_column_id)
20*1*(3-2)*2=40

–//cpu cost
45486 + 9950 + 40  = 55476
–//Exactly

7. Finally, let’s look at the predicate condition existence function

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 section:
7121.44 * blocks + 150*rows + 20*rows* ( Highest_column_id – 1)
= 7121.44 * 4 + 150*100 = 43485.76 =  43486

–//It can be found that the cost comparison in where is as follows:
to_char(a1)=’100′ 58486-43486 = 15000
to_char(a2)=’100′ 60486-43486 = 17000
to_char(a3)=’100′ 62486-43486 = 19000

–//Corresponding to A1 = 100 conditions, table t uses 100 records for comparison 100 times, so each time 15000 / 100 = 150
–//So A2 = 100, 17000 / 100 = 170. A3 = 100, 19000 / 100 = 190
–//According to the previous test, the cost of excluding the field (20) can be estimated to account for 100 in the function part after comparing 50 each time. It feels that this part is too small!!
–//Field = constant comparison is 50 at a time, while the introduced function is only 100. I personally feel that Oracle is too small and should be set to 500

8. summary:
–//The calculation of cup cost is 7121.44 for each block, 150 for each line, the field viewing position (n-1) * 20 (note only the most tested field calculation), comparison 50 and times, function 100
–//Using bound variable to compare cost 150, I don’t know why. I will not explore this
–//Many cases are very complex. Finally, we do an example to verify it

[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 part, query A2
7121.44 * blocks + 150*rows + 20*rows* ( Highest_column_id – 1)
=7121.44 * 4 + 150*100 +20*100*1 = 45485.76 = 45486

–//Note only one return. It seems that Oracle does not consider this part
100*rows*3
=1*100*3 = 300

–//Where section:
–//Compare 100 times, function 100, compare 50
100*(100+50) = 15000

–//Field cost, only one return, that is to say, take A3 field only once. Note that the previous select section has found A2 field, so lowest column id = 2
20*rows* ( Highest_column_id – Lowest_column_id)
= 20*1*(3-2) = 20

/ / total:
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

–//It can be seen that the select part contains functions, and CPU cost is not calculated. Finally, go back to the previous questions asked by others:
–//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 part, query val
7121.44 * blocks + 150*rows + 20*rows* ( Highest_column_id – 1)
7121.44* 10000 + 150*10000 + 20*10000* ( 2- 1) = 72914400
–//It’s a match
–//Why there is no comparative CPU cost consumption? My understanding is that this is determined by the characteristics of the index. The key values are sorted through the row directory, so that when scanning the leaves, the comparison is made
–//The number of times is limited.
–//Obviously Oracle ignores the comparison test in the index leaf block.
–//Looking at the index:
[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 section:
7121.44*1+1*150+20*1*(3-1) = 7311.44
8361-1050 = 7311
–//The select part is consistent
–//Where part (index part) and how to determine the CPU cost 1050 of unique scan?

[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 access block is calculated as 7121.44
–//7321-7121 = 200, 150 per line, compare once 50. Guess not know if it is correct?
–//This reverses the CPU cost of unique scan block = 1050 – 200 = 850.

–//Why is the situation of binding variable 150? Don’t understand? It feels a little high. Is there a replacement process for binding variable
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:
–//Compare 100 + 1
100*50 = 5000
1*150 = 150
–//Field cost:
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:
–//Compare 100 + 1, 2 are bound variables
101*150  = 15150
–//Field cost:
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:
–//Compare 100 + 1 + 0.01
101*150  = 15150
101.01*150 = 15151.50
–//Field cost:
20*rows* ( Highest_column_id – Lowest_column_id)
20*100*(3-1) = 4000

–//43486 + 15150 + 4000 = 62636. Difference 1. I estimate that if 101.01 * 150 = 15151.50, the calculation basically conforms to
–//43485.76+15151.50+4000 = 62637.26

–//It’s a bit of a bull’s-eye again. These details don’t matter. But it’s quite a sense of achievement to see that the calculation results are consistent with the test

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’);

Recommended Today

Method of DOS intercepting string

In order to illustrate the problem of intercepting strings in variables, the following example has been given: Copy codeThe code is as follows: @echo offset str=123456789Echo first character is:% STR: ~ 0,1%The first two characters of echo are:% STR: ~ 0,2%The first 5 characters of echo are:% STR: ~ 0,5%Echo the string after the last […]