Oracle诊断事件实例（一）

最新推荐文章于 2025-07-05 10:34:00 发布

转载最新推荐文章于 2025-07-05 10:34:00 发布 · 119 阅读

本文介绍了Oracle数据库中的多种调试技巧，包括SQL跟踪事件设置、使用oradebug进行进程状态跟踪、immediate dump控制文件、ErrorStack的dump级别设置、挂起分析事件(hanganalyze)的使用方法等。这些技巧对于诊断Oracle数据库的性能问题、死锁情况和系统hang住等问题非常有用。

1、设置SQL跟踪事件

使用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关键字的，例如

SQL>oradebug dump processstate 1

oradebug与procedure set_ev对应关系如下：

oradebug dump

sys.dbms_system.set_ev(sid,serial#,65535,,);

3、immediate dump控制文件control file

SQL>oradebug setospid 4081

SQL>oradebug dump controlf 10

或者

SQL>sys.dbms_system.set_ev(7,10,65535,10,'controlf');

备注：除了上面的controlf，其他的immediate dump包括 file_hdrs, redohdr, processstate, 和 systemstate。

4、Error Stack的dump levels

这里假设操作系统进程Id为4018，以下为设置当前process的事件，它对共享服务器进程和并行查都是有用的。

SQL> oradebug setospid 4018
SQL> oradebug unlimit
SQL> oradebug dump errorstack 1

5、改变行为的事件

改变行为的事件通常被设置进参数文件。使用的语法和跟踪事件的完全一致。

例，设置该event事件来阻止SMON进程coalescing空闲空间：

event = "10269 trace name context forever, level 10"

6、10046事件

10046事件等同于sql_trace=true的作用。

使用该事件的好处是通过执行该事件的level，能够把更详细的内部信息输出到跟踪文件中。

SQL> alter session set events '10046 trace name context forever, level 12';

或者

SQL> alter system set events '10046 trace name context forever, level 12';

作用：

Level 1 提供标准的sql_trace功能；

Level 4 除了1外，还增加了绑定变量跟踪功能；

Level 8 除了1外，还能跟踪wait事件，通过这个来发现全表扫描和索引扫描。

Level12 除了1外，还有绑定变量跟踪与wait事件跟踪。

7、Events与Spfile的关系

ALTER SYSTEM命令一直是作为静态的改变；不允许指定scope=momery。disable或者alter列表中的单个event，则整个列表会被重新加入。

Disable所有的events，语法如下：

SQL> alter system reset event scope=SPFILE sid='*';

上面的“*”可以是RAC环境中的一个实例名。

例：

SQL> alter system set event ='10325 trace name context forever, level 10 :10015 trace name context forever, level 1' comment = 'Debug tracing of control and rollback' scope = SPFILE;

8、hanganalyze event
这是一个数据库被hang住，或者发生死锁时的典型事件，它能够基于ORA-60错误被设置到trigger中。hanganalyze试图找出谁在等待被谁建立的等待链，然后根据不同进程的level来dump它们的错误堆。这就好比手工去做但更快速。
注意：
hanganalyze不是特意为了诊断应用级死锁而提供的。
这个hanganalyze事件能够通过以下三种方法来发起：
a。SQL> ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level 4';
b。EVENT="60 trace name HANGANALYZE level 5" (instance level, triggered by ORA-60)
c。SQL> ORADEBUG hanganalyze 10
这里的level决定了哪些进程被dump到errorstack中，主要的Level有：
10 Dump all processes (voluminous data output, not a good idea)
5 Dump all processes involved in wait chains (can still produce a lot of output)
4 Dump leaf nodes in wait chains
3 Dump only processes thought to be in a hang situation
2 Minimal output
1 Very minimal output

9、执行immediate系统状态dump

SQL> alter session set events 'immediate trace name systemstate level 10';

或者，使用以下方式用oradebug来处理当前process：

SQL> oradebug setospid
SQL> oradebug dump systemstate 10

10、阅读System state的dump文件

A typical system state dump consists of three sections; the trace file header, system global information and process information. Of the three, the process information section is the most important for root cause investigations. The heading in the file for this section is “System State.” The first process state objects listed under this heading are the Oracle background processes. User processes (client) generally follow as do the other types of state objects (session, call, enqueue, etc.). The next few slides show a few of the different type of state objects as they would appear in a dump.

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.)

11、阅读Session State 的dump文件

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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9399028/viewspace-681507/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9399028/viewspace-681507/

标签