OVERVIEW

---

업무과정에서 오라클 최적화를 위한 튜닝 공부를 시작했다. 현재 개인적으로 진행하는 딥러닝 스터디와는 관련 전혀 없다. 기록으로 남기고자 이렇게 포스팅을 하고자 한다.
참조하고 있는 도서는 아래와 같다.

친절한 sql 튜닝

이 포스팅 시리즈에서는 각 용어들 별로 예제와 자세한 설명을 최소화 하고자 한다. 읽어보고 이해가 안 가거나 중요한 부분은 구글링을 통해 습득하는 것이 더 자세함과 동시에 시간낭비도 적을 것으로 생각한다.

sql 파싱과 최적화

데이터베이스를 사용하는 사람들은 JPA, 하이버네이트, JDBC 등의 SQL 라이브러리(?) 를 사용하지 않는 경우에 SQL 을 사용하게 될 것이다. 서비스가 커질수록 단순히 결과만을 보는 것이 중요한 것이 아니라, 결과적으로 성능 최적화를 통해 빠른 응답을 받고 싶을 것이다. 이번 포스팅 시리즈를 통해 해당 사항에 조금이라도 도움이 되길 바란다.

구조적, 집합적, 선언적 질의 언어인 sql

• SQL 은 Structured Query Language 의 줄임말이다.
• structured + set based 가 핵심 키워드이다.
• sql을 사용하면서 절차적인 수행이 진행된다.
• 우리가 쿼리를 작성하면, sql 옵티마이저가 자체적으로 최적의 성능을 위해 프로그래밍을 대신해준다.
• 위 과정을 sql 최적화 라고 한다.

sql 최적화

• 순서는 아래와 같다.

1. sql 파싱

   • 사용자가 작성한 쿼리문을 파싱을 진행한다.
   • 파싱 트리를 생성한다. 즉 개별 구성요소를 확인한다.
   • 문법 오류를 체크한다.
   • 의미상 오류를 체크한다. 예를들면 없는 테이블이라던지..

2. 최적화 진행

   • 실행계획을 작성한다.
   • 옵티마이저가 어떻게하면 가장 효율적으로 결과물을 반환할 수 있는지 자체적인 최적화를 진행한다.

3. Raw Source 생성

   • 실행가능한 소스, 프로시저형태로 변환해서 실행한다.

sql 옵티마이저

• 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획을 찾아본다
• 데이터 딕셔너리에 수집해둔 오브젝트 통계 및 시스템 통계 정보를 이용해서 예상비용을 확인한다.
• 최저 비용의 실행계획을 실행한다.

실행계획과 비용

• 자동차 네비게이션, 또는 네이버 지도의 최단거리 경로찾기를 생각해보면 이해가 쉽다.
• 데이터베이스에는 실행계획 이라는 것을 확인할 수 있다.
• 미리보기 기능을 통해서 어떠한 순서대로 쿼리를 실행하는지 확인해보고 최적화를 진행할 수 있다.

옵티마이저 힌트

• 쿼리단계에서 옵티마이저를 실행하기전에 사용자는 힌트를 줄 수 있다.
• 예를들면 인덱스는 어떠한 칼럼을 확인하고 나머지는 알아서 판단해! 라는 명령어를 실행시킬 수 있다.
• 최단경로에서 중간점을 사용자가 지시할 수 있다고 생각하면 이해가 쉽다.

sql 공유 및 재사용

소프트 파싱과 하드 파싱

• sql파싱, 최적화, 로우소스 생성과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있게 캐싱해두는 메모리 공간을 라이브러리 캐시 라고 한다.

• 라이브러리 캐시는 System Global Area의 구성요소이다.

• 사용자가 쿼리를 실행시키면 캐시에 해당 내부 프로시저가 있는지(최적화된..) 확인해서 있다면 실행시키는 것을 soft parsing이라 한다.

• 반대로, 존재하지 않아서 결국 데이터베이스 최적화를 진행하고 SGA에 저장하는 파싱을 Hard parsing 이라고 한다.

• 이런식으로 캐시를 활용하는 이유는 데이터베이스는 아래와 같은 여러가지 고려사항을 토대로 최적화를 진행하기 때문이다.

  1. 테이블, 컬럼, 인덱스 구조의 기본 데이터구조
  2. 테이블통계, 인덱스 통계, 컬럼 통계
  3. 시스템 통계, cpu나 메모리, Singleblock i/o, multiblock i/o...
  4. 옵티마이저를 위한 내부 변수들..

• 하나의 쿼리를 실행할때마다 고려할 사항이 많다보니, 매번 하드파싱을 한다는 것은 성능면에서 이점이 전혀 없다!!!

데이터 저장의 구조 및 I/O 메커니즘

• 결국 I/O 튜닝이 sql 튜닝이라는 것이 이 책의 핵심이다.
• 엄밀히 말하면 Random I/O 성능을 개선하는것이 이 책의 목적인데, 이 후 포스팅에서 자세하게 이해할 수 있을 것 같다.

sql 이 느린 이유

• 결론부터 말하면 디스크 I/O 때문에 느려진다.
• I/O란 잠을 자는 것으로 이해하면 좋다.
• OS가 디스크에 접근하는 순간 프로세스는 휴식에 취한다(BLOCKING)
• I/O를 통해 디스크의 블록을 읽는데는 대충 10ms가 걸린다고 한다.
• 즉 1초에 100개의 블록에 접근할 수 있다...
• sql 이 느린 이유는 여기에 있다.
• 이 후에 나오겟지만 이것을 병렬적으로 한번에 가져오는 것이 multiblock I/O이다.

데이터 베이스의 저장 구조

• 테이블 스페이스 -> 세그먼트 -> 익스텐트 -> 블록 의 단계로 테이블 저장이 구성되어 있다.
• 블록 : 데이터를 읽고 쓰는 단위
• 익스텐트 : 여러 블록으로 구성
• 세그먼트 : 데이터 저장공간이 필요한 오브젝트 (테이블, 파티션, 인덱스..)
• 테이블 스페이스 : 세그먼트를 담는 콘테이너
• 단순히 사람이 관리하기 위해 여러 명칭으로 분리해놓음과 동시에 블락단위로 저장하는 것을 관리하기 위한 오브젝트로 이해하면 될 것 같다.

시퀀셜 액세스와 랜덤 액세스

• 시퀀셜 액세스 : 말 그대로 순차적으로 접근하는 것을 말한다. 인덱스 리프 노드들 또는 물리적으로 저장된 블록을 주소 순서대로 읽는 것
• 랜덤 액세스 : 인덱스 리프노드 -> 물리적 저장소 와 같이 데이터를 위해서 한 개의 블록을 접근하는 것을 말한다.
• 뒤에 나오겟지만 랜덤액세스를 줄이기 위해 시퀀셜 액세스(풀스캔) 을 활용하는 것이 성능 향상에 도움이 되는 경우가 꽤 있다고 한다.

논리적 I/O 와 물리 I/O

• 논리적 I/O는 빠른 프로시저 실행을 위해 캐시 버퍼를 활용하는 접근(SGA)를 의미한다.
• 물리적 I/O는 실제 디스크의 블록에 접근하는 것을 말한다.
• 버퍼캐시 히트율이란 공식을 토대로 옵티마이저 정도를 표현하기도 한다. 자세한 것을 블로그를 찾아보자..

single block I/O vs Multi block I/O

• 물리적 I/O를 한 번에 하나씩 하는지, 여러개를 하는지에 따라 2가지 용어를 사용한다.

Table Full scan vs Index Range Scan

• table full scan은 Multiblock I/O를 사용한다.
• INDEX를 사용하는 경우에는 Singleblock I/O를 사용한다.
• 따라서 여러 정보를 찾는경우 인덱스 스캔이 더 안 좋을 수 있다.
• JPA에서 N+1 문제와 느낌은 비슷하지만, DISK I/O관점이라는 것이 차이점이다.