[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.
--//可以发现并不是每个函数相同。
--//以前测试过,当时一边做一边猜测,反推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.
--//可以发现并不是每个函数相同。