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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【sql調優之執行計劃】nested loops join and nested loop join outerSQLOOP
- nested loops 和hash join的一點測試OOP
- [20171002]NESTED LOOPS(PARTITION OUTER).TXTOOP
- 統計資訊過期導致SQL進行NESTED LOOPS查詢緩慢SQLOOP
- 關於迴圈巢狀nested loops的一點分析巢狀OOP
- 標準成本計算和平均成本計算比較
- Oracle標準成本計算和平均成本計算比較Oracle
- Oracle 成本計算公式Oracle公式
- CBO成本計算初探
- 產品成本計算方法
- 雲端計算降低辦公成本
- 專案成本管理計算公式公式
- B-Tree Index 成本計算Index
- Nested loops、Hash join、Sort merge join（三種連線型別原理、使用要點）OOP型別
- 雲端計算如何避免隱性成本
- 質量成本如何計算(轉載)
- 【TUNE_ORACLE】列出NL（NESTED LOOPS）被驅動表走了全表掃描的SQL參考OracleOOPSQL
- CBO各種型別成本計算公式型別公式
- 有關工序成本的計算邏輯
- 【TUNE_ORACLE】列出返回行數較多的巢狀迴圈（NESTED LOOPS）SQL的SQL參考Oracle巢狀OOPSQL
- CBO Cost Formulas基於成本優化器的成本計算公式大全ORM優化公式
- oracle基於cbo成本計算方式說明Oracle
- OPTIMIZER_INDEX_COST_ADJ與成本計算Index
- 索引成本計算的基礎知識索引
- [zt] CBO在查詢中如何計算成本
- 微課sql最佳化(14)、表的連線方法(3)-關於Nested Loops Join(巢狀迴圈)SQLOOP巢狀
- 抖音小程式開發成本是如何計算的？
- 恆訊科技分析：雲端計算的成本貴嗎？
- 【轉】OPTIMIZER_INDEX_COST_ADJ與成本計算Index
- React Loops 嚐鮮ReactOOP
- 智慧景區無線AP覆蓋方案的成本計算
- [20190821]關於CPU成本計算.txt
- CO聯產品成本差異分攤計算邏輯
- oracle實驗記錄（INDEX fast full scan 的成本計算）OracleIndexAST
- SQL Server三大演算法的I/O成本計算SQLServer演算法
- HDU 3853 LOOPS(概率dp)OOP
- 分享Epicor開發的月加權計算成本的案例
- 雲端計算技術降低了IT成本，但是降低的幅度會多大?