행위

ASH

DB CAFE

thumb_up 추천메뉴 바로가기

1 ASH 아카텍처[편집]

?scode=mtistory2&fname=https%3A%2F%2Fblog.kakaocdn.net%2Fdn%2FwawN2%2Fbtrqd4Y3YVc%2FMAoDK9fnygTF3kLX2TLWKk%2Fimg.png

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

2 Active Session History[편집]

  1. V$ACTIVE_SESSION_HISTORY 뷰는 인스턴스에서 샘플링된 세션 활동을 제공
  2. 활성 세션은 매초마다 샘플링되어 SGA의 순환 버퍼에 저장
  3. 데이터베이스에 연결되어 있고 유휴 대기 클래스에 속하지 않는 이벤트를 기다리는 모든 세션은 활성 세션으로 간주
    1. 여기에는 샘플링 시 CPU에 있었던 모든 세션 포함
    2. 각각 세션 샘플은 일련의 행이며 V$ACTIVE_SESSION_HISTORY 뷰는 샘플당 각 활성 세션에 대해 하나의 행을 반환하며 최신 세션 샘플 행을 먼저 반환
    3. 활성 세션 샘플은 SGA의 순환 버퍼에 저장되기 때문에 시스템 활동이 클수록 순환 버퍼에 저장할 수 있는 세션 활동의 시간(초)은 줄어듬.
    4. 즉, 세션 샘플이 V$뷰에 나타나는 기간 또는 V$ 뷰에 표시되는 세션 활동의 시간(초)은 전적으로 데이터베이스 활동에 따라 달라짐.
  4. AWR(Automatic Workload Repository) 스냅샷의 일부로 V$ACTIVE_SESSION_HISTORY의 콘텐츠도 디스크로 플러시 됨
    1. 이 V$뷰의 내용은 과도한 시스템 활동 중에 상당히 커질 수 있으므로 세션 샘플의 일부만 디스크에 기록 함
  5. 활성 세션만 캡처하면 시스템에서 허용되는 세션 수가 아니라 수행 중인 작업과 직접 관련된 크기로 관리 가능한 데이터 세트가 표시
  6. 활성 세션 기록을 사용하면 V$ACTIVE_SESSION_HISTORY 뷰의 현재 데이터와 DBA_HIST_ACTIVE_SESS_HISTORY 뷰의 기록 데이터 모두에 대한 자세한 분석을 검사하고 수행할 수 있으므로 추가 성능 추적 정보를 수집하기 위해 워크로드를 재생할 필요가 없는 경우가 많음
  7. 활성 세션 기록에는 캡처된 각 SQL 문에 대한 실행 계획 정보도 포함
    1. 이 정보는 SQL 실행의 어느 부분이 SQL 경과 시간에 가장 크게 기여했는지 식별하는 데 사용
  8. ASH에 있는 데이터는 다음을 포함하여 캡처하는 다양한 차원에서 롤업 가능
    1. SQL 문의 SQL 식별자 (SQL identifier of SQL statement)
    2. SQL 문을 실행하는 데 사용되는 SQL 계획의 SQL 계획 식별자 및 해시 값(SQL plan identifier and hash value of the SQL plan used to execute the SQL statement)
    3. SQL 실행 계획 정보 (SQL execution plan information)
    4. 개체 번호, 파일 번호 및 블록 번호 (Object number, file number, and block number)
    5. 대기 이벤트 식별자 및 매개변수 (Wait event identifier and parameters)
    6. 세션 식별자 및 세션 일련 번호 (Session identifier and session serial number)
    7. 모듈 및 작업 이름 (Module and action name)
    8. 세션의 클라이언트 식별자 (Client identifier of the session)
    9. 서비스 해시 식별자 (Service hash identifier)
    10. 소비자 그룹 식별자 (Consumer group identifier)

3 ACTIVE SESSION HISTORY TOP 10[편집]

  • v$active_session_history
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

4 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

5 TOP 세션[편집]

  • v$active_session_history
  • v$session
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

6 Top 쿼리[편집]

  • v$active_session_history
  • v$sqlarea sqlarea
  • dba_users
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

7 Top 세그먼트[편집]

  • v$active_session_history
  • dba_objects
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

8 IO가 많이 사용하는 쿼리[편집]

  • gv$active_session_history
  • gv$event_name
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));

9 Top 10 CPU 소비 (60분 동안)[편집]

  • v$active_session_history
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;

10 Top 10 대기세션 (60분동안)[편집]

  • v$active_session_history
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;

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

12 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

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

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

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

select * from v$session_wait_history where sid=&sid

15 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

16 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;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=ASH&oldid=9888"