深入淺出等待事件和效能診斷記載03

近段時間在忙於年會舞蹈和過年回家的事情，主要是火車票難買，公司的年假又批得遲，搞得俺這些漂泊在外的人糾結啊，學習習慣也有點懶散了，收心！

先摘要記錄幾個等待事件

Db file sequential read（資料檔案順序讀取）是個非常常見的I/O相關的等待事件，通常顯示於單個資料塊相關讀取操作，讀取索引塊或者透過索引塊讀取資料塊時，可能會記錄這個等待事件。

SQL> col name for a20

SQL> col parameter1 for a20

SQL> col parameter2 for a20

SQL> col parameter3 for a20

SQL> col wait_class for a20

SQL> select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name='db file sequential read';

NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS

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

db file sequential r file# block# blocks User I/O

ead

可以透過v$event_name檢視發現db file sequential read等待事件的三個引數分別是絕對檔案號 檔案中開始讀取的起始資料塊塊號以及讀取的block數量，在oracle 10g中db file sequential read被劃分為user I/O等待事件類。

這個等待事件很可能是：因為多表連線中，表的連線順序存在問題，沒有選擇正確的驅動表；或者索引使用並不高效，不是最佳選擇，例如讀取表中大量資料時，由於索引是單塊I/O，讀取大量資料選擇性並不高，

從oracle 9IR2開始，新增了階級統計資訊收集的新特性v$segment_statistics檢視，在oracle 10g中v$segstat_statistics檢視收集段級統計資訊由oracle9IR2的11類變為15類，可以透過v$segstat_name檢視來獲取具體統計資訊類別，可以查詢物理讀取顯著的索引段或表段，是否採取重建索引 劃分分割槽 儲存引數等手段降低其I/O。sql語句消耗在I/O 解析 network三大方面。

Db file scattered read資料檔案離散讀取，和db file sequential read順序讀取引數意義一樣，當這個等待事件比較顯著時，很多都是採取了全表掃描，這裡可以透過檢視v$session_longops動態效能檢視記錄執行時間超過6秒的事務。

從oracle 9I後，oracle又新增檢視v$sql_plan記錄當前系統library_cache中sql語句執行計劃，例如獲得全表掃描的物件和全索引掃描的物件

執行計劃是全表掃描的前4行

SQL> select object_owner,object_name,hash_value,address from v$sql_plan where operation='TABLE ACCESS'

2 and options='FULL' and rownum<5；

OBJECT_OWNER OBJECT_NAME HASH_VALUE ADDRESS

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

SYSMAN MGMT_DB_TABLESPACES_ECM 3776970919 1B266B14

SYSMAN MGMT_PERFORMANCE_NAMES 1396966618 25CE1408

執行計劃是全索引掃描的前4行

SQL> select object_owner,object_name,hash_value,address from v$sql_plan where operation='INDEX'

2 and options='FULL SCAN' and rownum<5;

OBJECT_OWNER OBJECT_NAME HASH_VALUE ADDRESS

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

SYSTEM LOGMNR_SESSION_PK 1503528053 1AFF0F98

SYS SCHEDULER$_WINGRP_MEMBER_PK 593239587 1B1908A8

SYS SCHEDULER$_WINGRP_MEMBER_PK 593239587 1B1908A8

SYS SCHEDULER$_WINGRP_MEMBER_PK 593239587 1B1908A8

透過v$sql_plan的hash_value和address聯合v$sql_text得到完整的sql語句，進而對sql語句進行進一步調優等。

SQL> select sql_text,cost from v$sqltext a,v$sql_plan b

2 where a.hash_value=b.hash_value and b.operation='TABLE ACCESS'

3 and b.options='FULL' and b.object_owner='XIAOYU';

SQL_TEXT COST

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

select * from test18 order by object_id desc 71

select count(*) from test01 2

SELECT * FROM test01 2

SELECT * FROM test18 71

這裡梳理一下如何透過等待事件找到相應的sql語句和其執行計劃然後對其進行進一步調整。

1 透過v$session_wait檢視的等待事件找到sid

2 聯合v$session檢視透過sid找到sql_address和sql_hash_value

3 聯合v$sqltext和v$sql_plan透過address和hash_value進而捕捉到完整的sql語句和執行計劃

如果是oracle 10g由於v$session檢視中已經新增了event欄位，相應的等待事件已經聯合到v$session中。

Direct path read/write直接路徑讀/寫

Direct path read通常發生在oracle直接讀資料到PGA時，這個讀取不需要SGA，直接路徑讀等待事件的三個引數分別是：file#，file block#和block數量，這類等待事件被歸於user I/O

這類讀取通常在以下情況：

磁碟排序I/O操作

並行查詢從屬程式

預讀操作

dss系統中，存在大量direct path read是正常的，但是OLTP系統中，通常顯著的direct path read代表著系統存在大量的磁碟排序讀取操作

direct path write通常發生在oracle直接從PGA寫資料到資料檔案或臨時檔案，期間繞過了SGA，direct path write的三個引數和direct path read一樣，該等待事件同樣被歸於user I/O一類

這類寫入通常在：

直接路徑載入（sql語句直接路徑載入，或者sqlldr利用direct=y繞過sga直接從pga到資料檔案）

並行dml操作（並行插入時，就會採取繞過sga直接高水位插入）

磁碟排序

對未快取的lob段寫入，會記錄為direct path write（lob）等待（對於eygle提出的關於lob等待）還是有頗多疑惑最為常見的是因為磁碟排序引起的。排序是很消耗資源的，而且也建議排序在pga中進行，因為涉及到磁碟效率就會下降很多，對於磁碟排序引起的direct path write等待，應該找到I/O操作頻繁的資料檔案，很多情況都是臨時檔案，分散負載加快磁碟I/O,如果是臨時表空間引起的也可以採用臨時表空間組的方式分散I/O負載。

在oracle 10g中區分特定的對於臨時檔案的直接讀取操作，direct path read temp和direct path write temp

SQL> col name for a40

SQL> select name from v$event_name where name like 'direct%';

NAME

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

direct path read

direct path read temp

direct path write

direct path write temp

[@more@]