Oracle：执行计划+cost成本计算
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'；
PNAMEPVAL1
------------------------------ ----------
CPUSPEEDNW795.134
IOSEEKTIM10
IOTFRSPEED4096
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
以上的计算值与实际所获得的成本值完全一致。
页:
[1]