Oracle IO问题解析——4

3.2.2 db file scattered read

　　这是另外一个常见的引起数据库IO性能问题的等待事件。它通常发生在Oracle将“多数据块”读取到Buffer Cache中的非连续(分散的 Scattered)区域。多数据块读就是我们上述所说的一次读取“DB_FILE_MULTIBLOCK_READ_COUNT”块数据块，前面提到，它通常发生在全表扫描(Full Table Scan)和快速全索引扫描(Fast Full Index Scan)时。当发现db file scattered read等待事件是系统引起IO性能的主要原因时，我们可以采取以下措施对系统进行优化。

　　3.2.2.1 优化存在Full Table Scan和Fast Full Index Scan的SQL语句

　　我们可以首先从statspack或者awr报告中的“SQL ordered by Reads”部分中找出存在Full Table Scan和Fast Full Index Scan的Top SQL。因为这些Top SQL往往是整个系统的瓶颈。

　　从9i开始，我们还可以通过视图V$SQL_PLAN来查找系统中存在Full Table Scan和Fast Full Index Scan的SQL语句。查找Full Table Scan的语句：

　　select sql_text from v$sqlarea t, v$sql_plan p

　　where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'

　　and p.options='FULL';

　　查找Fast Full Index Scan的语句

　　select sql_text from v$sqlarea t, v$sql_plan p

　　where t.hash_value=p.hash_value and p.operation='INDEX'

　　and p.options='FULL SCAN';

　　Full Table Scan通常是由于以下几个原因引起的：

　　条件字段上没有索引;

　　在这种情况下，如果表的数据量比较大，我们就需要在相应字段上建立起索引。

　　CBO中，对象的统计数据不正确

　　CBO中，如果对象的统计数据或者其柱状图(Histogram)信息不正确，会导致优化器计算出错误的查询计划，从而选择全表扫描。这种情况下，我们要做的就重新分析(Analyze)表、索引及字段。

　　CBO中，SQL语句中引用到了无法估算统计数据的对象

　　在PLSQL中，可以建立一些高级的数据类型，如“TABLE OF”、ARRAY等，通过TABLE、CAST函数可以在SQL语句中将这些对象当成表来处理。而这些对象的数据只存在于调用PLSQL的会话中，因此他们没有相应的统计数据，Oracle会为他们生产一些假的统计数据以完成查询计划代价估算。但是基于这些假的数据计算出的查询计划一般是错误的。我们可以考虑通过提示来强制SQL使用索引或者强制SQL采用RBO优化器。

　　此外，如果SQL中引用到了临时表(Temporary Table)也会产生同样的问题。其原因和解决方法和上面相同。

　　优化器认为索引扫描代价过高;

　　在Oracle中存在一个参数optimizer_index_cost_adj，该参数的值代表一个百分数，如果对索引扫描的代价达到或超过全表扫描的代价的这个百分比值时，优化器就采用全表扫描。

　　optimizer_index_cost_adj是一个全局性的参数，它的合理值是通过长期调整出来的。一般来说是一个介于1到100之间的数字。我们可以按照以下方法来选取optimizer_index_cost_adj的合理值。

　　先由以下语句得出optimizer_index_cost_adj的一个初始值：

　　SQL> select

　　2 a.average_wait "Average Waits FTS"

　　3 ,b.average_wait "Average Waits Index Read"

　　4 ,a.total_waits /(a.total_waits + b.total_waits) "Percent of FTS"

　　5 ,b.total_waits /(a.total_waits + b.total_waits) "Percent of Index Scans"

　　6 ,(b.average_wait / a.average_wait)*100 "optimizer_index_cost_adj"

　　7 from

　　8 v$system_event a,

　　9 v$system_event b

　　10 where a.EVENT = 'db file sequential read'

　　11 and b.EVENT = 'db file scattered read';

　　Average Waits FTS Average Waits Index Read Percent of FTS Percent of Index Scans

　　----------------- ------------------------ -------------- ----------------------

　　optimizer_index_cost_adj

　　------------------------

　　1.25 1.06 .041867874 .958132126

　　84.8

　　这里，84.8是我们系统的初始值。在系统经过一段时间运行后，再次运行上面的语句，重新调整optimizer_index_cost_adj的值。经过多次如此反复的调整之后，最终上面语句得出值趋于稳定，这时这个值就是符合我们系统性能需求的最合理的值。

　　当然这个数值也可以通过statspack的历史数据来调整，在9i中：

　　select to_char(c.end_interval_time, 'MM/DD/YYYY') "Date",

　　sum(a.time_waited_micro)/sum(a.total_waits)/10000 "Average Waits FTS",

　　sum(b.time_waited_micro)/sum(b.total_waits)/10000 "Average Waits Index Read",

　　(sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 "Percent of FTS",

　　(sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 "Percent of Index Scans",

　　(sum(b.time_waited_micro)/sum(b.total_waits)) /

　　(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 "optimizer_index_cost_adj"

　　from dba_hist_system_event a, dba_hist_system_event b, dba_hist_snapshot c

　　where a.event_name = 'db file scattered read'

　　and b.event_name = 'db file sequential read'

　　and a.snap_id = c.snap_id

　　and b.snap_id = c.snap_id

　　group by c.end_interval_time

　　order by 1;

　　10g中：

　　select to_char(c.snap_time, 'MM/DD/YYYY') "Date",

　　sum(a.time_waited_micro)/sum(a.total_waits)/10000 "Average Waits FTS",

　　sum(b.time_waited_micro)/sum(b.total_waits)/10000 "Average Waits Index Read",

　　(sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 "Percent of FTS",

　　(sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 "Percent of Index Scans",

　　(sum(b.time_waited_micro)/sum(b.total_waits)) /

　　(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 "optimizer_index_cost_adj"

　　from stats$system_event a, stats$system_event b, stats$snapshot c

　　where a.event = 'db file scattered read'

　　and b.event = 'db file sequential read'

　　and a.snap_id = c.snap_id

　　and b.snap_id = c.snap_id

　　group by c.snap_time

　　order by 1;

　　当optimizer_index_cost_adj的值对于整个系统来说已经是比较合理的值，而某些语句由于该值选择了全表扫描扫描导致了IO性能问题时，我们可以考虑通过提示来强制语句命中索引。

　　建立在条件字段上的索引的选择性不高，结合上一条导致全表扫描;

　　当索引的选择性不高，且其代价过高，系统则会选择全表扫描来读取数据。这时我们可以考虑通过选择/建立选择性比较高的索引，使查询命中索引从而避免全表扫描。

　　SQL> create index t_test1_idx1 on t_test1(owner) compute statistics;

　　Index created.

　　SQL> set autot trace

　　SQL> select object_name

　　2 from t_test1

　　3 where owner = 'SYS'

　　4 and created > sysdate - 30;

　　no rows selected

　　Execution Plan

　　----------------------------------------------------------

　　Plan hash value: 1883417357

　　-----------------------------------------------------------------------------

　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

　　-----------------------------------------------------------------------------

　　| 0 | SELECT STATEMENT | | 49 | 1715 | 152 (2)| 00:00:02 |

　　|* 1 | TABLE ACCESS FULL| T_TEST1 | 49 | 1715 | 152 (2)| 00:00:02 |

　　-----------------------------------------------------------------------------

　　Predicate Information (identified by operation id):

　　---------------------------------------------------

　　1 - filter("OWNER"='SYS' AND "CREATED">SYSDATE@!-30)

　　... ...

　　SQL> create index t_test1_idx2 on t_test1(owner, created) compute statistics;

　　Index created.

　　SQL> select 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 | 1715 | 2 (0)

　　| 00:00:01 |

　　| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 49 | 1715 | 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)

　　... ...

　　3.2.2.2 调整DB_FILE_MULTIBLOCK_READ_COUNT

　　当SQL已经没有优化余地后，问题仍没有解决，我们可以考虑调整DB_FILE_MULTIBLOCK_READ_COUNT大小。其作用我们在3.1.2中有做叙述，这里不再赘述。不过要注意一点就是，DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE是一次IO读取的传输量，它不能大于系统的max_io_size大小。

　　从Oracle 10gR2开始，如果没有设置DB_FILE_MULTIBLOCK_READ_COUNT的大小，Oracle会自动为其调整一个默认值，这个默认值的大小与平台最大IO大小(max_io_size)相关(对大多数平台来说max_io_size是1M)，其大小被设置为(max_io_size / DB_BLOCK_SIZE)。

　　3.2.2.3 将频繁访问的全扫描的表CACHE住

　　由于通过Full Table Scan和Fast Full Index Scan读取的数据块会被放置到Buffer Cache的LRU链表的LRU端，从而使数据块尽快从Buffer Cache中移出。因此，对于那些会被频繁访问到全扫描的表，且其数据量不大的情况下，我们可以考虑将它们CACHE住。

　　SQL> alter table t_test1 cache;

　　Table altered.

　　对于Fast Full Index Scan的索引对象，则可以考虑把它放置在KEEP池中。

　　SQL> alter index t_test1_idx1 storage(buffer_pool keep);

　　Index altered.

　　利用V$SESSION_EVENT视图，我们同样可以找到当前系统中发生全扫描的对象。

　　SQL> select p1 "fileid", p2 "block_id", p3 "block_num"

　　2 from v$session_wait

　　3 where event = 'db file scattered read';

　　fileid block_id block_num

　　---------- ---------- ----------

　　359 152972 16

　　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: 359

　　old 7: where &fileid = file_id

　　new 7: where 359 = file_id

　　Enter value for block_id: 152972

　　old 8: and &block_id >= block_id

　　new 8: and 152972 >= block_id

　　Enter value for block_id: 152972

　　old 9: and &block_id <= block_id+blocks

　　new 9: and 152972 <= block_id+blocks

　　Segment Name

　　--------------------------------------------------------------------------------

　　Segment Type First Block of Segment Last Block of Segment

　　------------------ ---------------------- ---------------------

　　CSS_TP_SHMT_QUEUE

　　TABLE 152969 153001

　　3.2.2.4 利用分区表减少全扫描操作读取的数据块数量

　　前面我们有介绍分区裁剪(Partition Pruning)技术。将表分区，利用分区裁剪技术，在进行全扫描时只会扫描在WHERE条件中出现的分区，从而可以减少全扫描所读取到的数据块数量。

　　3.2.2.5 Housekeep历史数据

　　同样，housekeep不需要的、历史的数据，减少数据段中的数据块数量，也能减少全扫描的IO请求次数

　3.2.3 db file parallel read

　　首先，不要被该事件名称所误导——它和并行DML或者并行查询都无关。当从多个数据文件并行读取数据到非联系的内存(PGA、Buffer Cache)缓冲中时，会发生该等待事件。它通常发生在Recovery操作或者利用缓冲预提取(Buffer Prefetching)从数据文件并行读取数据时。

　　我们可以通过以下语句找出发生db file parallel read等待事件的数据文件和数据块：

　　select p1 "fileid", p2 "block_id", p3 "requests"

　　from v$session_wait

　　where event = 'db file parallel read';

　　优化该等待事件的手段可以参考优化db file sequential read等待事件中非SQL优化方法部分。

　　3.2.4 direct path read & direct path read (lob)

　　当直接读取(Direct Read)数据到PGA(而不是到Buffer Cache)中去时，会发生Direct Path Read等待事件。对Lob数据的直接读有一个单独的等待事件——direct path read (lob)。

　　当Oracle设置支持异步IO时，进程可以在提交IO请求后继续做其他操作，并且在稍后再提取IO请求返回的结果，在提取结果时就产生了direct path read等待事件。

　　在没有启用异步IO时，IO请求在完成之前会被阻塞，但在执行IO操作时并不会产生等待事件。进程稍后回来提取那些已经读取到的IO数据，这时尽管能够很快返回，但仍然会显示direct path read等待事件。

　　和其他IO等待事件不同的是，对Direct Path Read等待事件要注意以下两点：

　　等待次数并不等于IO请求次数;

　　统计(如statspack报告中)得出的Direct Path Read的等待时间并不一定代表该事件引起的真正等待时间。

　　事件中的P1、P2、P3参数分别代表：

　　P1：发生等待事件的数据块所在文件号;

　　P2：发生等待事件的数据块号;

　　P3：等待事件涉及的连续数据块数量。

　　直接读(Direct Read)请求一般发生在以下几种情况：

　　磁盘排序IO(Sort Area不足时，排序用到的临时数据会被写到临时表空间上去，当读取这些数据时就使用直接读);

　　并行查询;

　　预读取(当一个进程认为某个数据块将很快被用到而发出IO请求时)

　　Hash Join(Hash Area不足)

　　IO负载系统中，服务进程处理缓存的速度比系统IO返回数据到缓存的速度更快时

　　通过视图V$SESSION_EVENT我们可以找出当前产生等待的会话，再根据会话中正在进行的操作确定导致等待的原因。针对不同的原因，我们可以采取不同的措施减少Direct Path Read等待事件。

　　3.2.4.1 磁盘排序

　　首先我们可以考虑优化语句以减少排序操作。排序一般是由以下操作引起的：

　　o Order By;

　　o JOIN;

　　o UNION;

　　o Group By;

　　o 聚合操作;

　　o Select unique;

　　o Select distinct;

　　可以尝试在语句中减少没必要的上述操作来避免排序操作。另外，创建索引也会引起排序操作。在专业模式(Dedicated)下，排序所占用的内存是从PGA中分配出来的一块区域，叫Sort Area，由参数sort_area_size控制其大小;在MTS中，排序区是从Large Pool中分配的。当sort area大小无法满足排序操作要求时，就会占用临时表空间来存放排序数据，因而产生Direct Path Read等待事件。我们可以通过适当增加该参数来减少磁盘排序操作。

　　这个参数可以在系统范围或会话范围进行修改。对于一些需要做大量排序操作而且又比较独立的会话(如Create Index)，我们可以在会话级别为其设置比较大的Sort Area以满足排序需要：

　　SQL> alter session set sort_area_size = 10000000;

　　Session altered.

　　该参数大小一般推荐设置为1~3M。在9i之后，不推荐设置该参数，我们可以通过设置PGA_AGGREGATE_TARGET进行PGA内存自动管理(设置WORKAREA_SIZE_POLICY为TRUE)。对于PGA_AGGREGATE_TARGET的大小设置，可以参考文章《Oracle内存全面分析》中的PGA_AGGREGATE_TARGET部分。

　　此外，我们还可以通过以下语句来查找系统中存在磁盘排序的会话及其语句：

　　SELECT a.sid,a.value, b.name, d.sql_text from

　　V$SESSTAT a, V$STATNAME b, V$SESSION c, V$SQLAREA d

　　WHERE a.statistic#=b.statistic#

　　AND b.name = 'sorts (disk)'

　　and a.sid = c.sid

　　and c.SQL_ADDRESS = d.ADDRESS(+)

　　and c.SQL_HASH_VALUE = d.HASH_VALUE(+)

　　and value > 0

　　ORDER BY 2 desc,1;

　　3.2.4.2 并行查询

　　当设置表的并行度非常高时，优化器可能就对表进行并行全表扫描，这时会引起Direct Path Read等待。

　　在使用并行查询前需要慎重考虑，因为并行查询尽管能教师程序的响应时间，但是会消耗比较多的资源。对于低配置的数据库服务器不建议使用并行特性。此外，需要确认并行度的设置要与IO系统的配置相符(建议并行度为2~4 * CPU数)。在10g中，可以考虑使用ASM。

　　对于表的并行度，我们不建议直接用ALERT修改表的物理并行度：

　　ALTER TABLE t_test1 PARALLEL DEGREE 16;

　　而是推荐针对特定语句使用提示来设置表的并行度：

　　SQL> SELECT /*+ FULL(T) PARALLEL(T, 4)*/ object_name FROM t_test1 t;

　　47582 rows selected.

　　Execution Plan

　　----------------------------------------------------------

　　Plan hash value: 2467664162

　　--------------------------------------------------------------------------------

　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

　　| TQ |IN-OUT| PQ Distrib |

　　--------------------------------------------------------------------------------

　　| 0 | SELECT STATEMENT | | 47582 | 1068K| 42 (3)| 00:00:01

　　| | | |

　　| 1 | PX COORDINATOR | | | | |

　　| | | |

　　| 2 | PX SEND QC (RANDOM)| :TQ10000 | 47582 | 1068K| 42 (3)| 00:00:01

　　| Q1,00 | P->S | QC (RAND) |

　　| 3 | PX BLOCK ITERATOR | | 47582 | 1068K| 42 (3)| 00:00:01

　　| Q1,00 | PCWC | |

　　| 4 | TABLE ACCESS FULL| T_TEST1 | 47582 | 1068K| 42 (3)| 00:00:01

　　| Q1,00 | PCWP | |

　　--------------------------------------------------------------------------------

　　3.2.4.3 Hash Join

　　Hash Area是用于hash join的内存区域。Hash Area过小会引起Direct Path Read等待。当WORKAREA_SIZE_POLICY为FALSE时，可以考虑增加hash_area_size的大小(建议为sort_area_size大小的1.5倍);当WORKAREA_SIZE_POLICY为TRUE时，可以考虑增加PGA_AGGREGATE_TARGET大小。

　　3.2.4.4 Direct path read (lob)

　　为了减少LOB的读写时间，通常我们会设置LOB的存储参数NOCACHE，这时读取LOB时会引起Direct Path Read (lob)等待事件。但当我们发现Direct path read (lob) 引起了IO性能问题，就需要考虑将那些被经常读取的LOB字段设置为CACHE。另外，如果操作系统的文件系统有足够的Buffer Cache时可以考虑将LOB数据段存储在文件系统上。

　　3.2.4.5 其他优化措施

　　当内存资源不足、IO读取数据到内存效率远远低于内存中数据被处理的效率时，会引起Direct Path Read等待事件。作为对上述处理措施的补充，增加内存(PGA)、在确保操作系统支持AIO情况下设置DISK_ASYNCH_IO为TRUE以支持异步IO、采用效率更高的存储设备都能帮助我们减少Direct Path Read等待。

　　3.2.5 direct path write & direct path write (lob)

　　直接写(Direct Path Write)允许一个会话先将IO写请求放入一个队列中，让操作系统去处理IO，而自身可以继续处理其他操作。当会话需要知道写操作是否完成(如会话需要一块空闲的缓存块或者会话需要确认内存中所有写操作都被flush到磁盘了)，会话就会等待写操作完成从而产生Direct Path Write等待事件。Direct Path Write (lob) 是在对LOB数据段(NOCACHE)直接写时产生的等待事件。

　　在没有启用异步IO时，IO写请求在完成之前会被阻塞，但在执行IO写操作时并不会产生等待事件。进程稍后回来提取那些已经完成的IO操作数据，这时尽管能够很快返回，但仍然会显示direct path write等待事件。

　　和Direct Path Read等待事件相似，对Direct Path Write等待事件也要注意以下两点：

　　等待次数并不等于IO请求次数;

　　统计(如statspack报告中)得出的Direct Path Write的等待时间并不一定代表该事件引起的真正等待时间。

　　事件中的P1、P2、P3参数分别代表：

　　P1：发生等待事件的数据块所在文件号;

　　P2：发生等待事件的数据块号;

　　P3：等待事件涉及的连续数据块数量。

　　直接写请求一般发生在以下几种情况：

　　直接数据载入操作(如CTAS、SQL*Loader设置Direct选项等);

　　并行DML操作;

　　磁盘排序(排序内存空间不足，数据写入磁盘);

　　载入NOCACHE数据段;

　　对Direct Path Write的优化处理措施基本上和Direct Path Write类似。