Oracle IO问题解析(6)

[English]

作者： fuyuncat

来源： www.HelloDBA.com

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

分享到 

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 (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_sys、owner_xdb、owner_others从分区访问列表中裁剪出去，只访问分区owner_demo和owner_test上的数据或者通过这两个分区上的索引来访问数据。 3.2.1.2  处理非SQL导致的IO问题 如果从statspack或者AWR报告中找不到明显产生db file sequential read事件的SQL，则该等待事件可能是由于以下原因导致的：   热点数据文件或磁盘   数据文件所在的磁盘IO负荷过重导致对IO请求反映慢，这时，我们可以通过statspack或AWR报告中的“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   2  from V$FILESTAT a, V$DATAFILE b   3  where a.file# = b.file#;   NAME --------------------------------------------------------------------------------     PHYRDS    PHYWRTS ---------- ---------- C:\ORACLE\PRODUCT\10.2.0\ORADATA\EDGAR\DATAFILE\O1_MF_SYSTEM_20TFOB4Q_.DBF     132767      11565   C:\ORACLE\PRODUCT\10.2.0\ORADATA\EDGAR\DATAFILE\O1_MF_UNDOTBS1_20TFQP78_.DBF       1943      19924   C:\ORACLE\PRODUCT\10.2.0\ORADATA\EDGAR\DATAFILE\O1_MF_SYSAUX_20TFSGC6_.DBF 659458     100811   ... ...   找到热点数据文件（磁盘）后，我们可以考虑将数据文件转移到性能更高的存储设备上去，或者利用我们上述说的条带化、RAID等存储技术来均衡IO负荷。   热点数据段   从Oracle9.2开始，出现了数据段的概念。每个表和索引都存储在自己的数据段中。我们可以通过视图V$SEGMENT_STATISTICS查找物理读最多的段来找到热点数据段。通过对热点段的分析，考虑采用重建索引、分区表等方式来降低该数据段上的IO负荷。   SQL> select owner, object_name, tablespace_name, object_type, value   2  from V$SEGMENT_STATISTICS   3  where statistic_name = 'physical reads'   4  order by value desc;   OWNER                          OBJECT_NAME ------------------------------ ------------------------------ TABLESPACE_NAME                OBJECT_TYPE             VALUE ------------------------------ ------------------ ---------- SYS                            CONTEXT$ SYSTEM                         TABLE                      71   SYS                            I_CONTEXT SYSTEM                         INDEX                      70   ... ...   另外，我们还可以根据视图v$session_wait中的P1（热点段所在的数据文件号）、P2（发生db file sequential read事件的起始数据块）、P3（数据块的数量，db file sequential read读取数据块数量为1）来定位出热点段： 先找出文件号、起始数据块、数据块数量：   SQL> select p1 "fileid", p2 "block_id", p3 "block_num"   2  from v$session_wait   3  where event = 'db file sequential read';       fileid   block_id  block_num ---------- ---------- ----------        396      44869          1   然后根据找出的文件号、起始数据块、数据块数量来定位出数据段：   SQL> select   2     segment_name     "Segment Name",   3     segment_type     "Segment Type",   4     block_id         "First Block of Segment",   5     block_id+blocks  "Last Block of Segment"   6  from dba_extents   7  where &fileid = file_id   8  and &block_id >= block_id   9  and &block_id <= block_id+blocks; Enter value for fileid: 396 old   7: where &fileid = file_id new   7: where 396 = file_id Enter value for block_id: 44869 old   8: and &block_id >= block_id new   8: and 44869 >= block_id Enter value for block_id: 44869 old   9: and &block_id <= block_id+blocks new   9: and 44869 <= block_id+blocks   Segment Name -------------------------------------------------------------------------------- Segment Type       First Block of Segment Last Block of Segment ------------------ ---------------------- --------------------- CSS_TP_SHMT_QUEUE_ACTIVITY TABLE                               44841                 44873 3.2.1.3  调整Buffer Cache 如果系统中即不存在性能有问题的SQL语句，而且所有磁盘的IO负载也比较均衡（不存在热地磁盘），则我们需要考虑增加Buffer Cache来降低磁盘IO请求。 在8i，主要是根据缓存命中率（Buffer Cache Hit Ratio）来调整buffer cache。当Buffer Cache调整到一定大小，对命中率没什么影响了时，就没有必要在增大Buffer Cache了。可以通过以下语句来查看Buffer Cache命中率：   SQL> select 1-(physical_reads)/(consistent_gets+db_block_gets)   2  from v$buffer_pool_statistics;   1-(PHYSICAL_READS)/(CONSISTENT_GETS+DB_BLOCK_GETS) --------------------------------------------------                                          .95628981   在9i中，可以利用statspack report中的Buffer Cache建议部分来调整Buffer Cache的大小。   Buffer Pool Advisory for DB: ICSSPRD  Instance: icssprd  End Snap: 259 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate           Size for  Size      Buffers for  Est Physical          Estimated P   Estimate (M) Factr         Estimate   Read Factor     Physical Reads --- ------------ ----- ---------------- ------------- ------------------ D            304    .1           37,715          9.18      5,928,235,496 D            608    .2           75,430          6.88      4,443,709,043 D            912    .3          113,145          5.73      3,699,496,220 D          1,216    .4          150,860          3.87      2,502,670,372 D          1,520    .5          188,575          2.32      1,499,049,228 D          1,824    .6          226,290          1.70      1,099,326,418 D          2,128    .7          264,005          1.41        912,042,579 D          2,432    .8          301,720          1.22        790,925,174 D          2,736    .9          339,435          1.09        703,357,378 D          2,992   1.0          371,195          1.00        645,905,997 D          3,040   1.0          377,150          0.99        636,992,420 D          3,344   1.1          414,865          0.90        583,996,250 D          3,648   1.2          452,580          0.84        542,063,246 D          3,952   1.3          490,295          0.79        508,261,496 D          4,256   1.4          528,010          0.74        480,472,150 D          4,560   1.5          565,725          0.71        455,533,563 D          4,864   1.6          603,440          0.67        434,743,759 D          5,168   1.7          641,155          0.64        416,285,837 D          5,472   1.8          678,870          0.62        400,208,242 D          5,776   1.9          716,585          0.60        385,785,401 D          6,080   2.0          754,300          0.57        365,597,932           -------------------------------------------------------------   这里，Est Physical Read Factor是估算的从磁盘物理读取次数与从buffer cache中读取的次数的比值。从意见估算的图表中，当Buffer Cache的增长对该因子影响不大时，则说明无需在增大Buffer Cache，我们就可以去相应临界点的大小作为Buffer Cache的大小。上述例子中，我们可以考虑设置Buffer Cache大小为2992M。   在Oracle10g中，引入了新的内存管理特性——自动共享内存管理（Automatic Shared Memory Management ASMM）。基于这一特性，oracle能够自动根据当前的负荷计算出最优的Buffer Cache大小。关于ASMM，可以参见文章《Oracle内存全面分析》的SGA_TARGET部分。   我们可以采用多尺寸缓冲池技术将热点数据段（表或索引）KEEP在缓冲池中：   SQL> alter table t_test1 storage(buffer_pool keep);   Table altered.   关于多尺寸缓冲的更多内容，可以参考文章《Oracle内存全面分析》的“多缓冲池部分”部分。 3.2.1.4  Housekeep历史数据 对于一些被频繁访问到的大表，我们需要定期对其做housekeep，将一些不用的、老的数据从表中移除，以减少访问的数据块。定期对含有时间轴的Transaction表做housekeep是降低IO负载的重要措施。