診斷Oracle數據庫Hanging問題
2024-08-29 13:34:10
供稿：網友
適用范圍：Oracle任何平臺上的企業版數據庫 適用對象：所有數據庫治理員和數據庫支持人員 本文目的：這篇文章主要描述用于診斷數據庫hanging和性能問題的方法和 工具，這些問題可能是由于調整問題，設計問題或者Oracle的bug引起的。 將討論如下的診斷步驟： 1） 描述清楚出現的現象問題 2） 尋找具體錯誤 3） 收集操作系統級別上的數據 4） 獲取systemstate和hanganalyze的dump 5） 獲取STATPACK的輸出報告 6） 獲取PROCESSSTATE的dump 注：可能很多時候沒有必要關閉數據庫來停止hanging，建議假如要關閉數據庫之前獲取這些診斷信息以便找出錯誤的原因所在。 下面就來具體討論如何診斷數據庫Hanging問題。 描述清楚出現的現象問題： 先弄清楚運行的數據庫版本，需要完整的版本號，例如9.2.0.4. 確定當前數據庫是否是真的hanging還是處于活動狀態但是運行的非常慢？檢查下在Alert文件中是否還有日志切換，檢查當前的CPU，I/O，內存的利用率。 查看數據庫hanging的開始時間，持續了多長時間？數據庫hanging是否是忽然發生還是由于增加的活動事務導致性能的逐步降低？當前有多少的連接用戶？最近的系統負載是否是在上升？ 是否在初始化參數文件中設置了任何event？數據庫當前正在做什么類型的事務？數據庫的數據量多大？ 數據庫是運行在集群環境嗎？假如是集群數據庫，那么關閉其他實例就留下一個實例，問題是否還持續存在？這里討論的某些解決方法適用于集群數據庫，但是大部分的方法不適合。例如，一個不大的buffer cache通常對于集群數據庫來說意味著較好的性能。關于集群數據庫的大部分hanging的問題這里不做討論，其中包括PCM鎖問題，pinging，空間治理問題，節點間并行查詢調優，共享磁盤或者虛擬共享磁盤問題，網絡問題，DLM問題等。 數據庫是運行在MTS環境下嗎？假如取消MTS，是否問題持續存在？是否使用了Oracle的應用或者工具？最近是否升級了數據庫，應用，工具或者操作系統，硬件？問題發生的頻率？是否能夠重現問題？ 是否整個數據庫都被hanging？ 所有的實例？所有的連接？所有的操作？所有的節點？ 首先確認是否能夠執行查詢select * from dual？日志文件多久切換一次？假如在Alert日志中有歸檔相關的錯誤信息，那么可以著手解決歸檔錯誤問題，因為歸檔問題經常會掛起數據庫。例如：歸檔目的地空間滿了，或者數據庫處于歸檔模式下但是ARCH進程被停止了。一般可以先以sysdba權限連接到數據庫中，執行ARCHIVE LOG LIST，查看數據庫是否歸檔模式，是否啟用了自動歸檔，一般假如沒有啟用自動歸檔，就很輕易掛起數據庫了，這個時候通常的做法就是把數據庫改成自動歸檔模式或者是非歸檔模式。 一個指定的SQL語句操作？ 1） 假如是由于指定的SQL語句導致數據庫掛起，先執行帶有timed_statistics參數的TKPROF輸出報告以及SQL語句的執行計劃，然后就需要分SQL語句類型來分析了： 2） 假如是select語句，那么這個SQL語句應該是需要被調整，假如是一個非常復雜的SQL語句，那么嘗試是否可以中斷。 3） 假如是一個并行查詢語句，可以參考監控當前并行查詢運行狀況腳本獲得并行查詢的執行計劃?？赡苁强臻g事務競爭，假如在Alert日志文件中出現ORA-1575錯誤，那么請將臨時表空間的參數pct_increase設置為0以便禁止SMON進程接合連續的extents，因此減少查詢slaves的競爭。同時將數據文件盡量分散到不同的磁盤上去，減少磁盤I/O的競爭，適當增加sort_area_size的大小可能會‘減少’并行度。 4） 假如是DML語句，那么可能是由于鎖導致的，需要去獲取v$lock的輸出信息，關于鎖的信息可以參考返回鎖信息腳本。查看DML語句的對象上是否有限制或者觸發器，有可能產生級聯鎖問題。把索引建立在相關的外鍵列上，這樣會改變在父表上的鎖行為。 5） 假如是DDL語句，可能是一個數據字典的相關問題。假如是create index語句則可能是一個空間事務競爭問題。調整I/O是一個比較好的方法，分布式I/O，分開索引和數據的存放空間，并行執行都是比較有用的方法，還可以設置初始化參數pre_page_sga為true. 指定的數據庫對象？
在指定對象能是否能做任何操作？做一個select count（*）是否有問題？假如只是update該對象存在問題，那么可能鎖了，可以從上面3）、4）中的腳本獲取鎖的信息。 是否預先分配好了空間給這個對象？假如是，那么將提高HWM并且導致全表掃描，以至于讓數據庫看起來像是“掛起”了。全表掃描總是會掃描HWM，即使表只存在很少的數據。解決方案就是盡量避免預分配extents除非馬上要執行一個大的并行插入或者常規的裝載。千萬不要在直接裝載的時候預分配extents. 假如對象是一個表，那么可以嘗試 ANALYZE TABLE <tablename> VALIDATE STRUCTURE CASCADE； 是否有報錯，假如有報錯，意味著表或者表上的索引存在壞塊了。假如沒有報錯，那么繼續嘗試下面的SQL語句得到相應的的信息： 塊級上的空間信息，一個高的chain out，也可能是問題的一部分。 SELECT * FROM sys.dba dba_tables WHERE table_name = '<TABLENAME>'； 假如你有很多的更新和刪除操作，那么一個不適合的索引也會造成問題，下面的SQL語句能幫你得到相關的索引信息： SELECT i.* FROM sys.index_stats i， sys.dba_indexes d WHERE i.name = d.index_name AND d.table_name = '<TABLENAME>'；SELECT i.* FROM sys.index_stats i， sys.dba_indexes d WHERE i.name = d.index_name AND d.table_name = '<TABLENAME>'； 假如是一個視圖，那么需要查看視圖建立在的表的信息： SELECT text FROM sys.dba_views WHERE view_name = '<VIEWNAME>'； 大規模的更新操作（例如使用SQLLDR，IMPORT或者批處理操作）？ 這些操作上的表上存在有哪些索引？是否這些更新操作是在數據庫高峰時期運行的？是否在Alert文件中存在有"checkpoint not complete"的錯誤信息？假如有表明重做日志文件太小了，需要調整它們。是否表空間被置于在熱備模式下？（v$backup）假如表空間處于熱備模式，那么產生日志“records”而不是“vectors”，在一個大的更新操作中，就可能導致相當多的競爭和性能下降。 假如是一個SQLLDR操作，是否使用了傳統路徑方式？是否使用了REPLACE選項？（推薦使用TRUNCATE選項）在SQLLDR的控制文件中是否有sql functions？是否采用了readbuffers，bindsize，rows，parallele方式？ 假如是一個IMPORT操作，是否使用了commit=y，indexes=y，constraints=y這些參數？是否增大了buffer？ 假如在update期間，有很多的用戶在操作，那么輕易造成資源競爭，導致系統變慢?；貪L段，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： 10.2.0.1.0. System parameters with non-default values： …………………… Beginning crash recovery of 1 threads Started redo scan Completed redo scan 120 redo blocks read， 46 data blocks need recovery Recovery of Online Redo Log： Thread 1 Group 2 Seq 143 Reading mem 0 Completed redo application Completed crash recovery at Thread 1： logseq 143， block 4358， scn 512699 46 data blocks read， 46 data blocks written， 120 redo blocks read SMON： enabling cache recovery SMON： enabling tx recovery Completed： ALTER DATABASE OPEN 假如正常的關閉或者immediate關閉掛起，那么意味著Oracle正在等待激活的會話退出。 在Unix系統上，還可以尋找正在掛起的啟動或者關閉操作，然后trace pid. 尋找錯誤： 1） 檢查AlertSID.log告警日志文件看看是否存在錯誤信息，此告警日志文件的具體路徑位置可以由初始化參數中的background_dump_dest中獲得或者在sqlplus中執行show parameter dest獲得。 2） 檢查上述目錄中的在數據庫掛起時間生成的跟蹤文件。查看里面的錯誤信息，不用搜索整個跟蹤文件，相關的錯誤信息一般都是在文件的最開始出現。 3） 假如是遠程訪問的問題，那么還需要檢查sql*net跟蹤目錄下的跟蹤文件。 4） 檢查系統信息的錯誤日志，在大多數的Unix下都是在/var/adm目錄下。 輸出查看相關的V$視圖： 當數據庫掛起的時候，執行下面的查詢： SPOOL v_views.log； SELECT * FROM v$parameter； SELECT class， value， name FROM v$sysstat； SELECT sid， id1， id2， type， lmode， request FROM v$lock； SELECT l.latch#， n.name， h.pid， l.gets， l.misses， l.immediate_gets， l.immediate_misses， l.sleeps FROM v$latchname n， v$latchholder h， v$latch l WHERE l.latch# = n.latch# AND l.addr = h.laddr（+）；
SELECT * FROM v$session_wait ORDER BY sid； /* 重復最后一個查詢最少三遍，以確定哪個在重復等待*/ SPOOL OFF； 假如是指定的查詢被掛起了，可以使用下面的查詢找出相應的查詢SQL語句： 通過操作系統上的PID找出相應的SQL語句的SID： SELECT s.sid， p.spid FROM v$session s， v$process p WHERE s.paddr = p.addr AND …… < p.spid = <os pid> or perhaps s.sid = <sid from v$session> > 然后通過SID找出相應的SQL語句的具體內容： SELECT s.sid， s.status， q.sql_text FROM v$session s， v$sqltext q WHERE s.sql_hash_value = q.hash_value AND s.sql_address = q.address AND s.sid = <sid> order by q.piece； 查詢V$SESSION_WAIT視圖看看當前的等待事件 column sid format 990 column seq# format 99990 column wait_time heading 'WTime' format 99990 column event format a30 column p1 format 9999999990 column p2 format 9999999990 column p3 format 9990 select sid，event，seq#，p1，p2，p3，wait_time from V$session_wait where sid=<SID> order by sid； 查詢當前掛起數據庫的SQL語句中的lockwait設置的是多少，假如非空，那么看看什么鎖住了當前對象，是什么類型的鎖。 SELECT lockwait FROM v$session WHERE sid = <sid>； col Username format A15 col Sid format 9990 heading SID col Type format A4 col Lmode format 990 heading 'HELD' col Request format 990 heading 'REQ' col Id1 format 9999990 col Id2 format 9999990 select SN.Username， M.Sid， M.Type， DECODE（M.Lmode， 0， 'None'， 1， 'Null'， 2， 'Row Share'， 3， 'Row Excl.'， 4， 'Share'， 5， 'S/Row Excl.'， 6， 'Exclusive'， LTRIM（TO_CHAR（Lmode，'990'））） Lmode， DECODE（M.Request， 0， 'None'， 1， 'Null'， 2， 'Row Share'， 3， 'Row Excl.'， 4， 'Share'， 5， 'S/Row Excl.'， 6， 'Exclusive'， LTRIM（TO_CHAR（M.Request， '990'））） Request， M.Id1， M.Id2 from V$SESSION SN， V$LOCK M WHERE （SN.Sid = M.Sid and M.Request ！ = 0）
or （SN.Sid = M.Sid and M.Request = 0 and Lmode ！= 4 and （id1， id2） in （select S.Id1， S.Id2 from V$LOCK S where Request ！= 0 and S.Id1 = M.Id1 and S.Id2 = M.Id2） ） order by Id1， Id2， M.Request； 查詢v$process視圖中的LATCHWAIT設置是多少？假如這個值非空，那么繼續查是誰保存了這個latch. SELECT latchwait FROM v$process WHERE spid = <pid>；SELECT latchwait FROM v$process WHERE spid = <pid>； column name format a32 heading 'LATCH NAME' column pid heading 'HOLDER PID' select c.name，a.addr，a.gets，a.misses，a.sleeps， a.immediate_gets，a.immediate_misses，b.pid from v$latch a， v$latchholder b， v$latchname c where a.addr = b.laddr（+） and a.latch# = c.latch# and c.name like '&latch_name%' order by a.latch#； 上述這些保存了鎖和latch的會話是否關閉了終端但是沒有退出，這可能會導致一個影子進程繼續保存那些資源，這樣就需要殺掉相應的進程，可以使用如下語句： alter system kill session '<sid， serial# from v$session>' 假如會話沒有被掛起而只是運行緩慢，那么需要查看會話的具體信息： SELECT s.sid， s.value， t.name FROM v$sesstat s， v$statname t WHERE s.statistic# = t.statistic# AND s.sid = <sid>； 假如會話極度的緩慢或者是被掛起了，那么需要查看會話的等待信息： SELECT * FROM v$session_wait where sid = <sid>； 假如是個分布式事務，那么需要在各個節點上都運行如下SQL語句： SELECT * FROM dba_2pc_pending； SELECT * FROM pending_sessions$； SELECT * FROM pending_sub_sessions$； SELECT * FROM dba_2pc_neighbors； 假如是MTS服務器，那么可以查看一下當前的dispatcher的繁忙程度： select name，network，status， （busy /（busy + idle）） * 100 "% of time busy" from v$dispatchers； 還可以查看V$SHARED_SERVERS視圖獲取相應的信息： select name，status，requests， （busy /（busy + idle）） * 100 "% of time busy" from v$shared_servers 收集操作系統的相關信息： 1） 簡短的描述你的架構，包括CPU的數量，磁盤的數量。是否使用了裸設備，使用了NFS文件系統，共享磁盤……是否鏡像了這些？ 2） 測量不同操作系統級別的活動：過量的CPU或者I/O，頁面，交換區等。有許多的工具可以監測這些，例如TOP. Unix上的工具：SAR，VMSTAT，NETSTAT，TOP，TRUSS等
Vms上的工具：MONITOR，ANALYZE，PROCESS等 Windows上的工具：Performance Monitor， Event Monitor， Dr. Watson，qslice等 3） 檢查系統的日志文件，在大多數Unix平臺上日志文件都存在于/var/adm目錄下。 獲取SYSTEMSTATE和HANGANALYZE的dump 這兩個命令將在user_dump_dest目錄下創建一個非常大的跟蹤文件，初始化參數文件中的MAX_DUMP_FILE_SIZE參數確定了能夠容納的最大跟蹤文件的大小。使用Oradebug命令設置unlimit將能答應執行一個完全的dump.請確認整個數據庫已經掛起或者即將掛起，并且在Alert告警日志文件中沒有任何歸檔的錯誤的時候才可以做此操作。 注重：當數據庫是集群數據庫的時候，假如需要診斷掛起的問題，則需要在每個節點上都執行systemstate dump操作，建議做3次左右，以便能夠確定數據庫或者進程是否是真的掛起還是激活狀態。 對于Oracle 8.0.5.x to 8.1.7.x的版本： $ svrmgrl svrmgr> connect internal svrmgr>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME HANGANALYZE LEVEL 3'； wait 90 seconds svrmgr>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME HANGANALYZE LEVEL 3'； EXIT …… then reconnect svrmgr>ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED； svrmgr>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10'； wait 90 seconds svrmgr>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10'； wait 90 seconds svrmgr>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10'； 對于Oracle 9.2.0.1或者更高的版本： $ sqlplus /nolog connect / as sysdba oradebug setmypid oradebug unlimit oradebug hanganalyze 3 wait 90 seconds oradebug hanganalyze 3 oradebug dump systemstate 10 wait 90 seconds oradebug dump systemstate 10 wait 90 seconds oradebug dump systemstate 10 獲取STATPACK的輸出報告 對于如何得到和分析statpack的輸出報告，可以參考eygle的個人網站上的文章。 http://www.eygle.com/archives/2004/11/statspack_list.Html 獲取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 3
