1. SQL 튜닝을 위한 선결 과제
(물리적인 환경)
1) 업무에 적당한 H/W(시스템 설계자)
구현될 업무가 운영되기에 충분한 용량의 서버 용량 산정 및 환경 구축
CPU, Memory, Network 등
2) 오라클 서버 튜닝(데이터베이스 관리자)
SQL 특성에 맞도록 오라클 서버튜닝
데이터 베이스 메모리 및 I/O 튜닝
3) 운영 체제 튜닝(운영 체제 관리자)
오라클 서버가 운용되는 데 필요한 기본적인 리소스 파라미터 튜닝
(설계 중의 과제)
4) 업무 기능 분석(업무 분석가)
목표로 하는 업무의 범위 선정
명확한 업무 분석 및 설계
5) 데이터 모델 설계(설계자)
업무를 가장 잘 표현하는 단순 명료한 데이터 모델 필요
- 명확한 업무 분석이 선결 과제
- 업무의 범위의 명확한 구분
2. SQL 튜닝 기술이 필요한 사람
1) 설계자
SQL 튜닝 방법을 알고 있는 설계자가 쉽게 튜닝 할 수 있는 스키마 설계 가능
2) 응용 프로그램 개발자
SQL 튜닝은 물론 물리적 구조 튜닝 병행
성능 좋은 응용 프로그램 개발
3) 데이터베이스 관리자
SQL 튜닝 요구 및 해결 방법을 정확히 숙지
3. 튜닝에 필요한 파일들
Oracle Alert and Trace Files
a. Alert log file : db 상황, Oracle Instance가 수행시 발생하는 error등을 기록함
파라미터 파일의 “background_dump_dest = ” 로 설정함
nomount 단계에서 기록을 시작함.
b. Background Process trace file : LGWR,DBWR,SMON,PMON,CKPT....
(문제시마다 생성되므로 여러개 생성됨)
파라미터 파일의 “background_dump_dest = ” 로 설정함
파일이름을 보면 어떤 프로세스인지 알수 있음.
c. User trace file : user request에 의해서 생성됨.
(여러개 생성됨)
파라미터 파일의 “user_dump_dest= " 로 설정함
4. 일반적인 튜닝 과정
1) 원하는 목표치 까지 반복적으로 튜닝
원인분석(부하), 조치(서버/SQL 튜닝), 점검(부하)
튜닝 후에도, 시스템 상황은 항상 변화한다.
- 튜닝 후에도 예측치 못한 튜닝 포인트는 계속 발생
- 시간이 지나면 사용자 및 서버 데이터 량 증가
5. SQL 튜닝의 3가지 접근 방법
1) 부하의 감소
동일한 부하를 보다 효율적인 방법으로 수행
일반적인 SQL Tuning
2) 부하의 조정
부하 정도에 따라 업무 조정
- 배치, 리포트 업무 등과 일반 업무(OLTP)를 분리
- 응용 프로그램별, 시간대별
3) 부하의 병렬 수행
병렬로 수행하여 응답 시간을 크게 단축
과도한 사용은 일반 사용자에게 악 영향 가능성
주로 배치 업무에 많이 사용됨
6. SQL 튜닝 실패
1) SQL 튜닝을 해도 호전 되지 않는 경우
시스템 사이징 실패
잘 못된 데이터 모델링
비 효율적인 스키마 및 프로세스 흐름
개발 중에 모델 및 스키마의 변경
프로젝트 관리 미숙 및 과도한 요구
업무 진행 중 SQL 튜닝 마인드 부재
7. SQL 튜닝 실패 대책
1) 최악의 경우, 시스템 확장이 필요
부하 분산
고 비용 응용 프로그램 제외
CPU, 메모리, N/W 및 서버 확장
서버 병렬화
- 응용 프로그램 별로 서버 분리
실현 불가능한 상황이 많음
- RAC(Real Application Cluster)
동일한 DB 작업을 여러 개의 노드에서 동시 작업
8 . 요약
1) SQL 튜닝은 개발 과정 상의 문제 해결 방법
동일 부하를 효율적인 방법으로 변환
부하의 대부분 원인은 비 효율적인 개발
2) 업무 설계, 모델링이 잘못 되면 튜닝은 무의미
측정 가능한 목표 설정
설계에서 미리 효율적인 시스템 고려
부적절한 리소스, 과도한 요구 배제
개발 시작 이후, 뒤로 돌아가는 것은 금물
SQL 처리 구조
Oracle Server = Instance + Database
Instance = SGA Memory + Background Processes
Oracle Process = Background Process + Server Process
Server Process = Dedicated or Shared (MTS)
Database = Data file + Redo log file + Control file + Parameter file
SGA Memory = Shared Pool + Buffer Cache + Log buffer
Shared Pool = Library Cache + Dictionary Cache + ……
1. Shared Pool
1) SQL 수행 시 자주 사용되는 정보들을 공유
Library Cache : SQL 및 PL/SQL 공유
Row Cache : SQL 연산에 참조되는 각종 딕셔너리 정보 공유
2. Shared SQL Area
1) SQL 문장을 실행하기 위해 그 문장과 관련된 실행 계획과 구문분석 정보 보관
2) 동일한 SQL 문장은 서로 공유된다.
구문 분석 시간 감소/메모리 공유
3. PGA (프로세스 글로벌 영역)
1) 서버 프로세스의 데이터와 제어 정보를 위해 할당
서버 프로세스 구동 위한 영역(스택 등)
사용자 작업 영역(UGA)
4. SQL Execution Plan/Parse Tree
1) 어떤 방식으로 SQL 문을 실행 할 것인지 정해 놓은 계획(구문 분석 단계)
2) 최소한의 비용이 들도록 SQL 최적기가 작성
3) 주변 환경을 고려하여 실행 경로 판단
테이블 인덱스를 사용할 것인가?
한 테이블에 인덱스가 여러 개인 겨우 어떤 것을 사용할 것인가?
두 테이블간에 어떤 조인을 수행할 것인가?
테이블 간의 조인을 어떤 순서로 할 것인가?
사용자의 힌트를 어떻게 처리할 것인가?
5. 구문 분석
1) SQL 문장을 분석(PARSE)하여 실행 계획 작성
2) Shared SQL 영역의 동일한 문장 재 사용(SQL Get Hit)
3) 커서 공유 실패 시, 새로 구문 분석
Syntax 검사(SQL 문장이 문법에 맞는 지)
Semantic 검사(의미 및 권한 검사)
- SQL 문장에 대한 실행 권한 조사
- SQL 문에서 참조된 객체에 대한
유효성, 보안 제약 조건 등을 검사
- Data Dictionary 정보 참조(Recursive SQL)
Query Rewrite
- 뷰 및 서브 쿼리 등을 메인 SQL 블록에 적용
실행 계획 결정 및 저장
6. 커서의 공유
1) 이미 수립된 실행 계획을 공유해서 재 사용
구문 분석 비용 감소
메모리 사용 개선
2) 동일한 SQL 문장 이란?
텍스트 동일(대소문자 구분, 빈 칸, 주석 포함)
사용자 및 참조 객체 동일
바인드 변수의 데이터 형식 동일
3) SQL 형식 표준을 정하여 코딩
대소문자, 빈 칸, 주석 사용 규칙
- 아예 SQL에 주석 사용 금지 등
SQL 문장을 동일한 위치에 따로 관리
자주 쓰는 것은 PL/SQL로 대치
7. Recursive SQL
1) 단일 SQL 문장이라도 파생적인 SQL 실행 발생
2) SQL 문장 수행 중에 내부적으로 수행 되는 SQL
SQL 구문 분석 단계
- SQL 문장의 semantic 검증
- Data Dictionary 정보 질의
DDL 문장 수행 중
- 데이터 변경에 의해 DB 구조 변경
- Data Dictionary 정보 변경
3) Sub Query, View
일부 Sub Query나 View의 경우 별도의 SQL 블록이 사용될 수 있음.
