適用范圍Oracle任何平台上的企業版數據庫
適用對象所有數據庫管理員和數據庫支持人員
本文目的這篇文章主要描述用於診斷數據庫hanging和性能問題的方法和 工具這些問題可能是由於調整問題設計問題或者Oracle的bug引起的
將討論如下的診斷步驟
） 描述清楚出現的現象問題
） 尋找具體錯誤
） 收集操作系統級別上的數據
） 獲取systemstate和hanganalyze的dump
） 獲取STATPACK的輸出報告
） 獲取PROCESSSTATE的dump
注可能很多時候沒有必要關閉數據庫來停止hanging建議如果要關閉數據庫之前獲取這些診斷信息以便找出錯誤的原因所在
下面就來具體討論如何診斷數據庫Hanging問題
描述清楚出現的現象問題
先弄清楚運行的數據庫版本需要完整的版本號例如
確定當前數據庫是否是真的hanging還是處於活動狀態但是運行的非常慢？檢查下在Alert文件中是否還有日志切換檢查當前的CPUI/O內存的利用率
查看數據庫hanging的開始時間持續了多長時間？數據庫hanging是否是突然發生還是由於增加的活動事務導致性能的逐步降低？當前有多少的連接用戶？最近的系統負載是否是在上升？
是否在初始化參數文件中設置了任何event？數據庫當前正在做什麼類型的事務？數據庫的數據量多大？
數據庫是運行在集群環境嗎？如果是集群數據庫那麼關閉其他實例就留下一個實例問題是否還持續存在？這裡討論的某些解決方法適用於集群數據庫但是大部分的方法不適合例如一個不大的buffer cache通常對於集群數據庫來說意味著較好的性能關於集群數據庫的大部分hanging的問題這裡不做討論其中包括PCM鎖問題pinging空間管理問題節點間並行查詢調優共享磁盤或者虛擬共享磁盤問題網絡問題DLM問題等
數據庫是運行在MTS環境下嗎？如果取消MTS是否問題持續存在？是否使用了Oracle的應用或者工具？最近是否升級了數據庫應用工具或者操作系統硬件？問題發生的頻率？是否能夠重現問題？
是否整個數據庫都被hanging？
所有的實例？所有的連接？所有的操作？所有的節點？
首先確認是否能夠執行查詢select * from dual？日志文件多久切換一次？如果在Alert日志中有歸檔相關的錯誤信息那麼可以著手解決歸檔錯誤問題因為歸檔問題經常會掛起數據庫例如歸檔目的地空間滿了或者數據庫處於歸檔模式下但是ARCH進程被停止了一般可以先以sysdba權限連接到數據庫中執行ARCHIVE LOG LIST查看數據庫是否歸檔模式是否啟用了自動歸檔一般如果沒有啟用自動歸檔就很容易掛起數據庫了這個時候通常的做法就是把數據庫改成自動歸檔模式或者是非歸檔模式
一個指定的SQL語句操作？
） 如果是由於指定的SQL語句導致數據庫掛起先執行帶有timed_statistics參數的TKPROF輸出報告以及SQL語句的執行計劃然後就需要分SQL語句類型來分析了
） 如果是select語句那麼這個SQL語句應該是需要被調整如果是一個非常復雜的SQL語句那麼嘗試是否可以中斷
） 如果是一個並行查詢語句可以參考監控當前並行查詢運行狀況腳本獲得並行查詢的執行計劃可能是空間事務競爭如果在Alert日志文件中出現ORA錯誤那麼請將臨時表空間的參數pct_increase設置為以便禁止SMON進程接合連續的extents因此減少查詢slaves的競爭同時將數據文件盡量分散到不同的磁盤上去減少磁盤I/O的競爭適當增加sort_area_size的大小可能會減少並行度
） 如果是DML語句那麼可能是由於鎖導致的需要去獲取v$lock的輸出信息關於鎖的信息可以參考返回鎖信息腳本查看DML語句的對象上是否有限制或者觸發器有可能產生級聯鎖問題把索引建立在相關的外鍵列上這樣會改變在父表上的鎖行為
） 如果是DDL語句可能是一個數據字典的相關問題如果是create index語句則可能是一個空間事務競爭問題調整I/O是一個比較好的方法分布式I/O分開索引和數據的存放空間並行執行都是比較有用的方法還可以設置初始化參數pre_page_sga為true
指定的數據庫對象？
在指定對象能是否能做任何操作？做一個select count（*）是否有問題？如果只是update該對象存在問題那麼可能鎖了可以從上面））中的腳本獲取鎖的信息
是否預先分配好了空間給這個對象？如果是那麼將提高HWM並且導致全表掃描以至於讓數據庫看起來像是掛起了全表掃描總是會掃描HWM即使表只存在很少的數據解決方案就是盡量避免預分配extents除非馬上要執行一個大的並行插入或者常規的裝載千萬不要在直接裝載的時候預分配extents
如果對象是一個表那麼可以嘗試
ANALYZE TABLE <tablename> VALIDATE STRUCTURE CASCADE
是否有報錯如果有報錯意味著表或者表上的索引存在壞塊了如果沒有報錯那麼繼續嘗試下面的SQL語句得到相應的的信息
塊級上的空間信息一個高的chain out也可能是問題的一部分
SELECT *
FROM sysdba dba_tables
WHERE table_name = <TABLENAME>
如果你有很多的更新和刪除操作那麼一個不適合的索引也會造成問題下面的SQL語句能幫你得到相關的索引信息
SELECT i*
FROM sysindex_stats i sysdba_indexes d
WHERE iname = dindex_name
AND dtable_name = <TABLENAME>SELECT i*
FROM sysindex_stats i sysdba_indexes d
WHERE iname = dindex_name
AND dtable_name = <TABLENAME>
如果是一個視圖那麼需要查看視圖建立在的表的信息
SELECT text
FROM sysdba_views
WHERE view_name = <VIEWNAME>
大規模的更新操作（例如使用SQLLDRIMPORT或者批處理操作）？
這些操作上的表上存在有哪些索引？是否這些更新操作是在數據庫高峰時期運行的？是否在Alert文件中存在有checkpoint not complete的錯誤信息？如果有表明重做日志文件太小了需要調整它們是否表空間被置於在熱備模式下？（v$backup）如果表空間處於熱備模式那麼產生日志records而不是vectors在一個大的更新操作中就可能導致相當多的競爭和性能下降
如果是一個SQLLDR操作是否使用了傳統路徑方式？是否使用了REPLACE選項？（推薦使用TRUNCATE選項）在SQLLDR的控制文件中是否有sql functions？是否采用了readbuffersbindsizerowsparallele方式？
如果是一個IMPORT操作是否使用了commit=yindexes=yconstraints=y這些參數？是否增大了buffer？
如果在update期間有很多的用戶在操作那麼容易造成資源競爭導致系統變慢回滾段redo latches i/o和數據緩沖區都可能成為競爭的區域我們可以從V$session_wait以及statpack中獲取更多關於具體競爭的相關信息
指定的包存儲過程或者PRO*C應用？
首先需要查看這些包存儲過程或者PRO*C的具體內容其中的哪個語句一直在執行？去掉這個語句後相應的程序是否能運行正常？如果是存儲過程那麼可以利用DBMS_ALERT查看那裡開始掛起了如果是PRO*C程序那麼可以使用tkprof來識別parsing是否是瓶頸？如果是那麼可以使用預編譯參數
hold_cursor和release_cursor來調整如果是一個包那麼嘗試是否能單獨執行每個存儲過程？查看是否包和存儲過程被刷新出了共享池如果是可以嘗試把這些包和存儲過程pin在共享池中
SELECT *
FROM v$db_object_cache
WHERE name = <NAME>
僅僅是遠程訪問？
是否可以執行select * from dual@db_link？是否能夠連接到遠程的機器上執行本地的操作？是否是在做一個分布式的更新操作？初始化參數distributed_lock_timeout設置了多少？是否正在刷新快照？是否使用了對稱復制？嘗試做一個tkprof輸出得到相應的執行計劃執行計劃中如果標明是REMOTE的那麼就是遠程執行的操作如果在一個遠程的機器上join兩張表那麼請嘗試在本地節點上生成join視圖之後查詢這個視圖在sql操作中設置ARRAYSIZE多使用pl/sql而不是單獨的sql語句使用顯性游標這些都可以減少網絡的負載
使用第三方應用軟件的操作
是否能在sqlplus中重現問題？如果不可以重現那麼就需要聯系第三方應用軟件供應商尋求幫助
數據關閉/啟動過程中出現掛起
關閉使用的什麼參數？數據庫是否crash了？如果是數據庫啟動掛起並且非正常關閉但是在Alert日志文件中沒有任何的錯誤那麼可能只是一個正常的實例恢復如果在Alert文件中出現內部錯誤系統錯誤那麼請嘗試正常的關閉數據庫然後啟動
下面是一個正常實例恢復的時候在Alert日志文件中列出的相關信息
Starting ORACLE instance （normal）
…………………
Starting up ORACLE RDBMS Version
System parameters with nondefault values
……………………
Beginning crash recovery of threads
Started redo scan
Completed redo scan
redo blocks read data blocks need recovery
Recovery of Online Redo Log Thread Group Seq Reading mem
Completed redo application
Completed crash recovery at
Thread logseq block scn
data blocks read data blocks written redo blocks read
SMON enabling cache recovery
SMON enabling tx recovery
Completed ALTER DATABASE OPEN
如果正常的關閉或者immediate關閉掛起那麼意味著Oracle正在等待激活的會話退出
在Unix系統上還可以尋找正在掛起的啟動或者關閉操作然後trace pid
尋找錯誤
） 檢查AlertSIDlog告警日志文件看看是否存在錯誤信息此告警日志文件的具體路徑位置可以由初始化參數中的background_dump_dest中獲得或者在sqlplus中執行show parameter dest獲得
） 檢查上述目錄中的在數據庫掛起時間生成的跟蹤文件查看裡面的錯誤信息不用搜索整個跟蹤文件相關的錯誤信息一般都是在文件的最開始出現
） 如果是遠程訪問的問題那麼還需要檢查sql*net跟蹤目錄下的跟蹤文件
） 檢查系統信息的錯誤日志在大多數的Unix下都是在/var/adm目錄下
輸出查看相關的V$視圖
當數據庫掛起的時候執行下面的查詢
SPOOL v_viewslog
SELECT *
FROM v$parameter
SELECT class value name
FROM v$sysstat
SELECT sid id id type lmode request
FROM v$lock
SELECT llatch# nname hpid lgets lmisses
limmediate_gets limmediate_misses lsleeps
FROM v$latchname n v$latchholder h v$latch l
WHERE llatch# = nlatch#
AND laddr = hladdr（+）
SELECT *
FROM v$session_wait
ORDER BY sid
/* 重復最後一個查詢最少三遍以確定哪個在重復等待*/
SPOOL OFF
如果是指定的查詢被掛起了可以使用下面的查詢找出相應的查詢SQL語句
通過操作系統上的PID找出相應的SQL語句的SID
SELECT ssid pspid
FROM v$session s v$process p
WHERE spaddr = paddr
AND …… < pspid = <os pid> or perhaps
ssid = <sid from v$session> >
然後通過SID找出相應的SQL語句的具體內容
SELECT ssid sstatus qsql_text
FROM v$session s v$sqltext q
WHERE ssql_hash_value = qhash_value
AND ssql_address = qaddress
AND ssid = <sid>
order by qpiece
查詢V$SESSION_WAIT視圖看看當前的等待事件
column sid format
column seq# format
column wait_time heading WTime format
column event format a
column p format
column p format
column p format
select sideventseq#pppwait_time from V$session_wait
where sid=<SID>
order by sid
查詢當前掛起數據庫的SQL語句中的lockwait設置的是多少如果非空那麼看看什麼鎖住了當前對象是什麼類型的鎖
SELECT lockwait
FROM v$session
WHERE sid = <sid>
col Username format A
col Sid format heading SID
col Type format A
col Lmode format heading HELD
col Request format heading REQ
col Id format
col Id format
select SNUsername MSid MType
DECODE（MLmode None Null Row Share Row
Excl Share S/Row Excl Exclusive
LTRIM（TO_CHAR（Lmode））） Lmode
DECODE（MRequest None Null Row Share Row
Excl Share S/Row Excl Exclusive
LTRIM（TO_CHAR（MRequest ））） Request
MId MId from V$SESSION SN V$LOCK M
WHERE （SNSid = MSid and MRequest ！ = ）
or （SNSid = MSid and MRequest = and Lmode ！= and （id id）
in （select SId SId from V$LOCK S where Request ！= and SId
= MId and SId = MId） ） order by Id Id MRequest
查詢v$process視圖中的LATCHWAIT設置是多少？如果這個值非空那麼繼續查是誰保存了這個latch
SELECT latchwait
FROM v$process
WHERE spid = <pid>SELECT latchwait
FROM v$process
WHERE spid = <pid>
column name format a heading LATCH NAME
column pid heading HOLDER PID
select cnameaaddragetsamissesasleeps
aimmediate_getsaimmediate_missesbpid
from v$latch a v$latchholder b v$latchname c
where aaddr = bladdr（+） and alatch# = clatch#
and cname like &latch_name% order by alatch#
上述這些保存了鎖和latch的會話是否關閉了終端但是沒有退出這可能會導致一個影子進程繼續保存那些資源這樣就需要殺掉相應的進程可以使用如下語句
alter system kill session <sid serial# from v$session>
如果會話沒有被掛起而只是運行緩慢那麼需要查看會話的具體信息
SELECT ssid svalue tname
FROM v$sesstat s v$statname t
WHERE sstatistic# = tstatistic#
AND ssid = <sid>
如果會話極度的緩慢或者是被掛起了那麼需要查看會話的等待信息
SELECT *
FROM v$session_wait
where sid = <sid>
如果是個分布式事務那麼需要在各個節點上都運行如下SQL語句
SELECT * FROM dba_pc_pending
SELECT * FROM pending_sessions$
SELECT * FROM pending_sub_sessions$
SELECT * FROM dba_pc_neighbors
如果是MTS服務器那麼可以查看一下當前的dispatcher的繁忙程度
select namenetworkstatus
（busy /（busy + idle）） * % of time busy
from v$dispatchers
還可以查看V$SHARED_SERVERS視圖獲取相應的信息
select namestatusrequests （busy /（busy + idle）） * % of time busy
from v$shared_servers
收集操作系統的相關信息
） 簡短的描述你的架構包括CPU的數量磁盤的數量是否使用了裸設備使用了NFS文件系統共享磁盤……是否鏡像了這些？
） 測量不同操作系統級別的活動過量的CPU或者I/O頁面交換區等有許多的工具可以監測這些例如TOP
Unix上的工具SARVMSTATNETSTATTOPTRUSS等
Vms上的工具MONITORANALYZEPROCESS等
Windows上的工具Performance Monitor Event Monitor Dr Watsonqslice等
） 檢查系統的日志文件在大多數Unix平台上日志文件都存在於/var/adm目錄下
獲取SYSTEMSTATE和HANGANALYZE的dump
這兩個命令將在user_dump_dest目錄下創建一個非常大的跟蹤文件初始化參數文件中的MAX_DUMP_FILE_SIZE參數確定了能夠容納的最大跟蹤文件的大小使用Oradebug命令設置unlimit將能允許執行一個完全的dump請確認整個數據庫已經掛起或者即將掛起並且在Alert告警日志文件中沒有任何歸檔的錯誤的時候才可以做此操作
注意當數據庫是集群數據庫的時候如果需要診斷掛起的問題則需要在每個節點上都執行systemstate dump操作建議做次左右以便能夠確定數據庫或者進程是否是真的掛起還是激活狀態
對於Oracle x to x的版本
$ svrmgrl
svrmgr> connect internal
svrmgr>ALTER SESSION SET EVENTS IMMEDIATE TRACE NAME HANGANALYZE LEVEL
wait seconds
svrmgr>ALTER SESSION SET EVENTS IMMEDIATE TRACE NAME HANGANALYZE LEVEL
EXIT …… then reconnect
svrmgr>ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED
svrmgr>ALTER SESSION SET EVENTS IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL
wait seconds
svrmgr>ALTER SESSION SET EVENTS IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL
wait seconds
svrmgr>ALTER SESSION SET EVENTS IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL
對於Oracle 或者更高的版本
$ sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug hanganalyze
wait seconds
oradebug hanganalyze
oradebug dump systemstate
wait seconds
oradebug dump systemstate
wait seconds
oradebug dump systemstate
獲取STATPACK的輸出報告
對於如何得到和分析statpack的輸出報告可以參考eygle的個人網站上的文章
獲取PROCESSSTATE的dump
獲取processstate dump可以使用如下命令建議執行三遍將可以在user_dump_dest目錄下找到生成的跟蹤文件
$ sqlplus /as sysdba
oradebug setospid <process ID>
oradebug unlimit
如果要獲取errorstacks dump可以使用如下命令建議執行三遍同樣可以在user_dump_dest目錄下找到生成的跟蹤文件
$ sqlplus /as sysdba
oradebug setospid <process ID>
oradebug unlimit
oradebug dump errorstack
From:http://tw.wingwit.com/Article/program/Oracle/201311/18122.html
