ASH (Active Session History)
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
Active Session History The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling.
Each session sample is a set of rows and the V$ACTIVE_SESSION_HISTORY view returns one row for each active session per sample, returning the latest session sample rows first. Because the active session samples are stored in a circular buffer in SGA, the greater the system activity, the smaller the number of seconds of session activity that can be stored in the circular buffer. This means that the duration for which a session sample appears in the V$ view, or the number of seconds of session activity that is displayed in the V$ view, is completely dependent on the database activity.
As part of the Automatic Workload Repository (AWR) snapshots, the content of V$ACTIVE_SESSION_HISTORY is also flushed to disk. Because the content of this V$ view can get quite large during heavy system activity, only a portion of the session samples is written to disk.
By capturing only active sessions, a manageable set of data is represented with the size being directly related to the work being performed rather than the number of sessions allowed on the system. Using the Active Session History enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, often avoiding the need to replay the workload to gather additional performance tracing information. Active Session History also contains execution plan information for each SQL statement that is captured. This information can be used to identify which part of the SQL execution contributed most significantly to the SQL elapsed time. The data present in ASH can be rolled up on various dimensions that it captures, including the following:
SQL identifier of SQL statement
SQL plan identifier and hash value of the SQL plan used to execute the SQL statement
SQL execution plan information
Object number, file number, and block number
Wait event identifier and parameters
Session identifier and session serial number
Module and action name
Client identifier of the session
Service hash identifier
Consumer group identifier
목차
- 1 ACTIVE SESSION HISTORY TOP 10
- 2 TOP 이벤트
- 3 Top 세션
- 4 Top 쿼리
- 5 Top 세그먼트
- 6 IO가 많이 사용하는 쿼리
- 7 Top 10 CPU 소비 (60분 동안)
- 8 Top 10 대기세션 (60분동안)
- 9 Find session detail of top sid by passing sid
- 10 Find different sql_ids of queries executed in above top session by-passing sid
- 11 Find full sqltext (CLOB) of above sql
- 12 find session wait history of above found top session
- 13 find all wait events for above top session
- 14 session statistics for above particular top session :
1 ACTIVE SESSION HISTORY TOP 10[편집]
select * from (
select
SQL_ID ,
sum(decode(session_state,'ON CPU',1,0)) as CPU,
sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
sum(decode(session_state,'ON CPU',1,1)) as TOTAL
from v$active_session_history
where SQL_ID is not NULL
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1)) desc
)
where rownum <11
2 TOP 이벤트[편집]
select event,
sum(wait_time +time_waited) ttl_wait_time
from v$active_session_history
where sample_time between sysdate - 60/2880 and sysdate
group by event
order by 2
3 Top 세션[편집]
select sesion.sid,
sesion.username,
sum(ash.wait_time + ash.time_waited)/1000000/60 ttl_wait_time_in_minutes
from v$active_session_history ash
, v$session sesion
where sample_time between sysdate - 60/2880 and sysdate
and ash.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3 desc
4 Top 쿼리[편집]
SELECT active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
SUM(active_session_history.wait_time +
active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds
FROM v$active_session_history active_session_history
, v$sqlarea sqlarea
, dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
and dba_users.username <>'SYS'
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4 DESC
5 Top 세그먼트[편집]
SELECT dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
SUM(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history
, dba_objects
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.current_obj# = dba_objects.object_id
GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event
ORDER BY 4 DESC
6 IO가 많이 사용하는 쿼리[편집]
SELECT sql_id
, COUNT(*)
FROM gv$active_session_history ash, gv$event_name evt
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'User I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQL_ID));
7 Top 10 CPU 소비 (60분 동안)[편집]
select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '60' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
8 Top 10 대기세션 (60분동안)[편집]
select * from
(
select session_id, session_serial#,count(*)
from v$active_session_history
where session_state='WAITING' and
sample_time > sysdate - interval '60' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
9 Find session detail of top sid by passing sid[편집]
select serial#,
username,
osuser,
machine,
program,
resource_consumer_group,
client_info
from v$session where sid=&sid;
10 Find different sql_ids of queries executed in above top session by-passing sid[편집]
select distinct sql_id, session_serial# from v$active_session_history
where sample_time > sysdate - interval '60' minute
and session_id=&sid
11 Find full sqltext (CLOB) of above sql[편집]
select sql_fulltext from v$sql where sql_id='&sql_id'
12 find session wait history of above found top session[편집]
select * from v$session_wait_history where sid=&sid
13 find all wait events for above top session[편집]
select event, total_waits, time_waited/100/60 time_waited_minutes,
average_wait*10 aw_ms, max_wait/100 max_wait_seconds
from v$session_event
where sid=&sid
order by 5 desc
14 session statistics for above particular top session :[편집]
select s.sid,s.username,st.name,se.value
from v$session s, v$sesstat se, v$statname st
where s.sid=se.SID and se.STATISTIC#=st.STATISTIC#
--and st.name ='CPU used by this session'
--and s.username='&USERNAME'
and s.sid='&SID'
order by s.sid,se.value desc
<comments />
SELECT sysmetric_history.sample_time,
cpu/60 AS cpu,
bcpu/60 AS bcpu,
DECODE(SIGN((cpu+bcpu)/60-cpu_ora_consumed), -1, 0, ((cpu+bcpu)/60-cpu_ora_consumed)) AS cpu_ora_wait,
scheduler/60 AS scheduler,
uio/60 AS uio,
sio/60 AS sio,
concurrency/60 AS concurrency,
application/60 AS application,
COMMIT/60 AS COMMIT,
configuration/60 AS configuration,
administrative/60 AS administrative,
network/60 AS network,
queueing/60 AS queueing,
clust/60 AS clust,
other/60 AS other
FROM
(SELECT
TRUNC(sample_time,'MI') AS sample_time,
DECODE(session_state,'ON CPU',DECODE(session_type,'BACKGROUND','BCPU','ON CPU'), wait_class) AS wait_class
FROM v$active_session_history
WHERE sample_time>sysdate-INTERVAL '1' HOUR
AND sample_time<=TRUNC(SYSDATE,'MI')) ash
PIVOT (COUNT(*) FOR wait_class IN ('ON CPU' AS cpu,'BCPU' AS bcpu,'Scheduler' AS scheduler,'User I/O' AS uio,'System I/O' AS sio,
'Concurrency' AS concurrency,'Application' AS application,'Commit' AS COMMIT,'Configuration' AS configuration,
'Administrative' AS administrative,'Network' AS network,'Queueing' AS queueing,'Cluster' AS clust,'Other' AS other)) ash,
(SELECT
TRUNC(begin_time,'MI') AS sample_time,
VALUE/100 AS cpu_ora_consumed
FROM v$sysmetric_history
WHERE GROUP_ID=2
AND metric_name='CPU Usage Per Sec') sysmetric_history
WHERE ash.sample_time (+)=sysmetric_history.sample_time
ORDER BY sample_time;