首页 > 代码库 > Oracle性能分析4：数据访问方法之全扫描
Oracle性能分析4：数据访问方法之全扫描
SQL语句执行时，Oracle的优化器会根据统计信息确定表的访问方式，一般来说，有两种基本的数据访问方式：1）全扫描。在全扫描（全表扫描或者快速全索引扫描）中，多个块被读入到一个IO运算中。2）索引扫描。索引扫描首先扫描索引叶子块以取得特定的行id（rowid），然后利用这些行id来访问父表取得实际的行数据，访问通过单块读取来完成。这里主要讲解全扫描方式，后面将介绍索引扫描。
使用全扫描
当对一个表进行全扫描时，会将表中所有数据块（block）取出并进行处理，筛选出符合条件的数据。注意Oracle必须将整个数据块（block）中的数据读到内存中，再取得符合条件的数据。因此Oracle的优化器需要关心两个信息：获取块的数量和每个块中舍弃的数据量。优化器将根据这两个信息来判断是否使用全扫描，首先我们来看看获取块的数量怎么影响优化器的选择。
获取块的数量
总的来说，如果查询需要取出表的大部分数据块，则应该采用全扫描。但由于很难评估查询将取出的表的数据块的数量，因此在使用全扫描上存在很多这样的“经验法则”：当你的查询会取出表中x%的数据行，则应该选择全扫描。这些法则有一定的道理，但是并不准确，因为当取出的数据行较大时，自然取出的数据块也会较大，这时采用全扫描并没有问题，但有时虽然取出的数据行较小，会取出的数据块也可能会较大，实际上这时也应该采用全扫描，但这些“经验法则”则不再生效。我们看看下面具体的例子。
我们创建一个表T1：
create table t1 as select trunc((rownum - 1) / 100) id, rownum value from dba_source where rownum <= 10000
然后为T1创建索引：
create index idx_t1_id on t1(id)
然后为T1收集统计信息：
BEGIN
dbms_stats.gather_table_stats(user,
't1',
method_opt => 'FOR ALL COLUMNS SIZE 1',
cascade => TRUE);
END;然后我们执行查询：
select * from t1 where id = 0
该查询的执行计划如下：
SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS BY INDEX ROWID INDEX RANGE SCAN
该执行计划使用了索引范围扫描，由于符合条件id为0的数据在表中只有100行数据，而整个表有1万行数据，查询出的数据只占整个数据的1%，因此我们认为这是一种合理的执行计划。
接下来我们看下面的例子，创建一个表格T2：
create table t2 as select mod(rownum,100) id, rownum value from dba_source where rownum <= 10000
同样为T2创建索引：
create index idx_t2_id on t2(id)
然后为T2收集统计信息：
BEGIN
dbms_stats.gather_table_stats(user,
't2',
method_opt => 'FOR ALL COLUMNS SIZE 1',
cascade => TRUE);
END;然后执行查询：
select * from t2 where id = 0
该查询的执行计划如下：
SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS FULL
我们看到表的执行计划变成的全表扫描，我们可以很容易的得到该查询的结果任然是100条数据，占T2表总数据量的1%，如果我们进一步比较T2和T1的数据，会发现两张表的id字段完全一样，那为什么T1选择的是索引扫描，而T2却选择了全表扫描呢？
要了解原因，我们需要从数据在数据块上的分布来分析，在T1表中，id字段的分布如下：
0 0...0 0 1 1...1 1 2 2...2 2......88 88...88 88......99 99...99 99
而T2表中id字段的分布如下：
0 1 2 3 ... 98 99 0 1 2 3 ... 98 99 ...... 0 1 2 3 ... 98 99
从这里可以看出T1表中id为0的数据都集中在几个数据块上，而T2表中id为0的数据则分布在很多不同的块上，这样导致T1的查询只需要读取很少块就可以得到结果，因此使用了索引范围扫描，而T2上的查询则需要读取大部分块，因此优化器选择了全表扫描。
舍弃
需要注意的是，全扫描的效率不仅取决于读取的数据块个数，也取决于最终的结果集行数。从上面的例子中我们可以看到：当一个数据块被读取后，查询将根据过滤条件舍弃不符合条件的数据。而这个舍弃的过程是需要耗费资源的，由于这个操作在内存中，因此耗费的将是CPU资源，而舍弃的数据量越大，耗费的CPU资源就越多。
因此，读取的数据块的个数越多，舍弃的数据量越大，全扫描的成本（cost）就越高。
不难想象，当表的数据量不断增大，舍弃的行的数量不断增加，全扫描的成本不断增加，最终可能导致优化器放弃全扫描，转而选择索引扫描。
多块读取方式
多扫描使用的是多块读取，即一个单独的IO调用将会读取多个块，读取的块的数量是可变的，但有一个上限，通过db_file_multiblock_read_count参数指定，该参数通过下面的SQL查看：
select * from v$parameter where name = 'db_file_multiblock_read_count'
下面描述了Oracle在几种情况下读取的块的数量：
1）Oracle不得不读取超过一定边界范围的数据块。
在这种情况下，Oracle将会在一次调用中读取直到边界范围的数据块，然后发起另一次调用来读取剩下的块。
2）存在块已经在内存中
首先读取那么已经在内存中的块，然后发起调用读取剩下的块，这意味着多块读取可能一次仅读取一块。例如，假定多块读取的上限是16，该次读取的数据块编号为1-16，并且编号为偶数的块已经在内存中，那么在这里例子中，将会有8次的单块读取调用来读取奇数编号的块。
3）多块读取大小超过了操作系统限制
这时取决于你操作系统，因此是可变的。
高水位线
所谓高水位线，就是表中最后一块有数据写入的数据块。需要注意的是即使几乎所有数据行都被删除了，并且一些块实际上已经完全变为空的了，高水位线还是保持不变。看下面的例子，当表创建并插入数据后：
而随着后面数据的变化（删除和修改），表中的数据变化为：
虽然很多存储区域已经没有数据，但高水位线任然保持不变。
那么，高水位线对全扫描会造成什么影响呢？
执行全扫描时，Oracle将一直读取到位于表中高水位线的数据块，即使它们是空的，这就意味着许多实际上不需要读取的数据块也被读取了。
下面通过一个具体的实例来看，使用先前的表T2。
1）通过下面的语句判断表所包含的数据块数量：
select blocks from user_segments where segment_name = 'T2' 结果：24
2）确定表中有多少数据块包含数据；
select count(distinct(dbms_rowid.rowid_block_number(rowid))) block_ct from t2 结果：17
3）执行下面的查询，并查看trace信息（trace信息的获取方面见Oracle性能分析1）
alter system flush buffer_cache;--清理缓存 select * from t2 where id = 0
trace信息为：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.06 18 20 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.07 18 20 0 100
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
100 TABLE ACCESS FULL T2 (cr=20 pr=18 pw=0 time=35975 us)查询出100行数据，物理读取的数据块数量（disk）为18，包括一个表头数据块的读取（只有17个数据块包含数据）。执行计划使用了全表扫描。
4）执行删除数据的操作
delete from T2
5）重新获取表包含的数据块数量
select blocks from user_segments where segment_name = 'T2' 结果：24
6）获取包含数据的数据块数量
select count(distinct(dbms_rowid.rowid_block_number(rowid))) block_ct from t2 结果：0
7）执行查询并查看trace信息
alter system flush buffer_cache;--清理缓存 select * from t2 where id = 0
trace信息为：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.21 18 20 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.22 18 20 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL T2 (cr=20 pr=18 pw=0 time=214806 us)我们可以看到查询出的行数是0，但任然物理读取了18个数据块，执行计划任然使用了全扫描。
修正高水位线
我们已经了解了高水位线给全扫描带来的性能问题，下面介绍了几种降低高水位线的方法。
使用truncate操作
truncate table_name
在删除数据时尽量使用truncate操作，降低高水位线。
move操作
alter table table_name move
注意move操作需要使用额外的表空间存储，会锁住表，这样其他并发的用户在表上执行的DML语句会产生等待。move操作会影响到表上的索引，因此索引需要rebuild。
shrink操作
shrink space操作，不需要任何额外的空间，但是速度要比move慢上很多。shrink命令分为下面两种：
1）只压缩空间不调整水位线，在业务繁忙时可以执行
alter table table_name shrink space compact
compact操作通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。但由于涉及到rowid的改变，因此需要enable row movement。
2)调整水位线 会产生锁，可以在业务比较少的时候执行，oracle 会记住1步骤中的操作，只调整水位线
alter table big_table shrink space
使用新表
复制要保留的数据到临时表t，drop原表，然后rename临时表t为原表。
Oracle性能分析4：数据访问方法之全扫描