tracing application tracing

tracing application tracing
关于跟踪的几种设置
实例级别跟踪
alter system set sql_trace=true;
会话级别跟踪
  跟踪自定义的会话
  (1) alter session set sql_trace=true/false;
  (2) dbms_session.set_sql_trace(true/false);
  跟踪其他会话（具备sysdba的权限）
  SQL> desc dbms_system.set_sql_trace_in_session;
 Parameter Type    Mode Default?
 --------- ------- ---- --------
 SID       NUMBER  IN    v$session.sid       
 SERIAL#   NUMBER  IN    v$session.SERIAL#         
 SQL_TRACE BOOLEAN IN    true/false
客户ID级别跟踪、模块、服务、动作级别
SQL> desc dbms_monitor
 Element                    Type     
 -------------------------- ---------
 ALL_MODULES                CONSTANT 
 ALL_ACTIONS                CONSTANT 
 CLIENT_ID_STAT_ENABLE      PROCEDURE
 CLIENT_ID_STAT_DISABLE     PROCEDURE
 SERV_MOD_ACT_STAT_ENABLE   PROCEDURE
 SERV_MOD_ACT_STAT_DISABLE  PROCEDURE
 CLIENT_ID_TRACE_ENABLE     PROCEDURE
 CLIENT_ID_TRACE_DISABLE    PROCEDURE
 SERV_MOD_ACT_TRACE_ENABLE  PROCEDURE
 SERV_MOD_ACT_TRACE_DISABLE PROCEDURE
 SESSION_TRACE_ENABLE       PROCEDURE
 SESSION_TRACE_DISABLE      PROCEDURE
 DATABASE_TRACE_ENABLE      PROCEDURE
 DATABASE_TRACE_DISABLE     PROCEDURE
(1)客户ID级别跟踪
如何标识客户端ID
SQL> desc dbms_session.set_identifier
 Parameter Type     Mode Default?
 --------- -------- ---- --------
 CLIENT_ID VARCHAR2 IN 
跟踪客户端ID
SQL> desc dbms_monitor.CLIENT_ID_TRACE_ENABLE
 Parameter Type     Mode Default?
 --------- -------- ---- --------
 CLIENT_ID VARCHAR2 IN      -- 客户端 identifier   监控者可以在v$session中获取  
 WAITS     BOOLEAN  IN   Y  --true 包含等待信息
 BINDS     BOOLEAN  IN   Y  --true 包含绑定变量信息
取消
 SQL> desc dbms_monitor.CLIENT_ID_TRACE_ENABLE
 Parameter Type     Mode Default?
 --------- -------- ---- --------
 CLIENT_ID VARCHAR2 IN           
 WAITS     BOOLEAN  IN   Y       
 BINDS     BOOLEAN  IN   Y 
收集统计信息
 SQL> desc dbms_monitor.client_id_stat_enable
 Parameter Type     Mode Default?
 --------- -------- ---- --------
 CLIENT_ID VARCHAR2 IN 
(2)模块、服务、动作级别跟踪
SQL> desc dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE
 Parameter     Type     Mode Default?
 ------------- -------- ---- --------
 SERVICE_NAME  VARCHAR2 IN      --通过向database listener注册的服务例如 实例 后台进程等     
 MODULE_NAME   VARCHAR2 IN   Y  --可以为不同的活动级别生成不同的程序模块     
 ACTION_NAME   VARCHAR2 IN   Y  --特定PL/SQL过程的动作     
 WAITS         BOOLEAN  IN   Y       
 BINDS         BOOLEAN  IN   Y       
 INSTANCE_NAME VARCHAR2 IN   Y  
(3)session级别
SQL> desc dbms_monitor.SESSION_TRACE_ENABLE
 Parameter  Type           Mode Default?
 ---------- -------------- ---- --------
 SESSION_ID BINARY_INTEGER IN   Y     
 SERIAL_NUM BINARY_INTEGER IN   Y       
 WAITS      BOOLEAN        IN   Y       
 BINDS      BOOLEAN        IN   Y
收集统计信息
SQL> desc dbms_monitor.serv_mod_act_stat_enable
 Parameter    Type     Mode Default?
 ------------ -------- ---- --------
 SERVICE_NAME VARCHAR2 IN           
 MODULE_NAME  VARCHAR2 IN           
 ACTION_NAME  VARCHAR2 IN   Y       

查看统计信息
SQL> select * from v$service_stats;
 
SERVICE_NAME_HASH SERVICE_NAME                      STAT_ID STAT_NAME            VALUE
----------------- ------------------------ ------------------------------ ----------  ------
       3427055676 SYS$USERS                      2666645286 logons cumulative     511
SQL> select * from v$serv_mod_act_stats;
 
AGGREGATION_TYPE      SERVICE_NAME    MODULE     ACTION      STAT_ID STAT_NAME        VALUE
--------------------- --------------- ---------- -------- ---------- ----------- ----------
查看启动的跟踪
v$dba_enable_traces;
使用em的顶级使用者模块 能更直观的实现上述功能。

问题二：
如果有N多的客户端或服务同时发起连接且被跟踪，那跟踪文件将会变得庞大复杂
trcsess utility的出现解决了这个问题，它依据客户端ID或服务、模块名称把跟踪日志单独抽出来
trcsess 
        [session=]
 [clientid=]
        [service=]
 [action=]
 [module=]
 [output=]
output= output destination default being standard output.
session= session to be traced.
Session id is a combination of session Index & session serial number e.g. 8.13.
clientid= clientid to be traced.
service= service to be traced.
action= action to be traced.
module= module to be traced.
Space separated list of trace files with wild card '*' supported.
问题三：
抽取出来的东东仍然让人看不懂。。
tkprof utility 跟踪日志的格式化工具
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor

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

转载于:http://blog.itpub.net/21993926/viewspace-667270/