1、oracle数据库访问的方法
在执行计划中，我经常看到有很多访问数据的方法如：
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18M| 1589M| | 189K
|* 1 | HASH JOIN | | 18M| 1589M| 11M| 189K
| 2 | INDEX FAST FULL SCAN| UNI_MY_OBJ_ID | 661K| 3874K| | 568
| 3 | TABLE ACCESS FULL | MYTEST | 18M| 1483M| | 64386
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 110 | 9900 | 121 (
| 1 | NESTED LOOPS | | 110 | 9900 | 121 (
|* 2 | INDEX UNIQUE SCAN | UNI_MY_OBJ_ID | 1 | 6 | 2 (
| 3 | TABLE ACCESS BY INDEX ROWID| MYTEST | 110 | 9240 | 119 (
|* 4 | INDEX RANGE SCAN | MY_OBJ_ID | 116 | | 2 (
--------------------------------------------------------------------------------
TABLE ACCESS FULL，全表扫描，对表进行全表访问
INDEX FAST FULL SCAN，全索引扫描，对某个索引进行全部扫描
INDEX UNIQUE SCAN，唯一索引扫描，对某个主键或者唯一索引指定的值进行检索
INDEX RANGE SCAN，按一定范围对索引进行检索
下面我就来说说访问数据的方式：
--全表扫描（Full Table Scans, FTS）
是对表的所有块进行全部扫描，通过ROWID的表存取（Table Access by ROWID或rowid lookup）获取表的全部数据
--索引扫描（Index Scan或index lookup），我们列出几种情况
a、索引范围扫描(index range scan)：范围扫描，如where后面的between，连接中的非唯一等值条件时用得较多
b、索引全扫描(index full scan)：需要检索的值包含在索引中
c、索引快速扫描(index fast full scan)：在连接条件中，只需要某表的索引做判断时，使用的是快速索引扫描
d、索引唯一扫描(index unique scan)：主键或者唯一索引等值
e、索引跳跃扫描(INDEX SKIP SCAN)：通常使用在组合索引中，对索引字段中，排在第二个及以后的值进行判断
2.oracle数据库连接的方式
--合并连接(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) 只能用于等值连接中
3.oracle成本计算
依据cpu消耗使用的成本计算
--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
--创建测试表
CREATE TABLE "HDNC57"."MYTEST"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"STATUS" VARCHAR2(7),
"TIMESTAMP" VARCHAR2(19)
)
插入了大概1000多万的数据.
--进行统计分析
SQL> EXEC dbms_stats.gather_table_stats('TEST_USER','MYTEST',cascade =>true,estimate_percent => 50,method_opt => 'for all columns size 1' );
PL/SQL procedure successfully completed.
--查看实际的IO和CPU成本
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set events '10053 trace name context forever, level 2';
Session altered.
SQL> select count(*) from test_user.mytest where object_id >= 400000;
COUNT(*)
----------
154560
提取跟踪文件相应数值：
跟踪文件：
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for MYTEST[MYTEST]
Column (#3): OBJECT_ID(
AvgLen: 5 NDV: 165297 Nulls: 0 Density: 0.000006 Min: 2 Max: 417809
Table: MYTEST Alias: MYTEST
Card: Original: 18511062.000000 Rounded: 789145 Computed: 789144.97 Non Adjusted: 789144.97
Access Path: TableScan
Cost: 64361.19 Resp: 64361.19 Degree: 0
Cost_io: 64195.00 Cost_cpu: 6130585710
Resp_io: 64195.00 Resp_cpu: 6130585710
Access Path: index (index (FFS))
Index: MY_OBJ_ID
resc_io: 11741.00 resc_cpu: 3501331838
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 11835.92 Resp: 11835.92 Degree: 1
Cost_io: 11741.00 Cost_cpu: 3501331838
Resp_io: 11741.00 Resp_cpu: 3501331838
Access Path: index (IndexOnly)
Index: MY_OBJ_ID
resc_io: 1850.00 resc_cpu: 173299064
ix_sel: 0.042631 ix_sel_with_filters: 0.042631
Cost: 1854.70 Resp: 1854.70 Degree: 1
Best:: AccessPath: IndexRange
Index: MY_OBJ_ID
Cost: 1854.70 Degree: 1 Resp: 1854.70 Card: 789144.97 Bytes: 0
oracle基于cbo分析比较后，认为采用index (IndexOnly)最优，所以得出了索引范围扫描的执行计划成本，根据索引范围扫描的成本为1855，具体的执行计划如下
SQL> explain plan for SELECT COUNT(*) "COUNT(*)" FROM "TEST_USER"."MYTEST" "MYTEST" WHERE "MYTEST"."OBJECT_ID">=400000;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2285605767
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1855 (1)| 00:00:23 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| MY_OBJ_ID | 789K| 3853K| 1855 (1)| 00:00:23 |
-------------------------------------------------------------------------------
所以根据cbo，采用了范围索引扫描。
4、手动计算成本
SQL> select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 3074.07407
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR
SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 30
该数值匹配到计算公式中mbrc
--根据执行计划来看，采用的是索引范围扫描，手动根据公司计算如下：
COST = IO COST + CPUCOST
IO COST = blevel+(leaf_blocks*ix_sel)+(clustering_factor*ix_sel_with_filters)
CPU COST = #cpucycles/(cpuspeed或者cpuspeednw) / 1000 / sreadtim
sreadtim = ioseektim + db_block_size / iotfrspeed = 10 + 8192 / 4096 = 12
IO COST计算需要统计信息
统计信息如下：
TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
-------------- ---------------- ---------- ----------- ------------- ----------------- ----------
MYTEST MY_OBJ_ID 2 43346 165297 18780224 18780270
IO COST = 2 + 43346 * 0.042631 = 1850
cpu成本如下：
CPU COST = 173299064 / 3074.07407 / 1000 / 12 = 4.69
所以总体成本为：
1849.883 + 4.69 = 1850 + 5 = 1855
说明的几点：
--IO COST = blevel+(leaf_blocks*ix_sel)+(clustering_factor*ix_sel_with_filters) 的计算过程中，(clustering_factor*ix_sel_with_filters)值为0，因为这一段表示的主要是对索引进行范围扫描，无再过滤可能
--resc_cpu: 173299064 值为毫秒，10053里面评估出来的值
--ix_sel：由于where条件中只有大于> ，所以他是开放，根据索引的统计：
AvgLen: 5 NDV: 165297 Nulls: 0 Density: 0.000006 Min: 2 Max: 417809，绑定的值是400000
(max - 当前值) / (max - min) = (417809 - 400000) / 417807 = 17809 / 417807 = 0.042629，与10053略有偏差。
5、其它扫描方式的成本计算
--全表扫描：
COST = IO COST + CPUCOST
IO COST = ceil(blocks/mbrc)*mreadtim/sreadtim
CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim
IO COST 和CPU COST前半部分主要计算所花费的时间，最后除以单块访问的时间用于换算成单块读需要读取的块数。
--索引唯一扫描：
COST = IO COST + CPUCOST
IO COST = blevel +1 + 1
CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim
IO COST 第一步+1是访问的叶子块，最后一步+1是统计回表的块，因为唯一索引回表块数也是1。
--索引全扫描：
COST = IO COST + CPUCOST
IO COST = blevel+leaf_blocks+(clustering_factor*ix_sel_wiht_filters)
CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim
索引全扫描是单块顺序读，扫描的结果有序；
(clustering_factor*ix_sel_wiht_filter)表示回表的IO COST，如果不回表则不需要计算这一步，索引全扫描可以回表，但是索引快速全扫描不会回表；
ix_sel：因为索引全扫描是扫描所有的叶子块，不需要涉及到索引块的选择率，所以ix_sel是1；
ix_sel_with_filters:既然走索引全扫描，则该索引就不会存在过滤的可能，因为如果索引有过滤的可能，则直接走该单索引的范围扫描，无再过滤值，所以ix_sel_with_filters也是1。
--索引快速全扫描：
COST = IO COST + CPUCOST
IO COST = ceil(leaf_blocks/ mbrc)*mreadtim/sreadtim
CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim
索引快速全扫描是多块读，扫描结果无序，不需要回表；
ix_sel、ix_sel_with_filters 这2个参数在索引快速全扫描中无效，因为索引快速全扫描是多块读且不需要回表。
--索引跳跃扫描：
COST = IO COST + CPUCOST
若叶子块数少于前导列的不同值数，IO COST以叶子块数来计算。
IO COST = blevel+ leaf_blocks+(CLUSTERING_FACTOR*ix_sel_filter)
若叶子块数多于前导列的不同值数，IO COST以前导列不同值数来计算。
IO COST = blevel+前导列不同值数*ceil(leaf_blocks/distinct_keys) +( CLUSTERING_FACTOR*ix_sel_with_filters)
CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim
ix_sel=ix_sel_with_filters：是where条件中走该索引谓词（一个/多个相乘）的选择率
跳跃扫描不仅仅出现在无第一个前导列时，如果复合索引包括三个字段，where条件中没有第二个字段的查询列，但有第一和第三的查询列时，
其实是可以走索引跳跃扫描，相当于跳过第二个字段，ix_sel和ix_sel_with_filters就是第一列和第三列的选择率相乘。而且也等于10053事件trace的ss sel这个参数；
前导列不同值数非常大时走索引跳跃扫描的成本会很高。