最強有力的輔助診斷工具SQL

　　本文主要針對SQL_TRACE的使用方法進行了詳細的介紹在文章的最後大家可以通過一個實際案例加深對SQL_TRACE的理解

　　SQL_TRACE概述

　　SQL_TRACE是Oracle數據庫提供的用於進行SQL跟蹤的手段在某種意義上講可以說是Oracle最強有力的輔助診斷工具

　　SQL_TRACE可以作為初始化參數在全局啟用也可以通過命令行方式在具體session啟用

　　．在全局啟用

　　在參數文件(pfile/spfile)中指定:

　　sql_trace =true

　　在全局啟用SQL_TRACE會導致所有進程的活動被跟蹤包括後台進程及所有用戶進程在此情況下通常會導致比較嚴重的性能問題所以在生產環境中必須要小心使用

　　注意: 通過在全局啟用sql_trace我們可以跟蹤到所有後台進程的活動很多在文檔中的抽象說明通過跟蹤文件的實時變化我們可以清晰的看到各個進程之間的緊密協調

　　． 在當前session級設置

　　大多數的情況下我們使用sql_trace跟蹤當前進程通過跟蹤當前進程可以發現當前操作的後台數據庫遞歸活動(這在研究數據庫新特性時尤其有效)研究SQL執行發現後台錯誤等

　　在session級啟用和停止sql_trace方式如下:

　　啟用當前session的跟蹤:

　　SQL> alter session set sql_trace=true;

　　Session altered

　　此時的SQL操作將被跟蹤:

　　SQL> select count(*) from dba_users;

　　COUNT(*)
   
   

　　結束跟蹤:

　　SQL> alter session set sql_trace=false;

　　Session altered

　　跟蹤其他用戶進程

　　在很多時候我們需要跟蹤其他用戶的進程而不是當前用戶我們可以通過Oracle提供的系統包DBMS_SYSTEM SET_SQL_TRACE_IN_SESSION來完成

　　SET_SQL_TRACE_IN_SESSION程序需要提供三個參數:

　　SQL> desc dbms_system
    …
    PROCEDURE SET_SQL_TRACE_IN_SESSION
     Argument Name                Type        In/Out Default?
                
     SID                         NUMBER                IN
     SERIAL#                     NUMBER                IN
     SQL_TRACE                   BOOLEAN               IN
    …

　　通過v$session我們可以獲得sidserial#等信息

　　獲得進程信息選擇需要跟蹤的進程:

　　SQL> select sidserial#username from v$session
        where username is not null;

　　SID    SERIAL#  USERNAME
   
                      SYS
                       EYGLE

　　設置跟蹤:

　　SQL> exec dbms_systemset_sql_trace_in_session(true)

　　PL/SQL procedure successfully completed

　　…

　　可以等候片刻跟蹤session執行任務捕獲sql操作……

　　停止跟蹤:

　　SQL> exec dbms_systemset_sql_trace_in_session(false)

　　PL/SQL procedure successfully completed

　　事件概述

　　事件是Oracle提供的內部事件是對SQL_TRACE的增強

　　事件可以設置以下四個級別:

　　 啟用標准的SQL_TRACE功能等價於sql_trace

　　 Level 加上綁定值(bind values)

　　 Level + 等待事件跟蹤

　　 Level + Level + Level

　　類似sql_trace事件可以在全局設置也可以在session級設置

． 在全局設置

　　在參數文件中增加:

　　event= trace name context foreverlevel

　　此設置對所有用戶的所有進程生效包括後台進程

　　．對當前session設置

　　通過alter session的方式修改需要alter session的系統權限:

　　SQL> alter session set events trace name context forever;
    Session altered
    SQL> alter session set events trace name context forever level ;
    Session altered
    SQL> alter session set events trace name context off;
    Session altered

　　．對其他用戶session設置

　　通過DBMS_SYSTEMSET_EV系統包來實現:

　　SQL> desc dbms_system
   
    PROCEDURE SET_EV
    Argument Name          Type          In/Out Default?
   
    SI                BINARY_INTEGER          IN
    SE                BINARY_INTEGER          IN
    EV                BINARY_INTEGER          IN
    LE                BINARY_INTEGER          IN
    NM                VARCHAR                IN
   

　　其中的參數SISE來自v$session視圖

　　查詢獲得需要跟蹤的session信息:

    SQL> select sidserial#username from v$session

　　where username is not null;SID SERIAL# USERNAME
   
    SYS
    EYGLE

　　執行跟蹤:

　　SQL> exec dbms_systemset_ev(eygle);

　　PL/SQL procedure successfully completed

　　結束跟蹤:

　　SQL> exec dbms_systemset_ev(eygle);

　　PL/SQL procedure successfully completed

　　獲取跟蹤文件

　　上面生成的跟蹤文件位於user_dump_dest目錄中位置及文件名可以通過下面的SQL查詢得到:

　　SQL> select      dvalue||/||lower(rtrim(iinstance
    chr()))||_ora_||pspid||trc trace_file_name 
    from      ( select pspid        from sysv$mystat m
    sysv$session ssysv$process p 
    where mstatistic# = and ssid = msid and paddr = spaddr) p
        ( select tinstance from sysv$thread  tsysv$parameter
    v        where vname = thread and
    (vvalue = or tthread# = to_number(vvalue))) i 
    ( select value from sysv$parameter
    where name = user_dump_dest) d   /

　　TRACE_FILE_NAME
   
    /opt/oracle/admin/hsjf/udump/hsjf_ora_trc

　　讀取當前session所設置的參數

　　假如我們通過alter session的方式設置了sql_trace此設置在正常情況下是不能通過show parameter方式獲取的需要我們通過dbms_systemread_ev來獲取

　　SQL> set feedback offSQL> set serveroutput on SQL> declare
    event_level number;
    begin
    for event_number in loop
    sysdbms_systemread_ev(event_number event_level);
    if (event_level > ) then
    sysdbms_outputput_line(
    Event ||
    to_char(event_number) ||
    is set at level ||
    to_char(event_level)
    );
    end if;
    end loop;
    end;
    /
    Event is set at level

From:http://tw.wingwit.com/Article/program/Oracle/201311/17241.html