lfree 日志
[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
1 用户, 点赞了这篇动态!