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
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)
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;
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
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
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;
- /
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
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
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
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
SQL> SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') CLIENT_IDENTIFIER
2 FROM DUAL;
CLIENT_IDENTIFIER
-----------------------------
Administrator:172.18.17.181
2.6.清除客户端标识符
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11417069/viewspace-683831/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11417069/viewspace-683831/