NESTED LOOPS 成本計算
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> create table t1 as select object_id id , object_name,owner from dba_objects;
Table created
SQL> create table t2 as select object_id id , status,temporary from dba_objects;
Table created
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
3 tabname => 'T1',
4 estimate_percent => 100,
5 method_opt => 'for all columns size AUTO',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
3 tabname => 'T2',
4 estimate_percent => 100,
5 method_opt => 'for all columns size AUTO',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed
SQL> explain plan for select /*+ use_nl(t1,t2) */ t1.owner,t1.object_name,t2.status from t1,t2 where t1.id=t2.id and t2.status='VALID';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72474 | 3255K| 2876K (2)| 09:35:18 |
| 1 | NESTED LOOPS | | 72474 | 3255K| 2876K (2)| 09:35:18 |
| 2 | TABLE ACCESS FULL| T1 | 72474 | 2477K| 91 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 11 | 40 (3)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."STATUS"='VALID' AND "T1"."ID"="T2"."ID")
15 rows selected
SQL> explain plan for select /*+ use_nl(t1,t2) */ t1.owner,t1.object_name,t2.status from t1,t2 where t1.id=t2.id and t2.status='VALID';
Explained
SQL> select OPERATION,OPTIONS,IO_COST,CPU_COST FROM PLAN_TABLE;
OPERATION OPTIONS IO_COST CPU_COST
------------------------------ -------------------- ------------------------------ ---------------------------------------
SELECT STATEMENT 2837902 1247963677419
NESTED LOOPS 2837902 1247963677419
TABLE ACCESS FULL 90 16640000
TABLE ACCESS FULL 39 17219238
SQL> select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
PNAME PVAL1
------------------------------ ----------
CPUSPEED 0
CPUSPEEDNW 2696.05568
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC 0
MREADTIM 0
SLAVETHR
SREADTIM 0
9 rows selected
由於MBRC=0 所以這裡採用的是非工作量統計
mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed
SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
2 (select value
from v$parameter
where name = 'db_file_multiblock_read_count') *
(select value from v$parameter where name = 'db_block_size') /
(select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "mreadtim"
3 4 5 6 7 from dual;
mreadtim
----------
42
sreadtim=ioseektim+db_block_size/iotfrspeed
SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
(select value from v$parameter where name = 'db_block_size') /
(select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"
from dual; 2 3 4
sreadtim
----------
12
CPU COST = ceil(PLAN_TABLE.cpu_cost/cpuspeed/1000/sreadtim)
SQL> select ceil(1247963677419/2696.05568/1000/12) from dual; ---得到總的CPU COST
CEIL(1247963677419/2696.05568/
------------------------------
38574
SQL> select 2837902+38574 from dual; --- 總的IO COST + 總的CPU COST
2837902+38574
-------------
2876476
COST 基本上等於2876K
IO COST 等於 2837902，它是怎麼得到的呢？
select owner,blocks from dba_tables where owner='TEST' and table_name in ('T1','T2');
SQL> select owner,table_name,blocks from dba_tables where owner='TEST' and table_name in ('T1','T2');
OWNER TABLE_NAME BLOCKS
------------------------------ ------------------------------ ----------
TEST T2 179
TEST T1 403
NESTED LOOPS 成本計算方法如下：
COST = (OUTER TABLE IO COST) + (OUTER TABLE CARDINALITY) * (INNER TABLE IO COST) + CPU COST
所以 IO COST = CEIL(outer table blocks*mreadtim/db_file_multiblock_read_count/sreadtim)+
CEIL((outer table cardinality)*inner table blocks*mreadtim/db_file_multiblock_read_count/sreadtim)+
_tablescan_cost_plus_one*2 ---- 這裡 有2次全表掃描 所以加2次，
注意：不管全表掃描會發生多少次,_tablescan_cost_plus_one 始終在計算COST末尾新增,有多少全表掃描就加幾
SQL> SELECT CEIL(403*42/16/12)+ceil(72474*(179*42/16/12))+1+1 FROM DUAL;
CEIL(403*42/16/12)+CEIL(72474*(179*42/16/12))+1+1
-------------------------------------------------
2837902
再來一個例子
SQL> explain plan for select /*+ use_nl(t1,t2) */ t1.owner,t1.object_name,t2.status from t1,t2 where t1.id=t2.id and t1.owner='ROBINSON';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2416 | 108K| 95709 (2)| 00:19:09 |
| 1 | NESTED LOOPS | | 2416 | 108K| 95709 (2)| 00:19:09 |
|* 2 | TABLE ACCESS FULL| T1 | 2416 | 84560 | 91 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 11 | 40 (3)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."OWNER"='ROBINSON')
3 - filter("T1"."ID"="T2"."ID")
16 rows selected.
SQL> select OPERATION,OPTIONS,IO_COST,CPU_COST FROM PLAN_TABLE;
OPERATION OPTIONS IO_COST CPU_COST
-------------------- -------------------- -------------- ----------
SELECT STATEMENT 94693 3.2867E+10
NESTED LOOPS 94693 3.2867E+10
TABLE ACCESS FULL 90 20263700
TABLE ACCESS FULL 39 13595488
現在來計算一下 IO_COST = 94693 是怎麼計算出來的
SQL> SELECT CEIL(403*42/16/12)+ceil(2416*(179*42/16/12))+1+1 FROM DUAL;
CEIL(403*42/16/12)+CEIL(2416*(179*42/16/12))+1+1
------------------------------------------------
94693
由此可見Oracle11g 成本演算法和9i 一樣，沒有變化，再次記錄一下巢狀迴圈成本演算法
NESTED LOOPS COST = (OUTER TABLE IO COST) +
(OUTER TABLE CARDINALITY) * (INNER TABLE IO COST) + TOTAL CPU COST
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> create table t1 as select object_id id , object_name,owner from dba_objects;
Table created
SQL> create table t2 as select object_id id , status,temporary from dba_objects;
Table created
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
3 tabname => 'T1',
4 estimate_percent => 100,
5 method_opt => 'for all columns size AUTO',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
3 tabname => 'T2',
4 estimate_percent => 100,
5 method_opt => 'for all columns size AUTO',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed
SQL> explain plan for select /*+ use_nl(t1,t2) */ t1.owner,t1.object_name,t2.status from t1,t2 where t1.id=t2.id and t2.status='VALID';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72474 | 3255K| 2876K (2)| 09:35:18 |
| 1 | NESTED LOOPS | | 72474 | 3255K| 2876K (2)| 09:35:18 |
| 2 | TABLE ACCESS FULL| T1 | 72474 | 2477K| 91 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 11 | 40 (3)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."STATUS"='VALID' AND "T1"."ID"="T2"."ID")
15 rows selected
SQL> explain plan for select /*+ use_nl(t1,t2) */ t1.owner,t1.object_name,t2.status from t1,t2 where t1.id=t2.id and t2.status='VALID';
Explained
SQL> select OPERATION,OPTIONS,IO_COST,CPU_COST FROM PLAN_TABLE;
OPERATION OPTIONS IO_COST CPU_COST
------------------------------ -------------------- ------------------------------ ---------------------------------------
SELECT STATEMENT 2837902 1247963677419
NESTED LOOPS 2837902 1247963677419
TABLE ACCESS FULL 90 16640000
TABLE ACCESS FULL 39 17219238
SQL> select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
PNAME PVAL1
------------------------------ ----------
CPUSPEED 0
CPUSPEEDNW 2696.05568
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC 0
MREADTIM 0
SLAVETHR
SREADTIM 0
9 rows selected
由於MBRC=0 所以這裡採用的是非工作量統計
mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed
SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
2 (select value
from v$parameter
where name = 'db_file_multiblock_read_count') *
(select value from v$parameter where name = 'db_block_size') /
(select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "mreadtim"
3 4 5 6 7 from dual;
mreadtim
----------
42
sreadtim=ioseektim+db_block_size/iotfrspeed
SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
(select value from v$parameter where name = 'db_block_size') /
(select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"
from dual; 2 3 4
sreadtim
----------
12
CPU COST = ceil(PLAN_TABLE.cpu_cost/cpuspeed/1000/sreadtim)
SQL> select ceil(1247963677419/2696.05568/1000/12) from dual; ---得到總的CPU COST
CEIL(1247963677419/2696.05568/
------------------------------
38574
SQL> select 2837902+38574 from dual; --- 總的IO COST + 總的CPU COST
2837902+38574
-------------
2876476
COST 基本上等於2876K
IO COST 等於 2837902，它是怎麼得到的呢？
select owner,blocks from dba_tables where owner='TEST' and table_name in ('T1','T2');
SQL> select owner,table_name,blocks from dba_tables where owner='TEST' and table_name in ('T1','T2');
OWNER TABLE_NAME BLOCKS
------------------------------ ------------------------------ ----------
TEST T2 179
TEST T1 403
NESTED LOOPS 成本計算方法如下：
COST = (OUTER TABLE IO COST) + (OUTER TABLE CARDINALITY) * (INNER TABLE IO COST) + CPU COST
所以 IO COST = CEIL(outer table blocks*mreadtim/db_file_multiblock_read_count/sreadtim)+
CEIL((outer table cardinality)*inner table blocks*mreadtim/db_file_multiblock_read_count/sreadtim)+
_tablescan_cost_plus_one*2 ---- 這裡 有2次全表掃描 所以加2次，
注意：不管全表掃描會發生多少次,_tablescan_cost_plus_one 始終在計算COST末尾新增,有多少全表掃描就加幾
SQL> SELECT CEIL(403*42/16/12)+ceil(72474*(179*42/16/12))+1+1 FROM DUAL;
CEIL(403*42/16/12)+CEIL(72474*(179*42/16/12))+1+1
-------------------------------------------------
2837902
再來一個例子
SQL> explain plan for select /*+ use_nl(t1,t2) */ t1.owner,t1.object_name,t2.status from t1,t2 where t1.id=t2.id and t1.owner='ROBINSON';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2416 | 108K| 95709 (2)| 00:19:09 |
| 1 | NESTED LOOPS | | 2416 | 108K| 95709 (2)| 00:19:09 |
|* 2 | TABLE ACCESS FULL| T1 | 2416 | 84560 | 91 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 11 | 40 (3)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."OWNER"='ROBINSON')
3 - filter("T1"."ID"="T2"."ID")
16 rows selected.
SQL> select OPERATION,OPTIONS,IO_COST,CPU_COST FROM PLAN_TABLE;
OPERATION OPTIONS IO_COST CPU_COST
-------------------- -------------------- -------------- ----------
SELECT STATEMENT 94693 3.2867E+10
NESTED LOOPS 94693 3.2867E+10
TABLE ACCESS FULL 90 20263700
TABLE ACCESS FULL 39 13595488
現在來計算一下 IO_COST = 94693 是怎麼計算出來的
SQL> SELECT CEIL(403*42/16/12)+ceil(2416*(179*42/16/12))+1+1 FROM DUAL;
CEIL(403*42/16/12)+CEIL(2416*(179*42/16/12))+1+1
------------------------------------------------
94693
由此可見Oracle11g 成本演算法和9i 一樣，沒有變化，再次記錄一下巢狀迴圈成本演算法
NESTED LOOPS COST = (OUTER TABLE IO COST) +
(OUTER TABLE CARDINALITY) * (INNER TABLE IO COST) + TOTAL CPU COST
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22990797/viewspace-1200393/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【TUNE_ORACLE】列出NL（NESTED LOOPS）被驅動表走了全表掃描的SQL參考OracleOOPSQL
- 【TUNE_ORACLE】列出返回行數較多的巢狀迴圈（NESTED LOOPS）SQL的SQL參考Oracle巢狀OOPSQL
- 微課sql最佳化(14)、表的連線方法(3)-關於Nested Loops Join(巢狀迴圈)SQLOOP巢狀
- [20190821]關於CPU成本計算.txt
- React Loops 嚐鮮ReactOOP
- 雲端計算如何避免隱性成本
- [20190823]關於CPU成本計算2.txt
- 恆訊科技分析：雲端計算的成本貴嗎？
- 抖音小程式開發成本是如何計算的？
- 智慧景區無線AP覆蓋方案的成本計算
- 分享Epicor開發的月加權計算成本的案例
- CO聯產品成本差異分攤計算邏輯
- 服裝ERP系統下的成本計算如何及時有效
- [Vue Router] Nested RoutesVue
- [譯] Dart非同步之Isolates 和event loopsDart非同步OOP
- 北鯤雲超算平臺——致力於提高科研效率，降低計算成本的雲超算平臺
- AI降成本利器！阿里雲彈性加速計算例項來了，最高節省50%推理成本AI阿里
- ElasticSearch多層nested查詢、nested過濾排除非結果內容Elasticsearch
- Arctic助力傳媒實現低成本的大資料準實時計算大資料
- treevalue——Master Nested Data Like TensorAST
- 軟體專案管理 6.10.成本預算專案管理
- 將渲染計算搬到雲端，開啟低成本、強互動、沉浸式體驗
- 開發運維效率提升 80%，計算成本下降 50%，分眾傳媒的 Serverless 實踐運維Server
- 節省 58% IT 成本，呼叫函式計算超過 30 億次，石墨文件的 Serverless 實踐函式Server
- 本地計算、雲端計算、霧計算、邊緣計算有什麼區別？
- 成本節省 50%，9人團隊使用函式計算開發 wolai 線上文件應用函式AI
- 如何降低 Flink 開發和運維成本？阿里雲實時計算平臺建設實踐運維阿里
- IOT邊緣計算閘道器有效解決老舊裝置接入難題，降低部署成本
- 成本節省 50%，10 人團隊使用函式計算開發 wolai 線上文件應用函式AI
- nested exception is java.lang.NoSuchMethodException: 實體類.()ExceptionJava
- Java | 靜態巢狀類（Static Nested Class）Java巢狀
- 28、Static Nested Class 和 Inner Class的不同。
- 邊緣計算、霧計算、雲端計算區別幾何？
- 計算
- Elasticsearch 第八篇：資料型別 Array、Nested、Object 的設計與應用Elasticsearch資料型別Object
- 邊緣計算與雲端計算
- 計算機組成原理 - 計算篇計算機
- ES 筆記四十二：物件及 Nested 物件筆記物件
- 資料庫實現原理#1（Nested Loop Join）資料庫OOP