NESTED LOOPS 成本计算
来源：互联网 发布：八极拳八卦掌太极知乎 编辑：程序博客网 时间：2024/06/10 11:56
SQL> select * from v$version where rownum=1;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionSQL> create table t1 as select object_id id , object_name,owner from dba_objects;Table createdSQL> create table t2 as select object_id id , status,temporary from dba_objects;Table createdSQL> 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 completedSQL> 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 completedSQL> 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';ExplainedSQL> 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 selectedSQL> 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';ExplainedSQL> select OPERATION,OPTIONS,IO_COST,CPU_COST FROM PLAN_TABLE;OPERATION OPTIONS IO_COST CPU_COST------------------------------ -------------------- ------------------------------ ---------------------------------------SELECT STATEMENT 2837902 1247963677419NESTED LOOPS 2837902 1247963677419TABLE ACCESS FULL 90 16640000TABLE ACCESS FULL 39 17219238SQL> select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';PNAME PVAL1------------------------------ ----------CPUSPEED 0CPUSPEEDNW 2696.05568IOSEEKTIM 10IOTFRSPEED 4096MAXTHR MBRC 0MREADTIM 0SLAVETHR SREADTIM 09 rows selected由于MBRC=0 所以这里采用的是非工作量统计mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeedSQL> 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---------- 42sreadtim=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 COSTCEIL(1247963677419/2696.05568/------------------------------ 38574SQL> 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 179TEST T1 403NESTED 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+10NESTED LOOPS 94693 3.2867E+10TABLE ACCESS FULL 90 20263700TABLE 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
- NESTED LOOPS 成本计算
- Nested loops
- nested loops/hash join
- NESTED LOOPS HASH JOIN
- Nested Loops Join(嵌套连接)
- Nested Loops Join(嵌套连接)
- Nested Loops Join(嵌套连接)
- filter与nested loops的区别
- nested loops以选择驱动表实验
- 嵌套循环连接(nested loops join)原理
- oracle执行计划-Nested Loops (nl)-1
- oracle执行计划-Nested Loops (nl)-2
- oracle执行计划-Nested Loops (nl)-3
- NESTED LOOPS & HASH JOINS & MERGE JOIN
- 嵌套循环连接(nested loops join)原理
- 成本计算
- Nested loops, Hash join and Sort Merge joins – difference?
- SQL Server 执行连接的方式 - Nested Loops Joins
- STDMETHOD介绍
- 取BOM的相关信息报表。
- STL运用的C++技术（2）——模板特化
- 【转】Spring中bean的配置
- 把字符串分割成固定长度的片段，返回数组
- NESTED LOOPS 成本计算
- 【转】张冰OGNL表达式
- 在查询语句中使用 NOLOCK 和 READPAST
- as3.0 获取用户屏幕分辨率
- 汇编与逆向学习汇总
- Yourkit8.08实现远程监控（tomcat+linux注册/配置）
- 喜欢踢球就没有什么不可能
- 转：as 3.0常用的正则表达式
- 做自己喜欢的，并在做的过程中赚取生活。
