"ORACLE 튜닝 대상 조회"의 두 판 사이의 차이
DB CAFE
1번째 줄: | 1번째 줄: | ||
− | == 튜닝 대상 | + | == 튜닝 대상 조회 == |
=== CPU를 많이 사용하는 세션의 식별 === | === CPU를 많이 사용하는 세션의 식별 === | ||
<source lang="sql"> | <source lang="sql"> |
2023년 1월 31일 (화) 23:29 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 튜닝 대상 조회[편집]
1.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;
1.2 Disk Read 가 많은 SQL문 찾기[편집]
SELECT DISK_READS, SQL_TEXT
FROM V$SQLAREA
WHERE DISK_READS > 100
ORDER BY DISK_READS DESC;
1.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;
1.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;
1.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;
1.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;
1.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;
1.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;
1.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) ;