"ORACLE 튜닝 대상 조회"의 두 판 사이의 차이
DB CAFE
(→인덱스 생성시 플랜 확인) |
(→v$sql_plan 이용한 SQL 플랜 보기 =) |
||
(같은 사용자의 중간 판 21개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
== 튜닝 대상 조회 == | == 튜닝 대상 조회 == | ||
− | === CPU를 많이 사용하는 세션의 식별 === | + | === 튜닝 대상 찾기 === |
+ | {{틀:고지상자 | ||
+ | |제목=*실행수 TOP 30, BUFFER_GET TOP 30,실행당 BUFFER_GET TOP 30,EXEC_FOR_SEC >= 10 초당 10회 이상 실행 ) | ||
+ | |내용=# 일간 실행수 대비 실행당 BUFFER_GETS가 크지 않은 경우 튜닝 대상 제외 | ||
+ | ## 단 CPU_TIME 비율이 높으면 포함. 제외 기준은 변경 및 삭제가 자유로움 | ||
+ | # AND NOT(EXEC_ELAPSED_TIME <= 0.0005) --실행당 ELASED_TIME이 0.0005초 미만 제외 | ||
+ | # AND NOT(EXEC_ROWS*2 >= EXEC_BUFFER_GETS)--실행당 건수가 실행당 BUFFER_GET*2이상 제외 | ||
+ | }} | ||
+ | |||
+ | <source lang=sql> | ||
+ | WITH TMP_SQLSTAT_HIST AS( | ||
+ | SELECT /*+ LEADING(X B) USE_NL(B) USE_HASH(A) FULL(B) */ | ||
+ | B.DBID, B.SQL_ID, B.INSTANCE_NUMBER | ||
+ | , TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD') SNAP_DATE | ||
+ | , MAX(PARSING_SCHEMA_NAME) PARSING_SCHEMA_NAME | ||
+ | , B.PLAN_HASH_VALUE | ||
+ | , MAX(MODULE) MODULE | ||
+ | , SUM(EXECUTIONS_DELTA) EXECUTIONS | ||
+ | , SUM(BUFFER_GETS_DELTA) BUFFER_GETS | ||
+ | , ROUND(SUM(BUFFER_GETS_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1, | ||
+ | SUM(EXECUTIONS_DELTA))) EXEC_BUFFER_GETS | ||
+ | , SUM(DISK_READS_DELTA) DISK_READS | ||
+ | , ROUND(SUM(DISK_READS_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1, | ||
+ | SUM(EXECUTIONS_DELTA))) EXEC_DISK_READS | ||
+ | , SUM(ROWS_PROCESSED_DELTA) ROWS_PROCESSED | ||
+ | , ROUND(SUM(ROWS_PROCESSED_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1, | ||
+ | SUM(EXECUTIONS_DELTA))) EXEC_ROWS | ||
+ | , SUM(CPU_TIME_DELTA) CPU_TIME | ||
+ | , ROUND(SUM(CPU_TIME_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1, | ||
+ | SUM(EXECUTIONS_DELTA)) / 1000000, 6) EXEC_CPU_TIME | ||
+ | , SUM(CLWAIT_DELTA) CLUSTER_WAIT_TIME | ||
+ | , ROUND(SUM(CLWAIT_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1, | ||
+ | SUM(EXECUTIONS_DELTA)) / 1000000, 6) EXEC_CLWAIT_TIME | ||
+ | , SUM(ELAPSED_TIME_DELTA) ELAPSED_TIME | ||
+ | , ROUND(SUM(ELAPSED_TIME_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1, | ||
+ | SUM(EXECUTIONS_DELTA)) / 1000000, 6) EXEC_ELAPSED_TIME | ||
+ | , ROUND(MAX(EXECUTIONS_DELTA) / 600, 3) EXEC_FOR_SEC | ||
+ | --최종 수행된 SQL 조회, 여러 INSTANCE에서 수행시는 BUFER_GETS 높은 것 우선 | ||
+ | , ROW_NUMBER() OVER(PARTITION BY B.DBID, B.SQL_ID | ||
+ | ORDER BY TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD') DESC | ||
+ | , SUM(BUFFER_GETS_DELTA) DESC) RNK | ||
+ | FROM (SELECT /*+ NO_MERGE */ | ||
+ | DBID, MIN(SNAP_ID) MIN_SNAP_ID, MAX(SNAP_ID) MAX_SNAP_ID | ||
+ | FROM SYS.WRM$_SNAPSHOT --DBA_HIST_SNAPSHOT --SQL 실행 구간 | ||
+ | WHERE END_INTERVAL_TIME >= TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW') - | ||
+ | AND END_INTERVAL_TIME < TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW') | ||
+ | GROUP BY DBID) X, --DBA_HIST_SQLSTAT과 조인 시 해당 파티션만 SCAN하기 위해 만든 집합임 | ||
+ | SYS.WRM$_SNAPSHOT A, --DBA_HIST_SNAPSHOT | ||
+ | SYS.WRH$_SQLSTAT B --DBA_HIST_SQLSTAT과 | ||
+ | WHERE X.DBID = B.DBID | ||
+ | AND B.SNAP_ID BETWEEN X.MIN_SNAP_ID AND X.MAX_SNAP_ID | ||
+ | AND A.DBID = B.DBID | ||
+ | AND A.SNAP_ID = B.SNAP_ID | ||
+ | AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER | ||
+ | AND A.END_INTERVAL_TIME >= TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW') - 7 | ||
+ | AND A.END_INTERVAL_TIME < TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW') | ||
+ | AND NVL(B.PARSING_SCHEMA_NAME,'-') NOT IN ('SYS','SYSMAN','DBSNMP','SYSTEM','EXFSYS') | ||
+ | AND NOT REGEXP_LIKE(NVL(B.MODULE,'-'), 'Orange|SQL Developer|SQLGate|Data Pump| | ||
+ | TOAD|golden|ERwin|PL.SQL Developer| | ||
+ | SQL Loader|sqlplus|SQL.Plus|oracle|DBMS_SCHEDULER', 'i') | ||
+ | AND PLAN_HASH_VALUE > 0 | ||
+ | AND MODULE IS NOT NULL | ||
+ | GROUP BY B.DBID, B.SQL_ID, B.INSTANCE_NUMBER, B.PLAN_HASH_VALUE | ||
+ | , TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD') | ||
+ | ) | ||
+ | SELECT --같거나 비슷한 SQL은 같은 GROUP으로 표현 | ||
+ | DENSE_RANK() OVER(ORDER BY DBMS_LOB.SUBSTR(B.SQL_TEXT, 100, 1)) SQL_GRP_TYPE | ||
+ | , A.DBID | ||
+ | , A.INSTANCE_NUMBER --최종 수행된 INSTANCE | ||
+ | , A.SNAP_DATE --최종 수행 일자 | ||
+ | , A.SQL_ID --SQL_ID | ||
+ | , A.PLAN_HASH_VALUE --실행 계획에 종속적인 값 | ||
+ | , A.MODULE --실행 모듈 | ||
+ | , A.EXECUTIONS --총 실행수 | ||
+ | , A.EXEC_ROWS --실행당 결과 건수 | ||
+ | , A.EXEC_BUFFER_GETS --실행당 BUFFER_GET | ||
+ | , A.BUFFER_GETS --총 BUFFER_GET | ||
+ | , A.EXEC_DISK_READS --실행당 DISK_READ | ||
+ | , A.EXEC_ELAPSED_TIME --실행당 수행 시간 | ||
+ | , A.EXEC_CPU_TIME --실행당 CPU 시간 | ||
+ | , A.EXEC_FOR_SEC --초당 실행수, 특정 시간에만 집중적으로 수행되는 SQL 판별위함 | ||
+ | , A.ELAPSED_TIME --총 수행시간 | ||
+ | , A.CPU_TIME --총 CPU 시간 | ||
+ | , A.CLUSTER_WAIT_TIME --총 CLUSTER 대기 시간 | ||
+ | , ROUND(A.CPU_TIME / A.ELAPSED_TIME, 2) CPU_RATE -- 수행시간 대비 CPU 시간 비율 | ||
+ | FROM ( | ||
+ | SELECT SNAP_DATE, DBID, INSTANCE_NUMBER, SQL_ID, PLAN_HASH_VALUE, MODULE | ||
+ | , EXECUTIONS, EXEC_ROWS, EXEC_BUFFER_GETS, BUFFER_GETS, EXEC_DISK_READS | ||
+ | , ELAPSED_TIME, CPU_TIME, CLUSTER_WAIT_TIME | ||
+ | , EXEC_ELAPSED_TIME, EXEC_CPU_TIME, EXEC_FOR_SEC | ||
+ | , RANK() OVER(PARTITION BY DBID ORDER BY EXECUTIONS DESC) EXECUTIONS_RNK | ||
+ | , RANK() OVER(PARTITION BY DBID ORDER BY BUFFER_GETS DESC) BUFFER_GETS_RNK | ||
+ | , RANK() OVER(PARTITION BY DBID ORDER BY EXEC_CPU_TIME DESC) EXEC_CPU_TIME_RNK | ||
+ | , RANK() OVER(PARTITION BY DBID ORDER BY EXEC_BUFFER_GETS DESC) EXEC_BUFFER_GETS_RNK | ||
+ | FROM TMP_SQLSTAT_HIST | ||
+ | WHERE NOT((CLUSTER_WAIT_TIME/DECODE(CPU_TIME, 0, 1, CPU_TIME) <= 0.95 OR | ||
+ | CPU_TIME/ELAPSED_TIME <= 0.9) AND | ||
+ | ( | ||
+ | (EXECUTIONS >= 300000 AND EXEC_BUFFER_GETS <= 20) OR | ||
+ | (EXECUTIONS >= 100000 AND EXECUTIONS < 300000 AND EXEC_BUFFER_GETS <= 50) OR | ||
+ | (EXECUTIONS >= 50000 AND EXECUTIONS < 100000 AND EXEC_BUFFER_GETS <= 100) OR | ||
+ | (EXECUTIONS >= 10000 AND EXECUTIONS < 50000 AND EXEC_BUFFER_GETS <= 200) OR | ||
+ | (EXECUTIONS >= 5000 AND EXECUTIONS < 10000 AND EXEC_BUFFER_GETS <= 500) OR | ||
+ | (EXECUTIONS >= 3000 AND EXECUTIONS < 5000 AND EXEC_BUFFER_GETS <= 1000) OR | ||
+ | (EXECUTIONS >= 1000 AND EXECUTIONS < 3000 AND EXEC_BUFFER_GETS <= 2000) OR | ||
+ | (EXECUTIONS >= 500 AND EXECUTIONS < 1000 AND EXEC_BUFFER_GETS <= 3000) OR | ||
+ | (EXECUTIONS >= 100 AND EXECUTIONS < 500 AND EXEC_BUFFER_GETS <= 5000) OR | ||
+ | (EXECUTIONS >= 10 AND EXECUTIONS < 100 AND EXEC_BUFFER_GETS <= 10000) OR | ||
+ | (EXECUTIONS >= 3 AND EXECUTIONS < 10 AND EXEC_BUFFER_GETS <= 30000) OR | ||
+ | (EXECUTIONS < 3 AND EXEC_BUFFER_GETS <= 100000) | ||
+ | ) | ||
+ | ) --일간 실행수 대비 실행당 BUFFER_GETS가 크지 않은 경우 튜닝 대상 제외 | ||
+ | --단 CPU_TIME 비율이 높으면 포함. 제외 기준은 변경 및 삭제가 자유로움 | ||
+ | AND NOT(EXEC_ELAPSED_TIME <= 0.0005) --실행당 ELASED_TIME이 0.0005초 미만 제외 | ||
+ | AND NOT(EXEC_ROWS*2 >= EXEC_BUFFER_GETS)--실행당 건수가 실행당 BUFFER_GET*2이상 제외 | ||
+ | AND RNK = 1) A, | ||
+ | DBA_HIST_SQLTEXT B | ||
+ | WHERE A.DBID = B.DBID | ||
+ | AND A.SQL_ID = B.SQL_ID | ||
+ | AND (A.EXECUTIONS_RNK <= 30 OR --실행수 TOP 30 | ||
+ | A.BUFFER_GETS_RNK <= 30 OR --BUFFER_GET TOP 30 | ||
+ | A.EXEC_BUFFER_GETS_RNK <= 30 OR --실행당 BUFFER_GET TOP 30 | ||
+ | A.EXEC_FOR_SEC >= 10); | ||
+ | </source> | ||
+ | |||
+ | === GV$SQL(V$SQL) 이용 튜닝 대상 조회 === | ||
+ | * (실행수 TOP 30, BUFFER_GET TOP 30,실행당 BUFFER_GET TOP 30,EXEC_FOR_SEC >= 10 초당 10회 이상 실행 ) | ||
+ | |||
+ | <source lang=sql> | ||
+ | WITH TMP_SQLSTAT_HIST AS( | ||
+ | SELECT SQL_ID, INST_ID | ||
+ | , MAX(LAST_ACTIVE_TIME) SNAP_DATE | ||
+ | , MAX(PARSING_SCHEMA_NAME) PARSING_SCHEMA_NAME | ||
+ | , PLAN_HASH_VALUE | ||
+ | , MAX(MODULE) MODULE | ||
+ | , SUM(EXECUTIONS) EXECUTIONS | ||
+ | , SUM(BUFFER_GETS) BUFFER_GETS | ||
+ | , ROUND(SUM(BUFFER_GETS) / DECODE(SUM(EXECUTIONS), 0, 1, | ||
+ | SUM(EXECUTIONS))) EXEC_BUFFER_GETS | ||
+ | , SUM(DISK_READS) DISK_READS | ||
+ | , ROUND(SUM(DISK_READS) / DECODE(SUM(EXECUTIONS), 0, 1, | ||
+ | SUM(EXECUTIONS))) EXEC_DISK_READS | ||
+ | , SUM(ROWS_PROCESSED) ROWS_PROCESSED | ||
+ | , ROUND(SUM(ROWS_PROCESSED) / DECODE(SUM(EXECUTIONS), 0, 1, | ||
+ | SUM(EXECUTIONS))) EXEC_ROWS | ||
+ | , SUM(CPU_TIME) CPU_TIME | ||
+ | , ROUND(SUM(CPU_TIME) / DECODE(SUM(EXECUTIONS), 0, 1, | ||
+ | SUM(EXECUTIONS)) / 1000000, 6) EXEC_CPU_TIME | ||
+ | , SUM(CLUSTER_WAIT_TIME) CLUSTER_WAIT_TIME | ||
+ | , ROUND(SUM(CLUSTER_WAIT_TIME) / DECODE(SUM(EXECUTIONS), 0, 1, | ||
+ | SUM(EXECUTIONS)) / 1000000, 6) EXEC_CLWAIT_TIME | ||
+ | , SUM(ELAPSED_TIME) ELAPSED_TIME | ||
+ | , ROUND(SUM(ELAPSED_TIME) / DECODE(SUM(EXECUTIONS), 0, 1, | ||
+ | SUM(EXECUTIONS)) / 1000000, 6) EXEC_ELAPSED_TIME | ||
+ | FROM GV$SQL B | ||
+ | WHERE NVL(B.PARSING_SCHEMA_NAME,'-') NOT IN ('SYS','SYSMAN','DBSNMP','SYSTEM','EXFSYS') | ||
+ | AND NOT REGEXP_LIKE(NVL(B.MODULE,'-'), 'Orange|SQL Developer|SQLGate|Data Pump| | ||
+ | TOAD|golden|ERwin|PL.SQL Developer| | ||
+ | SQL Loader|sqlplus|SQL.Plus|oracle|DBMS_SCHEDULER', 'i') | ||
+ | AND PLAN_HASH_VALUE > 0 | ||
+ | AND MODULE IS NOT NULL | ||
+ | GROUP BY SQL_ID, INST_ID, PLAN_HASH_VALUE | ||
+ | ) | ||
+ | SELECT --같거나 비슷한 SQL은 같은 GROUP으로 표현 | ||
+ | DENSE_RANK() OVER(ORDER BY DBMS_LOB.SUBSTR(B.SQL_TEXT, 100, 1)) SQL_GRP_TYPE | ||
+ | , A.INST_ID --최종 수행된 INSTANCE | ||
+ | , A.SNAP_DATE --최종 수행 일자 | ||
+ | , A.SQL_ID --SQL_ID | ||
+ | , A.PLAN_HASH_VALUE --실행 계획에 종속적인 값 | ||
+ | , A.MODULE --실행 모듈 | ||
+ | , A.EXECUTIONS --총 실행수 | ||
+ | , A.EXEC_ROWS --실행당 결과 건수 | ||
+ | , A.EXEC_BUFFER_GETS --실행당 BUFFER_GET | ||
+ | , A.BUFFER_GETS --총 BUFFER_GET | ||
+ | , A.EXEC_DISK_READS --실행당 DISK_READ | ||
+ | , A.EXEC_ELAPSED_TIME --실행당 수행 시간 | ||
+ | , A.EXEC_CPU_TIME --실행당 CPU 시간 | ||
+ | , A.ELAPSED_TIME --총 수행시간 | ||
+ | , A.CPU_TIME --총 CPU 시간 | ||
+ | , A.CLUSTER_WAIT_TIME --총 CLUSTER 대기 시간 | ||
+ | , ROUND(A.CPU_TIME / A.ELAPSED_TIME, 2) CPU_RATE -- 수행시간 대비 CPU 시간 비율 | ||
+ | FROM ( | ||
+ | SELECT SNAP_DATE, INST_ID, SQL_ID, PLAN_HASH_VALUE, MODULE | ||
+ | , EXECUTIONS, EXEC_ROWS, EXEC_BUFFER_GETS, BUFFER_GETS, EXEC_DISK_READS | ||
+ | , ELAPSED_TIME, CPU_TIME, CLUSTER_WAIT_TIME | ||
+ | , EXEC_ELAPSED_TIME, EXEC_CPU_TIME | ||
+ | , RANK() OVER(ORDER BY EXECUTIONS DESC) EXECUTIONS_RNK | ||
+ | , RANK() OVER(ORDER BY BUFFER_GETS DESC) BUFFER_GETS_RNK | ||
+ | , RANK() OVER(ORDER BY EXEC_CPU_TIME DESC) EXEC_CPU_TIME_RNK | ||
+ | , RANK() OVER(ORDER BY EXEC_BUFFER_GETS DESC) EXEC_BUFFER_GETS_RNK | ||
+ | FROM TMP_SQLSTAT_HIST | ||
+ | WHERE NOT((CLUSTER_WAIT_TIME/DECODE(CPU_TIME, 0, 1, CPU_TIME) <= 0.95 OR | ||
+ | CPU_TIME/ELAPSED_TIME <= 0.9) AND | ||
+ | ( | ||
+ | (EXECUTIONS >= 300000 AND EXEC_BUFFER_GETS <= 20) OR | ||
+ | (EXECUTIONS >= 100000 AND EXECUTIONS < 300000 AND EXEC_BUFFER_GETS <= 50) OR | ||
+ | (EXECUTIONS >= 50000 AND EXECUTIONS < 100000 AND EXEC_BUFFER_GETS <= 100) OR | ||
+ | (EXECUTIONS >= 10000 AND EXECUTIONS < 50000 AND EXEC_BUFFER_GETS <= 200) OR | ||
+ | (EXECUTIONS >= 5000 AND EXECUTIONS < 10000 AND EXEC_BUFFER_GETS <= 500) OR | ||
+ | (EXECUTIONS >= 3000 AND EXECUTIONS < 5000 AND EXEC_BUFFER_GETS <= 1000) OR | ||
+ | (EXECUTIONS >= 1000 AND EXECUTIONS < 3000 AND EXEC_BUFFER_GETS <= 2000) OR | ||
+ | (EXECUTIONS >= 500 AND EXECUTIONS < 1000 AND EXEC_BUFFER_GETS <= 3000) OR | ||
+ | (EXECUTIONS >= 100 AND EXECUTIONS < 500 AND EXEC_BUFFER_GETS <= 5000) OR | ||
+ | (EXECUTIONS >= 10 AND EXECUTIONS < 100 AND EXEC_BUFFER_GETS <= 10000) OR | ||
+ | (EXECUTIONS >= 3 AND EXECUTIONS < 10 AND EXEC_BUFFER_GETS <= 30000) OR | ||
+ | (EXECUTIONS < 3 AND EXEC_BUFFER_GETS <= 100000) | ||
+ | ) | ||
+ | ) --일간 실행수 대비 실행당 BUFFER_GETS가 크지 않은 경우 튜닝 대상 제외 | ||
+ | --단 CPU_TIME 비율이 높으면 포함. 제외 기준은 변경 및 삭제가 자유로움 | ||
+ | AND NOT(EXEC_ELAPSED_TIME <= 0.0005) --실행당 ELASED_TIME이 0.0005초 미만 제외 | ||
+ | AND NOT(EXEC_ROWS*2 >= EXEC_BUFFER_GETS)--실행당 건수가 실행당 BUFFER_GET*2이상 제외 | ||
+ | ) A, | ||
+ | GV$SQL B | ||
+ | WHERE A.INST_ID = B.INST_ID | ||
+ | AND A.SQL_ID = B.SQL_ID | ||
+ | AND B.CHILD_NUMBER = 0 | ||
+ | AND (A.EXECUTIONS_RNK <= 30 OR --실행수 TOP 30 | ||
+ | A.BUFFER_GETS_RNK <= 30 OR --TOTAL BUFFER_GETS TOP 30 | ||
+ | A.EXEC_BUFFER_GETS_RNK <= 30 OR --실행당 BUFFER_GETS TOP 30 | ||
+ | (A.EXECUTIONS >= 100 AND A.EXEC_ELAPSED_TIME >= 10)); --실행수 100이상, 수행시간 10초 이상 | ||
+ | </source> | ||
+ | ---- | ||
+ | |||
+ | === CPU를 많이 사용하는 세션의 식별 V$SESSTAT V$SESSION === | ||
<source lang="sql"> | <source lang="sql"> | ||
− | SELECT A.SID | + | SELECT A.SID , C.SERIAL# , A.VALUE |
− | , C.SERIAL# | + | , C.USERNAME , C.STATUS , C.PROGRAM |
− | |||
− | , C.USERNAME | ||
− | |||
− | |||
FROM V$SESSTAT A | FROM V$SESSTAT A | ||
, V$STATNAME B | , V$STATNAME B | ||
16번째 줄: | 235번째 줄: | ||
AND A.VALUE > 0 ORDER BY A.VALUE DESC; | AND A.VALUE > 0 ORDER BY A.VALUE DESC; | ||
</source> | </source> | ||
− | === Disk Read 가 많은 SQL문 찾기 === | + | |
+ | === Disk Read 가 많은 SQL문 찾기 V$SQLAREA === | ||
<source lang="sql"> | <source lang="sql"> | ||
SELECT DISK_READS, SQL_TEXT | SELECT DISK_READS, SQL_TEXT | ||
24번째 줄: | 244번째 줄: | ||
</source> | </source> | ||
− | === Rollback Segment를 사용하고 있는 SQL문 조회 === | + | === Rollback Segment를 사용하고 있는 SQL문 조회 V$ROLLSTAT === |
− | + | <source lang="sql"> | |
− | <source lang="sql"> SELECT A.NAME , B.XACTS | + | SELECT A.NAME , B.XACTS , C.SID |
− | , C.SERIAL# | + | , C.SERIAL# , C.USERNAME , D.SQL_TEXT |
− | FROM V$ROLLNAME A | + | FROM V$ROLLNAME A , V$ROLLSTAT B |
− | , V$SESSION C | + | , V$SESSION C , V$SQLTEXT D |
, V$TRANSACTION E | , V$TRANSACTION E | ||
WHERE A.USN = B.USN | WHERE A.USN = B.USN | ||
39번째 줄: | 259번째 줄: | ||
</source> | </source> | ||
− | === 오래도록 수행되는 Full Table Scan를 모니터링 | + | === 오래도록 수행되는 Full Table Scan를 모니터링 V$SESSION_LONGOPS === |
− | |||
− | |||
<source lang="sql"> | <source lang="sql"> | ||
SELECT SID | SELECT SID | ||
63번째 줄: | 281번째 줄: | ||
FROM ( | FROM ( | ||
− | SELECT A.SID | + | SELECT A.SID , C.SERIAL# , A.VALUE |
− | + | , C.USERNAME , C.STATUS , C.PROGRAM | |
− | |||
− | , C.USERNAME | ||
− | |||
− | |||
, C.SQL_ADDRESS | , C.SQL_ADDRESS | ||
, ROW_NUMBER() OVER (ORDER BY A.VALUE DESC) RN | , ROW_NUMBER() OVER (ORDER BY A.VALUE DESC) RN | ||
− | + | FROM V$SESSTAT A | |
, V$STATNAME B | , V$STATNAME B | ||
, V$SESSION C | , V$SESSION C | ||
− | WHERE | + | WHERE A.STATISTIC# = B.STATISTIC# |
− | AND | + | AND A.SID = C.SID |
− | AND | + | AND B.NAME = 'CPU used by this session' |
− | AND | + | AND A.VALUE > 0 |
− | AND | + | AND C.STATUS = 'ACTIVE' |
− | AND | + | AND C.USERNAME IS NOT NULL |
) A | ) A | ||
− | |||
WHERE A.RN <= 10; </source> | WHERE A.RN <= 10; </source> | ||
− | === 현재 세션에서 10초이상 걸리는 쿼리 조회 (SELECT절) | + | === 현재 세션에서 10초이상 걸리는 쿼리 조회 (SELECT절) V$SQLAREA === |
<source lang="sql"> | <source lang="sql"> | ||
SELECT ABS(SYSDATE - A.LAST_LOAD_TIME) * 24 * 60 * 60 AS SEC_TIEM, A.* | SELECT ABS(SYSDATE - A.LAST_LOAD_TIME) * 24 * 60 * 60 AS SEC_TIEM, A.* | ||
FROM V$SQLAREA A | FROM V$SQLAREA A | ||
, V$SESSION B | , V$SESSION B | ||
− | WHERE A.SQL_TEXT LIKE '%SELECT%' AND A.ADDRESS = B.SQL_ADDRESS AND B.STATUS = 'ACTIVE' AND A.ELAPSED_TIME >= 10 * 1000000 | + | WHERE A.SQL_TEXT LIKE '%SELECT%' |
+ | AND A.ADDRESS = B.SQL_ADDRESS | ||
+ | AND B.STATUS = 'ACTIVE' AND A.ELAPSED_TIME >= 10 * 1000000 | ||
-- 실행계획에서 10초 이상 걸리는 쿼리를 조회(실제 걸리는 시간은 아님.) | -- 실행계획에서 10초 이상 걸리는 쿼리를 조회(실제 걸리는 시간은 아님.) | ||
AND A.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM', 'SYSMAN') | AND A.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM', 'SYSMAN') | ||
99번째 줄: | 314번째 줄: | ||
<source lang="sql"> | <source lang="sql"> | ||
− | SELECT B.USERNAME | + | SELECT B.USERNAME , A.SID , A.PGA_USAGE |
− | + | , A.UGA_USAGE , A.CPU_USAGE_SECONDS , B.MACHINE | |
− | + | , B.PROGRAM , B.MODULE | |
− | , A.UGA_USAGE | ||
− | |||
− | |||
− | , B.PROGRAM | ||
− | |||
FROM (SELECT B.SID | FROM (SELECT B.SID | ||
, MAX(DECODE(C.NAME, 'session pga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS PGA_USAGE | , MAX(DECODE(C.NAME, 'session pga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS PGA_USAGE | ||
119번째 줄: | 329번째 줄: | ||
WHERE B.SID = A.SID AND B.STATUS = 'ACTIVE' AND B.USERNAME IS NOT NULL; | WHERE B.SID = A.SID AND B.STATUS = 'ACTIVE' AND B.USERNAME IS NOT NULL; | ||
</source> | </source> | ||
+ | == 동일 SQL 구분 / 분류 방법 == | ||
+ | # SQL문 시작 100자 내외가 같으면 같은 SQL로 판단 | ||
+ | # 동일한 업무별/동일한 신청자 별로 구분하여 튜닝 업무 진행 | ||
− | == | + | == v$sql_plan 이용한 SQL 플랜 보기 == |
<source lang=sql> | <source lang=sql> | ||
select a.sql_id, a.child_number child, | select a.sql_id, a.child_number child, | ||
160번째 줄: | 373번째 줄: | ||
order by a.sql_id, a.id, a.child_number; | order by a.sql_id, a.id, a.child_number; | ||
</source> | </source> | ||
− | |||
− | |||
− | |||
− | |||
[[Category:oracle]] | [[Category:oracle]] |
2023년 7월 24일 (월) 22:01 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 튜닝 대상 조회
- 1.1 튜닝 대상 찾기
- 1.2 GV$SQL(V$SQL) 이용 튜닝 대상 조회
- 1.3 CPU를 많이 사용하는 세션의 식별 V$SESSTAT V$SESSION
- 1.4 Disk Read 가 많은 SQL문 찾기 V$SQLAREA
- 1.5 Rollback Segment를 사용하고 있는 SQL문 조회 V$ROLLSTAT
- 1.6 오래도록 수행되는 Full Table Scan를 모니터링 V$SESSION_LONGOPS
- 1.7 CPU를 많이 사용하는 세션의 식별(SQL TEXT 조회)
- 1.8 현재 세션에서 10초이상 걸리는 쿼리 조회 (SELECT절) V$SQLAREA
- 1.9 현재 세션에서 PGA, UGA, CPU 사용량 세션별로 조회하는 쿼리
- 2 동일 SQL 구분 / 분류 방법
- 3 v$sql_plan 이용한 SQL 플랜 보기
1 튜닝 대상 조회[편집]
1.1 튜닝 대상 찾기[편집]
WITH TMP_SQLSTAT_HIST AS(
SELECT /*+ LEADING(X B) USE_NL(B) USE_HASH(A) FULL(B) */
B.DBID, B.SQL_ID, B.INSTANCE_NUMBER
, TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD') SNAP_DATE
, MAX(PARSING_SCHEMA_NAME) PARSING_SCHEMA_NAME
, B.PLAN_HASH_VALUE
, MAX(MODULE) MODULE
, SUM(EXECUTIONS_DELTA) EXECUTIONS
, SUM(BUFFER_GETS_DELTA) BUFFER_GETS
, ROUND(SUM(BUFFER_GETS_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1,
SUM(EXECUTIONS_DELTA))) EXEC_BUFFER_GETS
, SUM(DISK_READS_DELTA) DISK_READS
, ROUND(SUM(DISK_READS_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1,
SUM(EXECUTIONS_DELTA))) EXEC_DISK_READS
, SUM(ROWS_PROCESSED_DELTA) ROWS_PROCESSED
, ROUND(SUM(ROWS_PROCESSED_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1,
SUM(EXECUTIONS_DELTA))) EXEC_ROWS
, SUM(CPU_TIME_DELTA) CPU_TIME
, ROUND(SUM(CPU_TIME_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1,
SUM(EXECUTIONS_DELTA)) / 1000000, 6) EXEC_CPU_TIME
, SUM(CLWAIT_DELTA) CLUSTER_WAIT_TIME
, ROUND(SUM(CLWAIT_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1,
SUM(EXECUTIONS_DELTA)) / 1000000, 6) EXEC_CLWAIT_TIME
, SUM(ELAPSED_TIME_DELTA) ELAPSED_TIME
, ROUND(SUM(ELAPSED_TIME_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1,
SUM(EXECUTIONS_DELTA)) / 1000000, 6) EXEC_ELAPSED_TIME
, ROUND(MAX(EXECUTIONS_DELTA) / 600, 3) EXEC_FOR_SEC
--최종 수행된 SQL 조회, 여러 INSTANCE에서 수행시는 BUFER_GETS 높은 것 우선
, ROW_NUMBER() OVER(PARTITION BY B.DBID, B.SQL_ID
ORDER BY TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD') DESC
, SUM(BUFFER_GETS_DELTA) DESC) RNK
FROM (SELECT /*+ NO_MERGE */
DBID, MIN(SNAP_ID) MIN_SNAP_ID, MAX(SNAP_ID) MAX_SNAP_ID
FROM SYS.WRM$_SNAPSHOT --DBA_HIST_SNAPSHOT --SQL 실행 구간
WHERE END_INTERVAL_TIME >= TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW') -
AND END_INTERVAL_TIME < TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW')
GROUP BY DBID) X, --DBA_HIST_SQLSTAT과 조인 시 해당 파티션만 SCAN하기 위해 만든 집합임
SYS.WRM$_SNAPSHOT A, --DBA_HIST_SNAPSHOT
SYS.WRH$_SQLSTAT B --DBA_HIST_SQLSTAT과
WHERE X.DBID = B.DBID
AND B.SNAP_ID BETWEEN X.MIN_SNAP_ID AND X.MAX_SNAP_ID
AND A.DBID = B.DBID
AND A.SNAP_ID = B.SNAP_ID
AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
AND A.END_INTERVAL_TIME >= TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW') - 7
AND A.END_INTERVAL_TIME < TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW')
AND NVL(B.PARSING_SCHEMA_NAME,'-') NOT IN ('SYS','SYSMAN','DBSNMP','SYSTEM','EXFSYS')
AND NOT REGEXP_LIKE(NVL(B.MODULE,'-'), 'Orange|SQL Developer|SQLGate|Data Pump|
TOAD|golden|ERwin|PL.SQL Developer|
SQL Loader|sqlplus|SQL.Plus|oracle|DBMS_SCHEDULER', 'i')
AND PLAN_HASH_VALUE > 0
AND MODULE IS NOT NULL
GROUP BY B.DBID, B.SQL_ID, B.INSTANCE_NUMBER, B.PLAN_HASH_VALUE
, TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD')
)
SELECT --같거나 비슷한 SQL은 같은 GROUP으로 표현
DENSE_RANK() OVER(ORDER BY DBMS_LOB.SUBSTR(B.SQL_TEXT, 100, 1)) SQL_GRP_TYPE
, A.DBID
, A.INSTANCE_NUMBER --최종 수행된 INSTANCE
, A.SNAP_DATE --최종 수행 일자
, A.SQL_ID --SQL_ID
, A.PLAN_HASH_VALUE --실행 계획에 종속적인 값
, A.MODULE --실행 모듈
, A.EXECUTIONS --총 실행수
, A.EXEC_ROWS --실행당 결과 건수
, A.EXEC_BUFFER_GETS --실행당 BUFFER_GET
, A.BUFFER_GETS --총 BUFFER_GET
, A.EXEC_DISK_READS --실행당 DISK_READ
, A.EXEC_ELAPSED_TIME --실행당 수행 시간
, A.EXEC_CPU_TIME --실행당 CPU 시간
, A.EXEC_FOR_SEC --초당 실행수, 특정 시간에만 집중적으로 수행되는 SQL 판별위함
, A.ELAPSED_TIME --총 수행시간
, A.CPU_TIME --총 CPU 시간
, A.CLUSTER_WAIT_TIME --총 CLUSTER 대기 시간
, ROUND(A.CPU_TIME / A.ELAPSED_TIME, 2) CPU_RATE -- 수행시간 대비 CPU 시간 비율
FROM (
SELECT SNAP_DATE, DBID, INSTANCE_NUMBER, SQL_ID, PLAN_HASH_VALUE, MODULE
, EXECUTIONS, EXEC_ROWS, EXEC_BUFFER_GETS, BUFFER_GETS, EXEC_DISK_READS
, ELAPSED_TIME, CPU_TIME, CLUSTER_WAIT_TIME
, EXEC_ELAPSED_TIME, EXEC_CPU_TIME, EXEC_FOR_SEC
, RANK() OVER(PARTITION BY DBID ORDER BY EXECUTIONS DESC) EXECUTIONS_RNK
, RANK() OVER(PARTITION BY DBID ORDER BY BUFFER_GETS DESC) BUFFER_GETS_RNK
, RANK() OVER(PARTITION BY DBID ORDER BY EXEC_CPU_TIME DESC) EXEC_CPU_TIME_RNK
, RANK() OVER(PARTITION BY DBID ORDER BY EXEC_BUFFER_GETS DESC) EXEC_BUFFER_GETS_RNK
FROM TMP_SQLSTAT_HIST
WHERE NOT((CLUSTER_WAIT_TIME/DECODE(CPU_TIME, 0, 1, CPU_TIME) <= 0.95 OR
CPU_TIME/ELAPSED_TIME <= 0.9) AND
(
(EXECUTIONS >= 300000 AND EXEC_BUFFER_GETS <= 20) OR
(EXECUTIONS >= 100000 AND EXECUTIONS < 300000 AND EXEC_BUFFER_GETS <= 50) OR
(EXECUTIONS >= 50000 AND EXECUTIONS < 100000 AND EXEC_BUFFER_GETS <= 100) OR
(EXECUTIONS >= 10000 AND EXECUTIONS < 50000 AND EXEC_BUFFER_GETS <= 200) OR
(EXECUTIONS >= 5000 AND EXECUTIONS < 10000 AND EXEC_BUFFER_GETS <= 500) OR
(EXECUTIONS >= 3000 AND EXECUTIONS < 5000 AND EXEC_BUFFER_GETS <= 1000) OR
(EXECUTIONS >= 1000 AND EXECUTIONS < 3000 AND EXEC_BUFFER_GETS <= 2000) OR
(EXECUTIONS >= 500 AND EXECUTIONS < 1000 AND EXEC_BUFFER_GETS <= 3000) OR
(EXECUTIONS >= 100 AND EXECUTIONS < 500 AND EXEC_BUFFER_GETS <= 5000) OR
(EXECUTIONS >= 10 AND EXECUTIONS < 100 AND EXEC_BUFFER_GETS <= 10000) OR
(EXECUTIONS >= 3 AND EXECUTIONS < 10 AND EXEC_BUFFER_GETS <= 30000) OR
(EXECUTIONS < 3 AND EXEC_BUFFER_GETS <= 100000)
)
) --일간 실행수 대비 실행당 BUFFER_GETS가 크지 않은 경우 튜닝 대상 제외
--단 CPU_TIME 비율이 높으면 포함. 제외 기준은 변경 및 삭제가 자유로움
AND NOT(EXEC_ELAPSED_TIME <= 0.0005) --실행당 ELASED_TIME이 0.0005초 미만 제외
AND NOT(EXEC_ROWS*2 >= EXEC_BUFFER_GETS)--실행당 건수가 실행당 BUFFER_GET*2이상 제외
AND RNK = 1) A,
DBA_HIST_SQLTEXT B
WHERE A.DBID = B.DBID
AND A.SQL_ID = B.SQL_ID
AND (A.EXECUTIONS_RNK <= 30 OR --실행수 TOP 30
A.BUFFER_GETS_RNK <= 30 OR --BUFFER_GET TOP 30
A.EXEC_BUFFER_GETS_RNK <= 30 OR --실행당 BUFFER_GET TOP 30
A.EXEC_FOR_SEC >= 10);
1.2 GV$SQL(V$SQL) 이용 튜닝 대상 조회[편집]
- (실행수 TOP 30, BUFFER_GET TOP 30,실행당 BUFFER_GET TOP 30,EXEC_FOR_SEC >= 10 초당 10회 이상 실행 )
WITH TMP_SQLSTAT_HIST AS(
SELECT SQL_ID, INST_ID
, MAX(LAST_ACTIVE_TIME) SNAP_DATE
, MAX(PARSING_SCHEMA_NAME) PARSING_SCHEMA_NAME
, PLAN_HASH_VALUE
, MAX(MODULE) MODULE
, SUM(EXECUTIONS) EXECUTIONS
, SUM(BUFFER_GETS) BUFFER_GETS
, ROUND(SUM(BUFFER_GETS) / DECODE(SUM(EXECUTIONS), 0, 1,
SUM(EXECUTIONS))) EXEC_BUFFER_GETS
, SUM(DISK_READS) DISK_READS
, ROUND(SUM(DISK_READS) / DECODE(SUM(EXECUTIONS), 0, 1,
SUM(EXECUTIONS))) EXEC_DISK_READS
, SUM(ROWS_PROCESSED) ROWS_PROCESSED
, ROUND(SUM(ROWS_PROCESSED) / DECODE(SUM(EXECUTIONS), 0, 1,
SUM(EXECUTIONS))) EXEC_ROWS
, SUM(CPU_TIME) CPU_TIME
, ROUND(SUM(CPU_TIME) / DECODE(SUM(EXECUTIONS), 0, 1,
SUM(EXECUTIONS)) / 1000000, 6) EXEC_CPU_TIME
, SUM(CLUSTER_WAIT_TIME) CLUSTER_WAIT_TIME
, ROUND(SUM(CLUSTER_WAIT_TIME) / DECODE(SUM(EXECUTIONS), 0, 1,
SUM(EXECUTIONS)) / 1000000, 6) EXEC_CLWAIT_TIME
, SUM(ELAPSED_TIME) ELAPSED_TIME
, ROUND(SUM(ELAPSED_TIME) / DECODE(SUM(EXECUTIONS), 0, 1,
SUM(EXECUTIONS)) / 1000000, 6) EXEC_ELAPSED_TIME
FROM GV$SQL B
WHERE NVL(B.PARSING_SCHEMA_NAME,'-') NOT IN ('SYS','SYSMAN','DBSNMP','SYSTEM','EXFSYS')
AND NOT REGEXP_LIKE(NVL(B.MODULE,'-'), 'Orange|SQL Developer|SQLGate|Data Pump|
TOAD|golden|ERwin|PL.SQL Developer|
SQL Loader|sqlplus|SQL.Plus|oracle|DBMS_SCHEDULER', 'i')
AND PLAN_HASH_VALUE > 0
AND MODULE IS NOT NULL
GROUP BY SQL_ID, INST_ID, PLAN_HASH_VALUE
)
SELECT --같거나 비슷한 SQL은 같은 GROUP으로 표현
DENSE_RANK() OVER(ORDER BY DBMS_LOB.SUBSTR(B.SQL_TEXT, 100, 1)) SQL_GRP_TYPE
, A.INST_ID --최종 수행된 INSTANCE
, A.SNAP_DATE --최종 수행 일자
, A.SQL_ID --SQL_ID
, A.PLAN_HASH_VALUE --실행 계획에 종속적인 값
, A.MODULE --실행 모듈
, A.EXECUTIONS --총 실행수
, A.EXEC_ROWS --실행당 결과 건수
, A.EXEC_BUFFER_GETS --실행당 BUFFER_GET
, A.BUFFER_GETS --총 BUFFER_GET
, A.EXEC_DISK_READS --실행당 DISK_READ
, A.EXEC_ELAPSED_TIME --실행당 수행 시간
, A.EXEC_CPU_TIME --실행당 CPU 시간
, A.ELAPSED_TIME --총 수행시간
, A.CPU_TIME --총 CPU 시간
, A.CLUSTER_WAIT_TIME --총 CLUSTER 대기 시간
, ROUND(A.CPU_TIME / A.ELAPSED_TIME, 2) CPU_RATE -- 수행시간 대비 CPU 시간 비율
FROM (
SELECT SNAP_DATE, INST_ID, SQL_ID, PLAN_HASH_VALUE, MODULE
, EXECUTIONS, EXEC_ROWS, EXEC_BUFFER_GETS, BUFFER_GETS, EXEC_DISK_READS
, ELAPSED_TIME, CPU_TIME, CLUSTER_WAIT_TIME
, EXEC_ELAPSED_TIME, EXEC_CPU_TIME
, RANK() OVER(ORDER BY EXECUTIONS DESC) EXECUTIONS_RNK
, RANK() OVER(ORDER BY BUFFER_GETS DESC) BUFFER_GETS_RNK
, RANK() OVER(ORDER BY EXEC_CPU_TIME DESC) EXEC_CPU_TIME_RNK
, RANK() OVER(ORDER BY EXEC_BUFFER_GETS DESC) EXEC_BUFFER_GETS_RNK
FROM TMP_SQLSTAT_HIST
WHERE NOT((CLUSTER_WAIT_TIME/DECODE(CPU_TIME, 0, 1, CPU_TIME) <= 0.95 OR
CPU_TIME/ELAPSED_TIME <= 0.9) AND
(
(EXECUTIONS >= 300000 AND EXEC_BUFFER_GETS <= 20) OR
(EXECUTIONS >= 100000 AND EXECUTIONS < 300000 AND EXEC_BUFFER_GETS <= 50) OR
(EXECUTIONS >= 50000 AND EXECUTIONS < 100000 AND EXEC_BUFFER_GETS <= 100) OR
(EXECUTIONS >= 10000 AND EXECUTIONS < 50000 AND EXEC_BUFFER_GETS <= 200) OR
(EXECUTIONS >= 5000 AND EXECUTIONS < 10000 AND EXEC_BUFFER_GETS <= 500) OR
(EXECUTIONS >= 3000 AND EXECUTIONS < 5000 AND EXEC_BUFFER_GETS <= 1000) OR
(EXECUTIONS >= 1000 AND EXECUTIONS < 3000 AND EXEC_BUFFER_GETS <= 2000) OR
(EXECUTIONS >= 500 AND EXECUTIONS < 1000 AND EXEC_BUFFER_GETS <= 3000) OR
(EXECUTIONS >= 100 AND EXECUTIONS < 500 AND EXEC_BUFFER_GETS <= 5000) OR
(EXECUTIONS >= 10 AND EXECUTIONS < 100 AND EXEC_BUFFER_GETS <= 10000) OR
(EXECUTIONS >= 3 AND EXECUTIONS < 10 AND EXEC_BUFFER_GETS <= 30000) OR
(EXECUTIONS < 3 AND EXEC_BUFFER_GETS <= 100000)
)
) --일간 실행수 대비 실행당 BUFFER_GETS가 크지 않은 경우 튜닝 대상 제외
--단 CPU_TIME 비율이 높으면 포함. 제외 기준은 변경 및 삭제가 자유로움
AND NOT(EXEC_ELAPSED_TIME <= 0.0005) --실행당 ELASED_TIME이 0.0005초 미만 제외
AND NOT(EXEC_ROWS*2 >= EXEC_BUFFER_GETS)--실행당 건수가 실행당 BUFFER_GET*2이상 제외
) A,
GV$SQL B
WHERE A.INST_ID = B.INST_ID
AND A.SQL_ID = B.SQL_ID
AND B.CHILD_NUMBER = 0
AND (A.EXECUTIONS_RNK <= 30 OR --실행수 TOP 30
A.BUFFER_GETS_RNK <= 30 OR --TOTAL BUFFER_GETS TOP 30
A.EXEC_BUFFER_GETS_RNK <= 30 OR --실행당 BUFFER_GETS TOP 30
(A.EXECUTIONS >= 100 AND A.EXEC_ELAPSED_TIME >= 10)); --실행수 100이상, 수행시간 10초 이상
1.3 CPU를 많이 사용하는 세션의 식별 V$SESSTAT V$SESSION[편집]
SELECT A.SID , C.SERIAL# , A.VALUE
, C.USERNAME , C.STATUS , C.PROGRAM
FROM V$SESSTAT A
, V$STATNAME B
, V$SESSION C
WHERE A.STATISTIC# = B.STATISTIC#
AND A.SID = C.SID
AND B.NAME = 'CPU used by this session'
AND A.VALUE > 0 ORDER BY A.VALUE DESC;
1.4 Disk Read 가 많은 SQL문 찾기 V$SQLAREA[편집]
SELECT DISK_READS, SQL_TEXT
FROM V$SQLAREA
WHERE DISK_READS > 100
ORDER BY DISK_READS DESC;
1.5 Rollback Segment를 사용하고 있는 SQL문 조회 V$ROLLSTAT[편집]
SELECT A.NAME , B.XACTS , C.SID
, C.SERIAL# , C.USERNAME , D.SQL_TEXT
FROM V$ROLLNAME A , V$ROLLSTAT B
, V$SESSION C , V$SQLTEXT D
, V$TRANSACTION E
WHERE A.USN = B.USN
AND B.USN = E.XIDUSN
AND C.TADDR = E.ADDR
AND C.SQL_ADDRESS = D.ADDRESS
AND C.SQL_HASH_VALUE = D.HASH_VALUE
ORDER BY A.NAME, C.SID, D.PIECE;
1.6 오래도록 수행되는 Full Table Scan를 모니터링 V$SESSION_LONGOPS[편집]
SELECT SID
, SERIAL#
, OPNAME
, TO_CHAR(START_TIME, 'HH24:MI:SS') AS "START"
, (SOFAR / TOTALWORK) * 100 AS "PERCENT_COMPLETE"
FROM V$SESSION_LONGOPS;
1.7 CPU를 많이 사용하는 세션의 식별(SQL TEXT 조회)[편집]
SELECT A.*
, (SELECT SS.SQL_TEXT
FROM V$SQLAREA SS
WHERE SS.ADDRESS = A.SQL_ADDRESS
AND ROWNUM <= 1
) AS SQL_TEST
FROM (
SELECT A.SID , C.SERIAL# , A.VALUE
, C.USERNAME , C.STATUS , C.PROGRAM
, C.SQL_ADDRESS
, ROW_NUMBER() OVER (ORDER BY A.VALUE DESC) RN
FROM V$SESSTAT A
, V$STATNAME B
, V$SESSION C
WHERE A.STATISTIC# = B.STATISTIC#
AND A.SID = C.SID
AND B.NAME = 'CPU used by this session'
AND A.VALUE > 0
AND C.STATUS = 'ACTIVE'
AND C.USERNAME IS NOT NULL
) A
WHERE A.RN <= 10;
1.8 현재 세션에서 10초이상 걸리는 쿼리 조회 (SELECT절) V$SQLAREA[편집]
SELECT ABS(SYSDATE - A.LAST_LOAD_TIME) * 24 * 60 * 60 AS SEC_TIEM, A.*
FROM V$SQLAREA A
, V$SESSION B
WHERE A.SQL_TEXT LIKE '%SELECT%'
AND A.ADDRESS = B.SQL_ADDRESS
AND B.STATUS = 'ACTIVE' AND A.ELAPSED_TIME >= 10 * 1000000
-- 실행계획에서 10초 이상 걸리는 쿼리를 조회(실제 걸리는 시간은 아님.)
AND A.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM', 'SYSMAN')
AND B.USERNAME IS NOT NULL;
1.9 현재 세션에서 PGA, UGA, CPU 사용량 세션별로 조회하는 쿼리[편집]
SELECT B.USERNAME , A.SID , A.PGA_USAGE
, A.UGA_USAGE , A.CPU_USAGE_SECONDS , B.MACHINE
, B.PROGRAM , B.MODULE
FROM (SELECT B.SID
, MAX(DECODE(C.NAME, 'session pga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS PGA_USAGE
, MAX(DECODE(C.NAME, 'session uga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS UGA_USAGE
, MAX(DECODE(C.NAME, 'CPU used by this session', (B.VALUE / 100) || ' Sec', 0)) AS CPU_USAGE_SECONDS
FROM V$SESSTAT B
, V$STATNAME C
WHERE B.STATISTIC# = C.STATISTIC#
GROUP BY B.SID) A
, V$SESSION B
WHERE B.SID = A.SID AND B.STATUS = 'ACTIVE' AND B.USERNAME IS NOT NULL;
2 동일 SQL 구분 / 분류 방법[편집]
- SQL문 시작 100자 내외가 같으면 같은 SQL로 판단
- 동일한 업무별/동일한 신청자 별로 구분하여 튜닝 업무 진행
3 v$sql_plan 이용한 SQL 플랜 보기[편집]
select a.sql_id, a.child_number child,
a.id
, a.parent_id pid
----------------------------------------------------------------
, lpad(' ', 2*(a.depth))||a.operation || decode(a.id,0,' (Optimizer='||a.optimizer||') ')
|| decode(a.options,null,null,' (') || a.options || decode(a.options,null,null,')')
|| decode(a.object_name,null,null,' OF ' || a.object_owner||'.'||a.object_name
|| ' ('|| a.object_type ||')')
----------------------------------------------------------------(
|| decode(a.cost||a.cardinality||a.bytes,null,null,' (')
|| decode(a.cost, null, null, ' Cost='||a.cost)
|| decode(a.cardinality, null, null, ' Card='||a.cardinality)
|| decode(a.bytes, null, null, ' Bytes='||a.bytes)
|| decode(a.cost||a.cardinality||a.bytes,null,null,' )')
----------------------------------------------------------------)
|| decode(a.other_tag,null,null,'(')||a.other_tag||decode(a.other_tag,null,null,')') as "Operation"
---------------------------------------------------------------
, a.object_type "Type"
----------------------------------------------------------------
, a.cardinality "E-Rows"
, a.bytes "E-Bytes"
, a.cost
, a.time "E-Time"
----------------------------------------------------------------
, a.partition_start "Pstart"
, a.partition_stop "Pstop"
, a.partition_id "Pid"
----------------------------------------------------------------
, a.qblock_name "QB"
, a.access_predicates
, a.filter_predicates
----------------------------------------------------------------
from v$sql_plan a
where 1 = 1
and a.sql_id = '5q1b8cn1s123u' --and plan_hash_value = ''
-- and a.child_number = 1
order by a.sql_id, a.id, a.child_number;