"ASH (Active Session History)"의 두 판 사이의 차이
DB CAFE
(→Active Session History) |
(→Active Session History) |
||
(같은 사용자의 중간 판 44개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
+ | == ASH 아카텍처 == | ||
+ | https://img1.daumcdn.net/thumb/R1280x0/?scode=mtistory2&fname=https%3A%2F%2Fblog.kakaocdn.net%2Fdn%2FwawN2%2Fbtrqd4Y3YVc%2FMAoDK9fnygTF3kLX2TLWKk%2Fimg.png | ||
− | + | # ASH는 실시간 세션(Actvie Session)의 활동 정보, SQL 수행 빈도 등의 정보를 1초 간격으로 수집 | |
− | + | # SGA -> Shared Pool -> ASH Buffer 영역에 보관해뒀다가 가득 차게 되면 AWR로 내려씁니다. | |
− | + | # ASH 정보를 디스크로 내려 쓰는 역할을 담당하는 Background Process는 MMNL | |
− | |||
− | |||
+ | == 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$뷰의 내용은 과도한 시스템 활동 중에 상당히 커질 수 있으므로 세션 샘플의 일부만 디스크에 기록 함 | ||
+ | # active 세션만 캡처하면 시스템에서 허용되는 세션 수가 아니라 수행 중인 작업과 직접 관련된 크기로 관리 가능한 데이터 세트가 표시 | ||
+ | # active 세션 기록을 사용하면 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) | ||
− | * | + | == 시간별 시스템 리소스 통계 수치 조회 == |
− | + | * v$active_session_history | |
− | + | * v$sysmetric_history | |
− | + | <source lang=sql> | |
− | + | 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; | ||
+ | </source> | ||
== ACTIVE SESSION HISTORY TOP 10 == | == ACTIVE SESSION HISTORY TOP 10 == | ||
+ | * v$active_session_history | ||
<source lang=sql> | <source lang=sql> | ||
select * from ( | select * from ( | ||
52번째 줄: | 91번째 줄: | ||
== TOP 이벤트 == | == TOP 이벤트 == | ||
<source lang=sql> | <source lang=sql> | ||
− | select event, | + | select event |
− | sum(wait_time +time_waited) ttl_wait_time | + | , sum(wait_time +time_waited) ttl_wait_time |
− | from v$active_session_history | + | from v$active_session_history |
− | where sample_time between sysdate - 60/2880 and sysdate | + | where sample_time between sysdate - 60/2880 and sysdate |
− | group by event | + | group by event |
− | order by 2 | + | order by 2 |
</source> | </source> | ||
− | == | + | |
+ | == TOP 세션 == | ||
+ | * v$active_session_history | ||
+ | * v$session | ||
<source lang=sql> | <source lang=sql> | ||
− | select sesion.sid, | + | select sesion.sid,sesion.username |
− | sesion.username, | + | , sum(ash.wait_time + ash.time_waited)/1000000/60 ttl_wait_time_in_minutes |
− | sum(ash.wait_time + ash.time_waited)/1000000/60 ttl_wait_time_in_minutes | + | from v$active_session_history ash |
− | from v$active_session_history ash | + | , v$session sesion |
− | + | where sample_time between sysdate - 60/2880 and sysdate | |
− | where sample_time between sysdate - 60/2880 and sysdate | + | and ash.session_id = sesion.sid |
− | and ash.session_id = sesion.sid | + | group by sesion.sid, sesion.username |
− | group by sesion.sid, sesion.username | + | order by 3 desc |
− | order by 3 desc | ||
</source> | </source> | ||
− | == Top | + | == Top SQL 조회 == |
+ | * v$active_session_history | ||
+ | * v$sqlarea | ||
+ | * dba_users | ||
<source lang=sql> | <source lang=sql> | ||
− | SELECT active_session_history.user_id, | + | SELECT active_session_history.user_id , dba_users.username |
− | dba_users.username, | + | , sqlarea.sql_text , SUM(active_session_history.wait_time +active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds |
− | sqlarea.sql_text, | + | FROM v$active_session_history active_session_history |
− | SUM(active_session_history.wait_time + | + | , v$sqlarea sqlarea |
− | active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds | + | , dba_users |
− | FROM v$active_session_history active_session_history | + | WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE |
− | , v$sqlarea sqlarea | + | AND active_session_history.sql_id = sqlarea.sql_id |
− | , dba_users | + | AND active_session_history.user_id = dba_users.user_id |
− | WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE | + | AND dba_users.username <>'SYS' |
− | AND active_session_history.sql_id = sqlarea.sql_id | + | GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username |
− | AND active_session_history.user_id = dba_users.user_id | + | ORDER BY 4 DESC |
− | |||
− | GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username | ||
− | ORDER BY 4 DESC | ||
</source> | </source> | ||
== Top 세그먼트 == | == Top 세그먼트 == | ||
+ | * v$active_session_history | ||
+ | * dba_objects | ||
<source lang=sql> | <source lang=sql> | ||
− | SELECT dba_objects.object_name, | + | SELECT dba_objects.object_name,dba_objects.object_type |
− | dba_objects.object_type, | + | , active_session_history.event |
− | active_session_history.event, | + | , SUM(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time |
− | SUM(active_session_history.wait_time + | + | FROM v$active_session_history active_session_history |
− | active_session_history.time_waited) ttl_wait_time | + | , dba_objects |
− | FROM v$active_session_history active_session_history | + | WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE |
− | + | AND active_session_history.current_obj# = dba_objects.object_id | |
− | WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE | + | GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event |
− | AND active_session_history.current_obj# = dba_objects.object_id | + | ORDER BY 4 DESC |
− | GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event | ||
− | ORDER BY 4 DESC | ||
</source> | </source> | ||
+ | |||
== IO가 많이 사용하는 쿼리 == | == IO가 많이 사용하는 쿼리 == | ||
+ | * gv$active_session_history | ||
+ | * gv$event_name | ||
<source lang=sql> | <source lang=sql> | ||
SELECT sql_id | SELECT sql_id | ||
, COUNT(*) | , COUNT(*) | ||
− | FROM gv$active_session_history ash | + | FROM gv$active_session_history ash |
− | + | , gv$event_name evt | |
− | WHERE ash.sample_time > SYSDATE - 1/24 | + | WHERE ash.sample_time > SYSDATE - 1/24 |
− | AND ash.session_state = 'WAITING' | + | AND ash.session_state = 'WAITING' |
− | AND ash.event_id = evt.event_id | + | AND ash.event_id = evt.event_id |
− | AND evt.wait_class = 'User I/O' | + | AND evt.wait_class = 'User I/O' |
− | GROUP BY sql_id | + | GROUP BY sql_id |
− | ORDER BY COUNT(*) DESC; | + | ORDER BY COUNT(*) DESC; |
− | SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQL_ID)); | + | SELECT * |
+ | FROM TABLE(dbms_xplan.display_cursor('&SQL_ID)); | ||
</source> | </source> | ||
== Top 10 CPU 소비 (60분 동안)== | == Top 10 CPU 소비 (60분 동안)== | ||
+ | * v$active_session_history | ||
<source lang=sql> | <source lang=sql> | ||
select * from | select * from | ||
133번째 줄: | 179번째 줄: | ||
where rownum <= 10; | where rownum <= 10; | ||
</source> | </source> | ||
+ | |||
== Top 10 대기세션 (60분동안) == | == Top 10 대기세션 (60분동안) == | ||
+ | * v$active_session_history | ||
<source lang=sql> | <source lang=sql> | ||
select * from | select * from | ||
( | ( | ||
select session_id, session_serial#,count(*) | select session_id, session_serial#,count(*) | ||
− | from v$active_session_history | + | from v$active_session_history |
− | where session_state='WAITING' and | + | where session_state='WAITING' |
− | + | and sample_time > sysdate - interval '60' minute | |
− | group by session_id, session_serial# | + | group by session_id, session_serial# |
− | order by count(*) desc | + | order by count(*) desc |
) | ) | ||
where rownum <= 10; | where rownum <= 10; | ||
</source> | </source> | ||
− | == | + | |
+ | == SQL sid로 세션 정보조회 == | ||
<source lang=sql> | <source lang=sql> | ||
− | select | + | select serial# , username , osuser |
− | + | , machine , program , resource_consumer_group | |
− | + | , client_info | |
− | |||
− | |||
− | |||
− | |||
from v$session where sid=&sid; | from v$session where sid=&sid; | ||
</source> | </source> | ||
− | == | + | == (60분간) 세션id별 다르게 실행된 SQL 조회 == |
<source lang=sql> | <source lang=sql> | ||
− | select distinct sql_id, session_serial# from v$active_session_history | + | select distinct sql_id, session_serial# |
− | where sample_time > sysdate - interval '60' minute | + | from v$active_session_history |
− | and session_id=&sid | + | where sample_time > sysdate - interval '60' minute |
+ | and session_id=&sid | ||
</source> | </source> | ||
− | == | + | |
+ | == SQL 전체 내용 조회, sqltext(CLOB) == | ||
<source lang=sql> | <source lang=sql> | ||
− | select sql_fulltext from v$sql where sql_id='&sql_id' | + | select sql_fulltext |
+ | from v$sql | ||
+ | where sql_id='&sql_id' | ||
</source> | </source> | ||
− | == | + | |
+ | == sql의 SID로 세션 대기 이력(session wait history) 검색 == | ||
+ | * v$session_wait_history | ||
<source lang=sql> | <source lang=sql> | ||
− | select * from v$session_wait_history where sid=&sid | + | select * |
+ | from v$session_wait_history | ||
+ | where sid=&sid | ||
</source> | </source> | ||
+ | |||
== find all wait events for above top session == | == find all wait events for above top session == | ||
+ | * v$session_event | ||
<source lang=sql> | <source lang=sql> | ||
− | select event, total_waits, time_waited/100/60 time_waited_minutes, | + | select event |
− | + | , total_waits | |
− | from v$session_event | + | , time_waited/100/60 time_waited_minutes |
− | where sid=&sid | + | , average_wait*10 aw_ms |
− | order by 5 desc | + | , max_wait/100 max_wait_seconds |
+ | from v$session_event | ||
+ | where sid=&sid | ||
+ | order by 5 desc | ||
</source> | </source> | ||
− | == | + | |
+ | == 유저,SID의 세션 항목별(CPU,..,) 통계 == | ||
+ | * v$session | ||
+ | * v$sesstat | ||
+ | * v$statname | ||
<source lang=sql> | <source lang=sql> | ||
− | select s.sid,s.username,st.name,se.value | + | select s.sid,s.username |
− | from v$session s, v$sesstat se, v$statname st | + | , st.name |
− | where s.sid=se.SID and se.STATISTIC#=st.STATISTIC# | + | , 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 st.name ='CPU used by this session' | ||
--and s.username='&USERNAME' | --and s.username='&USERNAME' | ||
− | and s.sid='&SID' | + | and s.sid='&SID' |
− | order by s.sid,se.value desc | + | order by s.sid,se.value desc |
</source> | </source> | ||
− | + | {{:Ash size 변경 ASH SIZE}} | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
[[category:oracle]] | [[category:oracle]] |
2024년 9월 13일 (금) 17:06 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 ASH 아카텍처
- 2 Active Session History
- 3 시간별 시스템 리소스 통계 수치 조회
- 4 ACTIVE SESSION HISTORY TOP 10
- 5 TOP 이벤트
- 6 TOP 세션
- 7 Top SQL 조회
- 8 Top 세그먼트
- 9 IO가 많이 사용하는 쿼리
- 10 Top 10 CPU 소비 (60분 동안)
- 11 Top 10 대기세션 (60분동안)
- 12 SQL sid로 세션 정보조회
- 13 (60분간) 세션id별 다르게 실행된 SQL 조회
- 14 SQL 전체 내용 조회, sqltext(CLOB)
- 15 sql의 SID로 세션 대기 이력(session wait history) 검색
- 16 find all wait events for above top session
- 17 유저,SID의 세션 항목별(CPU,..,) 통계
- 18 ash size 변경
1 ASH 아카텍처[편집]
- ASH는 실시간 세션(Actvie Session)의 활동 정보, SQL 수행 빈도 등의 정보를 1초 간격으로 수집
- SGA -> Shared Pool -> ASH Buffer 영역에 보관해뒀다가 가득 차게 되면 AWR로 내려씁니다.
- ASH 정보를 디스크로 내려 쓰는 역할을 담당하는 Background Process는 MMNL
2 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$뷰의 내용은 과도한 시스템 활동 중에 상당히 커질 수 있으므로 세션 샘플의 일부만 디스크에 기록 함
- active 세션만 캡처하면 시스템에서 허용되는 세션 수가 아니라 수행 중인 작업과 직접 관련된 크기로 관리 가능한 데이터 세트가 표시
- active 세션 기록을 사용하면 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)
3 시간별 시스템 리소스 통계 수치 조회[편집]
- 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;
4 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
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 세션[편집]
- 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
7 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
8 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
9 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));
10 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;
11 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;
12 SQL sid로 세션 정보조회[편집]
select serial# , username , osuser
, machine , program , resource_consumer_group
, client_info
from v$session where sid=&sid;
13 (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
14 SQL 전체 내용 조회, sqltext(CLOB)[편집]
select sql_fulltext
from v$sql
where sql_id='&sql_id'
15 sql의 SID로 세션 대기 이력(session wait history) 검색[편집]
- v$session_wait_history
select *
from v$session_wait_history
where sid=&sid
16 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
17 유저,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
18 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;
18.1 발생원인[편집]
- Active session 증가에 따른 ASH 버퍼 부족
- 시스템의 일부 활동으로 인해 더 많은 Active session이 발생하여 ASH 버퍼를 평소보다 빠르게 채우면 alert log에 위 메시지가 표시됨
- 메세지 그 자체는 문제가 아니며 데이터베이스에서 최대 활동을 지원하기 위해 버퍼를 늘려야 할 수도 있음
18.2 버전별 최대 사이즈[편집]
- 11g 이하에서 "_ash_size"의 설정 가능한 최대 크기는 254MB(즉, 2MB 크기의 127개 청크)
- 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 메세지가 발생함