Oracle IO问题解析（6）

作者： fuyuncat

来源： www.HelloDBA.com

3.2     数据文件相关的IO事件 数据库系统中的大多数的IO请求都是针对数据文件的。因此大多数情况下，与数据文件相关的IO事件是引起系统IO性能的主要原因。这些事件也是我们文章需要重点介绍的事件。下面分别针对不同事件介绍问题的解决思路。 3.2.1      db file sequential read 这个事件是是最常见的IO等待事件。它一般发生在读取单独数据块时，如读取索引数据块或者通过索引访问一个表数据块，另外在读取数据文件头数据块时也会发生db file sequential read等待事件。 当发现这个等待事件成为系统等待事件中的主要事件，我们可以通过一下方法来处理： 3.2.1.1  优化Top SQL 从statspack或者awr报告中的“SQL ordered by Reads”部分或者通过V$SQL视图找出系统中的Top SQL，对SQL进行调优以减少IO请求。   当SQL中存在Index Range Scan时，如果访问的索引的选择性不好就会导致需要访问过多的数据块，这时可以通过建立一个、或强制SQL使用一个已经存在的选择性更好的索引。这样使我们访问更少的数据块来获取到需要的数据。   SQL> select object_id, object_name   2  from t_test1   3  where owner = 'SYS'   4  and created > sysdate - 30;   no rows selected   Execution Plan ---------------------------------------------------------- Plan hash value: 4014220762   -------------------------------------------------------------------------------- | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU) | Time     | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |              |     1 |    39 |    11   (0) | 00:00:01 | |*  1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1      |     1 |    39 |    11   (0) | 00:00:01 | |*  2 |   INDEX RANGE SCAN          | T_TEST1_IDX1 |   576 |       |     1   (0) | 00:00:01 | --------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("OWNER"='SYS' AND "CREATED">SYSDATE@!-30)     Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets         658  consistent gets          45  physical reads           0  redo size         339  bytes sent via SQL*Net to client         374  bytes received via SQL*Net from client           1  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           0  rows processed   SQL> create index t_test1_idx2 on t_test1(owner, created);   Index created.   SQL> select object_id, object_name   2  from t_test1   3  where owner = 'SYS'   4  and created > sysdate - 30;   no rows selected     Execution Plan ---------------------------------------------------------- Plan hash value: 3417015015 --------------------------------------------------------------------------------- | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU) | Time     | --------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |              |    49 |  1911 |     2   (0) | 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1      |    49 |  1911 |     2   (0) | 00:00:01 | |*  2 |   INDEX RANGE SCAN          | T_TEST1_IDX2 |    49 |       |     1   (0) | 00:00:01 | ---------------------------------------------------------------------------------     Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("OWNER"='SYS' AND "CREATED">SYSDATE@!-30)     Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets           2  consistent gets           1  physical reads           0  redo size         339  bytes sent via SQL*Net to client         374  bytes received via SQL*Net from client           1  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           0  rows processed   如果索引存在碎片，那每个索引数据块上的索引数据就更少，会导致我们需要访问更多的索引数据块。这时，我们需要考虑重建索引来释放碎片；   判断一个所以是否需要重建，我们介绍一个简单的方法：对一个索引进行结构分析后，如果该索引占用超过了一个数据块，且满足以下条件之一：B-tree树的高度大于3；使用百分比低于75%；数据删除率大于15%，就需要考虑对索引重建：   SQL> analyze index t_test1_idx1 compute statistics;   Index analyzed.   SQL> analyze index t_test1_idx1 validate structure;   Index analyzed.   SQL> select btree_space, -- if > 8192（块的大小）   2         height, -- if > 3   3         pct_used, -- if < 75   4         del_lf_rows/(decode(lf_rows,0,1,lf_rows)) *100 as deleted_pct -- if > 20%   5  from index_stats;   BTREE_SPACE     HEIGHT   PCT_USED DELETED_PCT ----------- ---------- ---------- -----------      880032          2         89           0   如果使用的索引的聚簇因子（Clustering Factor）很大，说明一条索引记录指向多个数据块，在返回结果时需要读取更多的数据块。通过重建表可以降低聚簇因子，因而可以在查找索引时减少表数据块的访问块数。   聚簇因子说明了表数据的物理存储位置相对于一个索引的排序性的符合程度。例如，一个非唯一索引是建立在A字段上的，如果表数据的存储是以A字段的顺序存储的，则索引与数据的关系如下图：     此时，索引的聚簇因子很低，从图上看到，假如我们需要获取A=A2的数据，只需要读取一个数据块就可以了； 相反，如果表数据物理存储顺序和索引顺序相差很大，就会出现下面的情况：     这时该索引的聚簇因子就很大，可以看到，如果需要获取A=A2的数据，我们需要读取4块或更多的数据块。   对索引进行分析后，我们可以从视图DBA_INDEXES中获取到索引的聚簇因子，字段名为Clustoring_Factor。如果一个索引是一张表主要被使用的索引（或者是该表的唯一索引），且它的聚簇因子过高导致IO请求过高的话，我们可以考虑采取以下措施来降低IO： 1)                以索引字段的顺序重建表以降低聚簇因子，可以用以下语句重建表（当然，你还需要重建触发器、索引等对象，还可能需要重建、重新编译有关联对象）：   CREATE new_table AS SELECT * FROM old_table ORDER BY A;   2)                建立基于索引字段IOT（索引表）。   如果该索引不是表的主要索引，只是被少量语句引用到，按照以上方式处理的话反而可能会使其他使用更加频繁的索引的聚簇因子增大，导致系统性能更差。这时我们可以建立包含返回字段的索引，以避免“TABLE ACCESS BY INDEX ROWID”。如以下例子：   SQL> set autot trace SQL> select status from t_test1   2  where owner = 'DEMO';   576 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 4014220762   -------------------------------------------------------------------------------- | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU) | Time     | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |              |   576 |  6336 |    11   (0) | 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST1      |   576 |  6336 |    11   (0) | 00:00:01 | |*  2 |   INDEX RANGE SCAN          | T_TEST1_IDX1 |   576 |       |     1   (0) | 00:00:01 | --------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("OWNER"='DEMO')     Statistics ----------------------------------------------------------         465  recursive calls           0  db block gets         222  consistent gets          43  physical reads           0  redo size        8368  bytes sent via SQL*Net to client         803  bytes received via SQL*Net from client          40  SQL*Net roundtrips to/from client           8  sorts (memory)           0  sorts (disk)         576  rows processed   SQL> create index t_test1_idx3 on t_test1(owner, status) compute statistics;   Index created.   SQL> select status from t_test1   2  where owner = 'DEMO';   576 rows selected.     Execution Plan ---------------------------------------------------------- Plan hash value: 2736516725   -------------------------------------------------------------------------------- | Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time| -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |              |   576 |  6336 |     2   (0)| 00:00:01| |*  1 |  INDEX RANGE SCAN| T_TEST1_IDX3 |   576 |  6336 |     2   (0)| 00:00:01| --------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - access("OWNER"='DEMO')     Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets          43  consistent gets           3  physical reads           0  redo size        8152  bytes sent via SQL*Net to client         803  bytes received via SQL*Net from client          40  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         576  rows processed   通过分区裁剪（partition pruning）技术来减少的SQL对数据块的访问。   采用分区裁剪技术，Oracle优化器会先分析FROM和WHERE字句，在建立访问分区列表时将那些不会被访问到的分区排除。例如，我们的表T_TEST1的owner字段的值有“SYS、SYSTEM、XDB、DEMO、TEST”，如果我们按照owner字段建立的是分区表：   CREATE TABLE t_test1