"ASH (Active Session History)"의 두 판 사이의 차이
DB CAFE
잔글 |
|||
15번째 줄: | 15번째 줄: | ||
where rownum <11 | where rownum <11 | ||
</source> | </source> | ||
+ | |||
+ | == TOP 이벤트 == | ||
+ | <source lang=sql> | ||
+ | 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 | ||
+ | </source> | ||
+ | == Top 세션 == | ||
+ | <source lang=sql> | ||
+ | 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 | ||
+ | </source> | ||
+ | == Top 쿼리 == | ||
+ | <source lang=sql> | ||
+ | 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 | ||
+ | </source> | ||
+ | == Top 세그먼트 == | ||
+ | <source lang=sql> | ||
+ | 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 | ||
+ | </source> | ||
+ | == IO가 많이 사용하는 쿼리 == | ||
+ | <source lang=sql> | ||
+ | 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)); | ||
+ | </source> | ||
+ | == Top 10 CPU 소비 (60분 동안)== | ||
+ | <source lang=sql> | ||
+ | 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; | ||
+ | </source> | ||
+ | == Top 10 대기세션 (60분동안) == | ||
+ | <source lang=sql> | ||
+ | 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; | ||
+ | </source> | ||
+ | == Find session detail of top sid by passing sid == | ||
+ | <source lang=sql> | ||
+ | select serial#, | ||
+ | username, | ||
+ | osuser, | ||
+ | machine, | ||
+ | program, | ||
+ | resource_consumer_group, | ||
+ | client_info | ||
+ | from v$session where sid=&sid; | ||
+ | </source> | ||
+ | |||
+ | == Find different sql_ids of queries executed in above top session by-passing sid == | ||
+ | <source lang=sql> | ||
+ | select distinct sql_id, session_serial# from v$active_session_history | ||
+ | where sample_time > sysdate - interval '60' minute | ||
+ | and session_id=&sid | ||
+ | </source> | ||
+ | == Find full sqltext (CLOB) of above sql == | ||
+ | <source lang=sql> | ||
+ | select sql_fulltext from v$sql where sql_id='&sql_id' | ||
+ | </source> | ||
+ | ==find session wait history of above found top session == | ||
+ | <source lang=sql> | ||
+ | select * from v$session_wait_history where sid=&sid | ||
+ | </source> | ||
+ | == find all wait events for above top session == | ||
+ | <source lang=sql> | ||
+ | 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 | ||
+ | </source> | ||
+ | == session statistics for above particular top session : == | ||
+ | <source lang=sql> | ||
+ | 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 | ||
+ | </source> | ||
+ | |||
<comments /> | <comments /> | ||
+ | |||
+ | <source lang=sql> | ||
+ | |||
SELECT sysmetric_history.sample_time, | SELECT sysmetric_history.sample_time, | ||
cpu/60 AS cpu, | cpu/60 AS cpu, | ||
51번째 줄: | 191번째 줄: | ||
WHERE ash.sample_time (+)=sysmetric_history.sample_time | WHERE ash.sample_time (+)=sysmetric_history.sample_time | ||
ORDER BY sample_time; | ORDER BY sample_time; | ||
+ | </source> |
2018년 11월 14일 (수) 15:50 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 ACTIVE SESSION HISTORY TOP 10
- 2 TOP 이벤트
- 3 Top 세션
- 4 Top 쿼리
- 5 Top 세그먼트
- 6 IO가 많이 사용하는 쿼리
- 7 Top 10 CPU 소비 (60분 동안)
- 8 Top 10 대기세션 (60분동안)
- 9 Find session detail of top sid by passing sid
- 10 Find different sql_ids of queries executed in above top session by-passing sid
- 11 Find full sqltext (CLOB) of above sql
- 12 find session wait history of above found top session
- 13 find all wait events for above top session
- 14 session statistics for above particular top session :
1 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
2 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
3 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
4 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
5 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
6 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));
7 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;
8 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;
9 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;
10 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
11 Find full sqltext (CLOB) of above sql[편집]
select sql_fulltext from v$sql where sql_id='&sql_id'
12 find session wait history of above found top session[편집]
select * from v$session_wait_history where sid=&sid
13 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
14 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;