Transcript ppt - Intelligent Data Systems Laboratory

Database Tuning
- Chapter 3,4
2008. 7
Intelligent Database Systems Lab.
Seungseok Kang
Statspack을 이용한 성능 데이터 수집
• 성능 데이터 수집
– 시스템의 비효율을 진단하기 위한 첫 번째 단계
• 성능 데이터 수집의 의미
–
–
–
–
대상 시스템 선정
시스템 진단을 위한 기초 데이터 수집
시스템의 비효율 검색
튜닝 계획서 작성을 위한 기초 자료 수집
• Statspack
– Oracle 자체에서 제공하는 데이터 수집 Tool
– 실행 시 데이터베이스 성능에 대한 Snapshot 저장
• Time Interval을 이용한 구간별 성능 데이터 생성
– 확인 가능한 성능 데이터
• 데이터베이스 일반정보, CPU 사용 분석 데이터, 비효율 Query, IO Cost 분
석 데이터, Wait Event 데이터, Physical Read 정보, File I/O 현황, Latch 성
능 데이터, 전체 시스템 진단 등
Statspack 실행 및 수행
• 사전 확인 사항
– Timed_statistics_parameter 설정
• 성능 데이터 수집 시 시간 정보를 추가
– 시스템 부하 확인
• Statspack 설치
– Perfstat DB User 생성
– Table 및 Synonym 생성
– Package 생성
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spcreate
– 성능 데이터 수집 계획 수립
• 성능 데이터 수집 시간
• 성능 데이터 수집 회수
Statspack 실행 및 수행 (cont.)
• Statspack 수행
– Perfstat User로 Login
– Statspack 수행 Package 실행
SQL> CONNECT PERFSTAT/PERFSTAT
SQL> EXECUTE STATSPACK.SNAP(i_snap_level=>10);
• Statspack Level
– 추출하는 성능 데이터 항목 결정
Level
특징
0
일반 성능 데이터
5
Level 0 + SQL 추가 데이터
•
•
•
•
10
SQL
SQL
SQL
SQL
실행 횟수
처리 시 발생하는 Disk Read
Parse Call 횟수
처리시 발생하는 Buffer Gets
Level 5 + Child Latch 정보
Statspack 분석
• Statspack 분석 시 고려사항
– 성능 데이터 분석의 중요성
– 객관적 성능 데이터 추출
– 튜닝 계획서 도출
• Statspack으로 수집되는 일반 정보 항목
Disk I/O Dependent
– 성능 데이터 수집 기간 (BeginTime, EndTime)
– Response Time 정보 (ResponseTime = CPUTime+WaitTime)
–
–
–
–
–
–
–
CPU 사용 정보
비효율 SQL 정보
I/O Cost 정보
Wait Event 정보
Physical Read가 많은 SQL 정보
File I/O 정보
Latch 정보
Statspack 분석
•
CPU 사용 정보
CPU Time
Time
Total
Per User Call
Per Transaction
100.00%
0.06
0.03
262.33
Parse CPU Time
52788
10.41%
0.01
0.00
27.31
Recursive CPU Time
23750
4.68%
0.00
0.00
12.29
430553
84.91%
0.05
0.03
222.74
–
–
Parse CPU Time : 구문 분석 중 소요되는 Time
Recursive CPU : Data Dictionary Table Access에 소요되는 Time
Other CPU Time
–
주로 Disk I/O와 관련된 시간이 많음
–
–
비효율적 SQL에 의한 많은 Block Gets
많은 Buffer Scan
•
–
–
–
Block Access 감소 필요 (Index/SQL/Application 튜닝)
과다한 DML로 인한 많은 Read Consistency Blocks
Commit 시 과다한 Block Cleanout
•
Index/DML/Application 튜닝
Rollback Segment에 대한 많은 Access
•
•
Per Execute
507091
Other CPU Time
•
Percentage
DML/Application 튜닝
일반적 경우 1회 SQL 수행시 1000 Block 내외를 Access해야 함
Statspack 분석 (cont.)
• 비효율 SQL
CPU Usage Per SQL Statement Executed
CPU sec
Executes
Hash Values
Total OF CPU
Statement
434.27
600
3287551105
7.96%
SELECT ……
112.25
777697
4267895531
2.06%
INSERT INTO ……
–
–
–
–
CPU Sec : 해당 SQL이 사용한 총 CPU Time
Executes : 해당 SQL의 수행 횟수
Hash Value : 구문 분석 시 Hash Function 실행 회수 (Join)
Total OF CPU : 해당 SQL 성능 데이터 수집 시간 중 CPU 사용률
• Tuning SQL Statement
– Critical Access Path
• 많은 횟수로 반복 실행되는 Access Path
• 절대 튜닝 대상
– High CPU Time Access Path
• 1회 수행에 많은 Block을 Access
Statspack 분석 (cont.)
• I/O Cost
Distribution of I/O Operations
Operation
Single block data file reads
Multi block data file reads
Control file reads
Data file writes
Control file writes
Log file writes
Foreground
Perc(%)
Background
Perc(%)
1227704
98.77
32
0.79
1227736
67.87
229
0.02
130
3.21
359
0.02
3453
0.28
1540
38.02
4993
0.28
10879
9.88
10879
1.94
21758
1.20
2517
0.20
1884
0.34
4401
0.24
548490
44.13
548493
97.59
1096983
60.64
– Single block data file reads : Index Scan
• Batch 작업 수행 시 성능 저하
– Multi block data file reads : Table Full Scan
–
–
–
–
• Index 선정의 잘못으로 인한 부하
Control file reads : Checkpoint I/O
Data file writes : DBWR I/O
Control file writes : Checkpoint I/O
Log file writes : LGWR / Redo Log File I/O
Total
Perc Total(%)
Statspack 분석 (cont.)
• Wait Event
Wait Time
Event
db file sequential
read
Time
Percentage
Avg. Wait
Per Execute
Per User Call
Per
Transaction
2340700
71.66%
51.03
0.77
0.39
209.31
705700
21.61%
1.11
0.23
0.12
63.10
Log file
sequential read
89800
2.75%
19.84
0.03
0.02
8.03
Buffer busy wait
61100
1.87%
0.16
0.02
0.01
5.46
Log buffer space
38900
1.19%
12.13
0.01
0.01
3.48
db file scattered
read
16800
0.51%
1.17
0.01
0.00
1.50
Log file sync
– db file sequential read : index scan 시 발생하는 wait event
• 잘못된 index scan / SQL이 많은 index block scan
(많은 block access로 인한 Disk I/O 증가)
• SQL / index / application 튜닝, Disk I/O 분산
– Log file sync : redo log file 기록 시 발생하는 wait event
• Index 튜닝, Disk I/O 분산, Log Buffer Size 감소
Statspack 분석 (cont.)
•
High Physical Read SQL
Statement
Executes
Physical Reads
Reads/Execute
Hash Value
SELECT ……
4
1362894
340723.50
125974
28.92%
SELECT distinct ……
1
306488
306488.00
140207
35.42%
3947
224551
56.89
22663
40.18%
INSERT INTO ……
– Executes : 성능 데이터 수집 시간 중 SQL 수행 횟수
– Reads / Execute : 1회 실행 시 평균 Block Read 수
•
Critical Access Path
– 표에서 3번째 항목
– Critical Access Path이면서 Physical Read가 많으면 튜닝 대상이 됨
•
Total
Physical Read와 수행 횟수와의 관계
–
–
–
–
High Physical Read : High CPU Time
Low Physical Read, High Executes : enables OLTP (Critical Access Path)
High Physical Read, Low Executes : enables Batch
High Physical Read, High Executes : needs tuning
• Critical Access Path with high Block Access
– Low Physical Read, Low Executes : don’t care
Statspack 분석 (cont.)
• File I/O
File I/O in msec
File Name
Reads
Avg Read
(ms)
Avg
Blocks/Rd
Writes
Buffer Waits
Avg Buffer
Waits (ms)
Card02.dbf
54824
12.9
1.0
65061
5
10.0
Card01.dbf
47603
13.5
1.0
51985
5
6.0
Card03.dbf
45808
13.5
1.0
52725
3
6.7
Mempnt04.dbf
41293
11.4
1.0
24629
0
Mempnt05.dbf
39680
12.0
1.0
24985
0
Mempnt06.dbf
38754
12.0
1.0
25085
0
Mempnt01.dbf
38074
12.0
1.0
27140
0
• File I/O가 많은 Datafile
– File I/O가 높은 Datafile이 동일한 Disk에 존재하는 지 확인
– 서로 다른 Physical Disk에 존재해야 함 (Disk I/O 분산)
• card02, card01, card03.dbf
Statspack 분석 (cont.)
• Latch 정보
– 동일 자원에 대한 동시 Access를 관리하는 제어 구조
– 특정 자원에 대한 접근을 serialize하는 것이 목적
– 대부분의 Process가 빠르게 exclusive 모드를 획득하고 해제함
Latch Sleep Statistics Per Latch
Latch Name
Cache Buffer Chain
Session Idle Bit
Library Cache
Cache Buffer LRU
Chain
Gets
Sleeps
(=latch wait)
Percentage
Cumulative
71502843
40
83.33%
83.33%
5345170
4
8.33%
91.67%
88764198
3
6.25%
97.92%
702243
1
2.08%
100.00%
– Gets : 성능 데이터 수집 시간 중 Latch 요청 횟수
– Percentage : (Latch Sleep / 전체 Sleep) * 100
– 성능에 영향을 주는 요소 : Latch Sleep 횟수
• Cache Buffer Chain Tuning
– Database Buffer Cache 확대, Hidden Parameter 이용
• SQL 튜닝을 통한 비효율 제거
– Disk I/O 감소
Statspack 분석 (cont.)
• 전체 시스템 진단
– 성능 데이터 리포트의 마지막에 제공
– 튜닝 요소별 중요도 제공
Maximum Gain (%)
What
Detail
0
Check your network for congestion
Non issue
1
Tune the log file sequential read event
No detailed information
10
Check SQL*Net configuration for SDU and TDU setting
……
14
Tune the log file sync
……
35
Reduce the number of physical reads per executions
……
– Maximum Gain
• 해당 사항을 Tuning했을 때 얻을 수 있는 상대적 효과
실시간 조회를 이용한 성능 데이터 수집
• 실시간 조회를 통해 시스템의 현 상태를 바로 확인
– 데이터 딕셔너리 뷰
• 데이터베이스의 모든 상태를 저장하고 있는 내부 Table view
– 동적 성능 뷰
• Oracle Startup 시 각 단계별로 조회할 수 있는 View
• 성능 데이터 수집
– Wait Event 수집
• Statspack : 일정 기간 (Time Interval) 동안의 Wait Event
• 동적 성능 뷰 : 실시간 Wait Event
–
–
–
–
–
V$SYSTEM_EVENT : Wait Event 누적 정보
V$SESSION : 각 Session 별 정보
V$SQL : SQL 정보
V$ WAITSTAT : Block 별 Wait 정보
V$SESSION_WAIT : 각 Session 별 Wait 정보
성능 데이터 조회
• Block Wait Event 조회
– Data Block Wait
• Disk I/O 분산, SQL 튜닝,
Index 튜닝
– Undo Block Wait
• Rollback Segment 튜닝
(Direct Loading)
– Undo Header Wait
• Rollback Segment 개수 증가
– Segment Header Wait
• Disk I/O 분산,
Extent Size 증가
성능 데이터 조회 (cont.)
• 시스템 전체 누적 Wait Event 조회
• SQL*Net message
– 추출하는 Row에 비해 통신하는 데이터가 많음
– SQL 튜닝
• db file sequential read
– 잘못된 index scan / SQL이 많은 index block scan
(많은 block access로 인한 Disk I/O 증가)
– SQL / index / application 튜닝, Disk I/O 분산
자원 사용 현황 수집
• Session 별 / 전체 시스템에서의 자원 사용 현황
– Redo 사용 현황
• High Redo Buffer Allocation Retries / Redo Log Space Request / Redo
Log Space Wait Time
– Rego Log에 경합 및 부하 전재
– Table Scan 현황
• High Table Scan (long tables)
– Table Full Scan을 의미 (OLTP에서의 성능 저하)
– Index 적용 및 Session 확인 분석
– Sort 사용 현황
• High Disk Sort
– Index / Application 튜닝
– SQL*Net 현황
• High SQL*Net message
– SQL 튜닝
– 구문 분석 현황
• High Parse Count (Hard)
– 처음부터 구문 분석을 수행하는 경우 : 시스템 부하 증가
– SGA Shared Pool Size 중가, SQL 튜닝을 통한 기존 구문 분석 정보 이용
참고 : Oracle 11g
• Oracle 11g
– Statspack을 지원하나 추천하지 않음 (deprecated)
– Oracle Enterprise Manager – Database Control
• 스냅샷 저장 및 세부 정보, 리포트 제공
• Statspack에서 제공하는 모든 기능 제공 + 부가 정보
– Oracle ADDM
•
•
•
•
Automatic Database Diagnostic Monitor
Oracle 10g에서 새롭게 추가된 기능
데이터베이스 성능 통계 제공, 성능 병목 구간 확인
SQL 구문에 대한 분석 및 성능 향상을 위한 조언 제공
Oracle 11g - Enterprise Manager
Oracle 11g – Snapshot Information
Oracle 11g – Workload Repository report
Oracle 11g – Advisor Central
Oracle 11g – Advisor Recommendation
• http://www.oracle.com/technology/oramag/oracle/04may/o34tech_talking.html
• http://www.oracle.com/technology/global/kr/pub/articles
/10gdba/week18_10gdba.html