[Sql 튜닝] SQL 처리과정과 IO

1. sql 파싱과 최적화

sql 은 기본적으로 선언형, 구조적 언어이다. 집합-베이스이기도 하다.
sql 문법을 보면 ~ 어떤 조건을 가지고(where) 어떤 테이블에서 (from) 데이터를 조회하라 (select) 하라는 선언적인 문법인데, 사실 이렇게 작성하더라도 내부적으로는 절차적으로 진행될 수 밖에 없다. 따라서 sql을 내부적으로 로우 소스로 변환하는 과정이 반드시 필한데, 그러한 프로시저를 만드는 dbms 내부의 엔진을 sql 옵티마이저라고 부른다.

그리고 dbms 내부에서 프로시저를 작성하고 컴파일해서 실행가능한 상태로 만드는 전 과정을, 'sql 최적화'라고 부른다.

최적화 과정은 다음과 같다.

최적화 과정

1. sql 파싱
   1. 파싱 트리 생성: 기본적으로 sql 언어를 쪼개서 트리를 만든다.
   2. 문법 체크 (syntax) : 문법적으로 오류가 없는지를 확인한다.
   3. semantic 체크 : 의미를 체크한다. 존재하지 않는 테이블을 조회하지는 않는지, 권한이 없는 테이블을 조회하지는 않는지 등을 본다.
2. sql 최적화
   1. sql 옵티마이저가 다양한 쿼리 실행 계획을 가지고, 각 비용들 중 최소 비용의 쿼리 실행계획을 선택한다.
   2. 마치 네비게이션이 목적지로 가는 다양한 경로 중에 최소 비용이 드는 곳을 택하는 것과 비슷한 방식이다.
   3. 이때, 실제 동네 운전자는 네비가 추천해주는 길보다 더 빠른 지름길을 알고 있을 때도 있는 것처럼, sql 옵티마이저가 추측한 비용은 실제와 다를 수도 있고 더 최적화할 수 있는 여지가 있다. 따라서 sql 옵티마이저가 실행하려는 쿼리 실행계획을 미리 살펴보고, 다른 방식으로 실행하도록 바꿀 수도 있다.
3. 로우 소스 생성
   1. Row-source generator 가 sql 옵티마이저가 선택한 쿼리실행계획을 실제로 실행가능한 코드 혹은 프로시저 형태로 포맷팅한다.

sql 옵티마이저

• 옵티마이저 힌트
  • 옵티마이저가 사용하려는 인덱스와 다른 인덱스를 가지고 쿼리를 실행하도록 명령할 수 있다.
  • SELECT / *+ INDEX(A 고객 _PK) */ 고객명/ 연락처/주소/ 가입일시 FROM 고객 A WHERE 고객 10 = ’000000008’
  • 위처럼 주석표시에 +를 붙이면 된다.
  • 힌트 사용할 때 주의할 점
    • 1. alias 사용한 경우 힌트에도 똑같이 사용해야 한다.
      2. 힌트 사이에는 콤마를 사용하면 안된다. 힌트안의 인자 사이에만 콤마가 존재할 수 있다.
      3. /*+ INDEX( A A _X01) INDEX( B, B_X03) */ • 모두 유효 /*+ INDEX(C), FULL (D) */ • 첫 번째 힌트만 유효
      4. 쿼리 힌트의 예시 : INDEX : 인덱스 스캔 유도, INDEX DESC: 인덱스 역방향 스캔 유도

1.2 sql 공유 및 재사용

소프트 파싱 vs 하드 파싱

• 내부 최적화 과정의 복잡성을 이해함으로써, 동시성이 높은 온라인 트랜잭션 처리 시스템에서 바인딩 변수의 중요성을 깨달을 수 있다.

1. Sql 최적화 과정을 거쳐서 생성한 실행가능한 코드 혹은 작성된 프로시저는 system global area(SGA) 안의 라이브러리 캐시에 저장된다.

   1. system global area 는 백그라운드 프로세스와 서버 프로세스가 공통으로 엑세스하는 데이터 구조 및 제어구조를 저장하는 메모리 공간이다.

   

2. 사용자가 sql 파싱 요청을 하면, sql 파싱을 한 후, 먼저 라이브러리 캐시에서 해당 sql 내용이 저장되어있는지 확인한다. 있으면 결과를 사용하고, 이 방식이 소프트 파싱이 된다.
3. 라이브러리 캐싱이 되지 않아서 최적화, 로우생성까지 거치는 방식을 하드 파싱이라고 한다.
   1. 하드 파싱을 하는 과정은 생각보다 무거운 과정이다. join 실행 방식도 여러가지고, 테이블 전체를 스캔할지, 인덱스를 활용할지, 활용한다면 어떤 방식으로 스캔할지도 결정해야하기 때문이다.
   2. 옵티마이저가 비용을 산출하기 위해 사용하는 데이터는 다음과 같다.
      • 테이블, 칼럼， 인텍스 구조에 관한 기본 정보
      • 오브젝트 통계 : 태이블 통계， 인텍스 통계， (히스토그램을 포함한) 컬럼 통계
      • 시스템 통계 : CPU 속도. Single Block I/O 속도， Multiblock 1/O 속도 등
      • 옵티마이저 관련 피라미터
      이러한 과정을 거치기 때문에, 프로시저를 만들고 나서 캐시를 사용하는 것이 매우 중요하다고 할 수 있다.
4. SQL 자체가 이름이기 때문에 텍스트중 작은 부분이라도 수정되면 그 순간 다른 객체가 새로 탄생히는 구조를 갖고 있다.
   1. 시용자 정의 함수/프로시저는 생성할 때부터 영구적으로 이름을 가져서 라이브러리 캐시에 저장되고 여러 사용자가 공유해서 사용할 수 있는 반면, sql 은 그렇지 않다.
   2. 그러한 dbms 도 존재하지만, orcle, SQL Server 는 그런 방식을 택하지 않고 있는데, 일회성 sql 도 많고 무효화된 sql 등이 많기 때문에, 굳이 모두 저장하는 경우 캐시에서 찾는 속도고 매우 느리고 메모리도 많이 잡아먹을 것이기 때문에 그러하다.
5. sql 자체가 캐시의 키로 사용된다는 점은, 반대로 이야기하면 소문자 하나, where 절의 조건 하나만 달라져도 서로 다른 sql 이 된다는 것을 의미한다. 따라서 이전에 말한 것처럼 트랜잭션이 자주 일어나는 상황에서 서로 다른 닉네임이 where 절에 포함된다면, 서로 다른 sql 로 인식되어서 하드 파싱이 계속 일어나고 있음을 의미한다.
6. 이런 경우 로그인 id 를 파라미터로 받는 프로시저를 하나 생성해서, 모든 사용자가 하나의 프로시저를 재사용하도록 하는 것이 바람직하다.
7. SELECT * FROM CUSTOMER WHERE LOG1N 10 = :1
8. create procedure LOGIN Oogin_id in varchar2) { ... }

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

• sql 이 느린 이유는 대부분 디스크 I/O 때문이다. I/O 작업이 일어나는 동안 프로세스는 보통 결과를 기다리며 잠을 잔다.
• I/O Call 속도는 Single Block I/O 기준으로 평균 10ms쯤 된다. 초당 100블록쯤 읽는다. ssd 까지 활용한다면 12ms 까지 가기도한다.(초당 5001000ms)
• 데이터의 저장 구조를 살펴보자.
  • 여러 물리적인 OS 파일로 구성된 데이터 파일을 담는 컨테이너인 '데이터 스페이스' 가 데이터 테이블의 구성요소이다.
• 테이블 스페이스 안은 세그먼트로 나뉘고, 각 데이터는 블록 단위로, 테이블 정보가 기록이 되는데, 블록들의 집합은 익스텐트라고 불리며 데이터의 확장이라고 생각하면 된다. 데이터를 저장하다가 블록의 양이 많아지면, 테이블 스페이스에서 익스텐트를 더 할당받아서 블록을 저장한다.
  • 익스텐트에 속한 단위들은 모두 같은 테이블에 해당하는 데이터이다.
  • 익스텐트 데이터는 보통 서로 다른 데이터 파일에 저장된다. 데이터 접근시 파일 경합을 줄이기 위해 데이터를 분산해서 저장하는 경향이 있기 때문이다.
  • 블록 데이터는 서로 연속되어 저장한다.

• 모든 데이터 블록은 디스크 상에서 몇 번 데이터파일 의 몇 번째 블록인지를 나타내는 자신만의 고유한 주소를 갖고 있다. 그 주소는 ‘DBA(Data Block Addess) ’라고 부른다.

• 테이블 칼럼 조회 시, 인덱스를 사용한다면 인덱스의 ROWID 값을 이용하면 되는데, 값을 분해해서 해당 테이블 칼럼의 DBA 를 알아내는 식이다.

• 테이블 전체를 스캔한다면, 테이블 세그먼트 헤더에 저장된 익스텐트 맵을 이용해, 첫번째 블록의 DBA 를 알아내서 수행한다.

• 

• 기본적으로 테이블, 인덱스 정보는 데이터베이스서버에 블록 단위로 저장된다.

• 시퀀셜 vs 랜덤 엑세스

  • 시퀀셜 엑세스 - 테이블
    • 테이블 스페이스 헤더의 익스텐트 맵에 대해 (가장 앞쪽 블록의 DBA 를 저장하고 있음. ), 각 저장된 DBA 로 접근해서 익스텐트 내의 블록 순서대로 읽는다. (블록은 연속으로 저장되어 있음. 한 익스텐트 내에는 같은 테이블 정보가 저장되어 있음. )
  • 랜덤 엑세스
    • 데이터에 접근할 때 물리적 / 논리적 순서를 따르지 않는 방식.

1.3.5. 논리적 I/O vs 물리적 I/O

DB 버퍼 캐시

• 힘들게 읽은 데이터를 캐싱해야 I/O 작업으로 인한 성능 저하를 최대한 줄일 수 있다. System global area 에 존재해서 같은 블록에 대한 반복적인 접근을 줄이는 데이터 캐시의 기능을 한다.
  논리적 IO
  • 메모리 버퍼 캐시에서 발생한 I/O 작업. (전기적 신호)
    물리적 IO
  • 디스크에서 발생한 I/O 작업. (물리적으로 직접 접근. 더 느리다. )
  • db 버퍼 캐시에 캐싱이 되었는지 확인하고, 캐싱이 안된 경우에 대해 디스크 접근을 하므로 논리적 I/O 접근을 먼저하고, 캐싱되어있지 않은 경우에 물리적 I/O 접근도 일어난다고 이해하면 된다.

버퍼캐시 히트율 = 캐시 히트율에 대해, 전체 I/O 를 줄일 수 있는 방법은 뭘까?
히트율 = (1 - 물리적 I/O / 논리적 I/O) * 100 이다.
물리적 I/O 가 디스크 접근 시간이 병목시간과 가장 밀접한 관련이 있기 때문에 이를 줄여야할 것 같지만,
캐시 히트율이 같다는 전제하에 논리적 I/O 를 줄이는 것도 물리적 I/O 를 줄이는데에 도움이 된다.

• 논리적 I/O는 일정하므로 물리적 I/O 는 BCHR에 의해 결정 된다.
  • 데이터 를 입력/수정/삭제하지 않는 상횡에서 조건절 에 같은 변수 값을 입력하면. 논리적 I/O는 항상 같다.
• 물리적 I/O 가 SQL 성능을 죄우한다고 했는데，BCHR이 SQL 성능을 좌우한다고도 해석 할 수 있다

논리적 I/O 는 어떻게 줄일 수 있을까? SQL을 튜닝해서 읽는 총 블록 개수를 줄이면 된다.

*논리적 I/O 는 항상 일정하게 발생하지만， SQL 튜닝을 통해 줄일 수 있는 통제 가능한 내생변수다. 논리적 I/O 를 줄임으로써 물리적 I/O 를 줄이는 것이 곧 SQL 튜닝이다. *

1.3.6.Single Block I/O vs Multi Block I/O

• 한번에 한 블록씩 메모리에 적재하는 방식 vs 한번에 여러 블록을 메모리에 적재하는 방식.
• 한 블록만 읽어도 되는 인덱스를 활용한 조회시 vs 전체를 스캔할 시에 유용.
• 오라클에서는 db_file_multiblock_read_count 파라미터로, multi block I/O 시에 읽어들일 양을 확인할 수 있다.
• 일반적으로 OS레벨 I/O 단위가 1MB, 오라클 레벨 I/O 단위가 8KB 이므로 이 파라미터를 128로 설정하면 담을 수 있는 만큼 최대한 담게 된다(8KB x 128 = 1MB)
• 한번 읽을 때 다른 익스텐트 범위까지 넘어서 읽지 않는다는 특징도 있다. (8개씩 읽는다고 했을 때, 익스텐트에 블록이 24개 있을 경우 8/8/4개를 읽는다. )

1.3.7 Table Full Scan vs . lndex Range Scan

• 테이블 전체를 스캔하느냐 vs 인덱스를 이용해서 조회하느냐.
• 많은 데이터를 읽을 때, 생각보다 인덱스를 활용하면 시간이 오래 걸린다.
• 테이블 스캔의 경우, multi block I/O 를 사용할 경우 한번의 I/O 작업에 대해 다량의 블록 정보를 가져올 수 있는 반면, 인덱스의 경우 한번에 하나의 블록 정보 밖에 못 가져온다. 그리고 매번 같은 블록을 여러번 다시 확인하게 된다.
• 따라서, 데이터의 용량이 일정용량을 넘으면 테이블 스캔 방식이 더 적절할 수 있다는 점을 기억하자.

1.3.8. 캐시 탐색 알고리즘

dbms 는 해시 구조로 db 버퍼 캐시 데이터를 관리한다.

• 해시 값이 같다면 늘 같은 해시 체인을 접근하게 하고, 해당하는 버퍼 헤더만 탐색하면 된다.
• 버퍼 헤더안의 데이터의 순서는 보장되지 않는다.

db 버퍼 캐시는 각 프로세스가 접근해야하는 공유자원이므로, 프로세스의 동시 접근을 막을 필요성이 있다.
이를 위해 '직렬화' 를 사용한다. 'Latch' 를 통해 이게 가능하게 한다.

프로세스 간의 동시 접근 막기 : 직렬화, Latch

캐시에서 해시 함수를 통해 버퍼 헤더를 탐색중일때, 다른 프로세스가 접근해서 구조를 변경하면 안된다. 그러한 상황을 막기 위해 Latch 가 존재한다.

-   system global area 에 존재하는 db 버퍼 캐시에 대한 프로세스간 동시접근을 막기 위해 존재하는 캐시 버퍼 체인 Latch, 캐시 버퍼 LRU 체인 Latch
-   블록 자체에도 존재하는 Latch

블록 자체에도 경합이 발생할 수 있기 때문에, 논리적 I/O 의 감소가 성능에서 중요해지는 이유가 추가된다.

*본 글은 sql 튜닝 책을 읽고 정리한 글입니다.