等待事件之--关于 scatter read wait事件

   oracle 建议，在一个正常的生产系统中，等待事件排序应该是按照这么个顺序，

首先是idle waits，然后是physical read waits.

  On a healthy system, physical read waits should be the biggest waits after the idle  waits.（Oracle® Database Performance Tuning Guide 10g Release 2 (10.2)  B14211-03 p200）

 那么，OLTP系统中，如果发生了如下的一些情况，ok，你的系统可能就有问题了，

 1、db file scatter read等待事件比较多

 2、direct read waits等待事件比较多-----》一般是因为并行查询全表扫描问题（full table scans with parallel query）

 3、Poor buffer cache hit ratio

 

针对OLTP系统来说，正常情况下应该都做小的 索引查询，修改。（(OLTP) system that should be doing small indexed accesses）

 

这些等待事件都反映了一个现象，即过度的系统IO。( indicate excessive I/O load on the system)。

 

要说明scatter read，我们需要先了解下 sequential read以及 direct read。以及他们之间的差别。从下可以看出根据这个图，可以知道，sequential read 强调的是从磁盘上读到sga的一个block中。而scattered read 强调的是从磁盘上读到sga中的 离散的block中。

 

那是什么造成的 scatter read呢?一般是因为全扫描，这种全扫描包含了全表扫描和全索引扫描。

A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan.

 

scatter read等待事件一般表示系统正在发生一次全扫描操作。（The db file scattered read wait event identifies that a full scan is occurring.）

 

 

关于scatter read wait 的原因如下:

The first course of action should be to find opportunities to reduce I/O. Examine the

SQL statements being run by sessions waiting for these events, as well as statements

causing high physical I/Os from V$SQLAREA. Factors that can adversely affect the

execution plans causing excessive I/O include the following:

■ Improperly optimized SQL

■ Missing indexes

■ High degree of parallelism for the table (skewing the optimizer toward scans)

■ Lack of accurate statistics for the optimizer

■ Setting the value for DB_FILE_MULTIBLOCK_READ_COUNT initialization

parameter too high which favors full scans

 

解决方法如下：

Managing Excessive I/O

There are several ways to handle excessive I/O waits. In the order of effectiveness,

these are as follows:

1. Reduce the I/O activity by SQL tuning

2. Reduce the need to do I/O by managing the workload

3. Gather system statistics with DBMS_STATS package, allowing the query optimizer

to accurately cost possible access paths that use full scans

4. Use Automatic Storage Management

5. Add more disks to reduce the number of I/Os for each disk

6. Alleviate I/O hot spots by redistributing I/O across existing disks

 

 

 

我们还可以定位scatter read 的对象：

Inadequate I/O Distribution

Besides reducing I/O, also examine the I/O distribution of files across the disks. Is

I/O distributed uniformly across the disks, or are there hot spots on some disks? Are

the number of disks sufficient to meet the I/O needs of the database?

See the total I/O operations (reads and writes) by the database, and compare those

with the number of disks used. Remember to include the I/O activity of LGWR and

ARCH processes.

Finding the SQL Statement executed by Sessions Waiting for I/O

Use the following query to determine, at a point in time, which sessions are waiting

for I/O:

SELECT SQL_ADDRESS, SQL_HASH_VALUE

FROM V$SESSION

WHERE EVENT LIKE 'db file%read';

Finding the Object Requiring I/O

To determine the possible causes, first query V$SESSION to identify the value of ROW_

WAIT_OBJ# when the session waits for db file scattered read. For example:

SELECT row_wait_obj#

FROM V$SESSION

WHERE EVENT = 'db file scattered read';

To identify the object and object type contended for, query DBA_OBJECTS using the

value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type

FROM DBA_OBJECTS

WHERE data_object_id = &row_wait_obj;

 

 

 关于什么是 fast full scan，其实就是全索引扫描。当查询的列值都包含在聚合索引或者普通索引中时，我们就可以采用

全索引扫描，一次性得到想要的数据。但是只要有一个待查询的列不在聚合索引或者索引中，那么就用不了全索引扫描(只有采用全表扫描了）

||||||||||||||||||||||||||||||||||||

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

关于 fast full scan的 metalink 帖子如下

Index Fast Full Scan Usage To Avoid Full Table Scans [ID 70135.1]  

 

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

 

  Modified 08-JAN-2002     Type BULLETIN     Status PUBLISHED  

 

 

PURPOSE

 

This article discusses using Index Fast Full Scans (Index FFS) to avoid Full

Table Scans (FTS).

 

SCOPE & APPLICATION

 

Support Analysts and customers who are experiencing problems with their queries

doing FTS as opposed to using Index FFS.

 

RELATED DOCUMENTS

 

Oracle8 Server Concepts Manual

 

 

When Will Index FFS be used in preference to FTS?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From the Oracle8 Server Concepts manual:

1. The index must contain all the columns referenced in the query.

2. Index FFS is only available with Cost Based Optimizer (CBO) (Index hint forces CBO).

3. Index FFS can be hinted with /*+ INDEX_FFS(<table> <index>) */  .

 

Index FFS was introduced in 7.3. In Oracle7 it requires initialization parameter

V733_PLANS_ENABLED to be set to TRUE .

 

An Index FFS will scan all blocks in the index. The returned data is not sorted.

Index FFS can use multiblock I/O and can be parallelized just like a

Full Table Scan.

 

 

Examples:

~~~~~~~~~

 

Using the standard emp and dept tables (these can be created with UTLSAMPL.SQL)

on Oracle 8.0.5 with no statistics on tables and indexes. The execution plans

were generated using autotrace (see Note:43214.1) :

 

Preparation: Create a composite index

   create index emp_ix on emp(empno, deptno, ename);

 

...........................................................................

 

1. Query of single table, where query selects all columns in index

SQL> select /*+ INDEX_FFS (emp emp_ix) */ empno, deptno, ename from emp;

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=21 Bytes=693)

   1    0   INDEX (FAST FULL SCAN) OF 'EMP_IX' (NON-UNIQUE) (Cost=4 Ca

          rd=21 Bytes=693)

 

...........................................................................

 

2. Query single table, where columns of index are either in select or where

   clause

SQL>  select /*+ INDEX_FFS (emp emp_ix) */ empno, ename from emp

      where deptno > :bind1;

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=66)

   1    0   INDEX (FAST FULL SCAN) OF 'EMP_IX' (NON-UNIQUE) (Cost=4 Ca

          rd=2 Bytes=66)  

 

...........................................................................

 

3. Query with all columns of index plus column that is not in the index

SQL> select /*+ INDEX_FFS (emp emp_ix) */ empno, ename from emp

     where deptno > :bind1 and sal <:bind2;

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=46)

   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=46)

 

Note: CBO chooses FTS as INDEX FFS cannot satisfy all columns in query

 

...........................................................................

 

4. Query involving some of the columns in composite index

SQL> select /*+ INDEX_FFS (emp emp_ix) */  ename from emp;

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=21 Bytes=147)

   1    0   INDEX (FAST FULL SCAN) OF 'EMP_IX' (NON-UNIQUE) (Cost=4 Ca

          rd=21 Bytes=147)

 

NOTE: INDEX FFS still chosen if subset of index columns in query

 

...........................................................................

 

5. Query involving join

SQL> select  /*+ INDEX_FFS ( e emp_ix) */  e.ename, d.dname

     from emp e , dept d

     where e.deptno=d.deptno;

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=4 Bytes=168)

   1    0   HASH JOIN (Cost=6 Card=4 Bytes=168)

   2    1     INDEX (FAST FULL SCAN) OF 'EMP_IX' (NON-UNIQUE) (Cost=4

          Card=21 Bytes=420)

   3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=21 Bytes=462)

 

...........................................................................

 Related

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

Products

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

 

Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

Keywords

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

CBO

|||||||||||||||||||||||||||||||||