ASH, AUTO SESSION HISTORY ( feat. v$session, dba_hist_active_sess_history)

1. ASH는 실시간 세션(Actvie Session)의 활동 정보, SQL 수행 빈도 등의 정보를 1초 간격으로 수집(1초 이상 수행된 SQL)
2. SGA > Shared Pool > ASH Buffer영역에 보관해뒀다가 가득 차게 되면 AWR로 내려씁니다.
   • ASH buffer size (목표:1시간동안의 시스템활동을 저장할수 있도록)

    select pool,bytes from v$sgastat where name = 'ASH buffers';

3. ASH 정보를 디스크로 내려 쓰는 역할을 담당하는 Background Process는 MMNL
4. ASH 최대 보관된 시간 조회

  
select max(sample_date) - min(sample_date) as interval_time
  from v$active_session_history;

Active Session History

 

컬럼명설명

SAMPLE_ID	샘플 데이터의 고유 식별자
SAMPLE_TIME	샘플이 수집된 시간
SESSION_ID	샘플 시점의 세션 ID
SESSION_SERIAL#	샘플 시점의 세션 시리얼 번호
USER_ID	샘플 시점의 사용자 ID
SQL_ID	샘플 시점에 실행 중인 SQL의 SQL ID
SQL_PLAN_HASH_VALUE	SQL 실행 계획의 해시 값
SQL_OPNAME	샘플 시점의 SQL 연산(Operation) 이름
FORCE_MATCHING_SIGNATURE	강제 매칭 시그니처 값 (유사 SQL 식별용)
EVENT	샘플 시점의 대기 이벤트 이름
EVENT_ID	대기 이벤트의 고유 ID
WAIT_CLASS	대기 이벤트의 클래스 (e.g., User I/O, System I/O)
WAIT_TIME	대기 시간이 기록된 경우, 대기 시간 (마이크로초 단위)
TIME_WAITED	대기 시간이 기록되지 않은 경우, 샘플 시간 동안 대기한 시간
BLOCKING_SESSION	샘플 시점에서 블로킹 중인 세션 ID
BLOCKING_SESSION_STATUS	블로킹 세션의 상태 (VALID/NO HOLDER 등)
CURRENT_OBJ#	샘플 시점에서 접근 중인 객체 ID
CURRENT_FILE#	샘플 시점에서 접근 중인 데이터 파일 번호
CURRENT_BLOCK#	샘플 시점에서 접근 중인 데이터 블록 번호
CURRENT_ROW#	샘플 시점에서 접근 중인 행 번호
SESSION_STATE	세션 상태 (e.g., WAITING, ON CPU)
PROGRAM	세션이 실행 중인 프로그램 이름
MODULE	샘플 시점에서 실행 중인 모듈 이름
ACTION	샘플 시점에서 실행 중인 작업(Action) 이름
SERVICE_HASH	연결된 서비스의 해시 값
CLIENT_ID	클라이언트 식별자
QC_INSTANCE_ID	쿼리 코디네이터(Query Coordinator)의 인스턴스 ID (병렬 쿼리 시)
INST_ID	샘플이 수집된 인스턴스의 ID

PLSQL_ENTRY_OBJECT_ID	NUMBER	Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack 스택의 최상위 PL/SQL 하위 프로그램의 개체 ID. 스택에 PL/SQL 하위 프로그램이 없으면 NULL입니다.
PLSQL_ENTRY_SUBPROGRAM_ID	NUMBER	Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack 스택의 최상위 PL/SQL 서브프로그램의 서브프로그램 ID. 스택에 PL/SQL 서브프로그램이 없으면 NULL입니다.
PLSQL_OBJECT_ID	NUMBER	Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL 현재 실행 중인 PL/SQL 하위 프로그램의 개체 ID입니다. SQL을 실행하는 경우 NULL입니다
PLSQL_SUBPROGRAM_ID	NUMBER	Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL 현재 실행 중인 PL/SQL 객체의 하위 프로그램 ID입니다. SQL을 실행하는 경우 NULL입니다.

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. active 세션만 캡처하면 시스템에서 허용되는 세션 수가 아니라 수행 중인 작업과 직접 관련된 크기로 관리 가능한 데이터 세트가 표시
6. active 세션 기록을 사용하면 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)

 

시간별 시스템 리소스 통계 수치 조회

• v$active_session_history
• v$sysmetric_history

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;

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

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

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

Top SQL 조회

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

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

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

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;

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;

SQL sid로 세션 정보조회

select serial# , username , osuser  
     , machine , program , resource_consumer_group
     , client_info
from v$session where sid=&sid;

(60분간) 세션id별 다르게 실행된 SQL 조회

select distinct sql_id, session_serial# 
  from v$active_session_history
 where sample_time >  sysdate - interval '60' minute 
   and session_id=&sid

SQL 전체 내용 조회, sqltext(CLOB)

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

sql의 SID로 세션 대기 이력(session wait history) 검색

• v$session_wait_history

select * 
  from v$session_wait_history 
 where sid=&sid

find all wait events for above top session

• v$session_event

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

유저,SID의 세션 항목별(CPU,..,) 통계

• v$session
• v$sesstat
• v$statname

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

ash size 변경

• alert log 내용중

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. 
If emergency flushes are a recurring issue, you may consider increasing ASH size by 
setting the value of _ASH_SIZE to a sufficiently large value. Currently, 
ASH size is 17000944 bytes. Both ASH size and the total number of emergency flushes 
since instance startup can be monitored by running the following query:

select total_size,awr_flush_emergency_count 
  from v$ash_info;

 

assignment 해결 방법 : ASH 크기를 현재보다 최소 50% 이상 증가

• RAC의 경우 한쪽에서 작업 시 모든 노드에 적용됨

SQL> select total_size from v$ash_info;
 
TOTAL_SIZE
----------
   2097152
-- 2MB로 할당되어 있음

• 시스템에 즉시 반영됨 (리부팅 필요없음)

SQL> alter system set "_ash_size"=3145728;
 
System altered.

-- 확인 
SQL> select total_size from v$ash_info;
 
TOTAL_SIZE
----------
   3145728

 

발생원인

1. Active session 증가에 따른 ASH 버퍼 부족
2. 시스템의 일부 활동으로 인해 더 많은 Active session이 발생하여 ASH 버퍼를 평소보다 빠르게 채우면 alert log에 위 메시지가 표시됨
3. 메세지 그 자체는 문제가 아니며 데이터베이스에서 최대 활동을 지원하기 위해 버퍼를 늘려야 할 수도 있음

버전별 최대 사이즈

1. 11g 이하에서 "_ash_size"의 설정 가능한 최대 크기는 254MB(즉, 2MB 크기의 127개 청크)
2. 12c 이상에서 "_ash_size"의 설정 가능한 최대 크기는 254MB 이상

 

• "_ash_size" 를 더 높은 값을 설정할 수 있지만 내부적으로 254MB로 조정됨
• 254MB 이상의 값으로 설정하면 ORA-2097 메세지가 발생

-- 400MB로 설정 시
SQL> alter system set "_ash_size"=419430400;alter system set "_ash_size"=419430400
 
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02097: parameter cannot be modified because specified value is invalid

• ORA-2097 메세지가 발생함

 

출처 : http://dbcafe.co.kr/w/index.php/ASH_(Active_Session_History)