HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

Oracle IO问题解析(6)

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2009-04-07 14:57:05

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

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优化器会先分析FROMWHERE字句,在建立访问分区列表时将那些不会被访问到的分区排除。例如,我们的表T_TEST1owner字段的值有“SYSSYSTEMXDBDEMOTEST”,如果我们按照owner字段建立的是分区表:

 

CREATE TABLE t_test1
(object_id    NUMBER(5), 
 object_name  VARCHAR2(30),
 owner        VARCHAR2(20),
 created      DATE)
PARTITION BY LIST(owner)
(
PARTITION owner_sys VALUES('SYS', 'SYSTEM'),
PARTITION owner_xdb VALUES ('XDB'),
PARTITION owner_demo VALUES('DEMO'),
PARTITION owner_test VALUES('TEST'),
PARTITION owner_others VALUES(DEFAULT)
);

 

则对于以下语句:

 

select object_name
from t_test1
where owner in ('DEMO', 'TEST')
and created > sysdate - 30;

 

优化器会先将分区owner_sysowner_xdbowner_others从分区访问列表中裁剪出去,只访问分区owner_demoowner_test上的数据或者通过这两个分区上的索引来访问数据。

3.2.1.2  处理非SQL导致的IO问题

如果从statspack或者AWR报告中找不到明显产生db file sequential read事件的SQL,则该等待事件可能是由于以下原因导致的:

 

  • 热点数据文件或磁盘

 

数据文件所在的磁盘IO负荷过重导致对IO请求反映慢,这时,我们可以通过statspackAWR报告中的“File I/O Statistics”部分(或者通过V$FILESTAT视图)来找到热点磁盘:

 

Statspack report

Tablespace               Filename
------------------------ ----------------------------------------------------
                 Av      Av     Av                    Av        Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
AFW_DATA                 /export/home/icssprd/data/data17/icssprd_afw_data_01
           726       0    4.3     1.0          381        0          0
 
AFW_INDX                 /export/home/icssprd/data/data18/icssprd_afw_indx_01
         1,741       0    6.3     1.0        2,104        0          0
 
CSS_AN_DATA              /export/home/icssprd/data/data03/icssprd_css_an_data
       200,649       5    1.8     3.2       24,192        1          0
                         /export/home/icssprd/data/data04/icssprd_css_an_data
       242,462       6    1.6     3.1       26,985        1          3    6.7
 
CSS_AN_INDX              /export/home/icssprd/data/data13/icssprd_css_an_indx
        70,789       2    5.0     1.6        5,330        0          0
 
CSS_AUDIT_RESOURCES_DATA /export/home/icssprd/data/data10/icssprd_css_audit_r
         2,394       0    0.6     1.0        1,781        0          0
 
CSS_AUDIT_RESOURCES_INDX /export/home/icssprd/data/data11/icssprd_css_audit_r
           248       0    4.3     1.0           52        0          0
 
... ...

 

视图:

SQL> select b.name, phyrds, phywrts