Oracle：执行计划+cost成本计算

zhangbinmy

　　1.访问数据的存取方法
　　全表扫描（Full Table Scans, FTS）
　　通过ROWID的表存取（Table Access by ROWID或rowid lookup）
　　索引扫描（Index Scan或index lookup）：根据索引的类型与where限制条件的不同，有4种类型的索引扫描：
　　索引唯一扫描(index unique scan)：索引返回单值+值不在索引中
　　索引范围扫描(index range scan)：
　　索引全扫描(index full scan)：值在索引中+输出排序
　　索引快速扫描(index fast full scan)：
　　2.目前为止，无论连接操作符如何，典型的连接类型共有3种：
　　排序 - - 合并连接(Sort Merge Join (SMJ) )
　　嵌套循环(Nested Loops (NL) )
　　哈希连接(Hash Join)
　　总结一下，在哪种情况下用哪种连接方法比较好：
　　排序 - - 合并连接(Sort Merge Join, SMJ)：
　　a) 对于非等值连接，这种连接方式的效率是比较高的。
　　b) 如果在关联的列上都有索引，效果更好。
　　c) 对于将2个较大的row source做连接，该连接方法比NL连接要好一些。
　　d) 但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O。
　　嵌套循环(Nested Loops, NL)：
　　a) 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。
　　b) NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。
　　哈希连接(Hash Join, HJ)：
　　a) 这种方法是在oracle7后来引入的，使用了比较先进的连接理论，一般来说，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。
　　b) 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。
　　c) 只能用于等值连接中
According to the CPU costing model:　　Cost = (
　　#SRds * sreadtim +
　　#MRds * mreadtim +
　　#CPUCycles / cpuspeed
　　) / sreadtim
　　where
　　#SRDs - number of single block reads
　　#MRDs - number of multi block reads
　　#CPUCycles - number of CPU Cycles
　　sreadtim - single block read time
　　mreadtim - multi block read time
　　cpuspeed - CPU cycles per second
以下计算执行计划中表扫描的成本实验是在AIX平台下10.2.0.1进行的：　　1、创建测试表
　　SQL>execute dbms_random.seed(0);
　　SQL>create table t1
　　pctfree 99
　　pctused 1
　　as
　　with generator as (
　　select --+ materialize
　　rownum id
　　from all_objects
　　where rownum true,
　　estimate_percent => null,
　　method_opt => 'for all columns size 1'
　　);
　　end;
　　/
　　3、查看实际的IO和CPU成本
　　SQL>alter system flush shared_pool;
　　SQL>alter session set events '10053 trace name context forever, level 2';
　　SQL>select max(val) from t1;
　　SQL>alter session set events '10053 trace name context off';
　　提取跟踪文件相应数值：
　　跟踪文件：
　　cost_io=2221
　　cost_cpu=73954130
　　#Blks = 10146
　　cost=2228.75
　　4、10g下noworkload统计信息以及相关参数查询
　　SQL>select pname, pval1 from sys.aux_stats$
　　where sname='SYSSTATS_MAIN'；
　　PNAME  PVAL1
　　------------------------------ ----------
　　CPUSPEEDNW  795.134
　　IOSEEKTIM  10
　　IOTFRSPEED  4096
　　SQL>show parameter DB_FILE_MULTIBLOCK_READ_COUNT;
　　DB_FILE_MULTIBLOCK_READ_COUNT=16
　　该数值匹配到计算公式中mbrc
　　5、ORACLE10g的成本计算公式
　　cost = (#SRDs * SREADTIM +
　　#MRDs * MREADTIM +
　　#CPUCycles/cpuspeed)/SREADTIM
　　= #SRDs +  --因为是表扫描，所以该值为0
　　#MRDs * MREADTIM/SREADTIM +  -- IO cost
　　#CPUCycles/(cpuspeed * SREADTIM)  -- CPU cost
　　6、手工计算IO成本，看是否与步骤2中IO成本实际值匹配
　　手工计算：
　　cost_io= #MRDs * MREADTIM/SREADTIM，所得数值取最大整数，另外隐含参数_table_scan_cost_plus_one=true，所以cost_io需要添加1。所以10g实际公式为：
　　cost_io=ceiling(#MRDs * MREADTIM/SREADTIM) +1
　　其中：
　　#MRDs=#Blks/mbrc=10146/16
　　MREADTIM=IOSEEKTIM + mbrc*db_block_size/IOTFRSPEED
　　= 10+ 16*8k/4k=42
　　SREADTIM=IOSEEKTIM + db_block_size/IOTFRSPEED
　　=10+8k/4k=12
　　cost_io=ceiling(10146/16*42/12)+1=ceiling(2219.4375)+1=2220+1=2221
　　计算值与步骤2中实际IO成本cost_io=2221一致。
　　7、手工计算CPU成本
　　cpu成本=#CPUCycles/(cpuspeed * SREADTIM)
　　其中
　　#CPUCycles等于10053跟踪文件中cost_cpu=73954130，
　　cpuspeed=795.134MHz=795134Hz
　　SREADTIM=12
　　所以
　　cpu成本=73954130/(795134*12)=7.75
　　8、总的成本计算值
　　cost=cost_io+ cpu成本=2221+7.75=2228.75
　　以上的计算值与实际所获得的成本值完全一致。