解析Oracle数据扫描 Oracle SQL查询优化 数据读取方式

解析Oracle数据扫描

——Oracle SQL查询优化

（1）

数据读取方式

1、 Oracle数据读取操作：

1.1数据读取操作类型：

        在任何的数据库中，当我们发起一个SQL语句进行数据查询时，SQL执行引擎为了能够返回我们需要的数据，都会进行数据扫描。这个扫描可能直接发生在特定的内存空间中，也可能发生在存放数据的磁盘上。但无论如何归结起来，数据扫描的类型大致可以分为两种，即顺序扫描和随机读取。

       顺序扫描主要发生在大范围数据读取时，而随机扫描主要会发生在定位单条记录数据时。而且顺序扫描发生时通常会伴随磁盘I/O的发生，也就是说从内存中直接读取数据时通常不会发生数据顺序扫描；但是随机读取却可能发生在内存数据读取时也可能发生在磁盘数据读取时。当SQL执行数据读取时，数据能够从数据缓冲区中命中时，此时通常就会发生内存随机数据读取；但是当通过索引访问数据，而数据又没有缓存在数据缓冲区中时，此时就会通过索引回表去读取索引对应的数据，此时就会发生磁盘随机I/O读取。

       通常如果数据读取数量不大，无论是数据扫描还是随机读取，数据读取性能都不会太差，都能够很快返回数据。但是随着读取数据范围的扩大，那么随机读取就会表现出对数据读取性能的极大损害。如果发生的随机读取都是内存数据读取，那么这种损害的表现还不是马上就会显现，但是随着时间的推移以及数据读取范围的不断扩大，那么即便是内存数据读取也会表现出性能的急剧下降。因为数据在内存中，是被复杂的数据结构来管理的，在内存中操作这些数据时不可避免的要使用各种复杂的数据结构的操作算法，同时可能还要伴随着各种并发控制策略，因此大数据量的内存随机读取代价其实是不菲的。但是上述情况通常是不常见的，更加常见的是另外一种情况，那就是发生大量的随机磁盘I/O读取，通常这种情况会发生在，读取数据的索引选择性太差或者索引的聚簇因子过高等原因造成的，这种情况一旦出现那么对系统的整体性能造成的伤害会非常大。因为随机I/O所读写的数据都是一些不连续的数据，因此要进行大量的随机I/O，那么不可避免会造成物理磁盘不停的进行数据读写寻道，这非常可能形成系统整体的I/O瓶颈。在Oracle中如果出现了由于大量随机I/O造成的数据读取性能问题，通常会伴随有较高的诸如buffer_busy_wait、db_sequence_read等待事件时间。因此如果涉及较大范围的数据读取，最好能够驱使数据库的执行引擎不要进行随机I/O读取，而进行数据扫描读取，也就是说在数据范围较大读取发生时，应该驱使数据库出现连续I/O操作，而不要出现不连续I/O操作，因为扫描操作其实是一种连续I/O操作。

       扫描操作也分为两种情况，即全部范围扫描和局部范围扫描。所谓全部范围扫描，是指一次性扫描满足“驱动查询条件”范围的所有数据，然后在扫描的结果基础上应用查询的过滤条件进行数据过滤进而得到最终查询结果；局部范围扫描是指在进行数据扫描时，不会一次性扫描满足“驱动查询条件”范围的所有数据，而是只扫描满足驱动查询条件一定范围的数据，这个扫描范围远远小于整体数据范围，在Oracle中称为批量数组Array_Size，当扫描的数据足以填满批量数组时，查询就立刻返回查询结果，而且在用户再次提出数据浏览要求之前停止查询的执行。

由此可见局部范围扫描能使扫描的数据量在很大程度上得到缩减，并能快速的返回查询结果，提高查询应用的响应性。局部范围扫描并不是真正的缩减了查询结果数据的范围，而是利用了大多数用户并不需要一次性浏览所有数据的特点，因此只返回部分数据供用户查看。因此在一个具有大数据量而又要求系统具有很高响应性，同时大多数查询操作涉及较大范围数据的应用系统中，应该更多的引导数据库的执行引擎进行数据局部范围扫描，来提升数据查询的性能和整体系统的响应性。

        全部范围扫描和局部范围扫描的处理过程如下图所示：

 

全局数据扫描

 

局部数据扫描

如图所示全部范围扫描，首先扫描符合驱动查询条件全部范围的数据，然后在该范围内应用过滤条件，并将过滤之后的数据保存在临时的存储空间中（可能位于内存也可能位于外存），然后对临时数据进行二次数据加工，之后填充批量数组，数组填充满后返回结果并结束查询直到下次请求出现。

在局部范围扫描中，也是先按照驱动查询条件扫描数据，与此同时通过应用过滤查询条件，对数据进行过滤，并且将满足条件的行直接填充到批量数组中，数组被填充满之后返回结果并终止查询。因此无论查询数据的范围有多大，局部范围扫描都会比较快速返回查询结果。

1.2局部范围扫描的条件：

        在有的情况下如果能够将SQL的执行方式从全局范围扫描转变成局部范围扫描，那么查询的执行速度就可能得到大幅度的提高。但是并不是所有的SQL都可以直接按照局部范围扫描的方式进行，因此要求我们应该知道SQL查询直接进行范围扫描的条件，同时还要知道如何将不能直接进行局部范围扫描的SQL查询，引导为进行局部范围扫描的方法。

什么样的查询可以直接使用局部范围扫描呢？通常来说就是通过具有较高选择性的过滤条件，进行的数据范围扫描（range scan），同时查询的结果不需要在见到所有范围内的数据后才能得出结果的情况下，SQL执行优化器都可以引导执行引擎使用局部范围扫描。这些操作包括：通过主键读取数据、通过唯一索引读取数据、以及通过各种索引（具有较高的选择性）进行数据扫描，这些操作都可以直接使用局部范围扫描进行数据读取。

        相反那些需要读取范围内全部数据的操作，无法直接使用局部范围扫描，这些操作都是运行在数据全局扫描模式下的。这些操作包括：分组函数（count,sum,min,max等）、引发排序的操作（order by,group by,distinct等）、使用Having进行分组过滤的查询、集合操作（union、minus、intersect等）、归并连接操作merge join、引发磁盘排序的hash join(non in memory hash join)。通常如果你在某条SQL语句的执行计划中发现了”sort”执行计划，那么通常就可以断定这个语句中存在非局部扫描，可能是整条语句无法局部扫描，也可能是该域中的某个部分无法进行局部扫描（如：子查询或者表连接操作），这时一种语句优化方式就是看一看能否将非局部扫描部分，通过引导方法引导成局部数据扫描（具体的引导方法，在后面相关部分论述）。

       这里还要针对集合操作强调一下，集合操作中大部分无法进行局部范围扫描，但是有一个例外就是union all操作，因为union all操作不需要对结果进行排重，因此也就省去了对数据的整体获取或排序，所以该操作可以按照局部范围扫描的方式进行。

1.3优化器模式对局部范围扫描的影响：

       对于某个SQL语句，即使逻辑上可以实现局部范围扫描，也会随着优化器模式的不同而存在一定差异。在RBO或者CBO的FIRST_ROWS模式下，基本上是按照局部范围扫描来制定执行计划；在CBO的ALL_ROWS模式下，基本上是按照全局范围扫描来制定执行计划。RBO模式是一种过时的优化器技术，当今只会在某些极其特殊的场合来使用它来发挥一些其特殊的作用，因此我们在此对它不做讨论。

    CBO的FIRST_ROWS模式所追求的是SQL操作部分范围最优化；ALL_ROWS追求的是SQL操作整体范围最优化，即追求“Best Throughput”。通过上述对数据局部范围扫描的描述，那么我们是不是就要将优化器设置为FIRST_ROWS模式而后万事大吉呢？事实上并不是这样。

       根据当今主流服务器硬件平台处理能力的发展和数据库系统等支撑软件的技术发展，以及系统架构高可用特性的追求，ALL_ROWS模式是比较符合发展趋势的优化器技术，因为它是追求整体最优，追求整个系统的性能成本的平衡。我想这也是为什么Oracle10G之后，将ALL_ROWS设置为优化器默认模式的一个重要原因。

那么我们正确的优化器设置策略应该是什么呢？我认为正确的优化器设置策略应该是使用 ALL_ROWS模式，然后在优化器结果无法令人满意的必要情况下，有的放矢的利用优化器引导技术（如：Hints、SQL语句调整重写等），来诱导优化器来采用正确的数据扫描方式，进行数据处理以便获得令我们满意的结果。