Oracle ASH和Session Tracing（ZT）

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> show parameter statistics;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE

 

1.2.包含的內容

 

V$ACTIVE_SESSION_HISTORY包含top wait events, top SQL, top SQL command types,top sessions等等對於診斷故障非常有用的資訊。

 

SQL> desc v$active_session_history;
Name Null? Type
----------------------------------------- -------- ----------------------------
SAMPLE_ID NUMBER
SAMPLE_TIME TIMESTAMP(3) --統計數字採集的時間
SESSION_ID NUMBER
SESSION_SERIAL# NUMBER
SESSION_TYPE VARCHAR2(10)
FLAGS NUMBER
USER_ID NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
SQL_OPCODE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
TOP_LEVEL_SQL_ID VARCHAR2(13)
TOP_LEVEL_SQL_OPCODE NUMBER
SQL_PLAN_HASH_VALUE NUMBER
SQL_PLAN_LINE_ID NUMBER
SQL_PLAN_OPERATION VARCHAR2(30)
SQL_PLAN_OPTIONS VARCHAR2(30)
SQL_EXEC_ID NUMBER
SQL_EXEC_START DATE
PLSQL_ENTRY_OBJECT_ID NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID NUMBER
QC_INSTANCE_ID NUMBER
QC_SESSION_ID NUMBER
QC_SESSION_SERIAL# NUMBER
EVENT VARCHAR2(64)
EVENT_ID NUMBER
EVENT# NUMBER
SEQ# NUMBER
P1TEXT VARCHAR2(64)
P1 NUMBER
P2TEXT VARCHAR2(64)
P2 NUMBER
P3TEXT VARCHAR2(64)
P3 NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_TIME NUMBER
SESSION_STATE VARCHAR2(7)
TIME_WAITED NUMBER
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_SESSION NUMBER
BLOCKING_SESSION_SERIAL# NUMBER
CURRENT_OBJ# NUMBER
CURRENT_FILE# NUMBER
CURRENT_BLOCK# NUMBER
CURRENT_ROW# NUMBER
CONSUMER_GROUP_ID NUMBER
XID RAW(8)
REMOTE_INSTANCE# NUMBER
IN_CONNECTION_MGMT VARCHAR2(1)
IN_PARSE VARCHAR2(1)
IN_HARD_PARSE VARCHAR2(1)
IN_SQL_EXECUTION VARCHAR2(1)
IN_PLSQL_EXECUTION VARCHAR2(1)
IN_PLSQL_RPC VARCHAR2(1)
IN_PLSQL_COMPILATION VARCHAR2(1)
IN_JAVA_EXECUTION VARCHAR2(1)
IN_BIND VARCHAR2(1)
IN_CURSOR_CLOSE VARCHAR2(1)
SERVICE_HASH NUMBER
PROGRAM VARCHAR2(48)
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
CLIENT_ID VARCHAR2(64)

 

1.3.生成ASH報表

 

V$ACTIVE_SESSION_HISTORY是生成ASH報表的來源，可以透過OEM來生成report,也可以透過Oracle新提供的一個指令碼來完成這個工作，這個指令碼是:$ORACLE_HOME/rdbms/admin/ashrpt.sql

 

1.4.查詢使用者在最近1小時內等待了多長時間

 

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

 

1.5.查詢使用者在最近1小時內執行SQL等待了多長時間

 

SELECT h.user_id,
       u.username,
       sql.sql_text,
       SUM(h.wait_time + h.time_waited) "total wait time" 
  FROM v$active_session_history h,
       v$sqlarea                sql,
       dba_users                u,
       v$event_name             e
 WHERE h.sample_time BETWEEN sysdate - 1 / 24 AND sysdate
   AND h.sql_id = sql.sql_id
   AND h.user_id = u.user_id
 group by h.user_id, u.username, sql.sql_text

 

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

 

SELECT o.owner,
       o.object_name,
       o.object_type,
       SUM(h.wait_time + h.time_waited) "total wait time" 
  FROM v$active_session_history h, dba_objects o, v$event_name e
 WHERE h.sample_time BETWEEN sysdate - 1 / 24 AND sysdate
   AND h.current_obj# = o.object_id
   AND e.event_id = h.event_id
 group by o.owner, o.object_name, o.object_type

 

2.Oracle Session Tracing

 

2.1.建立使用者登陸觸發器設定客戶端識別符號

 

CREATE OR REPLACE TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
  v_user_identifier varchar2(64);
BEGIN
  SELECT SYS_CONTEXT('USERENV', 'OS_USER') || ':' ||
         SYS_CONTEXT('USERENV', 'IP_ADDRESS')
    INTO v_user_identifier
    FROM dual;
  DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);
END;
/

 

2.2.查詢會話

 

SELECT SID, CLIENT_IDENTIFIER, SERVICE_NAME, ACTION, MODULE FROM V$SESSION;

SID CLIENT_IDENTIFIER        SERVICE_NAME ACTION          MODULE
--- ---------------------------    ------------ ---------------- --------------------
150 Administrator:172.18.17.181    orcl                      PlSqlDev.exe
143 Administrator:172.18.17.181    orcl         SQL Window - New PL/SQL Developer
142 Administrator:172.18.17.181    orcl         Main session     PL/SQL Developer

 

2.3.事件的等待時間

 

select session_id,
       client_id,
       event,
       sum(wait_time + time_waited) ttl_wait_time
  from v$active_session_history active_session_history
 where sample_time between sysdate - 60 / 2880 and sysdate
 group by session_id, client_id, event
 order by 2;

SESSION_ID CLIENT_ID                    EVENT                    TTL_WAIT_TIME
---------- --------------------------- ----------------------- -------------
150       Administrator:172.18.17.181                       36493
150       Administrator:172.18.17.181 db file sequential read 8632
142       Administrator:172.18.17.181                       7372

 

2.4.總的等待時間

 

select client_id, event, sum(wait_time + time_waited) ttl_wait_time
  from v$active_session_history active_session_history
 where sample_time between sysdate - 60 / 2880 and sysdate
 group by client_id, event
 order by 1;

CLIENT_ID                EVENT                TTL_WAIT_TIME
--------------------------- ----------------------- -------------
Administrator:172.18.17.181 db file sequential read 8632
Administrator:172.18.17.181                    3865

 

2.5.檢視客戶端識別符號

 

SQL> SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') CLIENT_IDENTIFIER
  2    FROM DUAL;

CLIENT_IDENTIFIER
-----------------------------
Administrator:172.18.17.181 

 

2.6.清除客戶端識別符號

 

DBMS_SESSION.CLEAR_IDENTIFIER

 

 come from：**.com/blog/216933