SQLP : 제3과목은 SQL 고급활용 및 튜닝 이다.
3과목. SQL 고급활용 및 튜닝
1. SQL 수행 구조
2. SQL 분석 도구
3. 인덱스 튜닝
4. 조인 튜닝
5. SQL 옵티마이저
6. 고급 SQL 튜닝
7. Lock과 트랜잭션 동시성 제어
실행계획 표시
DBMS_XPLAN.DISPLAY
- 단일 SQL문에 대해 "예측" 실행계획
| explain plan for SELECT * FROM Product WHERE ProductID = 1; select * from table(dbms_xplan.display(null, null, 'typical')); |
TYPICAL 포맷
| -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7900) 14 rows selected. |
ALL 포맷
| -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 2 - SEL$1 / EMP@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7900) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10] 2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22] 26 rows selected. |
DBMS_XPLAN.DISPLAY_CURSOR
- 단일 SQL문에 대해 "실제" 수행된 실행계획
| ALTER SESSION SET STATISTICS_LEVEL = ALL; SELECT * FROM Product WHERE ProductID = 1; SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'typical'); SELECT /*+ gather_plan_statistics */ * FROM Product WHERE ProductID = 1; SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'allstats last'); |
TYPICAL 포맷
| -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7900) 20 rows selected. |
ALL 포맷
| -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 2 - SEL$1 / EMP@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7900) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10] 2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22] 32 rows selected. |
|
Id
|
실행계획에서의 구분자
|
|
Operation
|
각 단계에서의 어떤 작업이 일어났는지 표시
|
|
Name
|
테이블명이나 index 명을 표시함
|
|
Rows
|
해당 쿼리 계획 단계에서 나올 것으로 예상되는 행의 수
|
|
Byte
|
실행 계획의 각 단계가 반환할 것으로 예상 되는 데이터의 크기를 바이트로 나타낸 수. 이 수는 행의 수와 행의 예상 길이에 따라 결정됨
|
|
Cost
|
CBO가 쿼리 계획의 각 단계에 할당한 비용. CBO는 동일한 쿼리에 대해 다양한 실행 경로/계획을 생성하며 모든 쿼리에 대해 비용을 할당함
위의 실행 계획에선 전체 비용이 13인것을 알수 있음
|
|
Time
|
각 단계별 수행 시간 |
|
Query Block Name
|
SQL 문장을 Query Block이라는 단위로 나눔. inline view 와 subquery 등 |
|
Outline Data
|
실행 계획을 수립 하는데 필요한 내부적으로 적용된 Hint들의 목록 |
|
Predicate
|
조건절 |
|
Column Projection
|
실행 계획의 특정 단계에서 어떤 Column을 추출하는가를 의미 |
ADVANCED ALLSTATS LAST 포맷
|
Starts
|
말 그대로 해당 오퍼레이션이 "시작"된 횟수를 의미함
이 개념은 Nested Loops Join을 생각하시면 쉽게 이해할 수 있음 Nested Loops Join은 선행 테이블에서 읽는 로우수만큼 후행 테이블을 탐색하는 구조임 만일 선행 테이블에서 100건이 나온다면 후행 테이블을 100번 액세스하게됨 이럴 경우에 후행 테이블에서 대한 읽기 작업의 Starts 값이 "100"이 되는것 |
|
E-Rows / E-Bytes / E-Time
|
예측값
|
|
A-Rows / A-Time
|
실제 실행된 값
|
SQL 트레이스
- SQL Trace는 실행되는 SQL문의 실행통계를 세션별로 모아서 Trace 파일을 만듬
- SQL Trace는 세션과 인스턴스 레벨에서 SQL문장들을 분석할 수 있음
- .trc 파일로 저장되고, tkprof 유틸리티를 이용하면 분석
| call | 커서 상태에 따라 Parse, Execute, Fetch 세 개의 Call로 나누어 각각에 대한 통계정보를 보여준다. |
| Parse | 커서를 파싱하고 실행계획을 생성하는 데 대한 통계 |
| Execute | 커서의 실행 단계에 대한 통계 |
| Fetch | 레코드를 실제로 Fetch하는데 대한 통계 |
| count | Parse, Execute, Fetch 각 단계가 수행된 횟수 |
| cpu | 현재 커서가 각 단계를 수행하는 데 소요된 시간 |
| Elapsed Time | CPU Time + Wait Time = Response시점 - Call 시점 CPU 시간에 비해 Elapsed 시간이 현저히 큰 경우는 디스크 I/O 때문이다. |
| disk | 디스크로부터 읽은 블록 수 |
| query | Consistent 모드에서 읽은 버퍼 블록 수 |
| current | Current모드에서 읽은 버퍼 블록 수 |
| rows | 각 단계에서 읽거나 갱신한 처리건 수 |
| Misses in library cache during parse | Parse 구간에서 해당 SQL을 Library Cache에서 읽지 못하고 잃어버린 횟수 값이 1이면 Hard Parse, 0이면 Soft Parse를 의미함 |
| Optimizer mode | 옵티마이저 모드 |
버퍼캐시 히트율
(1 - disk / (query+current)) * 100
또는
( (query+current) -disk) / (query+current) * 100
Row Source Operation 통계 출력
|
Rows
|
각 수행단계에 출력된 row수
|
|
Cr
|
consistent 모드 블록 읽기
|
|
Pr(r)
|
디스크 블록 읽기
|
|
Pw(w)
|
디스크 블록 쓰기
|
|
Time
|
소요시간(us= microsecond)
|
|
Cost
|
Cost Based Optimizer(CBO)에서 예측한 비용
|
|
Size
|
리턴한 데이터 size(bytes)
|
|
Card
|
Cardinality, Cost Based Optimizer(CBO)에서 예측한 row수
|
현재 세션에 SQL 트레이스 거는 방법
| alter session set sql_trace = true; select 절; => .trc 파일이 생성됨 |
AutoTrace
- SQL Plus에서 실행계획을 가장 쉽고 빠르게 확인해 볼 수 있는 방법
| grant plustrace to scott; - plustrace 권한부여 |
| set autotrace on - SQL 실제 수행 => SQL 실행결과, 실행계획 및 실행통계 출력 |
| set autotrace on explain - SQL 실제 수행 => SQL 실행결과, 실행계획 출력 |
| set autotrace on statistics - SQL 실제 수행 => SQL 실행결과, 실행통계 출력 |
| set autotrace traceonly - SQL 실제 수행 => 실행계획 및 실행통계 출력 |
| set autotrace traceonly explain - SQL 실제 수행 X => 실행계획 출력 |
| set autotrace traceonly statistics - SQL 실제 수행 => 실행통계 출력 |
| -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7900) |
통계
| Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 596 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
|
recursive calls
|
사용자의 SQL문을 실행하기 위하여 수행된 SQL 문의 수
|
|
db block gets
|
현재 모드(current mode)에서 버퍼 캐시로부터 읽어온 블록의 총 수
|
|
consistent gets
|
버퍼 캐시의 블록에 대한 일관된 읽기의 요청 횟수. 일관된 읽기는 언두 정보, 즉 롤백 정보에 대한 읽기를 요구할 수도 있으며 이들 언두에 대한 읽기도 계산됨
|
|
physical reads
|
물리적으로 데이터 파일을 읽어 버퍼 캐시에 넣은 횟수
|
|
redo size
|
해당 문이 실행되는 동안 생성된 리두의 전체 크기를 바이트 단위로 나타낸 수
|
|
byte sent via SQL*Net to client
|
서버로부터 클라이언트에 전송된 총 바이트 수
|
|
byte recevied via SQL*Net from client
|
클라이언트로부터 받은 총 바이트 수
|
|
SQL*Net roundtrips to/from client
|
클라이언트로(부터) 전송된 SQL*Net 메시지의 총 수. 다중 행 결과 집합으로부터 꺼내오기 위한 왕복을 포함함
|
|
sorts(memory)
|
사용자의 세션 메모리(정렬 영역)에서 수행된 정렬 sort_area_size 데이터베이스 매개변수에 의해 제어됨
|
|
sorts(disk)
|
사용자의 정렬 영역의 크기를 초과하여 디스크(임시 테이블 영역)를 사용하는 정렬
|
|
rows processed
|
수정되거나 select 문으로부터 반환된 행
|
MS-SQL Server
트레이스 확인 방법
set statistics profile on
set statistics io on
set statistics time on
- 구문분석 및 컴파일 시간
- 논리직/물리적 읽기 수
- 각 오퍼레이션 단계별 실행 횟수
| set statistics profile on - 실제 실행계획 표시 |
| Rows : 각 연산자에서 만든 실제의 행 수를 보여줍니다. Executes : 각 연산자가 몇 번 실행이 되었는지 보여줍니다 StmtText : 세부적으로 어떤 실행이 있었는지를 보여주거나, 실제로 실행된 쿼리를 보여줍니다 Argument : 수행되는 작업의 추가정보를 제공합니다. TOP 절의 행 수나, Group By 조건, Where 조건, Index Seek 혹은 Scan사항 등 |
| set statistics io on - I/O 통계 표시 |
| 테이블 'emp'. 스캔 수 1, 논리적 읽기 28, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0. |
| set statistics time on - 시간 통계 표시 |
| SQL Server 구문 분석 및 컴파일 시간: CPU 시간 = 0ms, 경과 시간 = 0ms. (100개 행이 영향을 받음) SQL Server 실행 시간: CPU 시간 = 0ms, 경과 시간 = 222ms |
SET SHOWPLAN_ALL ON
또는
SET SHOWPLAN_TEXT ON
- 예상 실행계획 표시
쿼리별로 실행계획에 나타나는 형태
| SELECT distinct / UNION HASH (UNIQUE) GROUP BY HASH (GROUP BY) ORDER BY SORT (ORDER BY) 집계함수 SUM(), COUNT() SORT (AGGREGATE) 윈도우함수 SUM() over(partition by xx) WINDOW (SORT) ROWNUM 사용시 COUNT (STOPKEY) |
Response Time Analysis 방법론
- 세션 또는 시스템 전체에 발생하는 병목 현상과 그 원인을 찾아 문제를 해결하는 방법
- CPU time과 Wait time을 각각 break down하면서 서버의 일량과 대기 시간을 분석
Response Time = Servic Time + Wait Time
또는
Response Time = CPU time + Queue Time
AWR (Automatic Workload Repository) 보고서
- DB 의 상태를 진단하는 정보
- DBA에게 데이터베이스 실행 시간과 관련된 스냅샷과 상세한 정보를 제공
- Statpack 을 업그레이드 해서 개발 되었다.
DB 대기 이벤트 및 통계 정보
- 시스템 통계 정보
- 데이터베이스 부하 정보
- SQL 수행 정보
- 활동 세션 정보
'데이터베이스' 카테고리의 다른 글
| [SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 3. 인덱스튜닝 (0) | 2025.03.03 |
|---|---|
| [SQLP 요점정리] 3과목. SQL 고급활용 및 튜닝 - 1. SQL 수행 구조 (0) | 2025.03.01 |
| [SQLD/SQLP 요점정리] 2과목. SQL 기본 및 활용 (0) | 2025.02.26 |
| [SQLD/SQLP 요점정리] 1과목. 데이터 모델링의 이해 (0) | 2025.02.25 |
| mysql / mariadb 슬로우쿼리(slowquery) 탐지하기 (0) | 2022.03.29 |
