Active Session History를 이용한 TOP SQL 분석
DB Version : Oracle 11.2.0.2
OS Version : AIX 6.1
* TOP 10 Event (RAC)
- 현재 일자 -1 부터 보기(AND SAMPLE_TIME >= TRUNC(SYSDATE) -1)
SELECT
SAMPLE_TIME
, INST_ID
, EVENT
, TOT_COUNT
, DECODE(RANK, 1, 'TOP',NULL) "RANK"
FROM (
SELECT
TO_CHAR(SAMPLE_TIME, 'YY/MM/DD(DY)') "SAMPLE_TIME"
, INST_ID
, EVENT
, COUNT(*) "TOT_COUNT"
, RANK() OVER(PARTITION BY TO_CHAR(SAMPLE_TIME, 'YY/MM/DD(DY)'), INST_ID ORDER BY COUNT(*) DESC, EVENT) "RANK"
FROM GV$ACTIVE_SESSION_HISTORY
WHERE EVENT IS NOT NULL
AND SAMPLE_TIME >= TRUNC(SYSDATE) -1
GROUP BY TO_CHAR(SAMPLE_TIME, 'YY/MM/DD(DY)'), INST_ID, EVENT
HAVING COUNT(*) > 10
)
WHERE RANK <= 10
ORDER BY 1, 2, RANK;
SAMPLE_TIME
, INST_ID
, EVENT
, TOT_COUNT
, DECODE(RANK, 1, 'TOP',NULL) "RANK"
FROM (
SELECT
TO_CHAR(SAMPLE_TIME, 'YY/MM/DD(DY)') "SAMPLE_TIME"
, INST_ID
, EVENT
, COUNT(*) "TOT_COUNT"
, RANK() OVER(PARTITION BY TO_CHAR(SAMPLE_TIME, 'YY/MM/DD(DY)'), INST_ID ORDER BY COUNT(*) DESC, EVENT) "RANK"
FROM GV$ACTIVE_SESSION_HISTORY
WHERE EVENT IS NOT NULL
AND SAMPLE_TIME >= TRUNC(SYSDATE) -1
GROUP BY TO_CHAR(SAMPLE_TIME, 'YY/MM/DD(DY)'), INST_ID, EVENT
HAVING COUNT(*) > 10
)
WHERE RANK <= 10
ORDER BY 1, 2, RANK;
* TOP 10 SQL (RAC)
- 현재 일자 -1 부터 보기(AND SAMPLE_TIME >= TRUNC(SYSDATE) -1)
SELECT
DISTINCT
A.SAMPLE_TIME
, A.INST_ID
, A.SQL_ID
, A.TOT_COUNT
, DECODE(A.RANK, 1, 'TOP',NULL) "RANK"
, B.SQL_TEXT
FROM (
SELECT
TO_CHAR(SAMPLE_TIME, 'YY/MM/DD(DY)') "SAMPLE_TIME"
, INST_ID
, SQL_ID
, COUNT(*) "TOT_COUNT"
, RANK() OVER(PARTITION BY TO_CHAR(SAMPLE_TIME, 'YY/MM/DD(DY)'), INST_ID ORDER BY COUNT(*) DESC, SQL_ID) "RANK"
FROM GV$ACTIVE_SESSION_HISTORY
WHERE SQL_ID IS NOT NULL
AND SAMPLE_TIME >= TRUNC(SYSDATE) -1
GROUP BY TO_CHAR(SAMPLE_TIME, 'YY/MM/DD(DY)'), INST_ID, SQL_ID
HAVING COUNT(*) > 10
) A,
GV$SQL B
WHERE 1=1
AND A.SQL_ID = B.SQL_ID
AND A.INST_ID = B.INST_ID
AND RANK <= 10
ORDER BY 1, 2, RANK;
DISTINCT
A.SAMPLE_TIME
, A.INST_ID
, A.SQL_ID
, A.TOT_COUNT
, DECODE(A.RANK, 1, 'TOP',NULL) "RANK"
, B.SQL_TEXT
FROM (
SELECT
TO_CHAR(SAMPLE_TIME, 'YY/MM/DD(DY)') "SAMPLE_TIME"
, INST_ID
, SQL_ID
, COUNT(*) "TOT_COUNT"
, RANK() OVER(PARTITION BY TO_CHAR(SAMPLE_TIME, 'YY/MM/DD(DY)'), INST_ID ORDER BY COUNT(*) DESC, SQL_ID) "RANK"
FROM GV$ACTIVE_SESSION_HISTORY
WHERE SQL_ID IS NOT NULL
AND SAMPLE_TIME >= TRUNC(SYSDATE) -1
GROUP BY TO_CHAR(SAMPLE_TIME, 'YY/MM/DD(DY)'), INST_ID, SQL_ID
HAVING COUNT(*) > 10
) A,
GV$SQL B
WHERE 1=1
AND A.SQL_ID = B.SQL_ID
AND A.INST_ID = B.INST_ID
AND RANK <= 10
ORDER BY 1, 2, RANK;
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
» | Active Session History를 이용한 TOP SQL 분석 (GV$ACTIVE_SESSION_HISTORY) | 에밀리오 | 2016.07.12 | 1210 |