熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

診斷Oracle數據庫Hanging問題

2013-11-13 16:18:43  來源: Oracle 

  適用范圍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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.