【效能調整】等待事件(二)

具體分析等待事件之前先看看比較有用的方法或者工具：

 

Trace event 10046

 

在很多時候，最好的診斷效能問題的方法是記錄wait event到trace文件，然後針對性的分析。

使用trace event 10046可以提供不同級別的很多追蹤系統。

Level 0:禁止trace，等價於sql_trace=false

Level 1:等價於標準的sql_trace=true

Level 4:在1的基礎上增加了繫結變數的值

Level 8:在1的基礎上增加了等待事件的資訊

Level 12L在1的基礎上增加了4和8的資訊。

分析等待事件，10046用處比較多。使用方法如下:

1，在session級別對當前會話進行trace並獲得trace file

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

 

會話已更改。

 

SQL> select * from dual;

 

D

-

X

 

SQL> alter session set events ='10046 trace name context off';

 

會話已更改。

 

SQL> create view v_get_trc_name as

  2  SELECT d.VALUE || (case

  3           when (select count(*) from v$version where banner like '%Windows%') > 0 then

  4            '\'

  5           else

  6            '/'

  7         end) || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' || p.spid ||

  8         '.trc' trace_file_name

  9    FROM (SELECT p.spid

 10            FROM v$mystat m, v$session s, v$process p

 11           WHERE m.statistic# = 1

 12             AND s.SID = m.SID

 13             AND p.addr = s.paddr) p,

 14         (SELECT t.INSTANCE

 15            FROM v$thread t, v$parameter v

 16           WHERE v.NAME = 'thread'

 17             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,

 18         (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;

 

檢視已建立。

 

SQL>

SQL> select * from v_get_trc_name;

 

TRACE_FILE_NAME

------------------------------------------------------------------

E:\ORACLE\PRODUCT\10.2.0\ADMIN\TEST\UDUMP\test_ora_2472.trc

 

SQL>

 

2，使用dbms包來完成

安裝dbmssupp.sql,使用dbms_supprt來完成

SQL> @ ?/rdbms/admin/dbmssupp.sql

 

程式包已建立。

 

 

程式包體已建立。

 

SQL> exec dbms_support.start_trace;

 

PL/SQL 過程已成功完成。

 

SQL> select * from dual union select * from dual;

 

D

-

X

 

SQL> exec dbms_support.stop_trace;

 

PL/SQL 過程已成功完成。

 

SQL>

或者使用下面的語句來對具體的sesion進行trace：

exec dbms_support.start_trace_in_session(sid => 121,serial => 1,waits => true,binds => true);

sid和serial是session的sid和serial

waits和binds代表是否trace waits或者binds，對應alter session的level：

level 1:waits => false,binds =>false

level 4:waits => false,binds =>true

level 8:waits => true,binds => false

level 12:waits => true,binds =>true

 

3，使用dbms_system.set_env

exec dbms_system.set_ev(si => 123,se =>12 ,ev => 10046,le => 8,nm => 'name');

其中

si => 123,                      --sid

se =>12 ,                               --serial

ev => 10046,                    --event

le => 8,                                --level，當level=0是結束trace

nm => 'name'                    --name

SQL> exec dbms_system.set_ev(si => 123,se =>12 ,ev => 10046,le => 8,nm => 'name');

 

PL/SQL 過程已成功完成。

 

SQL> exec dbms_system.set_ev(si => 123,se =>12 ,ev => 10046,le => 0,nm => 'name');

 

PL/SQL 過程已成功完成。

 

SQL>

 

4，使用oradebug

先找到session在os上的pid：

select a.USERNAME, b.SPID os_pid, b.PID oracle_pid

  from v$session a, v$process b

 where a.PADDR = b.ADDR

   and a.USERNAME = upper('&name');

然後：

oradebug setopsid 121;

oradebug unlimit;

oradebug event 10046 trace name context forever,level 8;

oradebug event 10046 trace name context off;

 

示例：

SQL> select a.USERNAME, b.SPID os_pid, b.PID oracle_pid

  2    from v$session a, v$process b

  3   where a.PADDR = b.ADDR

  4     and a.SID = (select sid from v$mystat where rownum = 1);  

 

USERNAME                       OS_PID       ORACLE_PID

------------------------------ ------------ ----------

SYS                            2472                 16

 

SQL>

 

SQL> oradebug setospid 2472

已處理的語句

SQL> oradebug nolimit

ORA-00070: 命令 nolimit 無效

SQL> oradebug unlimit

已處理的語句

SQL> oradebug event 10046 trace name context forever,level 8;

已處理的語句

SQL> select /*oradebug*/ * from dual;

 

D

-

X

 

SQL> oradebug event 10046 trace name context off;

已處理的語句

SQL>

 

查詢oradebug的tracefile使用下面的語句：

SQL> oradebug setospid 3016

Oracle pid: 17, Windows thread id: 3016, image: ORACLE.EXE (S001)

SQL> oradebug tracefile_name

e:\oracle\product\10.2.0\admin\test\bdump\test_s001_3016.trc

SQL>

 

 

5，使用dbms_monitor包

exec dbms_monitor.session_trace_enable(

     session_id => 121,

     serial_num => 1,

     waits => true,

     binds => true);

-- Let the session execute SQL script. or

-- program for some amount of time

-- To turn off the tracing:

exec dbms_monitor.session_trace_disable(

     session_id => 121,

     serial_num => 1);  

和dbms_system.set_ev類似的引數定義和使用方法，不再累述。

 

上面涉及到的dbms_support,dbms_monitor,dbms_system三個包比較有用，還有一些其他方法後續再敘。

而診斷事件的alter session/system set event和oradebug也非常強大，後續結合具體的問題講述。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-674969/，如需轉載，請註明出處，否則將追究法律責任。