[20250813]CPU COST转化为COST成本计算.txt

[20250813]CPU COST转化为COST成本计算.txt --//别人问的问题，一开始我以为对方想知道CPU COST的计算，给对方看了以前的链接：[20190821]关于CPU成本计算.txt --//实际上需要了解知道CPU COST，如何转化为COST的成本，实际上CPU COST在大多数情况下占比很低。 --//跟踪看看10053很容易确定如何计算。 --//自己做一些尝试： 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. 2.顺便温习以前CPU COST部分计算： --//如果你看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 --//谓词部分使用绑定变量比较成本 A=150 --//扫描唯一索引块成本  1050。 --//写一个简单的例子验证看看： SCOTT@book01p> explain plan set statement_id='emp' for  select  * from emp; Explained. SCOTT@book01p> column time format 99999 SCOTT@book01p> column OPTIONS format a20 SCOTT@book01p> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where STATEMENT_ID='emp'; STATEMENT_ID                   OPERATION                      OPTIONS                    COST   CPU_COST    IO_COST   TIME ------------------------------ ------------------------------ -------------------- ---------- ---------- ---------- ------ emp                            SELECT STATEMENT                                             3      39667          3      1 emp                            TABLE ACCESS                   FULL                          3      39667          3      1 --//CPU_COST=39667 SCOTT@book01p> select blocks,num_rows from user_tables where table_name='EMP';     BLOCKS   NUM_ROWS ---------- ----------          5         14 --//占5块，14行。表一共8个字段。 --//以上查询相对简单，没有谓词部分，计算公式如下： --// X * blocks + Y * rows + Z * rows * (Highest_column_id - Lowest_column_id) --//7121.44 * blocks + 150*rows + 20*effect_rows* (Highest_column_id - Lowest_column_id) --//7121.44 * 5 + 150*14 + 20*14* (8-1) = 39667.20 --//计算结果非常接近。 3.看看CPU COST转化为COST成本： SCOTT@book01p> select * from emp; ... 14 rows selected. SCOTT@book01p> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 1745700775 a2dk8bdn0ujx7            1      83879      3956160932  680d47a7  2025-08-13 17:09:50    16777217 SCOTT@book01p> @ 10053x a2dk8bdn0ujx7 1 /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4588_aa2dk8bdn0ujx7.trc --//查看跟踪文件内容： ----------------------------- SYSTEM STATISTICS INFORMATION ----------------------------- Using dictionary system stats.   Using NOWORKLOAD Stats   CPUSPEEDNW: 1512 millions instructions/sec (default is 100)   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)   IOSEEKTIM:  10 milliseconds (default is 10)   MBRC:       NO VALUE blocks (default is 8) --//一般多数情况下很少有人会收集WORKLOAD状况。NOWORKLOAD表示没有WORKLOAD。 --//CPUSPEEDNW=1512,信息来源查询sys.aux_stats$： SYS@book01p> column PVAL2 format a20 SYS@book01p> select * from sys.aux_stats$; SNAME                          PNAME                               PVAL1 PVAL2 ------------------------------ ------------------------------ ---------- -------------------- SYSSTATS_INFO                  STATUS                                    COMPLETED SYSSTATS_INFO                  DSTART                                    07-27-2021 20:33 SYSSTATS_INFO                  DSTOP                                     07-27-2021 20:33 SYSSTATS_INFO                  FLAGS                                   1 SYSSTATS_MAIN                  CPUSPEEDNW                     1512.17698 SYSSTATS_MAIN                  IOSEEKTIM                              10 SYSSTATS_MAIN                  IOTFRSPEED                           4096 SYSSTATS_MAIN                  SREADTIM SYSSTATS_MAIN                  MREADTIM SYSSTATS_MAIN                  CPUSPEED SYSSTATS_MAIN                  MBRC SYSSTATS_MAIN                  MAXTHR SYSSTATS_MAIN                  SLAVETHR 13 rows selected. --------------- *************************************** SINGLE TABLE ACCESS PATH   Single Table Cardinality Estimation for EMP[EMP]   SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE   Table: EMP  Alias: EMP     Card: Original: 14.000000  Rounded: 14  Computed: 14.000000  Non Adjusted: 14.000000   Scan IO  Cost (Disk) =   3.000000   Scan CPU Cost (Disk) =   39667.200000   Total Scan IO  Cost  =   3.000000 (scan (Disk))                        =   3.000000   Total Scan CPU  Cost =   39667.200000 (scan (Disk))                        =   39667.200000 --//CPU  Cost =   39667.200000，CPU Cost与我前面的计算一样。   Access Path: TableScan     Cost:  3.002186  Resp: 3.002186  Degree: 0       Cost_io: 3.000000  Cost_cpu: 39667       Resp_io: 3.000000  Resp_cpu: 39667   Best:: AccessPath: TableScan          Cost: 3.002186  Degree: 1  Resp: 3.002186  Card: 14.000000  Bytes: 0.000000 --//知道Cost_cpu=39667如何转化为COST成本呢？你可以理解Cost_cpu 就是执行指令的数量，这样除以CPUSPEEDNW=1512 * 10^6，就转 --//化为秒数。oracle基于成本的优化的COST单位相当于块，这样秒数再除以单块读时间SREADTIM就是对应的成本。 --//注意CPUSPEEDNW 的单位是 millions。而SREADTIM的单位是 毫秒。 --//这样计算公式如下： --// Cost = Cost_CPU_FROM_10053 /(CPUSPEEDNW * 10^6) / (SREADTIM/1000)           = Cost_CPU_FROM_10053 /CPUSPEEDNW /SREADTIM/1000 --//SREADTIM的计算公式如下： --//SREADTIM = IOSEEKTIM +  block_size  / IOTFRSPEED = 10+8192/4096 =12 --//MREADTIM = IOSEEKTIM +  mbrc * block_size  / IOTFRSPEED = 10+8*8192/4096 =26 --//注：IOSEEKTIM 表示寻道时间，IOTFRSPEED 表示IO传输的速度，相当于每毫秒4K。 --//这样CPU COST的计算就是 --//39667.2/1512/12/1000  = .00218624338624338624 ,取小数点后6位就是0.002186，与前面计算一致。 --// 以前写的计算公式： SCOTT@book01p> @sys_stats.sql PNAME                               PVAL1 CALCULATED FORMULA ------------------------------ ---------- ---------- ------------------------------------------------------------ CPUSPEED CPUSPEEDNW                     1512.17698 IOSEEKTIM                              10 IOTFRSPEED                           4096 MAXTHR MBRC                                               8 = _db_file_optimizer_read_count MREADTIM                                          26 = IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED SLAVETHR SREADTIM                                          12 = IOSEEKTIM + db_block_size        / IOTFRSPEED    maximum mbrc                           105.650794 = buffer cache size in blocks / sessions    single block Cost per block                     1 by definition    multi block Cost per block                  .2708 = 1/MBRC * MREADTIM/SREADTIM 12 rows selected. 4.附上10053x.sql脚本： $ cat 10053x.sql set term off execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1'); set term on set head off @ t set head on define 1=&trc