I/O SQL튜닝
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 I/O SQL튜닝[편집]
1.1 db file sequential read[편집]
- db file sequential read 대기 이벤트는 싱글 블록 I/O 수행 시에 발생하는 대기 이벤트
- 한번의 싱글 블록 I/O가 발생할 때마다 한 번의 db file sequential read이벤트 대기가 발생
- 싱글 블록 I/O는 파일로부터 하나의 블록을 읽는 모든 작업들에서 발생 가능하며 일반적으로 인데스 스캔 및 ROWID에 의한 테이블 액세스 시에 발생
1.1.1 개선 방법[편집]
- 원인
- 비효율적인 인덱스 스캔으로 인한 과다한 싱글 블록 I/O
- 진단 방법
- SQL 실행 계획 확인(DBMS_XPLAN 패키지를 이용)
- 인덱스 Clustering Factor 확인
- 개선 방법
- SQL 튜닝, 인덱스 Clustering Factor
- 원인
- Row changing 및 Row migration에 의한 추가적인 싱글 블록 I/O
- 진단 방법
- ANALYZE TABLE 수행
- V$SYSSTAT 및 V$SESSTAT의 table fetch continued row 값의 증가 여부 확인
- 개선 방법
- PCTFREE를 작게 해서 테이블 재생성, 더 큰 블록 크기를 이용해서 테이블 재생성
- 원인: 선택도(Selectivity)가 좋지 않은 인덱스 사용
- 진단 방법: 실행계획 확인
- 개선 방법: SQL 튜닝, 사용인덱스 변경 또는 신규인덱스 생성
1.2 db file scattered read[편집]
- db file scattered read 대기 이벤트는 멀티 블록 I/O시 발생하는 대기 이벤트
- Full Table Scan 및 Index Fast Full Scan을 수행하는 경우, 성능 향상을 위해서 여러 개의 블록을 한번에 읽는 멀티블록 I/O를 수행
- 멀티 블록 I/O를 한번 수행할 때마다 Physicial I/O가 끝나기를 기다리게 되며, db file scattered read 이벤트를 대기
- 멀티 블록 I/O는 OS마다 최대값에 한계가 있으며, DB_FILE_MULTIBLOCK_READ_COUNT 파라미터로 지정된 값만큼 수행
- 일반 문제상황 및 개선방법
- 원인: 비효율적인 Full Table Scan으로 인한 과도한 멀티 블록 I/O - 진단 방법: SQL 실행 계획 확인(DBMS_XPLAN 패키지를 이용) - 개선 방법: 파티션 적용, 인덱스 생성, KEEP BUFFER 적용, DB_FILE_MULTIBLOCK_READ_COUNT(MBRC) 설정 값 증가, 큰 사이즈의 블록 사용
- 원인: 버퍼 캐시의 크기가 지나치게 작아 반복적으로 Physical I/O가 발생 - 진단 방법: SQL 실행 계획 확인(DBMS_XPLAN 패키지를 이용) - 개선 방법: 다중 버퍼 풀 사용, 버퍼 캐시의 최적화
- 원인: 불필요한 Index Fast Full Scan 사용으로 인한 과도한 멀티 블록 I/O - 진단 방법: SQL 실행계획 확인(DBMS_XPLAN 패키지를 이용) - 개선 방법: SQL문 튜닝