ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (使用hanganlyze&oradebug)

oracle实验记录 (使用hanganlyze&oradebug)

原创 Linux操作系统 作者:fufuh2o 时间:2009-09-21 15:46:24 0 删除 编辑


针对oracle的DML dead lock oracle 会通过pmon发现并自动清理 回退其中的一个操作(是该事务的一个操作,而不是回退整个transaction),当PINS 或latches时oracle 不会检测处理这种dead lock


使用hanganlye可以查看db中各种资源的堵塞情况


例简单的lock 等待
SQL> create table t1 (a int);

表已创建。
SQL> select distinct sid from v$mystat;

       SID
----------
       159
SQL> insert into t1 values(1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> update t1 set a=2;

已更新 1 行。

 

SQL> select distinct sid from v$mystat;

       SID
----------
       146

SQL> update t1 set a=3;(hange住了)

 

LEVEL
1-2      Only HANGANALYZE output, no process dump at all(只有hanganalyeze输出,不dump任何进程)
3        Level 2 + Dump only processes thought to be in a hang (IN_HANG state)(DUMP IN_HANGE状态的进程)
4        Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)
5        Level 4 + Dump all processes involved in wait chains (NLEAF state)
10       Dump all processes (IGN state)


SQL> oradebug setmypid
已处理的语句          ORADEBUG setinst all(写上这个的话 对所有instance)

SQL> oradebug hanganalyze 3
Hang Analysis in d:\oracle\product\10.2.0\admin\xh\udump\xh_ora_23712.trc
打看下trace 文件

Open chains found:
Chain 1 : : 对应下面的格式很清晰
    <0/159/1/0x3424c60c/21212/SQL*Net message from client>
 -- <0/146/23/0x3424e3bc/23324/enq: TX - row lock contention>
~可以看到oracle发现 159,l46争夺LOCK ,wait_event:146等待159释放lock(TX)


Extra information that will be dumped at higher levels:
[level  4] :   1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW]
[level  5] :   5 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level  6] :   1 node dumps -- [NLEAF]
[level 10] :  13 node dumps -- [IGN]

State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
上面是解释下面的格式
nodenum:hanganalyze为记录session自动建立的编号,从0开始
sid:session sid
CNODE:node id
sess_srno:session serial#
ospid:os process id(v$process spid)
state:node状态
adjlist:blocker node
predecessor:waiter node


nodestat:又有几种状态
in_hang:表示该node处于dead lock,一般还是其它node(blocker)也处于该状态
leaf/leaf_nw:此node一般是 BLOCKER,用predecessor可判断是否是blocker,leaf说明 该node没有等待资源,leaf_nw可能是没有等待其它资源或者在使用CPU
NLEAF:一般是被堵塞资源,一般表示db发生性能问题,而不是hang
IGN/IGN-PMP:这类会话通常被认为是空闲会话,除非其adjlist列里存在node。如果是非空闲会话则说明其adjlist里的node正在等待其他node释放资源。
SINGLE_NODE/SINGLE_NODE_NW:近似于空闲会话
以上解释参考 ITPUB文档 使用hanganaly 杨洪志

([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[141]/0/142/50/0x3432b2dc/23712/SINGLE_NODE_NW/1/2//none
[142]/0/143/1/0x3432c5a4/21232/SINGLE_NODE/3/4//none
[143]/0/144/4/0x3432d86c/22208/SINGLE_NODE/5/6//none
[145]/0/146/23/0x3432fdfc/23324/NLEAF/7/10/[158]/none
[148]/0/149/1/0x34333654/18920/SINGLE_NODE/11/12//none
[154]/0/155/1/0x3433a704/20880/IGN/13/14//none
[155]/0/156/1/0x3433b9cc/20864/IGN/15/16//none
[156]/0/157/122/0x3433cc94/23964/SINGLE_NODE/17/18//none
[158]/0/159/1/0x3433f224/21212/LEAF/8/9//145
[159]/0/160/1/0x343404ec/19360/IGN/19/20//none
分析上面:

[145]/0/146/23/0x3432fdfc/23324/NLEAF/7/10/[158]/none
SID 146,SERIAL#,23,NODESTAT:nleaf(被堵塞资源),ADJLIST:158(blocker 158(NODE)),PERDECESSOR:none

[158]/0/159/1/0x3433f224/21212/LEAF/8/9//145
SID 159,SERIAL#1,NODESTAT:LEAF(BLOCKEER),ADJLIST:NULL,PERDECESSOR:被阻塞资源 145NODE
可以看到NODE 158 堵塞了NODE 145

 

另外两种设置方式
alter session set events 'immediate trace name  hanganalyze level n';
ORADEBUG hanganalyze

 

 

使用oradebug

SQL> oradebug  help 查看帮助
HELP           [command]                 Describe one or all commands
SETMYPID                                 Debug current process
SETOSPID                          Set OS pid of process to debug
SETORAPID      ['force']        Set Oracle pid of process to debug
SHORT_STACK                              Dump abridged OS stack
DUMP           [addr]  Invoke named dump
DUMPSGA        [bytes]                   Dump fixed SGA
DUMPLIST                                 Print a list of available dumps
EVENT                              Set trace event in process
SESSION_EVENT                      Set trace event in session
DUMPVAR        [level]  Print/dump a fixed PGA/SGA/UGA variable
DUMPTYPE      

  Print/dump an address with type info
SETVAR           Modify a fixed PGA/SGA/UGA variable
PEEK           [level]      Print/Dump memory
POKE                 Modify memory
WAKEUP                           Wake up Oracle process
SUSPEND                                  Suspend execution
RESUME                                   Resume execution
FLUSH                                    Flush pending writes to trace file
CLOSE_TRACE                              Close trace file
TRACEFILE_NAME                           Get name of trace file
LKDEBUG                                  Invoke global enqueue service debugger
NSDBX                                    Invoke CGS name-service debugger
-G                Parallel oradebug command prefix
-R                Parallel oradebug prefix (return output
SETINST              Set instance list in double quotes
SGATOFILE               Dump SGA to file; dirname in double quotes
DMPCOWSGA      Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA               Map SGA as COW; dirname in double quotes
HANGANALYZE    [level] [syslevel]        Analyze system hang
FFBEGIN                                  Flash Freeze the Instance
FFDEREGISTER                             FF deregister instance from cluster
FFTERMINST                               Call exit and terminate instance
FFRESUMEINST                             Resume the flash frozen instance
FFSTATUS                                 Flash freeze status of instance
SKDSTTPCS                Helps translate PCs to names
WATCH         
  Watch a region of memory
DELETE         watchpoint     Delete a watchpoint
SHOW           watchpoints        Show  watchpoints
CORE                                     Dump core without crashing process
UNLIMIT                                  Unlimit the size of the trace file
PROCSTAT                                 Dump process statistics
CALL           [arg1] ... [argn]  Invoke function with arguments


SETMYPID command
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process

Once a process has been selected, this will be used as the ORADEBUG process until another process is selected

The SETMYPID command selects the current process as the ORADEBUG process

For example

  ORADEBUG SETMYPID
ORADEBUG SETMYPID can be used to select the current process to run systemwide commands such as dumps

Do not use ORADEBUG SETMYPID if you intend to use the ORADEBUG SUSPEND command

SETORAPID command
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process

Once a process has been selected, this will be used as the ORADEBUG process until another process is selected

The SETORAPID command selects another process using the Oracle PID as the ORADEBUG process

The syntax is

  ORADEBUG SETORAPID pid
where pid is the Oracle process ID of the target process For example
  ORADEBUG SETORAPID 9
The Oracle process id for a process can be found in V$PROCESS.PID

To obtain the Oracle process ID for a foreground process use

  SELECT pid FROM v$process
  WHERE addr =
  (
    SELECT paddr FROM v$session
    WHERE sid = DBMS_SUPPORT.MYSID
  );
Alternatively, if the DBMS_SUPPORT package is not available use
  SELECT pid FROM v$process
  WHERE addr =
  (
    SELECT paddr FROM v$session
    WHERE sid =
    (
      SELECT sid FROM v$mystat WHERE ROWNUM = 1
    )
  );
To obtain the process ID for a background process e.g. SMON use
  SELECT pid FROM v$process
  WHERE addr =
  (
    SELECT paddr FROM v$bgprocess
    WHERE name = 'SMON'
  );
To obtain the process ID for a dispatcher process e.g. D000 use
  SELECT pid FROM v$process
  WHERE addr =
  (
    SELECT paddr FROM v$dispatcher
    WHERE name = 'D000'
  );
To obtain the process ID for a shared server process e.g. S000 use
  SELECT pid FROM v$process
  WHERE addr =
  (
    SELECT paddr FROM v$shared_server
    WHERE name = 'S000'
  );
To obtain the process ID for a job queue process e.g. job 21 use
  SELECT pid FROM v$process
  WHERE addr =
  (
    SELECT paddr FROM v$session
    WHERE sid =
    (
      SELECT sid FROM dba_jobs_running WHERE job = 21
    )
  );
To obtain the process ID for a parallel execution slave e.g. P000 use
  SELECT pid FROM v$px_process
  WHERE server_name = 'P000';
SETOSPID command
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process

Once a process has been selected, this will be used as the ORADEBUG process until another process is selected

The SETOSPID command selects the another process using the operating system PID as the ORADEBUG process

The syntax is

  ORADEBUG SETOSPID pid
where pid is the operating system process ID of the target process For example
  ORADEBUG SETOSPID 34345
The operating system process ID is the PID on Unix systems and the thread number on Windows NT/2000 systems

On Unix the PID of interest may have been identified using a top or ps command

TRACEFILE_NAME command
This command prints the name of the current trace file e.g.

    ORADEBUG TRACEFILE_NAME
For example

    /export/home/admin/SS92003/udump/ss92003_ora_14917.trc
This command does not work on Windows 2000 (Oracle 9.2)

UNLIMIT command
In Oracle 8.1.5 and below the maximum size of the trace file is restricted by default. This means that large dumps (LIBRARY_CACHE, BUFFERS) may fail.

To remove the limitation on the size of the trace file use

    ORADEBUG UNLIMIT
In Oracle 8.1.6 and above the maximum size of the trace file defaults to UNLIMITED

FLUSH command
To flush the current contents of the trace buffer to the trace file use

    ORADEBUG FLUSH
CLOSE_TRACE command
To close the current trace file use

    ORADEBUG CLOSE_TRACE
SUSPEND command
This command suspends the current process

First select a process using SETORAPID or SETOSPID

Do not use SETMYPID as the current ORADEBUG process will hang and cannot be resumed even from another ORADEBUG process

For example the command

  ORADEBUG SUSPEND
suspends the current process
The command

  ORADEBUG RESUME
resumes the current process
While the process is suspended ORADEBUG can be used to take dumps of the current process state e.g. global area, heap, subheaps etc.

This example demonstrates how to take a heap dump during a large (sorting) query

This example requires two sessions, session 1 logged on SYS AS SYSDBA and session 2 which executes the query. In session 2 identify the PID using

    SELECT pid FROM v$process
    WHERE addr IN
    (
        SELECT paddr FROM v$session
        WHERE sid = dbms_support.mysid
    );
In this example the PID was 12

In session 1 set the Oracle PID using

    ORADEBUG SETORAPID 12
In session 2 start the query

    SELECT ... FROM t1 ORDER BY ....
In session 1 suspend session 2

    ORADEBUG SUSPEND
The query in session 2 will be suspended

In session 1 run the heap dump

    ORADEBUG DUMP HEAPDUMP 1
The heapdump will show the memory structures allocated for the sort. At this point further dumps e.g. subheap dumps can be taken.

In session 1 resume session 2

    ORADEBUG RESUME
The query in session 2 will resume execution

RESUME command
This command resumes the current process

First select a process using SETORAPID or SETOSPID

Do not use SETMYPID as the current ORADEBUG process will hang and cannot be resumed even from another ORADEBUG process

For example the command

  ORADEBUG SUSPEND
suspends the current process
The command

  ORADEBUG RESUME
resumes the current process
While the process is suspended ORADEBUG can be used to take dumps of the current process state e.g. global area, heap, subheaps etc.

See SUSPEND for an example of use of the SUSPEND and RESUME commands

WAKEUP command
To wake up a process use

    ORADEBUG WAKEUP pid
For example to wake up SMON, first obtain the PID using

    SELECT pid FROM v$process
    WHERE addr =
    (
        SELECT paddr FROM v$bgprocess
        WHERE name = 'SMON'
    );
If the PID is 6 then send a wakeup call using

    ORADEBUG WAKEUP 6

DUMP command
To perform. a dump use

    ORADEBUG DUMP dumpname level
For example for a level 4 dump of the library cache use

    ORADEBUG SETMYPID
    ORADEBUG DUMP LIBRARY_CACHE 4
EVENT command
To set an event in a process use

    ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level
For example to set event 10046, level 12 in Oracle process 8 use

    ORADEBUG SETORAPID 8
    ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
SESSION_EVENT command
To set an event in a session use

    ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level
For example

    ORADEBUG SESSION_EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
DUMPSGA
To dump the fixed SGA use

    ORADEBUG DUMPSGA
DUMPVAR
To dump an SGA variable use

    ORADEBUG DUMPVAR SGA variable_name
e.g.

    ORADEBUG DUMPVAR SGA kcbnhb
which returns the number of hash buckets in the buffer cache

The names of SGA variables can be found in X$KSMFSV.KSMFSNAM. Variables in this view are suffixed with an underscore e.g.

    kcbnhb_
PEEK
To peek memory locations use

    ORADEBUG PEEK address length
where address can be decimal or hexadecimal and length is in bytes

For example

    ORADEBUG PEEK 0x20005F0C 12
returns 12 bytes starting at location 0x20005f0c

POKE
To poke memory locations use

    ORADEBUG POKE address length value
where address and value can be decimal or hexadecimal and length is in bytes

For Example

    ORADEBUG POKE 0x20005F0C 4 0x46495845
    ORADEBUG POKE 0x20005F10 4 0x44205349
    ORADEBUG POKE 0x20005F14 2 0x5A45
WARNING Do not use the POKE command on a production system

IPC
To dump information about operating system shared memory and semaphores configuration use the command

    ORADEBUG IPC
This command does not work on Windows NT or Windows 2000 (Oracle 9.2)

On Solaris, similar information can be obtained using the operating system command

    ipcs -b
Dumping the SGA
In some versions it is possible to dump the entire SGA to a file

Freeze the instance using

    ORADEBUG FFBEGIN
Dump the SGA to a file using

    ORADEBUG SGATOFILE directory
Unfreeze the instance using

    ORADEBUG FFRESUMEINST

 

基本使用
使用oradebug来设置 event
SQL> oradebug setmypid
已处理的语句
SQL> oradebug unlimit 不限制trace 大小
已处理的语句

SQL> oradebug event 10046 trace name context forever,level 12
已处理的语句
SQL> oradebug event 10046 trace name context off
已处理的语句

 


使用oradebug 挂起进程

使用OSPID(os process id,v$process中SPID)

SQL> select distinct sid from v$mystat;

       SID
----------
       146
SQL> select spid,pid from v$process a,v$session b where a.addr=b.paddr and b.sid=14(另一个SESSION)
6;

SPID                PID
------------ ----------
23324                20


SQL> oradebug setospid 23324
Oracle pid: 20, Windows thread id: 23324, image: ORACLE.EXE (SHAD)
SQL> oradebug suspend
已处理的语句

SQL> insert into t1 values(1);(SID 146 已经hang)

SQL> oradebug resume;(另一SESSION )
已处理的语句
SQL>

已创建 1 行。(SID 146)

SQL> oradebug setorapid 20 (针对 oracle process id,v$process pid)
Windows thread id: 23324, image: ORACLE.EXE (SHAD)
SQL> oradebug suspend
已处理的语句
SQL>


另外orade 还有些dump功能
SQL> oradebug dump library_cache 4;
已处理的语句

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

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426862