Oracle診斷事件例項（一）

使用dbmsutil.sql指令碼來建立dbms_system包。

sid：目標session的sid；

serial#：目標session的序列號（serial number）；

以上sid和serial#資訊，可以透過v$session檢視獲得。

event：Oracle資料庫中內部定義的事件程式碼；

level：dump資訊的級別；當做一個塊的dump時，這個level被使用指定該data block address；

action：該跟蹤事件的Action；

例：跟蹤一個SQL語句（使用10046的12級）的事件，若該語句對應的Session的sid為8，serial#為219，則可以在SQL*Plus中執行如下指令碼：

SQL>execute sys.dbms_system.set_ev(8,219,10046,12,'');

 

2、使用oradebug跟蹤事件

首先，需要定位你想oradebug的後臺程式：

SQL>select spid from v$process where username = 'oracle';

然後，確認該程式spid為3514在作業系統級別存在：

ps -ef | grep 3514  

最後，使用oradebug來dump該程式的狀態資訊：

SQL>oradebug setospid 3514

SQL>oradebug unlimit

SQL>oradebug dump processstate 10

當我們dump程式狀態，系統狀態等等的時候，使用的語法如下：

oradebug dump

是不帶level關鍵字的，例如

 

                  Process State  Object

 

SYSTEM STATE
------------
SO: 40002c530, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=6, calls cur/top: 40005c698/40005c698, flag: (a) SYSTEM
  int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 51
              last post received-location: ktmchg
              last process to post me: 40002ccf0 2 0
              last post sent: 79 0 4
              last post sent-location: kslpsr
              last process posted by me: 40002ccf0 2 0
    (latch info) wait_event=0 bits=0
 O/S info: user: user1, term: ?, ospid: 32494
 OSD pid info:Unix process pid: 32494,image:ora_smon_u01

解讀如下：

Line 3: The state object ID, type (1=process), and owner of the process.
Line 4: The Oracle process id (6) from v$process.pid, address of the current call (cur) from the state object and the top level call's address (top). Some possible flag values are:
0x01: Process dead and will be cleaned up
0x02: SYSTEM process
0x04: The process is PMON
0x08: The process is SMON
0x10: The process is the PSEUDO process
0x20: Oracle Shared server
0x40: Dispatcher process
Line 5: The error code (ora-nnnn) to be raised on the next interrupt (int error), the error code to return on user call (call error) and the Oracle error returned on session creation (sess error.)
Lines 6-11: The inter-process posting information.
Line 12: Latch info dumped by ksldmp, includes events waiting for (wait event) and the bit array of any latches owned.
Lines 13-14: O/S data (user, process ID and Unix process name.)

 

                Session State Object

 

SO:400039930,type:3, owner:40002c530,flag: INIT/-/-/0x00
  (session) trans: 0, flag: (51) USR/- BSY/-/-/-/-/-
DID: 0001-0006-00000001, short-term DID: 0000-0000-00000000
 txn branch: 0
 oct: 0, prv: 0, user: 0/SYS
waiting for 'smon timer' seq=6639 wait_time=0  
sleep time=12c, failed=0,

解讀如下：

Line 1: The state object id and owner are stated here.
Line 2: Address of transaction SO (trans), session type (flag), some possible values are:
0x0001: A user session rather than a recursive session
0x0002: A recursive session (always internal)
0x0008: Disable commit/rollback from plsql
0x0010: User session created by system processes
0x0020: Set when the UGA is allocated in the SGA
0x0040: User session logs on to ORACLE
0x0080: User session created by Oracle Shared Server
Some descriptive letter codes follow. Possible values are:
USR: User session
BSY: Session is busy. It's in a call.
DED: Session marked dead by user process.
DEL: Session being deleted (through alter system kill session).
KIL: Session marked for kill (through alter system kill session).
Lines 3-4: Resource id (DID) information and transaction relationships are shown here.
Line 5: Oracle command type (oct), user privileges (priv), and Oracle user are shown.
Lines 6-7: Resource waited for, wait and sleep times are displayed here.

 

12、診斷hang或者loop是非常有用的檢視及資料字典如下：

V$SESSION_WAIT, V$SESSION_EVENT
V$LATCH, V$LATCHHOLDER, V$LATCHNAME
V$SYSSTAT, V$LOCK
$PROCESS, V$SESSION, V$TRANSACTION
X$KCBFWAIT (buffer waits), X$KSQST (enqueues)