[SQLP] 아키텍처 기반 튜닝원리

제 1절 데이터베이스 아키텍처

• 점점 초대용량화돼 가는 데이터베이스 환경에서 DBMS 내부 아키텍처와 SQL 수행원리에 대한 이해는 필수적이다.

• 서버 프로세스는 사용자 프로세스와 통신하면서 사용자로부터의 각종 명령을 처리한다.

• 백그라운드 프로세스는 Dirty 버퍼와 로그버퍼를 디스크에 기록하고 인스턴스 및 프로세스를 복구하는 등 각
  프로세스 별로 주어진 역할을 수행한다.

• 주요 파일구조는 데이터파일, 임시데이터 파일, 로그파일로 나뉠 수 있다.

• 메모리 구조는 시스템 공유 메모리(SGA) 와 프로세스 전용 메모리로 나뉘게 된다.

• 시스템 공유메모리의 3대 캐시 영역, 즉 Data 캐시, Code 캐시, Log 캐시를 중심으로 데이터베이스 성능 고도화
  핵심원리를 설명할 수 있고, 데이터베이스 Call을 통해 이루어지는 작업요청 횟수를 최소화 하는 것도 주요
  성능원리 중 하나이다.

 

제 2절 SQL 파싱부하

• 무거운 파싱 과정을 거친 SQL과 실행계획은 여러 사용자가 공유하면서 재사용 할 수 있도록 공유 메모리에
  캐싱해 둔다.

• 공유메모리에서 SQL을 찾기위핼 사용되는 키 값은 SQL 문장 그자체 이다. 따라서 중간에 작은 글자 하나만 달라도 DBMS는 서로 다른 SQL로 인식해 실행계획을 새로 생성한다.

• 조건절이 동적으로 생성되는 리터럴(Literal) SQL로 프로그램을 작성하면 새로운 값이 입력 될 때 마다 하드파싱을 일으켜 시스템 부하를 가중 시킨다.

• OLTP 환경에서 SQL 파싱 부하를 최소화 하려면 반드시 바인드 변수를 사용해야 한다.

• 바인드변수를 사용하면 옵티마이저가 컬럼 히스토그램을 활용하지 못하는 단점이 있어값의 분포가 균일하지 않은 컬럼을 조건절에서 사용할 때는 UNION ALL을 이용해 실행계획을 분기하는 방안을 고려해야 한다.

 

제 3절 데이터베이스 Call과 네트워크 부하

• 데이터베이스 Call과 결과집합 전송은 네트워크를 통해 이루어지며 서버와의 Roundtrip 횟수가 많을수록 쿼리
  수행속도가 떨어지는 것은 당연하다. 따라서 데이터베이스 Call 종류와 특성을 잘 이해함으로써 그 횟수를 줄이도록 노력해야한다.

• 루프를 돌면서 여러작업을 반복 수행하는 프로그램을 One SQL로 구현하면 큰 성능 개선효과를 얻을 수 있으며,
  그 핵심원리는 데이터베이스 Call을 줄이는데에 있다.

• Array Processing을 잘 활용하면 데이터베이스 Call횟수를 크게 줄여줘 One SQL로 통합할때와 비슷한 수준의
  성능효과를 얻을 수 있다.

제 4절 데이터베이스 I/O 원리

• 모든 DBMS에서 I/O는 블록(=페이지) 단위로 이루어진다.

• 물리적으로 한정된 시스템 자원을 효율적으로 사용하기 위해 디스크 I/O를 최소화하고 버퍼캐시 효율을 높이는
  것이 I/O 튜닝의 목표다.

• 네트워크 문제이든, 파일시스템 문제이든 I/O 성능에 관한 가장 확실하고 근본적인 해결책은 논리적인 블록 요청
  횟수를 최소화하는 것이다.

• 논리적인 I/O 요청횟수를 최소화하는 것이 I/O 효율화 튜닝의 핵심 원리다. 그러기 우해서는 필요한 최소 블럭만
  읽도록 SQL을 작성하는 것이 무엇보다 중요하다. 옵티마이저의 능력을 최대한 끌어올리기 위해서는 그만한
  옵티마이징 팩터를 제공해야 하며, 옵티마이저 힌트를 이용해 사용자가 직접 최적의 엑세스 경로로
  유도해야 할 때도 있다.

-끝-