"ASH (Active Session History)"의 두 판 사이의 차이
DB CAFE
잔글 |
|||
17번째 줄: | 17번째 줄: | ||
<comments /> | <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; |
2018년 11월 7일 (수) 23:06 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
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
<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;