在具体介绍隔离级别之前，谈谈为什么要有隔离级别。数据库作为应用的核心组件，除了保证数据一致性之外，另一个很重要的指标就是并发性（也是很多数据库对外宣传的口号）。但中所周知，数据库高的一致性比如避免脏数据，保证数据可重复读，或者可串行化，都需要进行一定的数据隔离，这也就意味着加锁，加锁肯定会丧失并发性。所以为了保证并发性，提出了不同的隔离级别，或者叫做弱化的一致性。这些隔离级别在不同的数据库中的表现方式各异，对性能的影响也各有差异，远没有表面看上去那么简单。

Oracle的隔离级别分为Read Committed，Serializable，Read Only，其中前两种是ANSI/ISO SQL92标准定义的隔离级别。它不支持Read Uncommited,它在Read Committed级别支持一个很好的特性，读一致性（这个一般在IOS级别的Repeatable Read级别支持）。不过Oracle的Read Committed不能完全代替Repeatable Read，因为它不能避免“第二类丢失更新”（这种在Repeatable Read中会报错的）。我们可以在应用层通过乐观锁来避免”第二类丢失更新“，这也就是说当你选择一款数据库的时候也在一定程度上决定了程序的设计。

下面分别介绍各个隔离级别的特点：

Read Committed 读已提交：

1. 不会出现脏读、第一类丢失更新（回滚其他事务已提交的数据）；
2. 支持语句级的读一致性；例如在统计一个表的数据时及时这些数据发生了变更也能得到正确的结果（查询发起时刻的结果）。这个是通过多版本技术实现的。
3. 读不会阻塞写。在读数据时不会对数据加锁。
4. 在Insert/Update时（1）如果有未提交的有冲突的项，则阻塞。（2）如果有已提交的有冲突的则报错。
5. 可以实现语句级别的查询一致性：同一个语句得到的结果肯定是数据库中某个时刻的一致的结果。
6. 不保证多个语句查询的一致性：例如分别查明细和汇总表，得到的结果可能是不一致的。
7. 如何保证查询结果后续未被更新（避免第二类丢失更新）：（1）采用乐观锁（vno等）当更新失败时报错；（2）采用Select for update 查询，以保证记录级的锁定。此语句的隔离效果和直接更新但未提交一样。
8. 如何实现数据库级最大并发：更新时采用“字段调用”或其改进方法¹。

Serailizable 可串行化：

1. Oracle利用多版本化技术可以做到在你问之前（执行sql之前）已经知道答案了，从而保证可串行化。而不是采用锁的技术，采用锁的技术性能会很差。
2. 支持可重复读、不允许幻想读（是指多次统计或范围读取仍能得到一致的结果）、第二类丢失更新（提交时覆盖别人已提交的数据，天然支持，无需使用乐观锁等技术）。
3. 读不会阻塞写。
4. 如果读取时某个数据已经被更新了，数据库会利用UNDO日志恢复到事务开始时刻，并返回那时的结果。如果根据UNDO日志无法恢复，则报错ORA-0155:snapshot too old。
5. 如果更新时发现某个数据已经被更新，则报错ORA-08177:can’t Serailize access for this transaction。可以看出如果要使用此级别，(1) 一般要保证没有其他人修改相同的数据； (2) 需要事务级的读一致性；（3) 事务都很短（有利于保证第1点）。
6. 此隔离级别下无法看到其他事务提交的所有更新，包括已经提交的。如果应用需要保证数据完整性可能会有问题，因为其他事务可能已经修改了事务，但你却看不到。
7. 此隔离级别不意味着所有事务就是像一个接一个串行执行一样。例子参见²。

Read Only 只读级别：

和Serailizable级别一致，只是不允许进行数据库更新操作。

其他一些注意事项：

1.热表的超出预期的IO：

由于Oracle利用多版本技术来保证读取数据的一致性，在对热表进行读操作时，在Serailizable隔离级别（或是需要执行很长时间的sql时）下可能会导致读取大量的UNDO块来恢复数据，从而导致大量的IO。

2.update重启动：

如果在更新时部分数据已经被其他事务更新（Read Committed级别），此时Oracle会回退update，然后尝试使用Select for update模式进行重启动更新。一般情况下这对应用是透明的，但是有些数据如触发器等是不能回滚的，另外如果我们的更新记录数过大重启动更新还是很影响效率的。

参考资料：

在具体介绍隔离级别之前，谈谈为什么要有隔离级别。数据库作为应用的核心组件，除了保证数据一致性之外，另一个很重要的指标就是并发性（也是很多数据库对外宣传的口号）。但中所周知，数据库高的一致性比如避免脏数据，保证数据可重复读，或者可串行化，都需要进行一定的数据隔离，这也就意味着加锁，加锁肯定会丧失并发性。所以为了保证并发性，提出了不同的隔离级别，或者叫做弱化的一致性。这些隔离级别在不同的数据库中的表现方式各异，对性能的影响也各有差异，远没有表面看上去那么简单。

Oracle的隔离级别分为Read Committed，Serializable，Read Only，其中前两种是ANSI/ISO SQL92标准定义的隔离级别。它不支持Read Uncommited,它在Read Committed级别支持一个很好的特性，读一致性（这个一般在IOS级别的Repeatable Read级别支持）。不过Oracle的Read Committed不能完全代替Repeatable Read，因为它不能避免“第二类丢失更新”（这种在Repeatable Read中会报错的）。我们可以在应用层通过乐观锁来避免”第二类丢失更新“，这也就是说当你选择一款数据库的时候也在一定程度上决定了程序的设计。

下面分别介绍各个隔离级别的特点：

Read Committed 读已提交：

1. 不会出现脏读、第一类丢失更新（回滚其他事务已提交的数据）；
2. 支持语句级的读一致性；例如在统计一个表的数据时及时这些数据发生了变更也能得到正确的结果（查询发起时刻的结果）。这个是通过多版本技术实现的。
3. 读不会阻塞写。在读数据时不会对数据加锁。
4. 在Insert/Update时（1）如果有未提交的有冲突的项，则阻塞。（2）如果有已提交的有冲突的则报错。
5. 可以实现语句级别的查询一致性：同一个语句得到的结果肯定是数据库中某个时刻的一致的结果。
6. 不保证多个语句查询的一致性：例如分别查明细和汇总表，得到的结果可能是不一致的。
7. 如何保证查询结果后续未被更新（避免第二类丢失更新）：（1）采用乐观锁（vno等）当更新失败时报错；（2）采用Select for update 查询，以保证记录级的锁定。此语句的隔离效果和直接更新但未提交一样。
8. 如何实现数据库级最大并发：更新时采用“字段调用”或其改进方法¹。

Serailizable 可串行化：

1. Oracle利用多版本化技术可以做到在你问之前（执行sql之前）已经知道答案了，从而保证可串行化。而不是采用锁的技术，采用锁的技术性能会很差。
2. 支持可重复读、不允许幻想读（是指多次统计或范围读取仍能得到一致的结果）、第二类丢失更新（提交时覆盖别人已提交的数据，天然支持，无需使用乐观锁等技术）。
3. 读不会阻塞写。
4. 如果读取时某个数据已经被更新了，数据库会利用UNDO日志恢复到事务开始时刻，并返回那时的结果。如果根据UNDO日志无法恢复，则报错ORA-0155:snapshot too old。
5. 如果更新时发现某个数据已经被更新，则报错ORA-08177:can’t Serailize access for this transaction。可以看出如果要使用此级别，(1) 一般要保证没有其他人修改相同的数据； (2) 需要事务级的读一致性；（3) 事务都很短（有利于保证第1点）。
6. 此隔离级别下无法看到其他事务提交的所有更新，包括已经提交的。如果应用需要保证数据完整性可能会有问题，因为其他事务可能已经修改了事务，但你却看不到。
7. 此隔离级别不意味着所有事务就是像一个接一个串行执行一样。例子参见²。

Read Only 只读级别：

和Serailizable级别一致，只是不允许进行数据库更新操作。

其他一些注意事项：

1.热表的超出预期的IO：

由于Oracle利用多版本技术来保证读取数据的一致性，在对热表进行读操作时，在Serailizable隔离级别（或是需要执行很长时间的sql时）下可能会导致读取大量的UNDO块来恢复数据，从而导致大量的IO。

2.update重启动：

如果在更新时部分数据已经被其他事务更新（Read Committed级别），此时Oracle会回退update，然后尝试使用Select for update模式进行重启动更新。一般情况下这对应用是透明的，但是有些数据如触发器等是不能回滚的，另外如果我们的更新记录数过大重启动更新还是很影响效率的。

参考资料：

近期决定对生产上流水表的索引进行优化，目前此表有18个索引，其中8个全局索引。这些索引的产生有历史原因了，因为当时大家对索引和分区也不太清楚，后来查询效率有问题时所作的优化也只是增加索引，进而导致现在索引数量过多，大量索引重复或不优（索引后面字段根本没有用）。

首先要确认有哪些查询会用到某个索引（可以先针对于某些可能有问题的索引进行排查），具体查询过程如下：

1.查询v$sql_plan视图查看当前有哪些使用此索引

---- object_name 为索引的名称
select sql_id from v$sql_plan where object_name='T1_INDEX_N1';  
select sql_fulltext from v$sql where sql_id='sql_id';

或是直接查

select sql_id,sql_fulltext from v$sql where sql_id in (select sql_id from v$sql_plan where object_name='T1_INDEX_N1');

如果sql_fulltext未显示完整，可以在sqlplus中配置long的长度，如下:

SQL> set linesize 200
SQL> set pagesize 1000
SQL> set long 10000

2.查询历史执行计划表dba_hist_sql_plan查看历史上有哪些查询使用了此sql

---- object_name 为索引的名称
select SQL_ID,PLAN_HASH_VALUE,OPERATION,OPTIONS,OBJECT_NAME from dba_hist_sql_plan where object_name='<OBJECT_NAME>';
select sql_text from dba_hist_sqltext where sql_id='<sql_id>';

或直接联查：

select sql_id,sql_text from dba_hist_sqltext where sql_id in ( select SQL_ID from dba_hist_sql_plan where object_name='<OBJECT_NAME>');

3. 索引优化需要考虑的地方

了解了哪些查询会使用这些索引,然后结合这个表中所有索引的情况来判断是否需要优化。需要考虑如下几方面：

1. 每个索引的字段，索引字段顺序，是否存在索引重复
2. 索引字段的区分度如何
3. 不同索引字段是否不是独立的，而是有相关性的
4. 索引字段取值是否有严重倾斜
5. 当前表是否有分区，分区方式如何
6. 索引是局部索引还是全局索引
7. 索引是唯一索引还是非唯一索引

近期决定对生产上流水表的索引进行优化，目前此表有18个索引，其中8个全局索引。这些索引的产生有历史原因了，因为当时大家对索引和分区也不太清楚，后来查询效率有问题时所作的优化也只是增加索引，进而导致现在索引数量过多，大量索引重复或不优（索引后面字段根本没有用）。

首先要确认有哪些查询会用到某个索引（可以先针对于某些可能有问题的索引进行排查），具体查询过程如下：

1.查询v$sql_plan视图查看当前有哪些使用此索引

---- object_name 为索引的名称
select sql_id from v$sql_plan where object_name='T1_INDEX_N1';  
select sql_fulltext from v$sql where sql_id='sql_id';

或是直接查

select sql_id,sql_fulltext from v$sql where sql_id in (select sql_id from v$sql_plan where object_name='T1_INDEX_N1');

如果sql_fulltext未显示完整，可以在sqlplus中配置long的长度，如下:

SQL> set linesize 200
SQL> set pagesize 1000
SQL> set long 10000

2.查询历史执行计划表dba_hist_sql_plan查看历史上有哪些查询使用了此sql

---- object_name 为索引的名称
select SQL_ID,PLAN_HASH_VALUE,OPERATION,OPTIONS,OBJECT_NAME from dba_hist_sql_plan where object_name='<OBJECT_NAME>';
select sql_text from dba_hist_sqltext where sql_id='<sql_id>';

或直接联查：

select sql_id,sql_text from dba_hist_sqltext where sql_id in ( select SQL_ID from dba_hist_sql_plan where object_name='<OBJECT_NAME>');

3. 索引优化需要考虑的地方

了解了哪些查询会使用这些索引,然后结合这个表中所有索引的情况来判断是否需要优化。需要考虑如下几方面：

1. 每个索引的字段，索引字段顺序，是否存在索引重复
2. 索引字段的区分度如何
3. 不同索引字段是否不是独立的，而是有相关性的
4. 索引字段取值是否有严重倾斜
5. 当前表是否有分区，分区方式如何
6. 索引是局部索引还是全局索引
7. 索引是唯一索引还是非唯一索引

Oracle 10g及以后的版本均采用CBO，而取消了之前的RBO。在CBO中数据库会对数据表的统计信息进行收集，并且基于收集的结果进行执行计划的选择。执行计划中的Cost的计算方式默认为CPU+I/O两者之和。 所以一般我们看执行计划时，Cost越低，SQL的性能就越好。

下面主要介绍统计信息收集的内容^{1 2}：

1.表的统计信息

包含表行数，使用的块数，空的块数，块的使用率，行迁移和链接的数量，pctfree，pctused的数据，行的平均大小：

SELECT 
	NUM_ROWS, --表中的记录数
	BLOCKS, --表中数据所占的数据块数
	EMPTY_BLOCKS, --表中的空块数
	AVG_SPACE, --数据块中平均的使用空间
	CHAIN_CNT, --表中行连接和行迁移的数量
	AVG_ROW_LEN --每条记录的平均长度
FROM USER_TABLES;

2.索引列的统计信息

包含索引的深度（B-Tree的级别），索引叶级的块数量，集群因子（clustering_factor), 唯一值的个数。

SELECT 
	BLEVEL, --索引的层数
	LEAF_BLOCKS, --叶子结点的个数
	DISTINCT_KEYS, --唯一值的个数
	AVG_LEAF_BLOCKS_PER_KEY, --每个KEY的平均叶块个数
	AVG_DATA_BLOCKS_PER_KEY, --每个KEY的平均数据块个数
	CLUSTERING_FACTOR --群集因子
FROM USER_INDEXES;

clustering factor反映的是数据块顺序程度和索引叶子节点的相合程度。索引叶子节点是有序的，clustering factor反映了索引对应的数据行在数据块里面的离散程度，即相同或者相邻的索引值行是不是在相同的数据块上。可见通过通过索引重建无法改变这个因子。这个因子主要影响index range scan和对于 index equal的查询影响相对小些。

3.列的统计信息

包含唯一的值个数，列最大小值，密度（选择率），数据分布（直方图信息），NULL值个数

SELECT
	NUM_DISTINCT, --唯一值的个数
	LOW_VALUE, --列上的最小值
	HIGH_VALUE, --列上的最大值
	DENSITY, --选择率因子（密度）
	NUM_NULLS, --空值的个数
	NUM_BUCKETS, --直方图的BUCKET个数
	HISTOGRAM --直方图的类型
FROM USER_TAB_COLUMNS;

4.直方图

直方图就是用来分析数据在列上的分布。直方图参数有如下几个：统计比例（采样率，一般为20%），要统计的列，直方图bucket的个数（为1相当于不统计直方图），并发度。

直方图可以反映列值的分布，在oracle中分为两种：1.频率直方图（针对列的NUM_DISTINCT较少时使用）。2.高度平衡直方图（针对NUM_DISTINCT大于BUCKET的情况）。oracle支持的BUCKET最大为254。

oracle在做统计更新时会根据直方图的统计信息来进行COST值的计算进而影响执行计划的选择，一般建议在当Where子句引用了列值分布存在明显偏差的列时，导致oracle执行计划选择错误时使用（这种情况也可以采用hint的方式来优化）。更多直方图的信息见³。实际上由于对于表很大时才会遇到执行计划选择问题，但是由于统计信息采样率一般很低(生产甚至是5%，这也是因为统计信息搜集也很消耗资源和时间)，这种情况下采用直方图效果不好，一般不采用。

参考资料：