"ASH"의 두 판 사이의 차이

1 ASH 아카텍처[편집]

2 개요[편집]

1. ASH는 실시간 세션(Actvie Session)의 활동 정보, SQL 수행 빈도 등의 정보를 1초 간격으로 수집
2. SGA -> Shared Pool -> ASH Buffer 영역에 보관해뒀다가 가득 차게 되면 AWR로 내려씁니다.
3. ASH 정보를 디스크로 내려 쓰는 역할을 담당하는 Background Process는 MMNL

3 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)

4 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

5 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

6 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

7 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

8 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

9 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));

10 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;

11 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;

12 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;

13 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

14 Find full sqltext (CLOB) of above sql[편집]

select sql_fulltext from v$sql where sql_id='&sql_id'

15 find session wait history of above found top session[편집]

select * from v$session_wait_history where sid=&sid

16 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

17 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

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;