특정 기간 중, CPU 사용율이 높은 쿼리를 조회한다.
CPU 점유율이 높은 쿼리를 찾아서 튜닝하기 위해서 사용한다. 기본적으로 oracle에서 제공하는 view를 활용한다.
사용 테이블
- DBA_HIST_SQLSTAT : SQL Historical Statixtics Information
DB에서 실행된 SQL에 대한 성능 통계치 view - DBA_HIST_SNAPSHOT : SnapShot Information
워크로드 저장소의 스냅샷에 대한 정보 view - DBA_HIST_SQLTEXT : SQL Text
워크로드 저장소에 캡쳐된 공유 SQL 커서에 속한 SQL 문의 텍스트 표시.
이 view는 주로 V$SQL view와 함께 사용됨.
최종 쿼리
WITH REF_DATE AS (
/* 대상 기간 지정 */
SELECT
TO_DATE('20220401' || ' 000000', 'YYYYMMDD HH24MISS') AS BGN_DE
, TO_DATE('20230420' || ' 235959', 'YYYYMMDD HH24MISS') AS END_DE
FROM DUAL
)
SELECT
X.SQL_ID
, X.CPU_TIME
, X.EXECUTIONS_DELTA
, X.CPU_TIME_PER_EXECUTIONS
, DBMS_LOB.SUBSTR(SUBSTR(D.SQL_TEXT, 1, 200)) AS SQL_TEXT
, D.SQL_TEXT AS SQL_FULLTEXT
FROM (
SELECT
SQL.DBID
, SQL.SQL_ID
, SUM(SQL.CPU_TIME_DELTA) / 1000000 AS CPU_TIME
, SUM(SQL.EXECUTIONS_DELTA) AS EXECUTIONS_DELTA
, ROUND((SUM(SQL.CPU_TIME_DELTA) / 1000000) / DECODE(SUM(SQL.EXECUTIONS_DELTA), 0, 1, SUM(SQL.EXECUTIONS_DELTA)) / DECODE(SQL.PX_SERVERS_EXECS_DELTA, 0, 1, SQL.PX_SERVERS_EXECS_DELTA)) AS CPU_TIME_PER_EXECUTIONS
FROM DBA_HIST_SQLSTAT SQL
, (
SELECT
MIN(SNAP_ID) AS START_SNAP_ID
, MAX(SNAP_ID) AS END_SNAP_ID
, MIN(BEGIN_INTERVAL_TIME) AS BEGIN_INTERVAL_TIME
, MAX(END_INTERVAL_TIME) AS END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT, REF_DATE
WHERE BEGIN_INTERVAL_TIME BETWEEN REF_DATE.BGN_DE
AND REF_DATE.END_DE
) SNAP
WHERE SQL.SNAP_ID BETWEEN SNAP.START_SNAP_ID AND SNAP.END_SNAP_ID
GROUP BY SQL.DBID, SQL.SQL_ID, SQL.PX_SERVERS_EXECS_DELTA
HAVING SUM(SQL.EXECUTIONS_DELTA) >= 0
ORDER BY CPU_TIME_PER_EXECUTIONS DESC
) X
INNER JOIN DBA_HIST_SQLTEXT D
ON D.SQL_ID = X.SQL_ID
AND D.DBID = X.DBID
WHERE ROWNUM <= 40
;분리
SELECT
SQL.DBID
, SUM(SQL.CPU_TIME_DELTA) / 1000000 AS CPU_TIME
, SUM(SQL.EXECUTIONS_DELTA) AS EXECUTIONS_DELTA
, ROUND((SUM(SQL.CPU_TIME_DELTA) / 1000000) / DECODE(SUM(SQL.EXECUTIONS_DELTA), 0, 1, SUM(SQL.EXECUTIONS_DELTA)) / DECODE(SQL.PX_SERVERS_EXECS_DELTA, 0, 1, SQL.PX_SERVERS_EXECS_DELTA)) AS CPU_TIME_PER_EXECUTIONS
FROM DBA_HIST_SQLSTAT SQL
, (
SELECT
MIN(SNAP_ID) AS START_SNAP_ID
, MAX(SNAP_ID) AS END_SNAP_ID
, MIN(BEGIN_INTERVAL_TIME) AS BEGIN_INTERVAL_TIME
, MAX(END_INTERVAL_TIME) AS END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME BETWEEN TO_DATE('20230401' || ' 000000', 'YYYYMMDD HH24MISS')
AND TO_DATE('20230419' || ' 235959', 'YYYYMMDD HH24MISS')
) SNAP
WHERE SQL.SNAP_ID BETWEEN SNAP.START_SNAP_ID AND SNAP.END_SNAP_ID
GROUP BY SQL.DBID, SQL.SQL_ID, SQL.PX_SERVERS_EXECS_DELTA
HAVING SUM(SQL.EXECUTIONS_DELTA) >= 0
ORDER BY CPU_TIME_PER_EXECUTIONS
; SELECT
MIN(SNAP_ID) AS START_SNAP_ID
, MAX(SNAP_ID) AS END_SNAP_ID
, MIN(BEGIN_INTERVAL_TIME) AS BEGIN_INTERVAL_TIME
, MAX(END_INTERVAL_TIME) AS END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME BETWEEN TO_DATE('20230401' || ' 000000', 'YYYYMMDD HH24MISS')
AND TO_DATE('20230419' || ' 235959', 'YYYYMMDD HH24MISS')
;728x90
반응형
'Database' 카테고리의 다른 글
| [DB] Oracle 쿼리 튜닝을 위한 쿼리 분석 방법 (0) | 2023.04.13 |
|---|---|
| [SQL] 오라클 조회 쿼리 성능 개선을 위한 정리 - 1 (0) | 2023.04.12 |
| 락 걸린 테이블 확인 및 락 해제 (0) | 2022.02.16 |
| [DB] INDEX REBUILD (0) | 2021.01.07 |
| [DBeaver - cubrid] DBeaver Cubrid UTF-8 문제 (1) | 2020.12.07 |
