Oracle性能分析4：数据访问方法之全扫描

wre2

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  

该查询的执行计划如下：
[plain] view plaincopy

    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字段的分布如下：
[plain] view plaincopy

    0 0...0 0 1 1...1 1 2 2...2 2......88 88...88 88......99 99...99 99  

而T2表中id字段的分布如下：
[plain] view plaincopy

    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信息为：
[plain] view plaincopy

    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信息为：
[plain] view plaincopy

    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为原表。