【等待事件】等待事件系列（1）--User I/O型別

1 BLOG文件結構圖

2 前言部分

2.1 導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① 等待事件系列（1）--User I/O型別

Tips：

① 本文在ITpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）有同步更新

② 文章中用到的所有程式碼，相關軟體，相關資料請前往小麥苗的雲盤下載（http://blog.itpub.net/26736162/viewspace-1624453/）

③ 若文章程式碼格式有錯亂，推薦使用搜狗、360或QQ瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式顯示有問題，可以去部落格園地址閱讀

④ 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

====》2097152*512/1024/1024/1024=1G

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

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

3 等待事件的源起

談到等待事件，必然會提到一種流行的診斷方法論OWI，即Oracle Wait Interface.

等待事件的概念大概是從Oracle 7.0.12中引入的，剛引入的時候大約有100多個等待事件，在Oracle 8.0中這個數目增大到了大約150個，在Oracle 8i中大約有220個事件，在Oracle 9i中大約有400多個等待事件，在Oracle 10gR2中，大約有800多個等待事件，在11gR2中約有1000多個等待事件。隨著等待事件的逐步完善，也能夠反映出對於問題的診斷粒度越來越細化。

雖然不同版本和元件安裝可能會有不同數目的等待事件，但是這些等待事件都可以透過查V$EVENT_NAME檢視獲得：

10.2.0.5版本：

select count(1) from v$event_name;

11g：

select count(1) from v$event_name;

4 分類

ORACLE的等待事件，主要可以分為兩類，即空閒(IDLE)等待事件和非空閒(NON-IDLE)等待事件。

1). 空閒等待事件指ORACLE正等待某種工作，在診斷和最佳化資料庫的時候，不用過多注意這部分事件。

2). 非空閒等待事件專門針對ORACLE的活動，指資料庫任務或應用執行過程中發生的等待，這些等待事件是在調整資料庫的時候需要關注與研究的。

下面來看一下ORACLE中等待事件的主要分類及各類等待事件的個數：

SELECT a.INST_ID, A.EVENT, COUNT(1)

FROM gv$session a

where a.username is not null

and a.STATUS = 'ACTIVE'

AND A.WAIT_CLASS<>'Idle'

GROUP BY a.INST_ID,A.EVENT;

SELECT wait_class#,

wait_class_id,

wait_class,

COUNT(*) AS "count"

FROM v$event_name

GROUP BY wait_class#,

wait_class_id,

wait_class

ORDER BY wait_class#;

11g：

常見的空閒事件有:

? dispatcher timer

? lock element cleanup

? Null event

? parallel query dequeue wait

? parallel query idle wait - Slaves

? pipe get

? PL/SQL lock timer

? pmon timer- pmon

? rdbms ipc message

? slave wait

? smon timer

? SQL*Net break/reset to client

? SQL*Net message from client

? SQL*Net message to client

? SQL*Net more data to client

? virtual circuit status

? client message

一些常見的非空閒等待事件有:

? db file scattered read

? db file sequential read

? buffer busy waits

? free buffer waits

? enqueue

? latch free

? log file parallel write

? log file sync

This appendix contains the following topics:

■ Classes of Wait Events

■ Descriptions of Common Wait Event Parameters

■ Descriptions of Wait Events

Information about wait events is displayed in three dynamic performance views:

■ V$SESSION_WAIT displays the events for which sessions have just completed waiting or are currently waiting.

■ V$SYSTEM_EVENT displays the total number of times all the sessions have waited for the events in that view.

■ V$SESSION_EVENT is similar to V$SYSTEM_EVENT, but displays all waits for each

session.

Many of these wait events are tied to the internal implementation of Oracle and

therefore are subject to change or deletion without notice. Application developers

should be aware of this and write their codeto tolerate missing or extra wait events.

The following SQL statement displays an alphabetical list of all Oracle wait events and the wait class to which they belong:

SQL> SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name;

4.1 Classes of Wait Events

Every wait event belongs to a class of wait event. The following list describes each of the wait classes.

Administrative

Waits resulting from DBA commands that cause users to wait (for example, an index rebuild)

Application

Waits resulting from user application code (for example, lock waits caused by row level locking or explicit lock commands)

Cluster

Waits related to Real Application Clusters resources (for example, global cache resources such as 'gc cr block busy')

Commit

This wait class only comprises one wait event - wait for redo log write confirmation after a commit (that is, 'log file sync')

Concurrency

Waits for internal database resources (for example, latches)

Configuration

Waits caused by inadequate configuration of database or instance resources (for example, undersized log file sizes, shared pool size)

Idle

Waits that signify the session is inactive, waiting for work (for example, 'SQL*Net message from client')

Network

Waits related to network messaging (for example, 'SQL*Net moredata to dblink')

Other

Waits which should not typically occur on a system (for example, 'wait for EMON to spawn')

Queue

Contains events that signify delays in obtaining additional data in a pipelined environment. The time spent on these wait events indicates inefficiency or other problems in the pipeline. It affects features such as Oracle Streams, parallel queries, or DBMS_PIPEPL/SQL packages.

Scheduler

Resource Manager related waits (for example, 'resmgr: become active')

System I/O

Waits for background process I/O (for example, DBWR wait for 'db file parallel write')

User I/O

Waits for user I/O (for example 'db file sequential read')

4.2 Descriptions of Common Wait Event Parameters

Oracle? Database Reference 11g Release 2 (11.2) E40402-09

Descriptions of Common Wait Event Parameters

This section provides descriptions of some of the more common wait event parameters.

block#

This is the block number of the block for which Oracle needs to wait. The block number is relative to the start of the file. Tofind the object to which this block belongs, issue the following SQL statement:

select segment_name, segment_type, owner, tablespace_name

from dba_extents

where file_id = file#

and block#

between block_id and block_id + blocks - 1;

blocks

The number of blocks that is being either read from or written to the file. The block

size is dependent on the file type:

■ Database files have a block size of DB_BLOCK_SIZE

■ Logfiles and control files have a block size that is equivalent to the physical block size of the platform

break?

If the value for this parameter equals 0, a reset was sent to the client. A nonzero value indicates that a break was sent to the client.

class

The class of the block describes how the contents of the block are used. For example, class 1 represents data block, and class 4 represents segment header.

dba

The initials "dba" represents the data block address, which consists of a file number and a block number.

driver id

The address of the disconnect function of the driver that is currently being used.

file#

The following query returns the name of the database file:

select *

from v$datafile

where file# = file#;

id1

The first identifier (id1) of the enqueue or global lock takes its value from P2 or P2RAW. The meaning of the identifier depends on the name (P1).

id2

The second identifier (id2) of the enqueue or global lock takes its value from P3 or P3RAW. The meaning of the identifier depends on the name (P1).

le

The relative index number into V$GC_ELEMENT.

mode

The mode is usually stored in the low order bytes of P1 or P1RAW and indicates the mode of the enqueue or global lock request.This parameter has one of the following values:

Table C-1 Lock Mode Values

Mode Value	Description
1	Null mode
2	Sub-Share
3	Sub-Exclusive
4	Share
5	Share/Sub-Exclusive
6	Exclusive

Use the following SQL statement to retrieve the name of the lock and the mode of the lock request:

select chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1, 16711680)/65535) "Lock",

bitand(p1, 65535) "Mode"

from v$session_wait

where event = 'DFS enqueue lock acquisition';

name and type

The name or "type" of the enqueue or globallock can be determined by looking at the two high order bytes of P1 or P1RAW. The name is always two characters. Use the following SQL statement to retrieve the lock name.

select chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1,16711680)/65535) "Lock"

from v$session_wait

where event = 'DFS enqueue lock acquisition';

namespace

The name of the object namespace as it is displayed in V$DB_OBJECT_CACHE view.

requests

The number of I/Os that are "requested." This differs from the number of blocks in that one request could potentially contain multiple blocks.

session#

The number of the inactive session. Use the following SQL statement to find more information about the session:

select *

from v$session

where sid = session#;

waited

This is the total amount of time the sessionhas waited for this session to terminate.

5 重要等待事件

一些常見的重要的等待事件：

1)資料檔案I/O相關的等待事件：

l db file sequential read

l db file scattered read

l db file parallel read

l direct path read

l direct path write

2)控制檔案I/O相關的等待事件：

l control file parallel write

l control file sequential read

l control file single write

3)重做日誌檔案I/O相關的等待事件：

l log file parallel write

l log file sync

l log file sequential read

l log file single write

l switch logfile command

l log file switch completion

l log file switch (clearing log file)

l log file switch (checkpoint incomplete)

l log switch/archive

l log file switch (archiving needed)

4)快取記憶體區I/O相關的等待事件：

l db file parallel write

l db file single write

l write complete waits

l free buffer waits

6 User I/O型別

SELECT * FROM V$EVENT_NAME A WHERE A.WAIT_CLASS = 'User I/O';

11g User I/O大約有84個。

6.1 db file sequential read（資料檔案順序讀）

db file sequential read這個等待事件在實際生產庫非常常見，是個與User I/O相關的等待事件，通常顯示與單個資料塊相關的讀取操作，在大多數情況下，讀取一個索引塊或者透過索引讀取一個資料塊時，都會記錄這個等待。當Oracle 需要每次I/O只讀取單個資料塊這樣的操作時，會產生這個等待事件。最常見的情況有索引的訪問（除IFFS外的方式），回滾操作，以ROWID的方式訪問表中的資料，重建控制檔案，對檔案頭做DUMP等。如果db file scattered read事件是伴隨Multi Block I/O發生的等待事件，那db file sequential read事件就是伴隨Single Block I/O發生的等待事件。每次發生Single Block I/O時，就會發生一次db file sequential read事件的等待。Single Block I/O可以發生在從檔案讀取一個塊的所有工作上，一般在索引掃描、透過ROWID的表掃描、讀取控制檔案和檔案頭時發生。

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表Oracle要讀取的檔案的絕對檔案號即File#，P2代表Oracle從這個檔案中開始讀取的起始資料塊的BLOCK號即Block#，P3代表Oracle從這個檔案開始讀取的BLOCK號後讀取的BLOCK數量即Blocks，通常這個值為1，表明是單個BLOCK被讀取，如果這個值大於1，則是讀取了多個BLOCK，這種多BLOCK讀取常常出現在早期的Oracle版本中從臨時段中讀取資料的時候。

這個等待事件有三個引數：

File#：要讀取的資料塊所在資料檔案的檔案號。

Block#：要讀取的起始資料塊號。

Blocks：要讀取的資料塊數目（這裡應該等於1）。

SELECT *

FROM v$event_name

WHERE NAME = 'db file sequential read';

db file sequential read等待使效能出現問題，這些效能問題大多數發生在低效的索引掃描、行遷移、行連結引發附加的I/O過程中。

1、應用程式層

低效的sql語句或低效的索引掃描經常被使用時，因不必要的物理I/O增加，可能增加db file sequential read等待。使用選擇性較差的索引是發生db file sequential read等待的主要原因。

2、oracle記憶體層

如果高速緩衝區過小，就會反覆發生物理I/O，因此可能增加db file sequential read等待，這時同時發生free buffer waits等待的機率較高。如果大量發生free buffer waits等待，應該考慮擴充套件快取記憶體區的大小。始終要考慮利用多重緩衝池，有效使用快取記憶體區。利用多重緩衝池減少db file sequential read等到的原理，與減少db file scattered read等待的原理相同。

3、OS/裸裝置層

如果sql最佳化或快取記憶體區最佳化、重建表也不能解決問題，就應該懷疑I/O系統本身的效能。將db file sequential read事件的等待次數和等待時間比較後，如果平均等待時間長，緩慢的I/O系統成為原因的可能性高。之前也討論過，I/O系統上的效能問題在多鍾情況下均會發生，因此需要充分調查各種因素。

利用v$filestat檢視，可分別獲得各資料檔案關於Multi Block I/O和Single Block I/O的活動資訊。

SELECT F.FILE#,

F.NAME,

S.PHYRDS,

S.PHYBLKRD,

S.READTIM, --所有的讀取工作資訊

S.SINGLEBLKRDS,

S.SINGLEBLKRDTIM, --SINGLE BLOCK I/O

(S.PHYBLKRD - S.SINGLEBLKRDS) AS MULTIBLKRD, --MULTI BLOCK I/O次數

(S.READTIM - S.SINGLEBLKRDTIM) AS MULTIBLKRDTIM, --MULTI BLOCK I/O時間

ROUND(S.SINGLEBLKRDTIM /

DECODE(S.SINGLEBLKRDS, 0, 1, S.SINGLEBLKRDS),

3) AS SINGLEBLK_AVGTIM, --SINGLE BLOCK I/O 平均等待時間（CS）

ROUND((S.READTIM - S.SINGLEBLKRDTIM) /

NULLIF((S.PHYBLKRD - S.SINGLEBLKRDS), 0),

3) AS MULTIBLK_AVGTIM --MULTI BLOCK I/O 平均等待時間（CS）

FROM VFILESTATS,VDATAFILE F

WHERE S.FILE# = F.FILE#;

如果特點檔案上平均執行時間表現的過高，則應該透過提高該檔案所在的I/O系統的效能，以此改善效能。沒有關於Multi Block I/O的最合理的平均等待時間值，但一般應該維持10微妙左右的平均等待時間。

在Oracle 10g中，這個等待事件被歸入User I/O一類：

這一事件通常顯示與單個資料塊相關的讀取操作(如索引讀取)。如果這個等待事件比較顯著，可能表示在多表連線中，表的連線順序存在問題，可能沒有正確的使用驅動表；或者可能索引的使用存在問題，不加選擇地進行索引，並非索引總是最好的選擇。

還有一種特殊的情況是，全表掃描過程還會產生單塊讀的情況有，讀UNDO塊。可以參考最後的老熊文章的例子http://blog.itpub.net/26736162/viewspace-2123513/。對於這種情況的解決辦法是加索引，或等大事務執行完成後再執行SQL。

這裡的sequential也並非指的是Oracle 按順序的方式來訪問資料，和db file scattered read一樣，它指的是讀取的資料塊在記憶體中是以連續的方式存放的。

在大多數的情況下讀取一個索引資料的BLOCK或者透過索引讀取資料的一個BLOCK的時候都會去要讀取相應的資料檔案頭的BLOCK。在早期的版本中會從磁碟中的排序段讀取多個BLOCK到快取記憶體區的連續的快取中。

在大多數情況下，透過索引可以更為快速地獲取記錄，所以對於一個編碼規範、調整良好的資料庫，這個等待事件很大通常是正常的。有時候這個等待過高和儲存分佈不連續、連續資料塊中部分被快取有關，特別對於DML頻繁的資料表，資料以及儲存空間的不連續可能導致過量的單塊讀，定期的資料整理和空間回收有時候是必須的。但是在很多情況下，使用索引並不是最佳的選擇，比如讀取較大表中大量的資料，全表掃描可能會明顯快於索引掃描，所以在開發中就應該注意，對於這樣的查詢應該避免使用索引掃描。

如果這個等待事件在整個等待時間中佔主要的部分，可以採用以下的幾種方法來調整資料庫。

方法一：從AWR的報告中的"SQL ordered by Reads"部分或者從V$SQL檢視中找出讀取物理磁碟I/O最多的幾個SQL語句，最佳化這些SQL語句以減少對I/O的讀取需求。

如果有Index Range scans，但是卻使用了不該用的索引，就會導致訪問更多的BLOCK，這個時候應該強迫使用一個可選擇的索引，使訪問同樣的資料儘可能的少的訪問索引塊，減少物理I/O的讀取；如果索引的碎片比較多，那麼每個BLOCK儲存的索引資料就比較少，這樣需要訪問的BLOCK就多，這個時候一般來說最好把索引rebuild，減少索引的碎片；如果被使用的索引存在一個很大的Clustering Factor，那麼對於每個索引BLOCK獲取相應的記錄的時候就要訪問更多表的BLOCK，這個時候可以使用特殊的索引列排序來重建表的所有記錄，這樣可以大大的減少Clustering Factor，例如：一個表有A,B,C,D,E五個列，索引建立在A,C上，這樣可以使用如下語句來重建表：

CREATE TABLE TABLE_NAME AS SELECT * FROM old ORDER BY A,C;

此外，還可以透過使用分割槽索引來減少索引BLOCK和表BLOCK的讀取。

方法二：如果不存在有問題的執行計劃導致讀取過多的物理I/O的特殊SQL語句，那麼可能存在以下的情況：

資料檔案所在的磁碟存在大量的活動，導致其I/O效能很差。這種情況下可以透過檢視AWR報告中的"File I/O Statistics"部分或者V$FILESTAT檢視找出熱點的磁碟，然後將在這些磁碟上的資料檔案移動到那些使用了條帶集、RAID等能實現I/O負載均衡的磁碟上去。

使用如下的查詢語句可以得到各個資料檔案的I/O分佈：

SELECT d.name NAME,

f.phyrds,

f.phyblkrd,

f.phywrts,

f.phyblkwrt,

f.readtim,

f.writetim

FROM v$filestat f,

v$datafile d

WHERE f.file# = d.file#

ORDER BY f.phyrds DESC,

f.phywrts DESC;

從Oracle9.2.0開始，我們可以從V$SEGMENT_STATISTICS檢視中找出物理讀取最多的索引段或者是表段，透過檢視這些資料，可以清楚詳細的看到這些段是否可以使用重建或者分割槽的方法來減少所使用的I/O。如果Statpack設定的level為7就會在報告中產生"Segment Statistics"的資訊。

SELECT statistic_name,

COUNT(1)

FROM v$segment_statistics T

GROUP BY T.STATISTIC_NAME;

從上面的查詢可以看到相應的統計名稱，使用下面的查詢語句就能得到讀取物理I/O最多的段：

SELECT object_name,

object_type,

statistic_name,

VALUE

FROM v$segment_statistics

WHERE statistic_name = 'physical reads'

ORDER BY VALUE DESC;

方法三：如果不存在有問題的執行計劃導致讀取過多的物理I/O的特殊SQL語句，磁碟的I/O也分佈的很均勻，這種時候我們可以考慮增大的快取記憶體區。對於Oracle8i來說增大初始化引數DB_BLOCK_BUFFERS，讓Statpack中的Buffer Cache的命中率達到一個滿意值；對於Oracle9i來說則可以使用Buffer Cache Advisory工具來調整Buffer Cache；對於熱點的段可以使用多緩衝池，將熱點的索引和表放入到KEEP Buffer Pool中去，儘量讓其在緩衝中被讀取，減少I/O。

6.1.1 例子

老熊的一篇文章：常識之外，全表掃描為何產生大量db file sequential read單塊讀(常識之外：全表掃描為何產生大量 db file sequential read 單塊讀？http://blog.itpub.net/26736162/viewspace-2123513/)：,介紹了，全表掃描過程還會產生單塊讀的情況有，讀UNDO塊。

6.2 db file scattered read（資料檔案離散讀）

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表Oracle要讀取的檔案的絕對檔案號，P2代表Oracle從這個檔案中開始讀取的BLOCK號，P3代表Oracle從這個檔案開始讀取的BLOCK號後讀取的BLOCK數量。

SELECT *

FROM v$event_name

WHERE NAME IN ('db file sequential read', 'db file scattered read');

從V$EVENT_NAME檢視可以看到，該等待事件有3個引數：

File#：要讀取的資料塊所在資料檔案的檔案號。

Block#：要讀取的起始資料塊號。

Blocks：需要讀取的資料塊數目。

這樣就可以找到那個物件：

SELECT EVENT, P1, P2, P3, ROW_WAIT_OBJ#

FROM GV$SESSION

WHERE EVENT = 'db file scattered read';

SELECT OBJECT_NAME, OBJECT_TYPE

FROM DBA_OBJECTS

WHERE OBJECT_ID = ROW_WAIT_OBJ#;

起始資料塊號加上資料塊的數量，這意味著Oracle session正在等待多塊連續讀操作的完成。

這個等待事件在實際生產庫中經常可以看到，這是一個使用者操作引起的等待事件，當使用者發出每次I/O需要讀取多個資料塊這樣的SQL 操作時或者說當Oracle從磁碟上讀取多個BLOCK到不連續的快取記憶體區的快取中，會產生這個等待事件，這個事件表明使用者程式正在讀資料到Buffer Cache中，等待直到物理I/O呼叫返回。最常見的兩種情況是全表掃描（FTS： Full Table Scan）和索引快速全掃描（IFFS： index fast full scan）。為保障效能，儘量一次讀取多個塊，這稱為Multi Block I/O。每次執行Multi Block I/O，都會等待物理I/O結束，此時等待db file scattered read事件。根據經驗，通常大量的db file scattered read等待可能意味著應用問題或者索引缺失。Oracle一次能夠讀取的最多的BLOCK數量是由初始化引數DB_FILE_MULTIBLOCK_READ_COUNT來決定。

這個名稱中的scattered(發散)，可能會導致很多人認為它是以scattered 的方式來讀取資料塊的，其實恰恰相反，當發生這種等待事件時，SQL的操作都是順序地讀取資料塊的，比如FTS或者IFFS方式（如果忽略需要讀取的資料塊已經存在記憶體中的情況）。這裡的scattered指的是讀取的資料塊在記憶體中的存放方式，他們被讀取到記憶體中後，是以分散的方式存在在記憶體中，而不是連續的。

在生產環境之中，db file scattered read這個等待事件可能更為常見。DB File Scattered Read發出離散讀，將儲存上連續的資料塊離散的讀入到多個不連續的記憶體位置。Scattered Read通常是多塊讀，在Full Table Scan或Fast Full Scan等訪問方式下使用。Scattered Read代表Full Scan，當執行Full Scan讀取資料到Buffer Cache時，通常連續的資料在記憶體中的儲存位置並不連續，所以這個等待被命名為Scattered Read（離散讀）。每次多塊讀讀取的資料塊數量受初始化引數DB_FILE_MULTIBLOCK_READ_COUNT限制。

Oracle按照db_file_multiblock_read_count（以下簡稱MBRC）引數值進行Multi Block I/O。這個值每個OS都有最大的界定，可以透過如下方法確認最大值。

SQL> show parameter db_file_multiblock_read_count

NAME TYPE VALUE

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

db_file_multiblock_read_count integer 128

SQL> alter system set db_file_multiblock_read_count=100000; --試圖變更為超大值

系統已更改。

SQL> show parameter db_file_multiblock_read_count;

NAME TYPE VALUE

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

db_file_multiblock_read_count integer 4096 --確認4096是一次可以讀取的最多塊數

oracle在執行FTS時也進行Single Block I/O。這時即便是FTS也會發生db file sequential read等待。FTS上使用Single Block I/O或讀取比MBRC值小的塊數的情況如下：

（1）達到區的界線時：如一個區有9個塊，一次Multi Block I/O讀取8個塊，則一次以Multi Block I/O讀取之後的剩餘一個塊透過Single Block I/O讀取，如果剩下的塊有兩個，就會執行Multi Block I/O，而且只讀取兩個塊。

（2）掃描過程中讀取被快取的塊時：如讀取8個塊時，其中第三個塊被快取，oracle將前兩個塊透過Multi Block I/O讀取，對於第三個塊執行一次Logical I/O，剩下的5個塊透過Multi Block I/O讀取。這種情況經常發生時，因引發多次的I/O，可能成為FTS速度下降的原因。

（3）存在行連結時：在執行FTS的過程中，如果發現了行連結，oracle為了讀取剩下的行引起的附加I/O，此時執行Single Block I/O。

圖9-17簡要說明了Scattered Read的資料讀取方式。

完成對等待事件的分類之後，Oracle 10g的ADDM可以很容易地透過故障樹分析定位到問題所在，幫助使用者快速發現資料庫的瓶頸及瓶頸的根源，這就是Oracle的ADDM專家系統的設計思想。透過圖9-18可以直觀而清晰地看到這個等待模型和ADDM結合實現的Oracle專家診斷系統。

這種情況通常顯示與全表掃描相關的等待。當資料庫進行全表掃時，基於效能的考慮，資料會分散(scattered)讀入Buffer Cache。如果這個等待事件比較顯著，可能說明對於某些全表掃描的表，沒有建立索引或者沒有建立合適的索引，我們可能需要檢查這些資料表已確定是否進行了正確的設定。然而這個等待事件不一定意味著效能低下，在某些條件下Oracle 會主動使用全表掃描來替換索引掃描以提高效能，這和訪問的資料量有關，在CBO 下Oracle 會進行更為智慧的選擇，在RBO 下Oracle 更傾向於使用索引。因為全表掃描被置於LRU(Least Recently Used，最近最少適用)列表的冷端(cold end)，對於頻繁訪問的較小的資料表，可以選擇把他們Cache 到記憶體中，以避免反覆讀取。

在實際環境的診斷過程中，可以透過v$session_wait檢視發現session的等待，再結合其他檢視找到存在問題的SQL等根本原因，從而從根本上解決問題。在11g也可以直接透過v$session檢視來查詢等待事件。當這個等待事件比較顯著時，也可結合v$session_longops動態效能檢視來進行診斷，該檢視記錄了長時間（執行時間超過6秒的）執行的事務。

db file scattered read事件與db file sequential read事件相同，是oracle中最經常發生的等待事件。因為從資料檔案讀取塊時只能執行Multi Block I/O或Single Block I/O。

1、應用程式層

需要篩選出主要發生db file scattered read等待的sql語句。如果不必要的執行FTS或Index Full San，修改sql語句或建立更合理的索引就可以解決。大量讀取資料時多數情況下FTS效能更好。不是盲目的建立索引，而是要考慮相應的sql語句後，判斷FTS有利，還是Index Full San有利。

2、oracle記憶體層

如果快取記憶體區過小，就會反覆需要物理I/O，相應的db file scattered read等待也會增加。這時free buffer waits等待事件一同出現的機率較高。FTS引起的db file scattered read等待的嚴重性不僅在於需要I/O，而且在於降低快取記憶體區的效率，進而影響會話工作。從這種角度出發，處理FTS的有效方法之一就是使用多重緩衝池。讀取一次後不再使用的資料，有必要儲存到快取記憶體區從而導致影響其他使用者的工作嗎？多重緩衝池雖然是有效管理快取記憶體區的強有力的方法，但是遺憾的是沒有被廣泛使用。多重緩衝池從三個方面改善快取記憶體區的效能。第一，將經常訪問的物件儲存與記憶體，進而將物理I/O最小化。第二，臨時性資料所佔用的記憶體被快速的重新使用，進而將記憶體的浪費最小化。第三，因為每個緩衝池各使用不同的cache buffers lru chain鎖存器，所以有減少鎖存器爭用的效果。指定DEFAULT將適用預設的緩衝池。這個選項適用於沒有分配給KEEP緩衝池和RECYCLE緩衝池的其它資料庫物件。通常將經常訪問的物件放入KEEP緩衝池中，指定KEEP將把資料塊放入KEEP緩衝池中。維護一個適當尺寸的KEEP緩衝池可以使Oracle在記憶體中保留資料庫物件而避免I/O操作。通常將偶爾訪問的大表放入RECYCLE緩衝池中，指定RECYCLE將把資料塊放入RECYCLE緩衝池中。一個適當尺寸的RECYCLE緩衝池可以減少預設緩衝池為RECYCLE緩衝池的資料庫物件的數量，以避免它們佔用不必要的緩衝空間。

有效使用FTS的另一種方法是將db_file_multiblock_read_count引數值提高。這個引數決定執行Multi Block I/O時一次讀取的塊數。因此這個值高，FTS速度相應也會提升，而且db file scattered read等待也會相應減少。將這個值在全系統級上設定得高，並不太妥當。最好是利用alter session set ...命令，只在執行sql語句期間提升這個值。因為這個值如果升高，有關FTS的費用會算的較低，可能會導致sql執行計劃的變更。

較大的塊也是提高FTS效能的方法。較大的塊在如下兩個方面改善FTS的效能。第一，增加一個塊所包含的行數，這樣相同大小的表時使用更少的塊數，相應的Multi Block I/O次數也會減少。第二，塊的大小較大，則發生行連結或行遷移的機率會降低，附加的I/O也隨之降低。大部分OLTP系統上一般只是用標準塊大小（8K）。但是經常掃描大量資料的OLAP上使用更大的塊能改善效能。

3、oracle段層

需要檢查，透過合理執行partition能否減少FTS範圍。例如為獲得100萬個資料中10萬個資料而執行FTS時，將10萬個資料相應的範圍利用partition分開，則可以將FTS的範圍縮小至1/10。

4、OS/裸裝置層

如果利用sql的最佳化或快取記憶體區的最佳化也不能解決問題，就應該懷疑I/O系統本身的效能。將db file scattered read事件的等待次數和等待時間比較後，如果平均等待時間長，緩慢的I/O系統成為原因的可能性高。之前也討論過，I/O系統上的效能問題在多鍾情況下均會發生，因此需要充分調查各種因素。

利用v$filestat檢視，可分別獲得各資料檔案關於Multi Block I/O和Single Block I/O的活動資訊。

select f.file#,

f.name,

s.phyrds,

s.phyblkrd,

s.readtim, --所有的讀取工作資訊

s.singleblkrds,

s.singleblkrdtim, --Single Block I/O

(s.phyblkrd - s.singleblkrds) as multiblkrd, --Multi Block I/O次數

(s.readtim - s.singleblkrdtim) as multiblkrdtim, --Multi Block I/O時間

round(s.singleblkrdtim /

decode(s.singleblkrds, 0, 1, s.singleblkrds),

3) as singleblk_avgtim, --Single Block I/O 平均等待時間（cs）

round((s.readtim - s.singleblkrdtim) /

nullif((s.phyblkrd - s.singleblkrds), 0),

3) as multiblk_avgtim --Multi Block I/O 平均等待時間（cs）

from vfilestats,vdatafile f

where s.file# = f.file#;

如果特點檔案上平均執行時間表現的過高，則應該透過提高該檔案所在的I/O系統的效能，以此改善效能。沒有關於Multi Block I/O的最合理的平均等待時間值，但一般應該維持10微妙左右的平均等待時間。

如果這個等待事件在整個等待時間中佔了比較大的比重，可以如下的幾種方法來調整Oracle資料庫：

方法一：找出執行全表掃描（FTS： Full Table Scan）和索引快速全掃描（IFFS： index fast full scan）掃描的SQL語句，判斷這些掃描是否是必要的，是否導致了比較差的執行計劃，如果是，則需要調整這些SQL語句，可以結合v$session_longops 動態效能檢視來進行診斷，該檢視中記錄了長時間(執行時間超過6 秒的)執行的事物，可能很多是全表掃描操作。

從Oracle9i開始提供了一個檢視V$SQL_PLAN用於記錄當前系統Library Cache中SQL語句的執行計劃，可以透過這個檢視找到存在問題的SQL語句，即可以很快的幫助找到那些全表掃描或者Fast Full Index掃描的SQL語句，這個檢視會自動忽略掉關於資料字典的SQL語句。

查詢全表掃描的SQL語句可以使用如下語句：

透過V$SQL_PLAN和V$SQLTEXT聯合，獲得全表掃描的SQL語句

SELECT sql_text

FROM v$sqltext t,

v$sql_plan p

WHERE t.hash_value = p.hash_value

AND p.operation = 'TABLE ACCESS'

AND p.options = 'FULL'

ORDER BY p.hash_value,

t.piece;

獲得全表掃描的物件

SELECT DISTINCT object_name,

object_owner

FROM v$sql_plan p

WHERE p.operation = 'TABLE ACCESS'

AND p.options = 'FULL'

AND object_owner = 'SYS';

查詢Fast Full Index掃描的SQL語句可以使用如下語句：

SELECT sql_text

FROM v$sqltext t,

v$sql_plan p

WHERE t.hash_value = p.hash_value

AND p.operation = 'INDEX'

AND p.options = 'FULL SCAN'

ORDER BY p.hash_value, t.piece;

獲得全索引掃描的物件

SELECT DISTINCT object_name,

object_owner

FROM v$sql_plan p

WHERE p.operation = 'INDEX'

AND p.options = 'FULL SCAN'

AND object_owner = 'SYS';

如果是Oracle8i的資料庫，可以從V$SESSION_EVENT檢視中找到關於這個等待事件的程式sid，然後根據sid來跟蹤相應的會話的SQL。

select sid,event from v$session_event where event='db file sequential read'

或者可以檢視物理讀取最多的SQL語句的執行計劃，看是否裡面包含了全表掃描和Fast Full Index掃描。透過如下語句來查詢物理讀取最多的SQL語句：

select sql_text from (

select * from v$sqlarea

order by disk_reads)

where rownum<=10;

方法二：有時候在執行計劃很好情況下也會出現多BLOCK掃描的情況，這時可以透過調整Oracle資料庫的多BLOCK的I/O，設定一個合理的Oracle初始化引數DB_FILE_MULTIBLOCK_READ_COUNT，儘量使得滿足以下的公式：

DB_BLOCK_SIZE x DB_FILE_MULTIBLOCK_READ_COUNT = max_io_size of system

DB_FILE_MULTIBLOCK_READ_COUNT是指在全表掃描中一次能夠讀取的最多的BLOCK數量，這個值受作業系統每次能夠讀寫最大的I/O限制，如果設定的值按照上面的公式計算超過了作業系統每次的最大讀寫能力，則會預設為max_io_size/db_block_size。例如DB_FILE_MULTIBLOCK_READ_COUNT設定為32，DB_BLOCK_SIZE為8K，這樣每次全表掃描的時候能讀取256K的表資料，從而大大的提高了整體查詢的效能。設定這個引數也不是越大越好的，設定這個引數之前應該要先了解應用的型別，如果是OLTP型別的應用，一般來說全表掃描較少，這個時候設定比較大的DB_FILE_MULTIBLOCK_READ_COUNT反而會降低Oracle資料庫的效能，因此CBO在某些情況下會因為多BLOCK讀取導致COST比較低從而錯誤的選用全表掃描。

方法三： 透過對錶和索引使用分割槽、將快取區的LRU末端的全表掃描和IFFS掃描的的BLOCK放入到KEEP快取池中等方法調整這個等待事件。

6.3 db file parallel read

SELECT *

FROM v$event_name

WHERE NAME IN ('db file parallel read');

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1為files代表有多少個檔案被讀取所請求，P2為blocks代表總共有多少個BLOCK被請求，P3為requests代表總共有多少次I/O請求。

db file parallel read物理讀等待事件涉及到的資料塊均是不連續的，同時可以跨越extent，這點不像db file scattered read。

這是一個很容易引起誤導的等待事件，實際上這個等待事件和並行操作（比如並行查詢，並行DML）沒有關係。這個事件發生在資料庫恢復的時候，當有一些資料塊需要恢復的時候，Oracle會以並行的方式把他們從資料檔案中讀入到記憶體中進行恢復操作。當Oracle從多個資料檔案中並行的物理讀取多個BLOCK到記憶體的不連續緩衝中(可能是快取記憶體區或者是PGA)的時候可能就會出現這個等待事件。這種並行讀取一般出現在恢復操作中或者是從緩衝中預取資料達到最最佳化(而不是多次從單個BLOCK中讀取，buffer prefetch以最佳化多個單塊讀)。這個事件表明會話正在並行執行多個讀取的需求。注意：在11g之前，這個等待事件發生在資料檔案的恢復過程中，但11g中新增了prefetch的特性，所以也可能導致這個等待事件的產生。

如果在等待時間中這個等待事件佔的比重比較大，可以按照處理db file sequential read等待事件的方法來處理這個事件。

若是由於prefetch引起的效能問題，我們可以透過新增隱含引數來解決該問題。可以參考blog：http://blog.itpub.net/26736162/viewspace-2123473

set pagesize 9999

set line 9999

col NAME format a40

col KSPPDESC format a50

col KSPPSTVL format a20

SELECT a.INDX,

a.KSPPINM NAME,

a.KSPPDESC,

b.KSPPSTVL

FROM x$ksppi a,

x$ksppcv b

WHERE a.INDX = b.INDX

and lower(a.KSPPINM) IN ('_db_block_prefetch_quota','_db_block_prefetch_limit','_db_file_noncontig_mblock_read_count');

ALTER SYSTEM SET "_db_block_prefetch_quota"=0 SCOPE=SPFILE SID='*';

ALTER SYSTEM SET "_db_block_prefetch_limit"=0 SCOPE=SPFILE SID='*';

ALTER SYSTEM SET "_db_file_noncontig_mblock_read_count"=0 SCOPE=SPFILE SID='*';

SYS@oraESKDB1> set pagesize 9999

SYS@oraESKDB1> set line 9999

SYS@oraESKDB1> col NAME format a40

SYS@oraESKDB1> col KSPPDESC format a50

SYS@oraESKDB1> col KSPPSTVL format a20

SYS@oraESKDB1> SELECT a.INDX,

2 a.KSPPINM NAME,

3 a.KSPPDESC,

4 b.KSPPSTVL

5 FROM x$ksppi a,

6 x$ksppcv b

7 WHERE a.INDX = b.INDX

8 and lower(a.KSPPINM) IN ('_db_block_prefetch_quota','_db_block_prefetch_limit','_db_file_noncontig_mblock_read_count');

INDX NAME KSPPDESC KSPPSTVL

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

881 _db_block_prefetch_quota Prefetch quota as a percent of cache size 10

883 _db_block_prefetch_limit Prefetch limit in blocks 0

1156 _db_file_noncontig_mblock_read_count number of noncontiguous db blocks to be prefetched 11

SYS@oraESKDB1> ALTER SYSTEM SET "_db_file_noncontig_mblock_read_count"=0 SCOPE=SPFILE SID='*';

System altered.

SYS@oraESKDB1> ALTER SYSTEM SET "_db_block_prefetch_quota"=0 SCOPE=SPFILE SID='*';

System altered.

SYS@oraESKDB1> ALTER SYSTEM SET "_db_block_prefetch_limit"=0 SCOPE=SPFILE SID='*';

System altered.

SYS@oraESKDB1> ALTER SYSTEM SET "_db_file_noncontig_mblock_read_count"=0 SCOPE=SPFILE SID='*';

6.4 db file single write

這個等待事件通常只發生在一種情況下，就是Oracle 更新資料檔案頭資訊時（比如發生Checkpoint）。

當這個等待事件很明顯時，需要考慮是不是資料庫中的資料檔案數量太大，導致Oracle需要花較長的時間來做所有檔案頭的更新操作（checkpoint）。

SELECT *

FROM v$event_name

WHERE NAME IN ('db file single write');

這個等待事件有三個引數：

l file#: 需要更新的資料塊所在的資料檔案的檔案號。查詢檔案號的SQL語句是：SELECT * FROM v$datafile WHERE file# = ;

l block#：需要更新的資料塊號，如果BLOCK號不是1，則可以透過如下查詢查出Oracle正在寫入的物件是什麼：

SELECT segment_name , segment_type ,

owner , tablespace_name

FROM sys.dba_extents

WHERE file_id =

AND

BETWEEN block_id AND block_id + blocks -1；

l blocks：需要更新的資料塊數目（通常來說應該等於1），或Oracle寫入file＃的資料檔案中從BLOCK#開始寫入的BLOCK的數量。頭一般來說都是BLOCK1，作業系統指定的檔案頭是BLOCK0，如果BLOCK號大於1，則表明Oracle正在寫入的是一個物件而不是檔案頭。

6.5 direct path read（直接路徑讀、DPR）

直接路徑讀等待事件的3個引數分別是：file#（指絕對檔案號）、first block#和block數量。

SELECT * FROM V$EVENT_NAME A WHERE A.NAME = 'direct path read';

這個等待事件有三個引數：

file number: 等待I/O讀取請求的檔案的絕對檔案號

first dba: 等待I/O讀取請求的第一個BLOCK號

block cnt: 以first block為起點，總共有多少個連續的BLOCK被請求讀取

由引數P1與P2推得訪問的資料物件：

select s.segment_name, s.partition_name

from dba_extents s

where between s.block_id and (s.block_id + s.blocks -1) and s.file_id =

直接路徑讀（direct path read）通常發生在Oracle直接讀取資料到PGA時，這個讀取不需要經過SGA。這類讀取通常在以下情況被使用：

①　大量的磁碟排序I/O操作在排序操作（ORDER BY、GROUP BY、UNION、DISTINCT、ROLLUP、合併連線）時，由於PGA中的SORT_AREA_SIZE空間不足，無法在PGA中完成排序，需要利用TEMP表空間進行排序。當從TEMP表空間中讀取排序結果時，會產生direct path read等待。注意，從Oracle 10g開始表現為direct path read temp等待事件。在DSS/OLAP系統中，存在大量的direct path read是很正常的，但是在OLTP系統中，通常顯著的直接路徑讀都意味著系統應用存在問題，從而導致大量的磁碟排序讀取操作。

②　大量的Hash Join操作，利用Temp表空間儲存Hash區，使用Hash連線的SQL語句，將不適合位於記憶體中的雜湊分割槽重新整理到Temp表空間中。為了查明匹配SQL謂詞的行，臨時表空間中的雜湊分割槽被讀回到記憶體中，此時會產生direct path read等待。

③　SQL語句的並行查詢，並行查詢從屬程式 使用並行掃描的SQL語句也會影響系統範圍的direct path read等待事件。在並行執行過程中，direct path read等待事件與從屬查詢有關，而與父查詢無關，執行父查詢的會話基本上會在PX Deq:Execute Reply上等待，從屬查詢會產生direct path read等待事件。

④　預讀操作

⑤　序列全表掃描（Serial Table Scan），大表的全表掃描，在Oracle 11g中，全表掃描的演算法有新的變化，根據表的大小、快取記憶體的大小等資訊，決定是否繞過SGA直接從磁碟讀取資料，也被稱為自適應直接讀（Adaptive Direct Read）。Oracle 10g的全表掃描是透過db file scattered read讀取到Buffer Cache中，在Oracle 11g中，認為大表全表掃描時使用直接路徑讀，可能比資料檔案離散讀（db file scattered read）速度更快，使用的latch也更少，即對於大表執行直接路徑讀效率更好，而對於小表透過將其快取可能受益更大。Oracle透過隱含引數“_small_table_threshold”來界定大表小表的臨界。這種新特性的好處是可以降低Buffer Cache的競爭，但是每次都要發生物理讀。若是有多個會話同時去掃描同一張大表，則這樣會增大物理I/O，也有可能導致系統的問題，而且在讀取之前可能需要觸發檢查點，避免讀到舊的映像，這些都有可能影響系統效能。

db file sequential read、db file scattered read、direct path read是常見的集中資料讀方式，下圖簡要描述了這3種方式的讀取示意。

大量的direct path read等待時間最可能是一個應用程式問題。

direct path read事件由SQL語句驅動，這些SQL語句執行來自臨時的或常規的表空間的直接讀取操作。當輸入的內容大於PGA中的工作區域時，帶有需要排序的函式的SQL語句將排序結果寫入到臨時表空間中，臨時表空間中的排序順序串隨後被合併，用於提供最終的結果。讀取排序結果時， Oracle會話在direct path read等待事件上等待。

對於這一寫入等待，我們應該找到I/O操作最為頻繁的資料檔案（如果有過多的排序操作，很有可能就是臨時檔案），分散負載，加快其寫入操作。

DB_FILE_DIRECT_IO_COUNT初始化引數可能影響direct path read的效能。

直接讀取可能按照同步或非同步的方式執行，取決於平臺和初始化引數disk_asynch_io引數的值。使用非同步I/O時，系統範圍的等待的事件的統計可能不準確，會造成誤導作用。

該事件一般不可能顯示為主要的瓶頸，但它實際上也許是就是禍首。由於ORACLE統計等待時間的方式會造成統計的時間量不準確(如：從屬查詢產生的時間無法進行統計)，所以對該事件不應該使用v$session_event檢視中的total_wait或time_waited進行估計，應該使用v$sesstat檢視中的直接讀取操作次數(physical reads direct)進行判斷：

select a.NAME,

b.SID,

b.VALUE,

round((sysdate - c.LOGON_TIME) * 24) hours_connected

from vstatnamea,vsesstat b, v$session c

where b.SID = c.SID

and a.STATISTIC# = b.STATISTIC#

and b.VALUE > 0

and a.NAME = 'physical reads direct'

order by b.VALUE;

由direct path read事件產生的原因，我們需要判斷該事件正在讀取什麼段(如：雜湊段、排序段、一般性的資料檔案)，由此可判斷產生該事件的原因是什麼，可使用以下語句進行查詢：

SELECT a.event,

a.sid,

c.sql_hash_value hash_vale,

decode(d.ktssosegt,

1,

'SORT',

2,

'HASH',

3,

'DATA',

4,

'INDEX',

5,

'LOB_DATA',

6,

'LOB_INDEX',

NULL) AS segment_type,

b.tablespace_name,

b.file_name

FROM vsessionwaita,dbadatafilesb,vsession c, x$ktsso d

WHERE c.saddr = d.ktssoses(+)

AND c.serial# = d.ktssosno(+)

AND d.inst_id(+) = userenv('instance')

AND a.sid = c.sid

AND a.p1 = b.file_id

AND a.event = 'direct path read'

UNION ALL

SELECT a.event,

a.sid,

d.sql_hash_value hash_value,

decode(e.ktssosegt,

1,

'SORT',

2,

'HASH',

3,

'DATA',

4,

'INDEX',

5,

'LOB_DATA',

6,

'LOB_INDEX',

NULL) AS segment_type,

b.tablespace_name,

b.file_name

FROM v$session_wait a,

dba_temp_files b,

v$parameter c,

v$session d,

x$ktsso e

WHERE d.saddr = e.ktssoses(+)

AND d.serial# = e.ktssosno(+)

AND e.inst_id(+) = userenv('instance')

AND a.sid = d.sid

AND b.file_id = a.p1 - c.VALUE

AND c.NAME = 'db_files'

AND a.event = 'direct path read';

注：如果是從臨時檔案中讀取排序段的會話，則表明SORT_AREA_SIZE或PGA_AGGREGATE_TARGET的設定是不是偏小。如果是從臨時檔案中讀取HASH段的會話，則表明HASH_AREA_SIZE或PAG_AGGREGATE_TARGET的設定是不是偏小。

當direct path read等待事件是由於並行查詢造成的(讀取的是一般的資料檔案而非臨時檔案)，父SQL語句的HASHVALUE與子SQL語句的HASHVALUE不同，可以透過以下SQL查詢產生子SQL語句的父SQL語句：

SELECT DECODE(A.QCSERIAL#, NULL, 'PARENT', 'CHILD') STMT_LEVEL,

A.SID,

A.SERIAL#,

B.USERNAME,

B.OSUSER,

B.SQL_HASH_VALUE,

B.SQL_ADDRESS,

A.DEGREE,

A.REQ_DEGREE

FROM VPXSESSIONA,VSESSION B

WHERE A.SID = B.SID

ORDER BY A.QCSID, STMT_LEVEL DESC;

儘量減少I/O請求的次數，初始化引數db_file_direct_io_count用來設定直接讀出和寫入操作設定最大的IO緩衝區大小，因此能影響direct path read的效能，透過設定初始化引數DB_FILE_DIRECT_IO_COUNT，使得滿足DB_BLOCK_SIZE x DB_FILE_DIRECT_IO_COUNT = max_io_size of system，在Oracle8i中預設這個值為64個BLOCK；在Oracle9i中可以設定隱含引數_DB_FILE_DIRECT_IO_COUNT，引數的值也變成了BYTES而不是BLOCK數量了，預設值也變成了1M。

l 使用10046第8層跟蹤直接讀取操作的ORACLE會話，其中P3參數列明塊讀取的數量。

l 也可使用strace,truss追蹤直接讀取或直接寫入操作的UNIX程式，從生成的TRACE檔案可獲得相應的直接IO大小。

l 在第1層使用追蹤事件10357，啟動執行直接IO操作的會話的除錯資訊。

注：

>> 1. 如果是Temp檔案，則表示該會話正在讀取它先前用direct path write操作所建立的臨時段，查明使用的是什麼型別的臨時段，有助於瞭解會話所做的事情。

SELECT DISTINCT decode(t.ktssosegt,

1,'SORT',

2,'HASH',

3,'DATA',

4,'INDEX',

5,'LOB_DATA',

6,'LOB_INDEX',

'UNDEFINED')

FROM sys.x$ktsso t

WHERE t.inst_id = userenv('instance') AND

t.kssoses = <當前session地址> AND

t.ktssosno =

>> 2. 如果是資料檔案，則可能是並行查詢從屬操作在工作，透過P1值確定資料檔案的名稱：

select s.NAME from v$datafile s where s.FILE# =

union all

select a.name

from vtempfilea,vparameter b

where b.NAME = 'db_files'

and a.FILE# + b.VALUE =

6.5.1 序列全表掃描(Serial Table Scan)--Oracle 11g全表掃描以Direct Path Read方式執行

在Oracle 11g之前，全表掃描使用db file scattered read的方式，將表中的資料塊離散的讀到Buffer Cache之後，供使用者訪問和使用，但是如果全表訪問的表非常大，則有可能佔用大量的Buffer Cache記憶體，這會導致Buffer Cache中其他資料被老化和擠出記憶體，而且在這一系列的讀取操作中，Oracle引擎需要去判斷每一個資料塊是否已經存在於記憶體中，然後還要去請求記憶體空間，不斷使用Cache Buffer Chain和Cache Buffer Lru Chain兩個Latch進行判斷,在某種程度上會加劇Latch競爭,如果全表訪問的資料只是偶爾個別的訪問,則佔據大量Buffer Cache就顯得過於昂貴,在Oracle Database 11g中,一種被稱為序列全表掃描(Serial Table Scan)的技術被引入,該特性根據資料塊的設定和統計資訊等,自動決定是採用Direct Path Read繞過SGA,還是採用常規方式讀取,因為這種自動選擇,這一特性又被稱為自適應直接讀(Adaptive Direct Read).這種方式的好處是可以降低Buffer Cache的競爭,但是每次都要發生物理讀,若是有多個會話同時去掃描同一張大表，這樣會增大IO，也有可能導致系統的問題,而且在讀取之前可能需要觸發檢查點,避免讀到舊的映像。

在Oracle Database 11g中有一個新特性，全表掃描可以透過直接路徑讀的方式來執行（Direct Path Read），這是一個合理的變化，如果全表掃描的大量資料讀取是偶發性的，則直接路徑讀可以避免大量資料對於Buffer Cache的衝擊。

當然對於小表來說，Oracle允許透過Buffer Cache來進行全表掃描，因為這可能更快，也對效能影響不大。小表受到隱含引數：_small_table_threshold 影響。如果表大於 5 倍的小表限制，則自動會使用DPR替代FTS。可以設定初始化引數： _serial_direct_read 來禁用序列直接路徑讀。

當然，Oracle透過一個內部的限制，來決定執行DPR的閾值。

可以透過設定10949事件遮蔽這個特性，返回到Oracle 11g之前的模式上：

altersession setevents '10949 trace name context forever, level 1';

還有一個引數 _very_large_object_threshold 用於設定（MB單位）使用DPR方式的上限，這個引數需要結合10949事件共同發揮作用。10949 事件設定任何一個級別都將禁用DPR的方式執行FTS，但是僅限於小於 5 倍 BUFFER Cache的資料表，同時，如果一個表的大小大於 0.8 倍的 _very_large_object_threshold 設定，也會執行DPR。

這些限定的目標在於：

對於大表的全表掃描，必須透過Direct Path Read方式執行，以減少對於Buffer Cache的衝擊和效能影響。但是我們可以透過引數調整來決定執行DPR的上限和下限。

Oracle透過隱含引數_small_table_threshold來界定大表小表的臨界，Oracle認為對於大表執行直接路徑讀取的意義比較大，對於小表透過將其快取可能受益更大。_small_table_threshold的單位為block。預設為db cache size的2%大小，在例項啟動過程中動態決定。11GR2之前，表的大小要是_small_table_threshold引數值的5倍才會採取直接路徑讀取方式，11GR2後只需要滿足_small_table_threshold定義的大小就會採取直接路徑讀取。

以下的AWR資訊是典型的DPR症狀：

在11g中，全表掃描可能使用direct path read方式，繞過buffer cache，這樣的全表掃描就是物理讀了。在10g中，都是透過gc buffer來讀的，所以不存在direct path read的問題。

一個隱含引數_serial_direct_read可以禁用序列直接路徑讀，11g預設值為auto：

禁用direct path read： _serial_direct_read = false

啟用direct path read： _serial_direct_read = true

alter system set "_serial_direct_read"=never scope=both sid='*'; 可以顯著減少direct path read

調整資料庫引數alter system setevent='10949 trace name context forever, level 1'來關閉“direct path read”（直接路徑讀）特性,使SQL語句可以從快取中查詢資料，達到降低I/O讀取量，使全表掃描的資料從快取中讀取，加快SQL語句執行速度的目的。

6.6 direct path write（直接路徑寫、DRW）

SELECT * FROM V$EVENT_NAME A WHERE A.NAME = 'direct path write';

這個等待事件有三個引數：

file number: 要寫入的絕對檔案號file number，可發現所進行的操作性質(如：排序/並行操作)

first dba: 起始塊號first dba

block cnt: 塊數block cnt，可發現直接寫入IO的大小

由引數P1與P2推得訪問的資料物件：

select s.segment_name, s.partition_name

from dba_extents s

where between s.block_id and (s.block_id + s.blocks -1) and s.file_id =

這個等待事件和direct path read 正好相反，是會話將一些資料從PGA中直接寫入到磁碟檔案(資料檔案或臨時檔案)上，而不經過SGA。這類讀取通常在以下情況被使用：

1. 直接路徑載入（使用append方式載入資料、CREATE TABLE AS SELECT）

2. 並行DML操作

3. 磁碟排序使用臨時表空間排序（記憶體不足）

最常見的直接路徑寫，多數因為磁碟排序導致。對於這一寫入等待，應該找到I/O操作最為頻繁的資料檔案（如果有過多的排序操作，很有可能就是臨時檔案），分散負載，加快其寫入操作。

如果系統存在過多的磁碟排序，會導致臨時表空間操作頻繁，對於這種情況，可以考慮為不同使用者分配不同的臨時表空間，使用多個臨時檔案，寫入不同磁碟或者裸裝置，從而降低競爭提高效能。

direct path write是允許一個會話讓一個I/O寫請求入佇列的同時處理作業系統的I/O。如果會話想確認明顯的寫是否已經完成就會出現這個等待事件。因為會話需要空的快取和空的槽位(等待之前的I/O釋放)，或者是會話需要確認所有的寫操作都已經完成。如果沒有使用非同步I/O，I/O請求會被阻塞直到之前的I/O請求完成後，但是此時不會出現I/O等待，會話稍後重新恢復並加速I/O請求的完成，此時就會出現direct path write等待。因此，對於這個等待事件容易產生兩方面的誤解：一是認為等待的總的數量不能反映出I/O請求的數量，二是消耗在這個等待事件上的總的時間不能反映出實際的等待時間。這型別的寫請求主要是用於直接裝載資料的操作(create table as select)、並行的DML操作、不在記憶體中排序的I/O以及寫入沒有cache的LOB段操作。

關於該等待事件，以下的幾點需要注意：

1. 從PGA寫入資料檔案，一個會話可以釋出多個寫入請求和連續的處理。

2. 直接寫入可以按同步或非同步方式執行，取決於平臺和DISK_ASYNC_IO引數的值。

3. 通常用於在資料載入（APPEND提示、CTAS－CREATE TABLE AS SELECT）、並行DML操作時寫入到臨時段。

4. 在使用非同步IO時，direct path write事件產生的等待時間不準確，所以透過v$sesstat檢視來獲得直接寫入次數來評估該事件的影響情況：

SELECT A.NAME,
       B.SID,
       B.VALUE,
       ROUND((SYSDATE - C.LOGON_TIME) * 24) HOURS_CONNECTED
FROM VSTATNAMEA,VSESSTAT B, V$SESSION C
WHERE A.STATISTIC# = B.STATISTIC#
   AND B.SID = C.SID
   AND B.VALUE > 0
   AND A.NAME = 'PHYSICAL WRITES DIRECT';

這個等待事件的等待時間是指等待BLOCK直到明顯的I/O請求完成的時間。通常來說，如果不是存在特殊的JOB,一般是不會出現這個等待事件，如果在等待事件中這個等待事件佔的比重比較大，可以從如下幾個方面來調整：

如果是等待的檔案是臨時表空間的檔案，那麼需要檢視是否存在大量不合理的磁碟排序，最佳化相應的存在問題的SQL語句。如果是Oracle9i可以考慮使用自動SQL執行記憶體管理，Oracle8i的話可以手工的調整各種排序區。

確認非同步I/O是否配置正確，非同步I/O不會減少這個等待事件的等待時間但是卻可以減少會話所消耗的時間。

檢查是否存在I/O消耗很嚴重的SQL語句，如果存在，嘗試最佳化SQL語句減少I/O的消耗。

最後確認一下是否達到了磁碟的I/O極限，如果是，則需要考慮更換更好的硬體裝置。

大量的direct path read等待事件最可能是一個應用程式的問題。

注：

>> 1. 如果是Temp檔案，則表示該會話正在寫入臨時表空間，查明使用臨時段的型別，有助於瞭解會話所做的事情。

SELECT DISTINCT decode(t.ktssosegt,

1,'SORT',

2,'HASH',

3,'DATA',

4,'INDEX',

5,'LOB_DATA',

6,'LOB_INDEX',

'UNDEFINED')

FROM sys.x$ktsso t

WHERE t.inst_id = userenv('instance') AND

t.kssoses = <當前session地址> AND

t.ktssosno =

>> 2. 如果是資料檔案，則可能正在執行一項直接路徑載入操作，透過P1值確定資料檔案的名稱：

select s.NAME from v$datafile s where s.FILE# =

union all

select a.name

from vtempfilea,vparameter b

where b.NAME = 'db_files'

and a.FILE# + b.VALUE =

6.6.1 direct path read temp、direct path write temp

為了排序工作在臨時區域讀寫時，等待direct path read temp、direct path write temp事件。oracle 9i為止是透過direct path read、direct path write等待觀察的。在Oracle 10g/11g中，為了區分特定的對於臨時檔案的直接讀寫操作，Oracle對direct path read/write進行了分離，將這類操作分列出來：

SELECT A.*

FROM V$EVENT_NAME A

WHERE NAME IN ('direct path read temp', 'direct path write temp');

可以看到，現在的direct path read/write temp就是單指對於臨時檔案的直接讀寫操作。排序段上的 direct path I/O是在需要排序的資料比排序所分配的PGA記憶體區大時發生的。因此在排序工作時若大量發生direct path read temp、direct path write temp等待，就可以透過追加分配記憶體區域而避免等待。

1、應用程式層

檢查需要排序的sql語句是否已經最最佳化。不必要的排序操作會導致CPU浪費、PGA區域浪費、磁碟I/O浪費。從UNION和UNION ALL的效能差異上可以得知，只靠減少不必要的排序操作，也能解決許多問題。

2、oracle記憶體層

在程式上分配的工作區大小內一次性實現的排序稱為One pass sort。與此相反的情況稱為Multi pass sort。發生Multi pass sort時，排序工作過程中將排序結果讀寫到排序段（sort segment）區域，因此發生direct path read temp、direct path write temp等待。如果該等待大量發生，就可以適當提高pga_aggregate_target值，以此消除問題。

oracle在調優指南上推薦如下設定pga_aggregate_target值。

OLTP：pga_aggregate_target=(total_mem * 80%) * 20%

OLAP：pga_aggregate_target=(total_mem * 80%) * 50%

上述的意思是，假設OS本身使用20%左右的記憶體，OLTP系統上使用剩餘記憶體的20%左右，OLAP系統因為排序工作較多，所以使用剩餘記憶體的50%左右。

結合Oracle 10g的一些特性，來進一步研究一下直接路徑讀／寫與臨時檔案。

首先在一個session中執行一個能夠引發磁碟排序的查詢：

tq@CCDB> select sid from v$mystat where rownum <2;

SID

----------

1066

tq@CCDB> select a.table_name,b.object_name,b.object_type

2 from t1 a,t2 b

3 where a.table_name = b.object_name

4 order by b.object_name,b.object_type;

在另外sessoin查詢相應等待事件：

tq@CCDB> select event,p1text,p1,p2text,p2,p3text,p3

2 from v$session_wait_history

3 where sid = 1066;

EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3

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

direct path read temp file number 201 first dba 313512 block cnt 31

direct path read temp file number 201 first dba 313481 block cnt 31

direct path read temp file number 201 first dba 386887 block cnt 31

direct path read temp file number 201 first dba 317736 block cnt 31

direct path read temp file number 201 first dba 317193 block cnt 31

direct path read temp file number 201 first dba 316646 block cnt 31

direct path read temp file number 201 first dba 316134 block cnt 31

direct path read temp file number 201 first dba 315622 block cnt 31

direct path read temp file number 201 first dba 315079 block cnt 31

direct path read temp file number 201 first dba 314567 block cnt 31

10 rows selected.

從以上輸出可以看到最近10次等待，direct path read temp就是這個查詢引起的磁碟排序。注意這裡的file number為201。而實際上，透過v$tempfile來查詢，臨時檔案的檔案號僅為1：

tq@CCDB> select file#,name from v$tempfile;

FILE# NAME

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

1 /oracle/oradata/ccdb/ccdb/temp01.dbf

如果透過10046事件跟蹤，也可以獲得類似的結果：

WAIT #3: nam='direct path write temp' ela= 1 file number=201 first dba=437862 block cnt=31 obj#=112141 tim=1270780

330976998

WAIT #3: nam='direct path write temp' ela= 1 file number=201 first dba=437416 block cnt=31 obj#=112141 tim=1270780

330977070

WAIT #3: nam='direct path read temp' ela= 7 file number=201 first dba=438471 block cnt=31 obj#=112141 tim=12707803

30982214

WAIT #3: nam='direct path read temp' ela= 4 file number=201 first dba=438502 block cnt=31 obj#=112141 tim=12707803

30983765

WAIT #3: nam='direct path read temp' ela= 8 file number=201 first dba=387015 block cnt=31 obj#=112141 tim=12707803

30993872

在Oracle文件中，file#被定義為絕對檔案號（The Absolute File Number）。這裡的原因何在呢？研究這個問題要先研究一下v$tempseg_usage這個檢視，可以從這個檢視出發動手研究一下這個物件究竟來自何方。

查詢dba_objects檢視，發現v$tempseg_usage原來是一個同義詞。

sys@CCDB> select object_type from dba_objects where object_name = 'V$TEMPSEG_USAGE';

OBJECT_TYPE

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

SYNONYM

再追本溯源原來v$tempseg_usage是v_$sort_usage的同義詞，也就是和v$sort_usage同源。從Oracle 9i開始，Oracle將v$sort_usage檢視從文件中移除了，因為這個名稱有所歧義，容易使人誤解僅記錄排序內容，所以v$tempseg_usage檢視被引入，用於記錄臨時段的使用情況：

sys@CCDB> select * from dba_synonyms where synonym_name = 'V$TEMPSEG_USAGE';

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK

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

PUBLIC VTEMPSEG_USAGE SYS V_SORT_USAGE

如果再進一步，可以看到這個檢視的構建語句：

sys@CCDB> select view_definition from v$fixed_view_definition

2 where view_name = 'GV$SORT_USAGE';

VIEW_DEFINITION

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

select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, p

rev_hash_value, prev_sql_id, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMP

ORARY'), decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_

DATA', 6, 'LOB_INDEX' , 'UNDEFINED'), ktssofno, ktssobno, ktssoexts, ktssoblks,

ktssorfno from xktsso,vsession where ktssoses = v$session.saddr and ktssosno

= v$session.serial#

格式化一下，v$sort_usage的建立語句如下：

SELECT x$ktsso.inst_id,username,username,ktssoses,ktssosno,

prev_sql_addr,prev_hash_value,prev_sql_id,ktssotsn,

DECODE (ktssocnt,

0,'PERMANENT',

1,'TEMPORARY'),

DECODE (ktssosegt,

1, 'SORT',

2, 'HASH',

3, 'DATA',

4, 'INDEX',

5, 'LOB_DATA',

6, 'LOB_INDEX',

'UNDEFINED'),

ktssofno,ktssobno,

ktssoexts,ktssoblks,ktssorfno

FROM xktsso,vsession

WHERE ktssoses = vsession.saddrANDktssosno=vsession.serial#;

注意到在Oracle文件中對v$sort_usage欄位SEGFILE#的定義為：

SEGFILE# NUMBER File number of initial extent

在檢視中，這個欄位來自x$ktsso.ktssofno，也就是說這個欄位實際上代表的是絕對檔案號。那麼這個絕對檔案號如何與臨時檔案關聯呢？能否與v$tempfile中的file#欄位關聯呢？

再來看一下v$tempfile的來源，v$tempfile由如下語句建立：

sys@CCDB> select view_definition from v$fixed_view_definition

2 where view_name = 'GV$TEMPFILE';

VIEW_DEFINITION

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

select tf.inst_id, tf.tfnum, to_number(tf.tfcrc_scn), to_date(tf.tfcrc_tim,'MM/D

D/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), tf.tftsn, tf.tfrfn, decode(bitand(tf

.tfsta, 2),0,'OFFLINE',2,'ONLINE','UNKNOWN'), decode(bitand(tf.tfsta, 12), 0,'DI

SABLED',4, 'READ ONLY', 12, 'READ WRITE',

'UNKNOWN'), fh.fhtmpfsz*tf.tfbsz, fh.fhtmpfsz, tf.tf

csz*tf.tfbsz,tf.tfbsz, fn.fnnam from xkcctftf,xkccfn fn, x$kcvfhtmp fh whe

re fn.fnfno=tf.tfnum and fn.fnfno=fh.htmpxfil and tf.tffnh=fn.fnnum and tf.tfdu

p!=0 and bitand(tf.tfsta, 32) <> 32 and fn.fntyp=7 and fn.fnnam is not null

格式化v$tempfile如下：

SELECT tf.inst_id,tf.tfnum,TO_NUMBER (tf.tfcrc_scn),

TO_DATE (tf.tfcrc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),

tf.tftsn,tf.tfrfn,

DECODE (BITAND (tf.tfsta, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'),

DECODE (BITAND (tf.tfsta, 12),

0, 'DISABLED',

4, 'READ ONLY',

12, 'READ WRITE',

'UNKNOWN'),

fh.fhtmpfsz * tf.tfbsz,fh.fhtmpfsz,tf.tfcsz * tf.tfbsz,tf.tfbsz,fn.fnnam

FROM xkcctftf,xkccfn fn, x$kcvfhtmp fh

WHERE fn.fnfno = tf.tfnum

AND fn.fnfno = fh.htmpxfil

AND tf.tffnh = fn.fnnum

AND tf.tfdup != 0

AND BITAND (tf.tfsta, 32) <> 32

AND fn.fntyp = 7

AND fn.fnnam IS NOT NULL;

考察x$kcctf底層表，注意到TFAFN（Temp File Absolute File Number）在這裡存在：

sys@CCDB> desc x$kcctf

Name Null? Type

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

ADDR RAW(8)

INDX NUMBER

INST_ID NUMBER

TFNUM NUMBER

TFAFN NUMBER

TFCSZ NUMBER

TFBSZ NUMBER

TFSTA NUMBER

TFCRC_SCN VARCHAR2(16)

TFCRC_TIM VARCHAR2(20)

TFFNH NUMBER

TFFNT NUMBER

TFDUP NUMBER

TFTSN NUMBER

TFTSI NUMBER

TFRFN NUMBER

TFPFT NUMBER

TFMSZ NUMBER

TFNSZ NUMBER

而這個欄位在構建v$tempfile時並未出現，所以不能透過v$sort_usage和v$tempfile直接關聯絕對檔案號。可以簡單構建一個排序段使用，然後來繼續研究一下：

sys@CCDB> select username,segtype,segfile#,segblk#,extents,segrfno# from v$sort_usage;

USERNAME SEGTYPE SEGFILE# SEGBLK# EXTENTS SEGRFNO#

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

SYS LOB_DATA 201 340361 1 1

看到這裡的SEGFILE#=201，而在v$tempfile是找不到這個資訊的：

sys@CCDB> select file#,rfile#,ts#,status,blocks from v$tempfile;

FILE# RFILE# TS# STATUS BLOCKS

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

1 1 3 ONLINE 443520

但是可以從x$kcctf中獲得這些資訊，v$tempfile.file#實際上來自x$kcctf.tfnum，是臨時檔案的檔案號；而絕對檔案號是x$kcctf.tfafn，這個欄位才可以與v$sort_usage.segfile#關聯：

sys@CCDB> select indx,tfnum,tfafn,tfcsz from x$kcctf;

INDX TFNUM TFAFN TFCSZ

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

0 1 201 2560

再進一步可以知道，實際上，為了分離臨時檔案號和資料檔案號，Oracle對臨時檔案的編號以db_files為起點，所以臨時檔案的絕對檔案號應該等於db_files+file#。

db_files引數的預設值為200：

sys@CCDB> show parameter db_files

NAME TYPE VALUE

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

db_files integer 200

sys@CCDB> select file#,name from v$tempfile;

FILE# NAME

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

1 /oracle/oradata/ccdb/ccdb/temp01.dbf

所以在Oracle文件中v$tempfile.file#被定義為The absolute file number是不確切的。

6.7 read by other session

WAITEVENT: "read by other session" Reference Note (文件 ID 732891.1)

當多個進程訪問同一個資料塊，而此資料塊不在記憶體中，這時會有一個進程將它從磁碟讀到記憶體時，其它讀取此資料塊程式的狀態就是 read by other session；因為Oracle記憶體不允許多個程式同時讀到同一個資料塊到記憶體，其它程式只能等待。

當我們查詢一條資料時，Oracle第一次會將資料從磁碟讀入 buffer cache。如果有兩個或者多個session請求相同的資訊，那麼第一個session會將這個資訊讀入buffer cache，其他的session就會出現等待。

SELECT A.*

FROM V$EVENT_NAME A

WHERE NAME IN ('read by other session');

l P1 = file# Absolute File# (AFN) This is the file number of the data file that contains the block that the waiting session wants.

l P2 = block# This is the block number in the above file# that the waiting session wants access to. See Note:181306.1 to determine the tablespace, filename and object for this file#,block# pair.

l P3 = class# Block class

This is the class of block being waited on. In particular:

class 1 indicates a "data block", which could be table or index

class 4 indicates a "segment header"

class >=15 indicate undo blocks

我們可以根據P1和P2引數值獲取到等待的物件名稱和型別：

SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME

FROM DBA_EXTENTS

WHERE FILE_ID = FILE#

AND BLOCK#

BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

另外，其實我們也可以根據v$session的ROW_WAIT_OBJ#列獲取到等待的物件的名稱，SQL如下：

SELECT A.ROW_WAIT_OBJ#,

B.OBJECT_NAME,

A.SQL_ID,

A.SID,

A.BLOCKING_SESSION,

A.EVENT,

A.P1TEXT,

A.P1,

A.P2TEXT,

A.P2,

A.P3TEXT,

A.P3,

A.WAIT_CLASS

FROM V$SESSION A, DBA_OBJECTS B

WHERE A.ROW_WAIT_OBJ# = B.OBJECT_ID

AND A.EVENT='read by other session';

其實 read by other session 是在 Oracle 10g （10.1.0.2 and later）新引入的一個等待事件，在 10g 以前版本，等待為 buffer busy waits，10g以後做的細分，所以才有了 read by other session。

Oracle官方解釋如下：

This event occurs when a session requests a buffer that is currently being read into the buffer cache by another session. Prior to release 10.1, waits for this event were grouped with the other reasons for waiting for buffers under the 'buffer busy wait' event。

此等待事件從側面也說明了資料庫存在讀的競爭，所以該等待事件經常會和db file sequential read 和db file scattered read同時出現。

下面是在 Metalink 上的解釋：

Solution：

This wait event occurs when we are trying to access a buffer in the buffer

cache but we find that the buffer is currently being read from disk by another user

so we need to wait for that to complete before we can access it. In previous versions, this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher, the wait time is now broken out into the "read by other session" wait event.

Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention。

一般來說出現這種等待事件是因為多個程式重複的讀取相同的 blocks，比如一些session 掃描相同的 index或者在相同的block上執行 full table scan。解決這個等待事件最好是找到並最佳化相關的SQL語句

1. 如果系統中有這種等待事件，我們可以透過以下SQL查詢v$session_wait得到詳細資訊

SELECT p1 "file#", p2 "block#", p3 "class#"

FROM v$session_wait

WHERE event = 'read by other session';

2. 如果上述查詢出的結果是熱塊造成的，執行如下 SQL，查詢出具體物件資訊，其實這部分可以直接從 AWR的Segments by Buffer Busy Waits 看出來。

SELECT RELATIVE_FNO, OWNER, SEGMENT_NAME, SEGMENT_TYPE

FROM DBA_EXTENTS

WHERE FILE_ID = &FILE

AND &BLOCK BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

3. 可以透過下面的SQL指令碼查詢到具體的SQL語句

SELECT HASH_VALUE, SQL_TEXT

FROM V$SQLTEXT

WHERE (HASH_VALUE, ADDRESS) IN

(SELECT A.HASH_VALUE, A.ADDRESS

FROM V$SQLTEXT A,

(SELECT DISTINCT A.OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE

FROM DBA_EXTENTS A,

(SELECT DBARFIL, DBABLK

FROM (SELECT DBARFIL, DBABLK

FROM X$BH

ORDER BY TCH DESC)

WHERE ROWNUM < 11) B

WHERE A.RELATIVE_FNO = B.DBARFIL

AND A.BLOCK_ID <= B.DBABLK

AND A.BLOCKS > B.DBABLK) B

WHERE A.SQL_TEXT LIKE '%' || B.SEGMENT_NAME || '%'

AND B.SEGMENT_TYPE = 'TABLE')

ORDER BY HASH_VALUE, ADDRESS, PIECE;

4.檢視對應 SQL 的執行計劃是否最優，必要時可以透過 DBMS_SQLTUNE 包迚行最佳化，透過SQL_PROFILE 檔案穩固執行計劃

5.檢視錶和索引的統計資訊是否陳舊，必要時收集統計資訊

6.7.1

CREATE TABLE TB_RBOS_20160829_LHR AS SELECT * FROM DBA_OBJECTS;

INSERT INTO TB_RBOS_20160829_LHR SELECT * FROM TB_RBOS_20160829_LHR;

COMMIT;

SELECT DISTINCT SID FROM V$MYSTAT;

DECLARE

I NUMBER := 0;

V_STRING VARCHAR2(50) := 'alter system flush buffer_cache';

BEGIN

LOOP

SELECT COUNT(*) INTO I FROM TB_RBOS_20160829_LHR;

EXECUTE IMMEDIATE V_STRING;

END LOOP;

END;

/

SELECT A.SID,A.BLOCKING_SESSION,A.EVENT,A.P1TEXT,A.P1,A.P2TEXT,A.P2,A.P3TEXT,A.P3,A.WAIT_CLASS

FROM V$SESSION A

WHERE SID IN (190, 5, 68);

首先，建表TB_RBOS_20160829_LHR：

SYS@lhrdb> SELECT * FROM V$VERSION;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SYS@lhrdb> CREATE TABLE TB_RBOS_20160829_LHR AS SELECT * FROM DBA_OBJECTS;

Table created.