ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle ASH和Session Tracing(ZT)

Oracle ASH和Session Tracing(ZT)

原创 Linux操作系统 作者:season0891 时间:2011-02-14 16:18:14 0 删除 编辑

1.認識V$ACTIVE_SESSION_HISTORY视图

 

   从Oracle10g开始引入了V$ACTIVE_SESSION_HISTORY视图,用于查询用户活动会话的历史信息。

 

1.1.相關參數設置

 

ASH缺省每一秒收集一下活动会话的情况,间隔时间由_ash_sampling_interval 参数确定。

V$ACTIVE_SESSION_HISTORY中的数据在被新数据周期性地覆盖前保留30 分钟,当数据从这个动态性能视图中清除时,这些数据被送到活动工作负载信息库(Active Workload Repository,AWR)中,它是一个基于磁盘的信息库。被清除的ASH(活动会话历史)数据可以在 DBA_HIST_ACTIVE_SESSION_HIST视图中看到,能够看到过去的会话的等待事件,在默认状态下,AWR中的数据7天后即被清除。

 

Sql代码
  1. SQL> show parameter statistics;  
  2.   
  3. NAME TYPE VALUE  
  4. ------------------------------------ ----------- ------------------------------  
  5. optimizer_use_pending_statistics boolean FALSE  
  6. statistics_level string TYPICAL  
  7. timed_os_statistics integer 0  
  8. timed_statistics boolean TRUE  

 

1.2.包含的內容

 

V$ACTIVE_SESSION_HISTORY包含top wait events, top SQL, top SQL command types,top sessions等等对于诊断故障非常有用的信息。

 

Sql代码
  1. SQL> desc v$active_session_history;  
  2. Name Null? Type  
  3. ----------------------------------------- -------- ----------------------------  
  4. SAMPLE_ID NUMBER  
  5. SAMPLE_TIME TIMESTAMP(3) --统计数字采集的时间  
  6. SESSION_ID NUMBER  
  7. SESSION_SERIAL# NUMBER  
  8. SESSION_TYPE VARCHAR2(10)  
  9. FLAGS NUMBER  
  10. USER_ID NUMBER  
  11. SQL_ID VARCHAR2(13)  
  12. SQL_CHILD_NUMBER NUMBER  
  13. SQL_OPCODE NUMBER  
  14. FORCE_MATCHING_SIGNATURE NUMBER  
  15. TOP_LEVEL_SQL_ID VARCHAR2(13)  
  16. TOP_LEVEL_SQL_OPCODE NUMBER  
  17. SQL_PLAN_HASH_VALUE NUMBER  
  18. SQL_PLAN_LINE_ID NUMBER  
  19. SQL_PLAN_OPERATION VARCHAR2(30)  
  20. SQL_PLAN_OPTIONS VARCHAR2(30)  
  21. SQL_EXEC_ID NUMBER  
  22. SQL_EXEC_START DATE  
  23. PLSQL_ENTRY_OBJECT_ID NUMBER  
  24. PLSQL_ENTRY_SUBPROGRAM_ID NUMBER  
  25. PLSQL_OBJECT_ID NUMBER  
  26. PLSQL_SUBPROGRAM_ID NUMBER  
  27. QC_INSTANCE_ID NUMBER  
  28. QC_SESSION_ID NUMBER  
  29. QC_SESSION_SERIAL# NUMBER  
  30. EVENT VARCHAR2(64)  
  31. EVENT_ID NUMBER  
  32. EVENT# NUMBER  
  33. SEQ# NUMBER  
  34. P1TEXT VARCHAR2(64)  
  35. P1 NUMBER  
  36. P2TEXT VARCHAR2(64)  
  37. P2 NUMBER  
  38. P3TEXT VARCHAR2(64)  
  39. P3 NUMBER  
  40. WAIT_CLASS VARCHAR2(64)  
  41. WAIT_CLASS_ID NUMBER  
  42. WAIT_TIME NUMBER  
  43. SESSION_STATE VARCHAR2(7)  
  44. TIME_WAITED NUMBER  
  45. BLOCKING_SESSION_STATUS VARCHAR2(11)  
  46. BLOCKING_SESSION NUMBER  
  47. BLOCKING_SESSION_SERIAL# NUMBER  
  48. CURRENT_OBJ# NUMBER  
  49. CURRENT_FILE# NUMBER  
  50. CURRENT_BLOCK# NUMBER  
  51. CURRENT_ROW# NUMBER  
  52. CONSUMER_GROUP_ID NUMBER  
  53. XID RAW(8)  
  54. REMOTE_INSTANCE# NUMBER  
  55. IN_CONNECTION_MGMT VARCHAR2(1)  
  56. IN_PARSE VARCHAR2(1)  
  57. IN_HARD_PARSE VARCHAR2(1)  
  58. IN_SQL_EXECUTION VARCHAR2(1)  
  59. IN_PLSQL_EXECUTION VARCHAR2(1)  
  60. IN_PLSQL_RPC VARCHAR2(1)  
  61. IN_PLSQL_COMPILATION VARCHAR2(1)  
  62. IN_JAVA_EXECUTION VARCHAR2(1)  
  63. IN_BIND VARCHAR2(1)  
  64. IN_CURSOR_CLOSE VARCHAR2(1)  
  65. SERVICE_HASH NUMBER  
  66. PROGRAM VARCHAR2(48)  
  67. MODULE VARCHAR2(48)  
  68. ACTION VARCHAR2(32)  
  69. CLIENT_ID VARCHAR2(64)  

 

1.3.生成ASH报表

 

V$ACTIVE_SESSION_HISTORY是生成ASH报表的来源,可以通过OEM来生成report,也可以通过Oracle新提供的一个脚本来完成这个工作,这个脚本是:$ORACLE_HOME/rdbms/admin/ashrpt.sql

 

1.4.查詢用戶在最近1小時內等待了多长时间

 

Sql代码
  1. SELECT s.sid,  
  2.        s.username,  
  3.        SUM(h.wait_time + h.time_waited) "total wait time"   
  4.   FROM v$active_session_history h, v$session s, v$event_name e  
  5.  WHERE h.sample_time BETWEEN sysdate - 1 / 24 AND sysdate  
  6.    AND h.session_id = s.sid  
  7.  group by s.sid, s.username;  

 

1.5.查詢用戶在最近1小時內执行SQL等待了多长时间

 

Sql代码
  1. SELECT h.user_id,  
  2.        u.username,  
  3.        sql.sql_text,  
  4.        SUM(h.wait_time + h.time_waited) "total wait time"   
  5.   FROM v$active_session_history h,  
  6.        v$sqlarea                sql,  
  7.        dba_users                u,  
  8.        v$event_name             e  
  9.  WHERE h.sample_time BETWEEN sysdate - 1 / 24 AND sysdate  
  10.    AND h.sql_id = sql.sql_id  
  11.    AND h.user_id = u.user_id  
  12.  group by h.user_id, u.username, sql.sql_text  

 

1.6.查詢在最近1小時內引起最多等待时间的資料庫物件

 

Sql代码
  1. SELECT o.owner,  
  2.        o.object_name,  
  3.        o.object_type,  
  4.        SUM(h.wait_time + h.time_waited) "total wait time"   
  5.   FROM v$active_session_history h, dba_objects o, v$event_name e  
  6.  WHERE h.sample_time BETWEEN sysdate - 1 / 24 AND sysdate  
  7.    AND h.current_obj# = o.object_id  
  8.    AND e.event_id = h.event_id  
  9.  group by o.owner, o.object_name, o.object_type  

 

2.Oracle Session Tracing

 

2.1.建立用户登陆触发器设置客户端标识符

 

Sql代码
  1. CREATE OR REPLACE TRIGGER LOGON_TRIGGER  
  2. AFTER LOGON ON DATABASE  
  3. DECLARE  
  4.   v_user_identifier varchar2(64);  
  5. BEGIN  
  6.   SELECT SYS_CONTEXT('USERENV''OS_USER') || ':' ||  
  7.          SYS_CONTEXT('USERENV''IP_ADDRESS')  
  8.     INTO v_user_identifier  
  9.     FROM dual;  
  10.   DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);  
  11. END;  
  12. /  

 

2.2.查詢會話

 

Sql代码
  1. SELECT SID, CLIENT_IDENTIFIER, SERVICE_NAME, ACTION, MODULE FROM V$SESSION;  
  2.   
  3. SID CLIENT_IDENTIFIER        SERVICE_NAME ACTION          MODULE  
  4. --- ---------------------------    ------------ ---------------- --------------------  
  5. 150 Administrator:172.18.17.181    orcl                      PlSqlDev.exe  
  6. 143 Administrator:172.18.17.181    orcl         SQL Window - New PL/SQL Developer  
  7. 142 Administrator:172.18.17.181    orcl         Main session     PL/SQL Developer  
 

2.3.事件的等待時間

 

Sql代码
  1. select session_id,  
  2.        client_id,  
  3.        event,  
  4.        sum(wait_time + time_waited) ttl_wait_time  
  5.   from v$active_session_history active_session_history  
  6.  where sample_time between sysdate - 60 / 2880 and sysdate  
  7.  group by session_id, client_id, event  
  8.  order by 2;  
  9.   
  10. SESSION_ID CLIENT_ID                    EVENT                    TTL_WAIT_TIME  
  11. ---------- --------------------------- ----------------------- -------------  
  12. 150       Administrator:172.18.17.181                       36493  
  13. 150       Administrator:172.18.17.181 db file sequential read 8632  
  14. 142       Administrator:172.18.17.181                       7372  

 

2.4.總的等待時間

 

Sql代码
  1. select client_id, event, sum(wait_time + time_waited) ttl_wait_time  
  2.   from v$active_session_history active_session_history  
  3.  where sample_time between sysdate - 60 / 2880 and sysdate  
  4.  group by client_id, event  
  5.  order by 1;  
  6.   
  7. CLIENT_ID                EVENT                TTL_WAIT_TIME  
  8. --------------------------- ----------------------- -------------  
  9. Administrator:172.18.17.181 db file sequential read 8632  
  10. Administrator:172.18.17.181                    3865  
 

2.5.查看客户端标识符

 

Sql代码
  1. SQL> SELECT SYS_CONTEXT('USERENV''CLIENT_IDENTIFIER') CLIENT_IDENTIFIER  
  2.   2    FROM DUAL;  
  3.   
  4. CLIENT_IDENTIFIER  
  5. -----------------------------  
  6. Administrator:172.18.17.181   

 

2.6.清除客户端标识符

 

Sql代码
  1. DBMS_SESSION.CLEAR_IDENTIFIER  
 

 come from:http://wuhuizhong.**.com/blog/216933

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

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

注册时间:2008-06-10

  • 博文量
    791
  • 访问量
    1946862