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') +
(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"
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;
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
