Oracle不同事务隔离级别分析

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

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. 如何实现数据库级最大并发：更新时采用“字段调用”或其改进方法1。

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. 此隔离级别不意味着所有事务就是像一个接一个串行执行一样。例子参见2。

Read Only 只读级别：

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

其他一些注意事项：

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

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

2.update重启动：

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

参考资料：

  1. 事务处理：概念与技术，7.12节 ↩

  2. Oracle Database 9i/10g/11g编程艺术深入数据库体系结构, 7.2.4节 ↩

文章未经特殊标明皆为本人原创，未经许可不得用于任何商业用途，转载请保持完整性并注明来源链接 《ITechLib》

Oracle查看索引历史使用情况

近期决定对生产上流水表的索引进行优化，目前此表有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. 索引是唯一索引还是非唯一索引

文章未经特殊标明皆为本人原创，未经许可不得用于任何商业用途，转载请保持完整性并注明来源链接 《ITechLib》

Oracle数据库统计信息与直方图

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的方式来优化）。更多直方图的信息见3。实际上由于对于表很大时才会遇到执行计划选择问题，但是由于统计信息采样率一般很低(生产甚至是5%，这也是因为统计信息搜集也很消耗资源和时间)，这种情况下采用直方图效果不好，一般不采用。

参考资料：

  1. Managing Optimizer Statistics http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#i41587 ↩

  2. Oracle Statistic统计信息小结 http://blog.csdn.net/tianlesoftware/article/details/4668723 ↩

  3. Oracle直方图解析 http://blog.csdn.net/javacoffe/article/details/5578206 ↩

文章未经特殊标明皆为本人原创，未经许可不得用于任何商业用途，转载请保持完整性并注明来源链接 《ITechLib》

Oracle数据库索引技术总结

近来生产环境上经常发现某个查询超时，或是耗CPU很高。究其原因还是索引建的不好或是sql盲目复用（本来可以使用count，实际则复用了分页查询的sql）。下面详细介绍下索引的基本原理以及建索引时需要考虑的事项，另外也会介绍下如何分析执行计划。

1.B*树索引的起源

oracle中最常用的索引就是B*树索引，当然oracle对其他索引也支持，包括位图索引、应用域索引（自定义索引）等，这里仅介绍B*树索引，其他索引有兴趣可以查询相关的资料。

一般的树每个节点只有1个元素，每个节点可以有多个孩子，对于二叉树每个节点最多有两个节点。这样当元素非常多的时候树的度就会很大，相应的查找某个元素的时间会很长。如果树的所有节点都可以放在内存中，查询效果很好，但当元素太多时（如几百万、几千万）时，内存无法容纳全部的树节点，需要将绝大部分的树节点放在硬盘上，如果采用二叉树来查找则需要频繁的读取硬盘来比较，具体读取次数和树的度数有关。

为了解决这个问题，就提出了B树的概念。B树又称为多路查找树，它是二叉树的一个延伸。它打破了每个节点只能存储1个元素的限制，每个节点可以保存大于2个节点，其中节点最大的孩子数目成为B树的阶。2-3树是3阶B树，2-3-4树是4阶B树。在实际使用的B树中，B树的阶数和硬盘存储的页大小（数据库块大小）相当。B树可以支持大量数据的查询，比如一棵B树的阶为1001（即1个节点包括1000个关键字），高度为2，则它可以存储10亿个节点，我们只要让根节点持久保存在内存中，那么寻找一个关键字之多需要两次硬盘读取。（一次读索引，一次读数据）。

详细的B树结构介绍可以参考《大话数据结构》8.9节，或在网上搜索相关内容。B树也有一些缺点，例如他的非叶子节点也保存客户的key值，这些key值不会在叶子节点上出现，这样在进行范围扫描时会有一些问题，需要频繁往返叶子节点和分支节点。为了避免这个问题，提出了B+树的概念，B+树中叶子节点保存完整的key值，分支节点仅用于检索，不用于数据获取。另外为了支持范围扫描，在每个叶子中保存下一个叶子节点的指针，可以快速的访问下一个key值。B*树在B+树的基础上增加了”分支节点也保存下一个兄弟节点的指针”同时B*树定义了非叶子节点关键字个数至少是(2/3)*M（M为最大保存key个数），这样可以提高存储效率。兄弟节点可以提高树分裂的效率。B*树分配新节点效率比B+树低，空间使用率高（目前Oracle使用B*树索引）。

2.索引键的数据模型

根据前面的描述，B*树中完整key值保存在叶子节点。叶子节点中保存了索引的键及实际数据的指针，这里的键是指索引列，指针是指ROWID。索引的建是指建立索引的列，可以是多列。例如在(X,Y)上建立索引，可以认为索引的完整存储为（X,Y,ROWID)。其中X,Y默认都是按照升序排列的，如果应用有特殊的查询需求，需要建立降序索引，来对个别字段在索引中降序排列。

下面说下非唯一索引和唯一索引的区别：首先，在一个非惟一索引中，Oracle会把 rowid作为一个额外的列（有一个长度字节）追加到键上，使得键惟一。例如，如果有一个CREATE INDEX I ON T(X,Y)索引，从概念上讲，它就是CREATE UNIQUE INDEX I ON T(X,Y,ROWID)。在一个惟一索引中，根据你定义的惟一性，Oracle不会再向索引键增加rowid。在非惟一索引中，你会发现，数据会首先按索引键值排序（依索引键的顺序） 。然后按rowid升序排序。而在惟一索引中，数据只按索引键排序。另外，两者在效率上也有差别，如果是唯一索引，当查到特定的值后则不会继续向后扫描看有没有其他值。但对于非唯一索引则会进行这个扫描。索引唯一索引性能要好于非唯一索引。当然了索引是不是非唯一的不是性能决定的，而是业务需求决定的。

下面简单说下ROWID，ROWID在oracle中显示时由18位base64编码表示，例如：OOOOOOFFFBBBBBBRRR

OOOOOO：data object number, 对应dba_objects.data_object_id
FFF：file#, 对应v$datafile.file#
BBBBBB：block#
RRR：row#

在物理存储时采用10字节的二进制来表示。通过rowid可以唯一定位一条记录。对于非分区表rowid一般不可变，只有当执行某些特殊指令比如flashback table或是 alter table shrink时会改变rowid，正常的update不会更新rowid，即使某个字段update后数据块放不下，数据转移到其他数据块保存，rowid也不会改变（原来数据存放位置会放置指向新位置的指针，这样查询时相当于两次操作，会影响效率）。顺便一说，对于clob、blob字段oracle在存储时会保存到单独的段中，查询时也会进行多次查询，具体可以参考1（小于4000字节的lob数据可以使用in row模式来避免这种情况)。rowid另一个会变化的场景是分区表，当分区字段发生修改后产生分区变动，这是会发生行移动，rowid也会变化，行移动代价很高。如果某个表没开行移动，在更新分区字段产品分区变动会直接报错。

3.局部索引与全局索引(全局分区索引与非分区索引)2

索引和表一样也可以分区。如果分区是随表对索引完成的分区，即索引分区和表分区一致，称为局部索引。如果按照区间或是散列来自行分区，和表分区不一致，称为全局分区索引（当然也可以不分区）。全局索引保存了所有分区信息的索引。全局分区索引目前oracle仅支持前缀分区，非前缀分区建立时会报错。分区很大程度上是为了管理方便，对性能尤其是查询性能没有提升，甚至会减慢。分区在OLTP系统中更是如此，在OLTP系统应该慎用分区。

在使用局部索引查询时需尽量保证能够分区消除，否则查询会很慢，相当于查询了多个索引来搜索数据。局部索引有一个好处是当表某个分区被删除时，索引也会一并删除，无需重建索引。但是对于全局索引，当删除分区时，索引需要重建，重建时索引是失效的，会影响数据库的性能。局部索引又分为局部前缀索引、局部非前缀索引，这两种差别仅在于是否使用分区键作为索引的前缀（局部非前缀索引的非前缀列也可以包含分区键。由于分区不一定都是散列，也可能是范围等，分区键放在索引中也是有必要的）。如果一个查询将索引作为查询的第一步，局部前缀和非前缀索引没什么区别，具体使用哪种要根据实际业务场景来定，如果对于局部索引没有分区消除，则相当于顺序扫描了多个局部索引，性能也会变差。局部索引只能保证分区内部的唯一性，不能保证全局的唯一性，如果要保证全局的唯一性，分区键必须在约束本身中，例如使用timestamp来分区，就不能对ID列通过局部索引实现唯一性，只能通过全局索引，如果要通过局部索引的话约束中必须加上timestamp。在表分区失效时局部索引和全局索引具有同样的可用性高度，oracle会返回可用的且正确的结果。

分区主要是为了增加系统的可用性和可维护性。删除表分区时会导致全局索引失效，必须重建索引，否则索引不可用。对于这种情况有两种方法，1.跳过索引，这样将不实用这些失效的索引，会影响性能。2.让查询时接收到一个错误。3.更新分区时使用update global indexes来实时更新索引，但这会导致变更时间的增长，且会产生大量的redo和undo信息。而局部索引可以通过滑动窗口技术来实现瞬时切换，不影响可用性。

一般OLTP系统分区可能不会提高性能，甚至还会使性能下降；在数据仓库上建立分区一般会提升查询的效率。建立分区主要出于提高可用性、减少管理负担(索引重建方便、数据删除导入）、改善语句性能（并行DML)。单分区全局索引（不分区）和分区全局索引在查询上没什么差别，增加分区只是为了可用性和可管理性。但分区对于局部索引或是分区索引使用不当，会带来数倍的查询消耗，因为要查询多个分区的这个索引。这些都和具体的查询语句、索引建立方式、表数据维护方式有关。

4.执行计划初步解析

Oracle访问数据的存取方法有下面几种：

  1. 全表扫描（Full Table Scans， FTS）
  2. 通过ROWID的表存取（Table Access by ROWID或rowid lookup）
  3. 索引扫描（Index Scan或index lookup）有4种类型的索引扫描：
    1. 索引唯一扫描（index unique scan）
    2. 索引范围扫描（index range scan）
      在非唯一索引上都是使用索引范围扫描。使用index rang scan的3种情况：
      1. 在唯一索引列上使用了range操作符（> < <> >= <= between）
      2. 在组合索引上，只使用部分列进行查询，导致查询出多行
      3. 对非唯一索引列上进行的任何查询。
  4. 索引全扫描（index full scan） oracle CBO通过分析决定走索引全扫描比表全扫描更快时，通常查询需要排序等时使用。
  5. 索引快速扫描（index fast full scan） 查询结果列全在索引中的，且查询条件未在索引前几列时。

多表联查时可能的连接方式：

下面简单介绍一下（详细参考3 4），连接方式有3种：

  1. Nested Loops
    对于外表返回的每一行都在内表中检索找到它的匹配行。外表为驱动表，内表查询时一定要有索引，驱动表的记录集比较小（<10000)。这样可以响应时间最快。
  2. Hash Join
    散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
  3. Sort Merge Join
    操作步骤为：是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配，因为merge join需要做更多的排序，所以消耗的资源更多。通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。

其他执行计划谓词：

  1. SORT 排序，比较耗资源
  2. FILTER 过滤，如not in、min函数等容易产生。（对于查询条件前几列通过索引匹配后，对于其他在索引中的列会进行FILTER过滤；另外从表里取出数据后一般也会进行过滤）
  3. VIEW视图，大都由内联视图产生
  4. PARTITION VIEW 分区视图。

执行计划结果参数说明：

Statistics(统计信息参数)
-————————
0 recursive calls（系统或用户自动默认执行的调用）
8 db block gets(当前读的块数,即通过update/delete/select for update读时执行的当前读块数)
6 consistent gets(一致性读的块数，oracle默认所有查询都是一致性读)
0 physical reads(物理读:从磁盘读到数据块数量)
0 redo size (重做数:执行SQL的过程中，产生的重做日志的大小)
551 bytes sent via SQL*Net to client 430 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) (在内存中发生的排序) 0 sorts (disk) (在硬盘中发生的排序)

执行计划查看方法5 6：

  1. 仅查看执行计划，不实际执行sql脚本，支持:形式参数

    EXPLAIN PLAN FOR
SELECT * FROM SCOTT.EMP; --要解析的SQL脚本
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

  2. 实际执行sql语句

    set autotrace on/traceonly --（traceonly不显示执行结果）
set timing on
-- 执行需要查看执行计划的SQL语句

  3. 查看历史执行计划

    1. DBA_HIST_SQLTEXT、DBA_HIST_SQL_PLAN、DBA_HIST_SQLSTAT、DBA_HIST_SNAPSHOT
    2. DBMS_XPLAN包
      1. select * from table(dbms_xplan.display);
      2. select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));
      3. select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'));
      4. select * from table(dbms_xplan.display_awr('sql_id'));

参考资料：

  1. Oracle_Database_9i/10g/11g编程艺术深入数据库体系结构，12.7节 ↩

  2. Oracle_Database_9i/10g/11g编程艺术深入数据库体系结构，13.3节 ↩

  3. 多表连接的三种方式详解http://blog.csdn.net/tianlesoftware/article/details/5826546 ↩

  4. 如何看懂ORACLE执行计划http://blog.csdn.net/java3344520/article/details/5515497 ↩

  5. Oracle执行计划详解http://www.cnblogs.com/jianggc/articles/2029854.html ↩

  6. ORACLE数据库查看执行计划http://www.cnblogs.com/kerrycode/archive/2012/05/24/2517210.html ↩

文章未经特殊标明皆为本人原创，未经许可不得用于任何商业用途，转载请保持完整性并注明来源链接 《ITechLib》

不积跬步，无以至千里；不积小流，无以成江海。

荀子《劝学篇》

页面

分类

友情链接

  1. GitHub
  2. Ruby On Rails
  3. Bootstrap