Oracle ASH和Session Tracing
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 FALSEstatistics_level string TYPICALtimed_os_statistics integer 0timed_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 NUMBERSAMPLE_TIME TIMESTAMP(3) --统计数字采集的时间SESSION_ID NUMBERSESSION_SERIAL# NUMBERSESSION_TYPE VARCHAR2(10)FLAGS NUMBERUSER_ID NUMBERSQL_ID VARCHAR2(13)SQL_CHILD_NUMBER NUMBERSQL_OPCODE NUMBERFORCE_MATCHING_SIGNATURE NUMBERTOP_LEVEL_SQL_ID VARCHAR2(13)TOP_LEVEL_SQL_OPCODE NUMBERSQL_PLAN_HASH_VALUE NUMBERSQL_PLAN_LINE_ID NUMBERSQL_PLAN_OPERATION VARCHAR2(30)SQL_PLAN_OPTIONS VARCHAR2(30)SQL_EXEC_ID NUMBERSQL_EXEC_START DATEPLSQL_ENTRY_OBJECT_ID NUMBERPLSQL_ENTRY_SUBPROGRAM_ID NUMBERPLSQL_OBJECT_ID NUMBERPLSQL_SUBPROGRAM_ID NUMBERQC_INSTANCE_ID NUMBERQC_SESSION_ID NUMBERQC_SESSION_SERIAL# NUMBEREVENT VARCHAR2(64)EVENT_ID NUMBEREVENT# NUMBERSEQ# NUMBERP1TEXT VARCHAR2(64)P1 NUMBERP2TEXT VARCHAR2(64)P2 NUMBERP3TEXT VARCHAR2(64)P3 NUMBERWAIT_CLASS VARCHAR2(64)WAIT_CLASS_ID NUMBERWAIT_TIME NUMBERSESSION_STATE VARCHAR2(7)TIME_WAITED NUMBERBLOCKING_SESSION_STATUS VARCHAR2(11)BLOCKING_SESSION NUMBERBLOCKING_SESSION_SERIAL# NUMBERCURRENT_OBJ# NUMBERCURRENT_FILE# NUMBERCURRENT_BLOCK# NUMBERCURRENT_ROW# NUMBERCONSUMER_GROUP_ID NUMBERXID RAW(8)REMOTE_INSTANCE# NUMBERIN_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 NUMBERPROGRAM 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_TRIGGERAFTER LOGON ON DATABASEDECLAREv_user_identifier varchar2(64);BEGINSELECT 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.exe143 Administrator:172.18.17.181 orcl SQL Window - New PL/SQL Developer142 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_timefrom 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 36493150 Administrator:172.18.17.181 db file sequential read 8632142 Administrator:172.18.17.181 7372
2.4.總的等待時間
select client_id, event, sum(wait_time + time_waited) ttl_wait_timefrom 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 8632Administrator:172.18.17.181 3865 2.5.查看客户端标识符
SQL> SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') CLIENT_IDENTIFIER2 FROM DUAL;CLIENT_IDENTIFIER-----------------------------Administrator:172.18.17.181
2.6.清除客户端标识符
DBMS_SESSION.CLEAR_IDENTIFIER
页:
[1]