[20250815]关于CPU成本计算(补充)3.txt

[20250815]关于CPU成本计算(补充)3.txt

--//以前测试过，当时一边做一边猜测，反推X，Y，Z的值，有点乱，做一些必要的整理，补充索引的一些测试。
--//看Jonathan 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

--//每块的CPU COST X=7121.44。
--//每行的CPU COST Y=150
--//每列的CPU COST Z=20，注意计算Highest_column_id
--//谓词部分使用常量比较成本 A=50
--//谓词部分使用绑定变量比较成本 A=150。
--//补充谓词部分字段参与运算成本的情况，每增加1个字段增加50,或者增加一个运算增加50.
--//以前测试在谓词使用函数存在问题，各种函数的成本并不是固定，另外写一篇blog说明。
--//扫描唯一索引块成本  1050。

--//里面的X，Y，Z值很容易确定，后面的测试有一些混乱，重新做1次，以前的测试参考：[20190821]关于CPU成本计算.txt

--//补充测试谓词使用函数的一些情况：

1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SCOTT@book01p> create table t as select rownum a1 , rownum a2 ,rownum a3 from dual connect by level<=100 ;
Table created.
--//分析表略。

SCOTT@book01p> select blocks,num_rows from user_tables where table_name='T';
    BLOCKS   NUM_ROWS
---------- ----------
         4        100

2.测试函数：
column STATEMENT_ID format a16
column OPTIONS format a10
column time format  9999

explain plan set statement_id='to_char'   for select 1 from t where to_char(a1)='100';
explain plan set statement_id='to_number' for select 1 from t where to_number(a1)='100';
explain plan set statement_id='power'     for select 1 from t where power(a1,2)=100;
explain plan set statement_id='sqrt'      for select 1 from t where sqrt(a1)=100;
explain plan set statement_id='abs'       for select 1 from t where abs(a1)=100;
explain plan set statement_id='ceil'      for select 1 from t where ceil(a1)=100;
explain plan set statement_id='floor'     for select 1 from t where floor(a1)=100;

explain plan set statement_id='xto_char'   for select 1 from t where to_char(a1)= :n1;
explain plan set statement_id='xto_number' for select 1 from t where to_number(a1)=:n1;
explain plan set statement_id='xpower'     for select 1 from t where power(a1,2)= :n1;
explain plan set statement_id='xsqrt'      for select 1 from t where sqrt(a1)=:n1;
explain plan set statement_id='xabs'       for select 1 from t where abs(a1)=:n1;
explain plan set statement_id='xceil'      for select 1 from t where ceil(a1)=:n1;
explain plan set statement_id='xfloor'     for select 1 from t where floor(a1)=:n1;

--//select部分cost,没有查询字段。
7121.44 * blocks + 150*rows + 20*rows* ( Highest_column_id - Lowest_column_id)
= 7121.44 * 4 + 150*100 + 20*150*(1-1) = 43485.76 =  43486

SCOTT@book01p> select STATEMENT_ID,OPERATION, CPU_COST, CPU_COST-43486 CPUX,(CPU_COST-43486)/100 CPUY from plan_table where options='FULL';

STATEMENT_ID     OPERATION                        CPU_COST       CPUX       CPUY
---------------- ------------------------------ ---------- ---------- ----------
to_char          TABLE ACCESS                        58486      15000        150     
to_number        TABLE ACCESS                        68486      25000        250
power            TABLE ACCESS                        73486      30000        300
sqrt             TABLE ACCESS                        68486      25000        250
abs              TABLE ACCESS                        53486      10000        100
ceil             TABLE ACCESS                        73486      30000        300
floor            TABLE ACCESS                        73486      30000        300
xto_char         TABLE ACCESS                        58486      15000        150
xto_number       TABLE ACCESS                        78486      35000        350
xpower           TABLE ACCESS                        83486      40000        400
xsqrt            TABLE ACCESS                        78486      35000        350
xabs             TABLE ACCESS                        63486      20000        200
xceil            TABLE ACCESS                        83486      40000        400
xfloor           TABLE ACCESS                        83486      40000        400
14 rows selected.

--//使用绑定变量除了to_char函数外，其他都是比使用文字变量增加100.
--//可以发现并不是每个函数相同。