ORACLE等待事件監控

　　監控oracle的等待事件

　　select event

　　sum(decode(wait_Time )) Prev

　　sum(decode(wait_Time )) Curr

　　count(*) Tot

　　from v$session_Wait

　　group by event

　　order by ;

　　 等待事件概述

　　Oracle的等待事件是衡量oracle運行狀況的重要依據及指標

　　等待事件的概念是在Oracle中引入的大致有個等待事件在Oracle 中這個數目增加到了大約個在Oraclei中大約有個事件在Oraclei中大約有個等待事件

　　主要有兩種類別的等待事件即空閒（idle）等待事件和非空閒（nonidle）等待事件

　　空閒等待事件是指Oracle正等待某種工作比如用sqlplus登錄之後但沒有進一步發出任何命令此時該session就處於SQL*Net message from/to client等待事件狀態等待用戶發出命令任何的在診斷和優化數據庫的時候我們不用過多注意這部分事件非空閒等待事件專門針對Oracle的活動指數據庫任務或應用運行過程中發生的等待這些等待事件是我們在調整數據庫的時候應該關注與研究的

　　  oracle等待事件類型

　　每一個等待事件都屬於某一類 下面給出了每一類等待事件的描述

　　管理類: administrative

　　類等待事件是由於DBA的管理命令引起的這些命令要求用戶處於等待狀態比如重建索引

　　應用程序類:

　　此類等待事件是由於用戶應用程序的代碼引起的（比如鎖等待）

　　群集類Cluster

　　此類等待事件和真正應用群集RAC的資源有關（比如gc cr block busy等待事件）

　　提交確認類Commit

　　此類等待事件只包含一種等待事件－－在執行了一個commit命令後等待一個重做日志寫確認（也就是log file sync）

　　並發類Concurrency

　　此類等待事件是由內部數據庫資源引起的比如闩鎖

　　配置類Configuration

　　此類等待事件是由數據庫或實例的不當配置造成的比如重做日志文件尺寸太小共享池的大小等

　　空閒類Idle

　　此類等待事件意味著會話不活躍等待工作比如sql * net messages from client

　　網絡類Network

　　和網絡環境相關的一些等待事件比如sql* net more data to dblink

　　Other

　　此類等待事件通常比較少見

　　調度類Scheduler

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

　　系統I/O類System I/O

　　此類等待事件通過是由後台進程的I/O操作引起的比如DBWR等待db file paralle write

　　用戶I/O類User I/O

　　此類等待事件通常是由用戶I/O操作引起的比如db file sequential read

　　 等待事件詳細描述

　　 db file scattered read (DB文件分散讀取)

　　這種情況通常與全表掃描相關 當數據庫進行全表掃描時 基於性能的考慮 數據會分散(scattered)讀入buffer cache 如果這個等待事件比較顯著 可能考慮查看對應的表有沒有創建合適的索引

　　然而這個等待事件並不一定就意味著性能低下 在某些條件下oracle會主動使用全表掃描來替換索引掃描以提高性能 這和訪問的數據量有關 在CBO下oracle會進行更為智能的選擇 RBO下oracle更傾向於使用索引

　　因為全表掃描到內存的數據塊被置於LRU鏈表的冷端 所以這些數據塊將可能在較短時間內被置換出物理內存 為了避免反復物理IO 對頻繁訪問的較小的數據表可以選擇把他們cache到內存中

　　當這個等待時間比較顯著時 可以結合v$session_longops動態性能視圖來進行診斷 該視圖中記錄了長時間(運行時間超過秒)運行的事務 可能很多是全表掃描操作

　　Column

　　Datatype

　　Description

　　SID

　　NUMBER

　　Session identifier

　　SERIAL#

　　NUMBER

　　Session serial number

　　OPNAME

　　VARCHAR()

　　Brief description of the operation

　　TARGET

　　VARCHAR()

　　The object on which the operation is carried out

　　TARGET_DESC

　　VARCHAR()

　　Description of the target

　　SOFAR

　　NUMBER

　　The units of work done so far

　　TOTALWORK

　　NUMBER

　　The total units of work

　　UNITS

　　VARCHAR()

　　The units of measurement

　　START_TIME

　　DATE

　　The starting time of operation

　　LAST_UPDATE_TIME

　　DATE

　　Time when statistics last updated

　　TIME_REMAINING

　　NUMBER

　　Estimate (in seconds) of time remaining for the operation to complete

　　ELAPSED_SECONDS

　　NUMBER

　　The number of elapsed seconds from the start of operations

　　CONTEXT

　　NUMBER

　　Context

　　MESSAGE

　　VARCHAR()

　　Statistics summary message

　　USERNAME

　　VARCHAR()

　　User ID of the user performing the operation

　　SQL_ADDRESS

　　RAW()

　　Used with the value of the SQL_HASH_VALUE column to identify the SQL statement associated with the operation

　　SQL_HASH_VALUE

　　NUMER

　　Used with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation

　　QCSID

　　NUMBER

　　Session identifier of the parallel coordinator

　　 db file sequential read(DB 文件順序讀取)

　　這一事件通常顯示與單個數據塊相關的讀取操作 比如對索引塊的讀取 如果這個等待事件比較顯著 可能表示在多表連接中 表的鏈接順序存在問題 可能沒有正確的使用驅動表; 或者可能說明不加選擇地進行索引

　　 free buffer (釋放緩沖區)

　　這個等待事件表明系統正在等待內存中的可用空間這說明當前Buffer 中已經沒有Free 的內存空間Free Buffer 等待可能說明DBWR 的寫出速度不夠或者磁盤存在嚴重的競爭可以需要考慮增加檢查點使用更多的DBWR 進程或者增加物理磁盤的數量分散負載平衡IO

　　 buffer busy(緩沖區忙)

　　該等待事件表示正在等待一個以unshareable方式使用的緩沖區或者表示當前正在被讀入buffer cache一般來說Buffer Busy Wait不應大於%檢查緩沖等待統計部分(或V$WAITSTAT)看一下等待是否位於段頭(Segment Header)如果是可以考慮增加自由列表(freelist對於Oraclei DMT)或者增加freelist groups(在很多時候這個調整是立竿見影的在之前這個freelists參數不能動態修改;在及以後版本動態修改 feelists需要設置COMPATIBLE至少為)

　　This view lists block contention statistics This table is only updated when timed statistics are enabled

　　Column

　　Datatype

　　Description

　　CLASS

　　VARCHAR()

　　Class of the block

　　COUNT

　　NUMBER

　　Number of waits by this OPERATION for this CLASS of block

　　TIME

　　NUMBER

　　Sum of all wait times for all the waits by this OPERATION for this CLASS of block

　　select * from V$WAITSTAT

　　CLASS

　　COUNT

　　TIME

　　data block

　　

　　

　　undo header

　　

　　

　　undo block

　　

　　

　　segment header

　　

　　

　　file header block

　　

　　

　　st level bmb

　　

　　

　　bitmap index block

　　

　　

　　system undo block

　　

　　

　　system undo header

　　

　　

　　unused

　　

　　

　　bitmap block

　　

　　

　　save undo header

　　

　　

　　save undo block

　　

　　

　　sort block

　　

　　

　　free list

　　

　　

　　rd level bmb

　　

　　

　　nd level bmb

　　

　　

　　extent map

　　

　　

　　如果這一等待位於undo header可以通過增加回滾段(rollback segment)來解決緩沖區的問題如果等待位於undo block上我們可能需要檢查相關應用適當減少大規模的一致性讀取或者降低一致性讀取(consistent read)的表中的數據密度或者增大DB_CACHE_SIZE

　　如果等待處於data block可以考慮將頻繁並發訪問的表或數據移到另一數據塊或者進行更大范圍的分布(可以增加pctfree值擴大數據分布減少競爭)以避開這個熱點數據塊或者可以考慮增加表中的自由列表或使用本地化管理的表空間(Locally Managed Tablespaces)

　　如果等待處於索引塊應該考慮重建索引分割索引或使用反向鍵索引為了防止與數據塊相關的緩沖忙等待也可以使用較小的塊:在這種情況下單個塊中的記錄就較少所以這個塊就不是那麼繁忙;或者可以設置更大的pctfree使數據擴大物理分布減少記錄間的熱點競爭

　　在執行DML (insert/update/ delete)時Oracle向數據塊中寫入信息對於多事務並發訪問的數據表關於ITL的競爭和等待可能出現為了減少這個等待可以增加 initrans使用多個ITL槽在Oraclei 中引入了一個新概念:ASSM(Segment Space Management Auto)通過這個新特性Oracle 使用位圖來管理空間使用

　　ASSM 結合LMT 徹底改變了Oracle 的存儲機制位圖freelist 能夠減輕緩沖區忙等待(buffer busy wait)這個問題在Oraclei 以前的版本裡曾是一個嚴重的問題

　　Oracle 宣稱ASSM 顯著地提高了DML 並發操作的性能因為(同一個)位圖的不同部分可以被同時使用這樣就消除了尋找剩余空間的串行化根據Oracle 的測試結果使用位圖freelist 會消除所有分段頭部(對資源)的爭奪還能獲得超快的並發插入操作在Oraclei 之中Buffer Busy wait 不再常見!

　　 latch free (latch 釋放)

　　latch是一種低級排隊機制用於保護SGA中共享內存結構latch就像是一種快速地被獲取和釋放的內存鎖用於防止共享內存結構被多個用戶同時訪問如果latch不可用就會記錄latch釋放失敗(latch free miss )有兩種與闩有關的類型:

　　■ 立刻

　　■ 可以等待

　　假如一個進程試圖在立刻模式下獲得闩而該闩已經被另外一個進程所持有如果該闩不能立可用的話那麼該進程就不會為獲得該闩而等待它將繼續執行另一個操作

　　大多數latch問題都與以下操作相關:

　　沒有很好的是用綁定變量(library cache latch)重作生成問題(redo allocation latch)緩沖存儲競爭問題(cache buffers LRU chain)以及buffer cache中的存在熱點塊(cache buffers chain)

　　通常我們說如果想設計一個失敗的系統不考慮綁定變量這一個條件就夠了對於異構性強的系統不使用綁定變量的後果是極其嚴重的

　　另外也有一些latch等待與bug有關應當關注Metalink相關bug的公布及補丁的發布當latch miss ratios大於%時就應當研究這一問題

　　 log buffer space(日志緩沖空間)

　　當你將日志緩沖(log buffer)產生重做日志的速度比LGWR 的寫出速度快或者是當日志切換(log switch)太慢時就會發生這種等待這個等待出現時通常表明redo log buffer 過小為解決這個問題可以考慮增大日志文件的大小或者增加日志緩沖器的大小

　　另外一個可能的原因是磁盤I/O 存在瓶頸可以考慮使用寫入速度更快的磁盤在允許的條件下設置可以考慮使用裸設備來存放日志文件提高寫入效率在一般的系統中最低的標准是不要把日志文件和數據文件存放在一起因為通常日志文件只寫不讀分離存放可以獲得性能提升

　　 等待事件相關視圖

　　 v$session_wait

　　這是一個尋找性能瓶頸的關鍵視圖 它提供任何情況下session在數據庫中當前正在等待什麼(如果session當前什麼也沒在做 則顯示它最後等待的事件)

　　每一個鏈接到實例的session都對應一條記錄

　　Column

　　Datatype

　　Description

　　SID

　　NUMBER

　　Session identifier

　　SEQ#

　　NUMBER

　　Sequence number that uniquely identifies this wait Incremented for each wait

　　EVENT

　　VARCHAR()

　　Resource or event for which the session is waiting session當前正在等待的事件 或者最後一次等待事件

　　PTEXT

　　VARCHAR()

　　Description of first additional parameter

　　P

　　NUMBER

　　First additional parameter

　　PRAW

　　RAW()

　　First additional parameter

　　PTEXT

　　VARCHAR()

　　Description of second parameter

　　P

　　NUMBER

　　Second additional parameter

　　PRAW

　　RAW()

　　Second additional parameter

　　PTEXT

　　VARCHAR()

　　Description of third parameter

　　P

　　NUMBER

　　Third additional parameter

　　PRAW

　　RAW()

　　Third additional parameter

　　WAIT_TIME

　　NUMBER

　　A nonzero value is the sessions last wait time A zero value means the session is currently waiting

　　SECONDS_IN_WAIT

　　NUMBER

　　If WAIT_TIME = then SECONDS_IN_WAIT is the seconds spent in the current wait condition If WAIT_TIME > then SECONDS_IN_WAIT is the seconds since the start of the last wait and SECONDS_IN_WAIT WAIT_TIME / is the active seconds since the last wait ended

　　STATE

　　VARCHAR()

　　Wait state:

　　·    WAITING (the session is currently waiting)

　　·     WAITED UNKNOWN TIME (duration of last wait is unknown)

　　·     WAITED SHORT TIME (last wait </th of a second)

　　·   > WAITED KNOWN TIME (WAIT_TIME = duration of last wait)

　　 v$session_event

　　本視圖記錄了每個session的每一項等待事件 由上文所知v$session_wait顯示了session的當前等待事件 而v$sesion_event則記錄了session自啟動起所有的事件

　　Column

　　Datatype

　　Description

　　SID

　　NUMBER

　　The ID of the session

　　EVENT

　　VARCHAR()

　　The name of the wait event

　　See Also: Appendix A Oracle Wait Events

　　TOTAL_WAITS

　　NUMBER

　　The total number of waits for this event by this session

　　TOTAL_TIMEOUTS

　　NUMBER

　　The total number of timeouts for this event by this session

　　TIME_WAITED

　　NUMBER

　　The total amount of time waited for this event by this session in hundredths of a second

　　AVERAGE_WAIT

　　NUMBER

　　The average amount of time waited for this event by this session in hundredths of a second

　　MAX_WAIT

　　NUMBER

　　The maximum time (in hundredths of a second) waited for this event by this session

　　 查詢所有連接的實例的session的相關信息

　　 session總體等待

　　select aSIDaUSERNAMEamachineaTERMINALbEVENTbTOTAL_WAITSbTOTAL_TIMEOUTS

　　from v$session a

　　V$SESSION_EVENT b

　　where aSID = bSID

　　and astatus = ACTIVE

　　and user# >;

　　 session當前等待

　　select aSIDaUSERNAMEamachineaTERMINALbEVENTbWAIT_TIMEbSECONDS_IN_WAITbSTATE

　　from v$session a

　　V$SESSION_wait b

　　where aSID = bSID

　　and astatus = ACTIVE

　　and user# >;

　　 當前session正在執行語句

　　select aSIDaUSERNAMEamachineaTERMINALbPIECEbSQL_TEXT

　　from v$session a

　　v$sqltext b

　　where bADDRESS = decode(aSQL_HASH_VALUEaPREV_SQL_ADDRaSQL_ADDRESS)

　　and astatus = ACTIVE

　　and user# >

　　order by aSQL_ADDRESSbPIECE;

　　 session當前等待

　　select aSIDaUSERNAMEamachineaTERMINALcNAMEbVALUE

　　from v$session a

　　V$SESStat b

　　v$statname c

　　where aSID = bSID

　　and bSTATISTIC# = cSTATISTIC#

　　and astatus = ACTIVE

　　and user# >

　　and bvalue > ;

From:http://tw.wingwit.com/Article/program/Oracle/201311/18898.html