Database Tuning - Chapter 3,4

1. Database Tuning- Chapter 3,4 2008. 7 Intelligent Database Systems Lab. Seungseok Kang

2. Statspack을 이용한 성능 데이터 수집 • 성능 데이터 수집 • 시스템의 비효율을 진단하기 위한 첫 번째 단계 • 성능 데이터 수집의 의미 • 대상 시스템 선정 • 시스템 진단을 위한 기초 데이터 수집 • 시스템의 비효율 검색 • 튜닝 계획서 작성을 위한 기초 자료 수집 • Statspack • Oracle 자체에서 제공하는 데이터 수집 Tool • 실행 시 데이터베이스 성능에 대한 Snapshot 저장 • Time Interval을 이용한 구간별 성능 데이터 생성 • 확인 가능한 성능 데이터 • 데이터베이스 일반정보, CPU 사용 분석 데이터, 비효율 Query, IO Cost 분석 데이터, Wait Event 데이터, Physical Read 정보, File I/O 현황, Latch 성능 데이터, 전체 시스템 진단 등

3. Statspack실행 및 수행 • 사전 확인 사항 • Timed_statistics_parameter설정 • 성능 데이터 수집 시 시간 정보를 추가 • 시스템 부하 확인 • Statspack설치 • Perfstat DB User 생성 • Table 및 Synonym 생성 • Package 생성 • 성능 데이터 수집 계획 수립 • 성능 데이터 수집 시간 • 성능 데이터 수집 회수 SQL> connect / as sysdba SQL> @%ORACLE_HOME%\rdbms\admin\spcreate

4. Statspack실행 및 수행 (cont.) • Statspack수행 • Perfstat User로 Login • Statspack수행 Package 실행 • Statspack Level • 추출하는 성능 데이터 항목 결정 SQL> CONNECT PERFSTAT/PERFSTAT SQL> EXECUTE STATSPACK.SNAP(i_snap_level=>10);

5. Statspack분석 • Statspack분석 시 고려사항 • 성능 데이터 분석의 중요성 • 객관적 성능 데이터 추출 • 튜닝 계획서 도출 • Statspack으로 수집되는 일반 정보 항목 • 성능 데이터 수집 기간 (BeginTime, EndTime) • Response Time 정보 (ResponseTime = CPUTime+WaitTime) • CPU 사용 정보 • 비효율 SQL 정보 • I/O Cost 정보 • Wait Event 정보 • Physical Read가 많은 SQL 정보 • File I/O 정보 • Latch 정보 Disk I/ODependent

6. Statspack분석 • CPU 사용 정보 • 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 • DML/Application 튜닝 • 일반적 경우 1회 SQL 수행시1000 Block 내외를 Access해야 함

7. Statspack분석 (cont.) • 비효율 SQL • 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

8. Statspack분석 (cont.) • I/O Cost • 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

9. Statspack분석 (cont.) • WaitEvent • db filesequential 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 감소

10. Statspack분석 (cont.) • High Physical Read SQL • Executes : 성능 데이터 수집 시간 중 SQL 수행 횟수 • Reads / Execute : 1회 실행 시 평균 Block Read 수 • Critical Access Path • 표에서 3번째 항목 • Critical Access Path이면서 Physical Read가 많으면 튜닝 대상이 됨 • 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

11. Statspack분석 (cont.) • File I/O • File I/O가 많은 Datafile • File I/O가 높은 Datafile이 동일한 Disk에 존재하는 지 확인 • 서로다른 Physical Disk에 존재해야 함 (Disk I/O 분산) • card02, card01, card03.dbf

12. Statspack분석 (cont.) • Latch 정보 • 동일 자원에 대한 동시 Access를 관리하는 제어 구조 • 특정 자원에 대한 접근을 serialize하는 것이 목적 • 대부분의 Process가 빠르게 exclusive 모드를 획득하고 해제함 • Gets : 성능 데이터 수집 시간 중 Latch 요청 횟수 • Percentage : (LatchSleep / 전체 Sleep) * 100 • 성능에 영향을 주는 요소 : Latch Sleep 횟수 • Cache Buffer Chain Tuning • Database Buffer Cache 확대, Hidden Parameter 이용 • SQL 튜닝을 통한 비효율 제거 • Disk I/O 감소

13. Statspack분석 (cont.) • 전체 시스템 진단 • 성능 데이터 리포트의 마지막에 제공 • 튜닝 요소별 중요도 제공 • Maximum Gain • 해당 사항을 Tuning했을 때 얻을 수 있는 상대적 효과

14. 실시간 조회를 이용한 성능 데이터 수집 • 실시간 조회를 통해 시스템의 현 상태를 바로 확인 • 데이터딕셔너리뷰 • 데이터베이스의 모든 상태를 저장하고 있는 내부 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 정보

15. 성능 데이터 조회 • 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 증가

16. 성능 데이터 조회 (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 분산

17. 자원 사용 현황 수집 • 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 튜닝을 통한 기존 구문 분석 정보 이용

18. 참고 : Oracle 11g • Oracle 11g • Statspack을 지원하나 추천하지 않음 (deprecated) • Oracle Enterprise Manager – Database Control • 스냅샷 저장 및 세부 정보, 리포트 제공 • Statspack에서 제공하는 모든 기능 제공 + 부가 정보 • Oracle ADDM • Automatic Database Diagnostic Monitor • Oracle 10g에서 새롭게 추가된 기능 • 데이터베이스 성능 통계 제공, 성능 병목 구간 확인 • SQL 구문에 대한 분석 및 성능 향상을 위한 조언 제공

19. Oracle 11g - Enterprise Manager

20. Oracle 11g –Snapshot Information

21. Oracle 11g – Workload Repository report

22. Oracle 11g – Advisor Central

23. Oracle 11g – Advisor Recommendation • http://www.oracle.com/technology/oramag/oracle/04-may/o34tech_talking.html • http://www.oracle.com/technology/global/kr/pub/articles/10gdba/week18_10gdba.html