ASH (Active Session History)
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 Active Session History
- 2 ACTIVE SESSION HISTORY TOP 10
- 3 TOP 이벤트
- 4 Top 세션
- 5 Top 쿼리
- 6 Top 세그먼트
- 7 IO가 많이 사용하는 쿼리
- 8 Top 10 CPU 소비 (60분 동안)
- 9 Top 10 대기세션 (60분동안)
- 10 Find session detail of top sid by passing sid
- 11 Find different sql_ids of queries executed in above top session by-passing sid
- 12 Find full sqltext (CLOB) of above sql
- 13 find session wait history of above found top session
- 14 find all wait events for above top session
- 15 session statistics for above particular top session :
1 Active Session History[편집]
- V$ACTIVE_SESSION_HISTORY 보기는 인스턴스에서 샘플링된 세션 활동을 제공합니다.
- 활성 세션은 매초마다 샘플링되어 SGA의 순환 버퍼에 저장됩니다.
- 데이터베이스에 연결되어 있고 유휴 대기 클래스에 속하지 않는 이벤트를 기다리는 모든 세션은 활성 세션으로 간주됩니다.
- 여기에는 샘플링 시 CPU에 있었던 모든 세션이 포함됩니다.
- 각각 세션 샘플은 일련의 행이며 V$ACTIVE_SESSION_HISTORY 뷰는 샘플당 각 활성 세션에 대해 하나의 행을 반환하며 최신 세션 샘플 행을 먼저 반환합니다.
- 활성 세션 샘플은 SGA의 순환 버퍼에 저장되기 때문에 시스템 활동이 클수록 순환 버퍼에 저장할 수 있는 세션 활동의 시간(초)은 줄어듭니다.
- 즉, 세션 샘플이 V$뷰에 나타나는 기간 또는 V$ 뷰에 표시되는 세션 활동의 시간(초)은 전적으로 데이터베이스 활동에 따라 달라집니다.
- AWR(Automatic Workload Repository) 스냅샷의 일부로 V$ACTIVE_SESSION_HISTORY의 콘텐츠도 디스크로 플러시됩니다.
- 이 V$뷰의 내용은 과도한 시스템 활동 중에 상당히 커질 수 있으므로 세션 샘플의 일부만 디스크에 기록됩니다.
- 활성 세션만 캡처하면 시스템에서 허용되는 세션 수가 아니라 수행 중인 작업과 직접 관련된 크기로 관리 가능한 데이터 세트가 표시됩니다.
- 활성 세션 기록을 사용하면 V$ACTIVE_SESSION_HISTORY 뷰의 현재 데이터와 DBA_HIST_ACTIVE_SESS_HISTORY 뷰의 기록 데이터 모두에 대한 자세한 분석을 검사하고 수행할 수 있으므로 추가 성능 추적 정보를 수집하기 위해 워크로드를 재생할 필요가 없는 경우가 많습니다.
- 활성 세션 기록에는 캡처된 각 SQL 문에 대한 실행 계획 정보도 포함됩니다.
- 이 정보는 SQL 실행의 어느 부분이 SQL 경과 시간에 가장 크게 기여했는지 식별하는 데 사용할 수 있습니다.
- ASH에 있는 데이터는 다음을 포함하여 캡처하는 다양한 차원에서 롤업할 수 있습니다.
- SQL 문의 SQL 식별자 (SQL identifier of SQL statement)
- SQL 문을 실행하는 데 사용되는 SQL 계획의 SQL 계획 식별자 및 해시 값(SQL plan identifier and hash value of the SQL plan used to execute the SQL statement
)
- SQL 실행 계획 정보 (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)
2 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
3 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
4 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
5 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
6 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
7 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));
8 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;
9 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;
10 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;
11 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
12 Find full sqltext (CLOB) of above sql[편집]
select sql_fulltext from v$sql where sql_id='&sql_id'
13 find session wait history of above found top session[편집]
select * from v$session_wait_history where sid=&sid
14 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
15 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;