"ORACLE 튜닝 대상 조회"의 두 판 사이의 차이
DB CAFE
(→Rollback Segment를 사용하고 있는 SQL문 조회) |
|||
29번째 줄: | 29번째 줄: | ||
== Rollback Segment를 사용하고 있는 SQL문 조회 == | == Rollback Segment를 사용하고 있는 SQL문 조회 == | ||
− | + | <source lang="sql"> SELECT A.NAME , B.XACTS , C.SID | |
− | <source lang="sql"> SELECT A.NAME | + | , C.SERIAL# , C.USERNAME , D.SQL_TEXT |
− | + | FROM V$ROLLNAME A , V$ROLLSTAT B | |
− | + | , V$SESSION C , V$SQLTEXT D | |
− | |||
− | , C.SERIAL# | ||
− | |||
− | |||
− | FROM V$ROLLNAME A | ||
− | |||
− | , V$SESSION C | ||
− | |||
, V$TRANSACTION E | , V$TRANSACTION E | ||
− | + | WHERE A.USN = B.USN | |
− | 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; </source> | + | 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; | ||
+ | </source> | ||
== 오래도록 수행되는 Full Table Scan를 모니터링 == | == 오래도록 수행되는 Full Table Scan를 모니터링 == |
2020년 7월 25일 (토) 09:02 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 CPU를 많이 사용하는 세션의 식별[편집]
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;
2 Disk Read 가 많은 SQL문 찾기[편집]
SELECT DISK_READS, SQL_TEXT FROM V$SQLAREA WHERE DISK_READS > 100 ORDER BY DISK_READS DESC;
3 Rollback Segment를 사용하고 있는 SQL문 조회[편집]
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;
4 오래도록 수행되는 Full Table Scan를 모니터링[편집]
SELECT SID
, SERIAL#
, OPNAME
, TO_CHAR(START_TIME, 'HH24:MI:SS') AS "START"
, (SOFAR / TOTALWORK) * 100 AS "PERCENT_COMPLETE"
FROM V$SESSION_LONGOPS;
5 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;
6 현재 세션에서 10초이상 걸리는 쿼리 조회 (SELECT절)[편집]
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;
7 현재 세션에서 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;
8 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;
9 인덱스 생성시 플랜 확인[편집]
EXPLAIN PLAN FOR
CREATE INDEX IDX_TB_BA_RGLR_99 ON TB_BA_RGLR (YEAR, EMP_NO, AGE) ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) ;