Oracle Wait Interface
Oracle等待事件的種類包括I/O locks latches enqueues background process activities network latencies memory等可以通過V$SYSTEM_WAIT_CLASS查詢到
使用之前必須設置TIMED_STATISTICS=TRUE
OWI的關鍵組件
視圖
V$EVENT_NAME包含定義的所有等待事件
V$SESSION_WAIT顯示每個會話當前正在等待的時間和資源的詳細信息這是一個實時視圖
在G中v$session_wait已經合並到了v$session 視圖中雖然仍包含v$session_wait
V$SESSION_EVENT顯示當前連接的會話的等待事件的聚集
V$SYSTEM_EVENT顯示所有會話遇到的所有等待事件的聚集
Oracle G新增的關鍵組件包括
V$SESTEM_WAIT_CLASS
V$SESSION_WAIT_CLASS
V$SESSION_WAIT_HISTORY
V$EVENT_HISTOGRAM
V$ACTIVE_SESSION_HISTORY
跟蹤
Trace event —擴充的SQL跟蹤
如果無法交互性的監控事件可以通過紀錄等待事件到跟蹤文件進行診斷性能問題
可以在實例（Event= tace name contex forever level 永遠不要這麼做）/會話級別（Alter session set events trace name context forever level ）啟用
運行要跟蹤的SQL
Alter session set events trace name context off
？？？以下過程在可能沒有
也可以使用dbms_supportstart_trace進行跟蹤
跟蹤其他會話Exec dbms_supportstart_trace_in_session（sid=>xxx serial#=>xxx waits=>true binds=>true）
結束跟蹤stop_trace_in_session
跟蹤文件的位置在user_dump_dest 目錄下
可以使用tracefile_identifer命名跟蹤文件名Alter session set tracefile_identifier= Tracefilesql
然後使用tkprof工具進行格式化輸出最近Metalink上有一個新的工具TRCA更加適合分析跟蹤文件
OWI的限制
沒有CPU 統計
非端到端的可見性
沒有歷史數據
由於當前計算機速度的飛增一些計算可能不精確
常見的等待事件
Buffer busy waitsg中更改為read by other session參數中P代表塊所在的文件號P代表實際的塊號P在i中代表reason for the waitg中代表v$waitstat中的CLASS列等待時間為s或cs
Control file parallel write參數中P代表塊駐留的絕對文件號P代表總塊數P代表I/O請求數量等待時間為完成所有I/O請求的實際延遲
Db file parallel read當一個進程從一個/多個數據文件讀取多個不連續的塊或數據庫恢復時有些數據庫塊需要恢復時會發生這種事件參數P代表需要讀取的文件數P讀取的總塊數P代表I/O請求的總數量等待時間為完成所有I/O請求的實際延遲
Db file parallel write當DBWR以批模式些藏塊到數據文件時會發生參數中P代表需要寫入的文件數P代表要寫的總塊數P（+）以百分之一秒為單位代表超時的值等待時間無超時
Db file scattered read通常在全表掃描時發生參數P代表開始讀取的塊的文件號P代表開始讀取的塊號P代表讀取的塊數等待時間無超時
Db file sequential read當從索引回滾段表（ROWID）數據文件頭一些臨時段讀取時會發生該事件參數P代表開始讀取的塊的文件號P代表開始讀取的塊號P通常為但是臨時段則大於等待時間無超時
Db file single write當Oracle更新數據文件頭時發生該事件通常在檢查點期間如果數據庫有大量數據文件可能會注意到這種情況參數P代表寫入的文件號P代表開始寫入的塊P通常為等待時間無超時
Direct path read當oracle直接讀取數據庫塊到會話PGA而不是SGA的緩沖緩存時時發生直接路徑讀取通常在訪問臨時段時使用參數P讀取的所在的絕對文件號P代表開始讀取的塊號P則為讀取的塊數等待時間無超時
Direct path write與Direct path read相反Oracle從PGA寫緩沖到數據文件通常用來寫到臨時段直接數據加載或並行DML操作
Enqueue是一種Oracle用來串行化訪問數據庫資源的共享內存結構進程為了得到該enqueue鎖必須等待在隊列中用來串行訪問資源的各種enqueue包括ST空間管理SQ序列號TX事務參數P代表正在等待的進程請求的enqueue名和模式P代表請求的鎖的資源標識符IDP代表請求的鎖的資源標識符ID等待時間依賴於enqueue名Oracle最多可以等待三秒或直到enqueue資源可用
Free buffer waits當會話無法在數據庫緩沖緩存中找到空閒緩沖讀入塊或者建立一致性讀時將發生該事件這會喚醒DBWR釋放髒緩沖參數中P代表文件號P代表需要讀入到緩存中的塊號P（i未使用）G顯示緩沖緩存中LRU列表的id等待時間Oracle最多等待s然後重試
Latch free當進程等待一個當前被其他進程占用的latch時會發生這種事件進程要得到latch不必等待在隊列中最常見的latch包括cache buffer chains library cache和shared pool參數P代表latch的地址P代表latch號（v$latchnamelatch#）P代表嘗試的次數等待時間指數增長G中latch由其自身的等待事件
Library cache pin / library cache lock當會話試圖將一個對象釘在庫緩存中以更改或測試它時會發生該事件必須得到一個釘確保對象不會被改變P代表庫對象地址P代表加載鎖的地址P為模式+名字空間（來自v$db_object_cache）等待時間PMON為S其他進程為S
Log buffer space當進程必須等待日志緩沖中的空間可用時發生未使用參數等待時間S如果必須等待日志切換則為S
Log file parallel write會話等待LGWR寫日志緩沖塊到重做組成員時發生參數P代表要寫入的日志文件數P代表要寫入的OS塊數P代表I/O請求數量等待時間實際的延遲
Log file sequential read當進程等待塊從在線重做日志文件讀取時會發生該事件參數P代表重做日志文件的相對序列號P代表開始讀取的塊號P代表讀取的OS塊數等待時間實際的延遲
Log file switch （archiving needed）指示ARCH 跟不上LGWR
Log file switch （checkpoint incomplete）文件歸檔前檢查點必須完成指示重做日志文件可能太小
Log file sync參數P代表需要同步的日志緩沖中的緩沖的數量等待時間S
SQL*Net message from/to client如果很大可能指示網絡有問題P顯示網絡驅動器的類型P代表字節數P未使用等待時間實際的延遲
跟蹤CPU和其他統計V$SESSTAT / V$SYSSTAT其中包含了
– CPU used by this session
– CPU used when call started
– Recursive CPU usage
– Parse time CPU
– Session logical reads
– Physical reads
– Physical writes
等待事件根本原因分析
需要收集等待事件統計歷史
Trace 會有很大的負載但是可以得到最小粒度的性能數據
Statspack不能得到會話級別的數據
使用BEFORE LOGOFF TRIGGER收集歷史數據—低負載（如果會話被KILL或掛起則沒有任何數據）建立表並保持大約天的數據可以歸檔這些數據進行長期比較將等待事件和產生該事件的SQL語句（V$SQLTEXT）保存起來
推斷常用等待事件找到以及修復？
診斷IO相關的等待事件
在任何系統中I/O操作都是最慢的活動
與I/O相關的最常見的事件
– Db file scattered read
– Direct path read
– Direct path write
– Log file parallel write
– Db file parallel write
– Controlfile parallel write
– Db file sequential read
Db file sequential read
Oracle進程想要的塊當前不在SGA中查看V$SESSION_EVENT的TIME_WAITED和AVERAGE_WAIT列從系統范圍的事件來看時這通常是TOP
大量的等待時間通常是由於應用程序問題當前應該小於cs
如果db files sequential reads的對象是索引應用程序可能執行了大量的索引讀考慮使用全表掃描是否合適？？？檢查聚簇因子檢查兩個初始化參數（optimizer_index_cost_adj和optimizer_index_caching）的設置
如果db files sequential reads的對象是表—記住索引讀後的rowid訪問是順序的檢查V$SYSTEM_EVENT的average_wait其為TOP 並不指示系統有問題如果沒有出現在top 中則表明其他等待有問題需要解決如果AVGERAGE_WAIT特別大—檢查磁盤熱點G使用ASM平衡負載
db file scattered read
Oracle會話使用db_file_multiblock_read_count從磁盤讀取塊到SGA多塊I/O請求通常與全表掃描和索引快速全掃描相關過高的該事件值通常是應用程序的問題需要使用更多的單塊讀（索引掃描）代替多塊讀
如果全表掃描時恰當的考慮實施多塊讀減少等待時間如果應用程序開始高效運行突然開始產生db file scattered read waits查看是否有索引被刪除或無效
可以使數據庫傾向於全表掃描的優化器參數包括db_file_multiblock_read_counthash_area_sizeoptimizer_index_cost_adj通常表很久未分析也會導致該等待事件增加檢查表的last_analyzed如果表有大量鏈接行也會產生該問題
Direct path read waits
通常是磁盤排序太多檢查V$STATNAMEv$sesstat決定內存排序的參數包括sort_area_sizepga_aggregate_target目標是盡可能的減少排序使用UNION ALL代替UNION哈希連接代替排序接合嵌套循環代替哈希連接
Direct path write waits
直接寫來自SORT CTAS HASH INDEX 以及運行在並行模式的sqlldr直接路徑寫的調整方法和直接路徑讀一樣首先調整對直接路徑讀和寫影響最大SQL語句
Db file parallel write
該事件屬於DBWR進程該事件如果很大意味著I/O問題沒有該事件的用戶會話可能顯示free buffer waitwrite complete wait檢查系統是否支持asynch_io（HP僅在RAW上支持）如果是則使用它否則考慮使用多DBWRs
Log file parallel write
該事件屬於LGWR這是一個系統范圍的事件用戶會話可能指示log file sync查看v$system_event的time_waited和average_wait列如果average is > cs則系統可能正在經歷較低的吞吐量該事件的修復同Db file parallel write不幸的是不能使用多個LGWRs檢查日志文件所在的位置是否有沖突對某些操作使用NOLOGGING
Control file parallel write
該事件通常是大量日志切換的征兆增加日志文件的大小
診斷鎖相關的等待事件
鎖與闩的區別是闩的唯一目的是探測排他訪問內存結構闩保護內存對象鎖的目的是當資源在兼容模式時允許多個進程共享資源當資源在不兼容模式時強制排它訪問鎖保護數據庫對象一共有種模式null row share row exclusive share row share exclusive以及exclusive
Latch Free wait
查看V$SYSTEM_EVENT表的TOTAL_WAITS列闩可以通過V$LATCH監控在高並發的系統中闩沖突時很常見的應該被預計種最常見的闩等待為shared pool library cache cache buffers chains cache buffers lru chain row cache objects
共享池和庫緩存闩等待通常是由於大量的硬解析在使用文本常量的應用程序中這是很頻繁的可以通過使用綁定變量設置CURSER_SHARING為FORCE解決通過查看V$SQLAREAPARSE_CALLS識別SQL語句
緩存緩沖鏈闩等待是由於低效的SQL語句引起的當應用程序打開多個並行會話執行相同的語句查詢相同的結果集時可以得到該沖突另一個原因是熱塊在緩存緩沖中可以查看V$SESSION_WAIT的PRAW 列檢查是否有熱塊在緩沖緩存中可以通過EXP/IMP帶熱塊的表增加PCTFREE的值進行擴展考慮減小具有許多熱塊的表的塊（i+）
緩存緩沖LRU鏈闩等待是由於有大量的緩存緩沖活動重復掃描大量低選擇性的索引或者執行全表掃描的語句通常是主要原因只能通過調整SQL
行緩存對象闩保護數據字典唯一方法是增加共享池的尺寸
enqueue wait
enqueue是應用於數據庫資源的鎖它們由應用程序請求初始化查詢V$ENQUEUE_STAT查看各種信息
最常見的enqueue等待是TX模式號為是一種行級鎖通過V$LOCK確定阻塞者另一種模式號為通常是主鍵強制或等待數據塊中的一個ITL槽查看V$SEGMENT_STATISTICS決定ITL等待的值統計名為ITL waits通過使用較高的INITTRANS和PCTFREE重建對象修復該問題
對於唯一鍵強制問題會在多個用戶同時向表插入相同的鍵時發生方法是找出為什麼應用程序允許用戶同時嘗試插入重復的鍵
另一種常見的enqueue是等待ST Enqueue每個數據庫只有一個ST鎖方法是更改UET$和FET$請求該鎖使用本地管理表空間修復使用TEMPFILE重建所有臨時表空間修復如果不能從字典管理表空間更改增加所有高速增長的段的next extent分區尺寸同時允許預分配分區
另一種常見Enqueue是等待TM Enqueue模式號為未索引外鍵是這種鎖沖突的主要原因如果應用程序顯示使用LOCK TABLE語句也會發生這種鎖見V$SQLAREA
Buffer busy waits
通常是由於多個會話嘗試讀取塊到內存中或者嘗試將塊釘在內存中時會發生可以通過嘗試降低級別或並行性或增加對象的FREELISTS/FREELIST GROUPS也可以嘗試重建表使用較小的塊或增加PCTFREE的值減少表中的行數
如果主要等待是段頭檢查NEXT分區的大小並確保PCTFREE和PCTUSED之間的gap不會太大如果主要等待是撤銷段頭可能是由於有太多的小的回滾段考慮使用系統管理撤銷
診斷延遲相關的等待事件
Log file sync
當會話等待LGWR寫出緩沖時會發生這種情況主要有三個原因高提交率—應用程序相關I/O子系統太慢—考慮RAWRAID 光纖通道過大的日志緩沖同時參數PROCESSES過大可能也會造成該事件等待增加
Log buffer space
如果由於空間不足或LGWR進程不夠快會話等待拷貝重做條目到日志緩沖時會發生這種情況如果是日志緩沖太小則考慮增加它如果是I/O子系統太慢—考慮使用NOLOGGING或升級硬件
Free buffer
當DBWR正在從SGA寫出藏塊時會發生該等待
主要原因有編寫得很差的SQLDBWRs不夠較慢的I/O延遲塊清除較小的緩沖緩存
延遲的塊清除第一個掃描剛被加載的表的進程將會進行該工作全表掃描剛加載的表可以最小化該問題或者分析這些數據也可以解決
緩沖緩存太小通常不是問題在增加它之前考慮增加DBWR
Write complete
是前台進程等待DBWR寫出塊的征兆檢查db file parallel writes事件的情況
Log file switch completion
當重做日志太小並且事務產生大量的重做日志條目時會發生這種事件可以通過創建更大的日志文件或可以通過創建更多的重組日志組完成
還有一些g新增的等待事件和RAC環境下的等待事件
Dumps和Traces
主要是ORA和核心ORA事件
作為一個DBA必須熟悉ORADEBUG可以通過oradebug dumplist和oradebug help得到幫助該工具沒有文檔
遇到塊中斷時需要dump數據文件和塊
alter system dump datafile <file#> block <block#>
控制文件也可以被dump當跟蹤恢復相關的問題和SCN同步問題時可能會有用
alter session set events immediate trace name controlf level
或
oradebug sitmypid
oradebug ulimit
oradebug dump controlf
共享池有問題時通常需要DUMP堆
alter sessions set events immediate trace name heapdump level <level>
或
oradebug sitmypid
oradebug ulimit
oradebug dump heapdump <level>
庫緩存DUMP會給出庫緩存中關於對象的詳細信息
alter sessions set events immediate trace name library_cache level
或
oradebug sitmypid
oradebug ulimit
oradebug dump library_cache
當診斷內存中斷或死鎖錯誤時可以使用DUMP進程狀態
alter sessions set events immediate trace name processstate level <level>
oradebug sitmypid
oradebug ulimit
oradebug dump processstate<level>
當診斷數據庫掛起條件時可以DUMP系統狀態
alter sessions set events immediate trace name systemstate level <level>
或
oradebug sitmypid
oradebug ulimit
oradebug dump systemstate <level>
From:http://tw.wingwit.com/Article/program/Oracle/201311/18546.html
