v$active_session_history的使用

有时想查会话的一些状态，或者实例的整体情况，ash的视图比较方便

实例近期等待事件（入门级）

col event for a40
SELECT
  h.event,
  SUM(h.wait_time + h.time_waited) "Total Wait Time"
FROM
  v$active_session_history h
WHERE
  h.sample_time BETWEEN sysdate - 1/24 AND sysdate
  and wait_class <> 'Idle'
GROUP BY event
having SUM(h.wait_time + h.time_waited)>100
ORDER BY 2 DESC;

会话的历史等待信息

set lines 120 trimspool on
col event head "Waited for" format a30
col total_waits head "Total|Waits" format 999,999
col tw_ms head "Waited|for (ms)" format 999,999.99
col aw_ms head "Average|Wait (ms)" format 999,999.99
col mw_ms head "Max|Wait (ms)" format 999,999.99
select event, total_waits, time_waited*10 tw_ms,
average_wait*10 aw_ms, max_wait*10 mw_ms
from v$session_event
where sid = &session_id;

会话所有的统计信息

col name for a55
select name, value
from v$sesstat s, v$statname n
where sid = &1
and n.statistic# = s.statistic#
and value>0
order by value desc;

99
sql对应的绑定变量

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN sql_text FORMAT A120
COLUMN sql_id FORMAT A13
COLUMN bind_name FORMAT A10
COLUMN bind_value FORMAT A26
SELECT
sql_id,
t.sql_text sql_text,
b.name bind_name,
b.value_string bind_value
FROM
v$sql t
JOIN
v$sql_bind_capture b using (sql_id)
WHERE
b.value_string is not null
AND
sql_id='&sqlid';

未完。。。