SQL 처리 과정과 I/O

SQL 처리 과정과 I/O

1.1 SQL 파싱과 최적화

• SQL은 구조적(structured)이고 집합적(set-based)이고 선언적(declarative)인 질의 언어이다.
• 우리는 SQL 쿼리를 한번에 '선언'하지만 결과를 만들어가는 과정은 '절차적'일 수 밖에 없다.
  • 이런 '절차'를 도와주는 것이 '프로시저'이고, 이 프로시저를 만들어 내는 DBMS 내부 엔진이 'SQL 옵티마이저'이다
  • DBMS 내부에서 프로시저를 작성하고 컴파일한 후 실행 가능한 상태로 만드는 전 과정이 'SQL 최적화이다.'
• SQL 최적화 과정
  • SQL 파싱
    • 사용자로부터 SQL 전달 -> SQL Parser가 파싱 진행(SQL 요소 분석 -> 문법 체크 -> 의미상 오류 체크)
    • (파싱 트리 생성, Syntax 체크, Semantic 체크)
• SQL 최적화
  • 옵티마이저 작동 단계 (자세한 내용은 뒤에)
• 로우 소스 생성
  • 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅
• SQL 옵티마이저
  • 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 액세스 경로를 선택해주는 DBMS 핵심 엔진
    • 즉, 쿼리 실행시 가장 효율적인 선택이 무엇인가 결정해주는 것
    • 무엇에 기반해서? -> 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 및 시스템 통계정보를 이용해 실행계획의 예상비용 산정
• 옵티마이저는 네비게이션과 같다.
  • 도착지를 선택하면 경로를 선택하고, 교통정보에 따라 코스가 달라질 수 있으며, 경유지를 추가하면 또 달라질 수 있듯이 말이다.
  • 그러나 네비게이션은 '예상'일 뿐이다. 실제로 차이가 생길 수 있다.

1.2 SQL 공유 및 재사용

• 고생고생해서 만든 프로시저는 휘발성이기때문에 반복 재사용할수 있도록 캐싱해 두는 메모리 공간은 라이브러리 캐시라고 한다.
  • 아래 사진에서 확인할 수 있듯, SGA 구성요소다.(System Global Area)
  • PGA는 Program Global Area(DB에 접속하는 모든 유저에게 할당되는 각각의 서버 프로세스가 독자적으로 사용하는 오라클 메모리 영역)

• 프로시저가 있다면 SQL은 번거롭게 최적화를 한번 더 거치지 않아도 되는데 이런 과정을 소프트 파싱, 반대의 경우를 하드파싱이라 한다.

• 최적화 과정 중 SQL 옵티마이저가 단서로 사용하는 것들
  • 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
  • 오브젝트 통계 : 테이블 통계, 인덱스 통계, 컬럼 통계
  • 시스템 통계 : CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
  • 옵티마이저 관련 파라미터
• 딕셔너리에 저장 될 수 있는 것
  • 사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖기 때문에 딕셔너리에 저장되어 계속해서 사용됨
  • 그러나 SQL문은 이름도 없고 형태도 다양하여 텍스트 자체가 이름 역할을 한다.
  • SELECT * FROM emp WHERE empno = 7900;
  • select * from EMP where EMPNO = 7900;
  • 의미가 같은 SQL문이지만 라이브러리 캐시에서 별도 공간을 사용함 SQL 텍스트 자체가 KEY값으로 저장되기 때문이다.
• 만약 동시간에대에 수만명의 사람이 회원가입을 한다고 생각해보자, 그럼 SQL은 내부에서 다음처럼 프로시저를 생성한다.
• create procedure LOGIN_ORAKING( ) { ... }
• create procedure LOGIN_JAVAKING( ) { ... }
• create procedure LOGIN_TOMMY( ) { ... }
• create procedure LOGIN_KARAJAN( ) { ... }
• 내부 처리 루틴은 모두 같으면 다음과 같은 로그인 ID를 파라미터로 받는 프로시저 하나를 공유하면서 재사용하는 것이 효율적이다.
  • 이처럼 파라미터 Driven 방식으로 SQL을 작성하는 방법이 바인드 변수를 사용한 방식이다.
    public void login(String login_id) throws Exception { String SQLStmt = "SELECT * FROM CUSTOMER WEHER LOGIN_ID = ?"; PreparedStatement st = con.prepareStatement(SQLStmt); st.setString(1, login_id); ResultSet rs = st.executeQuery(); if(rs.next()) { //do anything } rs.close(); st.close(); }
create procedure LOGIN (login_id in varchar2) { ... }

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

• IO 튜닝이 곧 SQL 튜닝이다.
• SQL이 느린 이유?
  • 십중팔구 I/O 때문이다. (구체적으로는 디스크 I/O)
  • I/O 처리중에 프로세스는 일을 하지 못한다.
  • 프로세스가 일을 해야하는데 I/O 요청이 많으면 처리가 느려지기 마련이다.

데이터베이스 저장 구조

• 저장단위
  • 테이블 스페이스 > 세그먼트(테이블) > 익스텐트(공간 단위) > 블록(SQL 쿼리단위)
  • 데이터 파일 : 디스크 상의 물리적인 OS 파일
  • 테이블 스페이스 : 세그먼트를 담는 콘테이너
  • 세그먼트 : 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스 , 파티션, LOB(Large Object) 등)
  • 익스텐트 : 공간을 확장하는 단위, 연속된 블록 집합
  • 블록 : 데이터를 읽고 쓰는 단위

 

 

시퀀셜 엑세스 vs 랜덤 엑세스

• 시퀀셜 엑세스
  • 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식 (링크드리스트 처럼)
• 랜덤 엑세스
  • 논리적, 물리적 순서를 따르지 않고 레코드 하나를 일기 위해 한 블록씩 접근하는 방식

 

 

논리적 I/O , 물리적 I/O

• DB 버퍼 캐시
  • I/O의 종류에 대해 말하기전에 위에서 SGA에 이야기 할 때의 사진을 보면 DB buffer cache가 있다.
  • 디스크에서 읽은 데이터 블록을 캐싱해두어서 반복적인 call을 줄일 수 있다.
  • 데이터 블록을 읽을 땐 버퍼캐시부터 먼저 탐색한다.
• 논리적 블록 I/O
  • SQL문을 처리하는 과정에서 메모리 버퍼캐시에서 발생한 총 블록 I/O
  • 전기적 신호
• 물리적 블록 I/O
  • 디스크에서 발생한 총 블록 I/O
  • SQL 처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할 때만 디스크를 엑세스하므로 논리적 블록 I/O중 일부를 물리적 I/O 한다.
  • 물리적 신호(논리적 블록 I/O보다 10,000배쯤 느림)
• BCHR (Buffer Cache Hit Raito), 버퍼캐시 히트
  • 곧바로 메모리에서 블록을 찾은 비율
• SQL 성능을 높이기 위해선 논리적 I/O를 줄여야한다.
  • SQL을 수행하면서 읽은 총 블록 I/O가 논리적 I/O이다.
  • 물리적 I/O를 낮추는 것도 방법이겠지만, 총 블록 수 자체를 낮춰야 성능이 높아진다.
• 논리적 I/O는 어떻게 줄일까?
  • SQL을 튜닝하여 읽는 총 블록 개수를 줄이면 된다.
  • 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다.

 

 

Single Block I/O vs MultiBlock I/O

• 그렇다면 SQL 성능을 높이려면 캐시 메모리양을 늘리면 되는거 아닌가?
  • 물리적인 한계가 당연히 존재한다.
  • 캐시에서 데이터 블록을 못찾으면 I/O콜을 통해 디스크에서 DB 버퍼캐시로 적재 후 읽는다.
  • 이 때 한번에 한 블록씩 요청하기도 하고 한 번에 여러 블록씩 요청하기도 한다.
  • 한번에 한 블록 요청하는 것이 Single Block I/O
  • 한번에 여러블록 요청하는 것이 MultiBlock I/O
• Single Block I/O
  • 인덱스를 이용할 때는 기본적으로 인덱스와 테이블 블록 모두 Single Block I/O 방식 사용
• MultiBlock I/O
  • 디스크 상의 인접한 블록을 함께 가져올 수 있음 (같은 익스텐트에 속한 블록), 익스텐트를 넘나들지는 못한다.
  • 많은 데이터 블록을 읽을 때 효율적

 

 

Table Full Scan vs Index Range Scan

• 테이블 전체 스캔 vs 인덱싱
  • 일반적으로 인덱싱이 빠르다고들 알고 있다.
  • 그러나 큰 테이블에서 소량 데이터를 찾을 땐 인덱싱이 유리하지만
  • 읽을 데이터가 많다면(일정 수준을 넘어간다면) Table Full Scan이 유리해질 수 있다.

 

 

캐시 탐색 메커니즘

• Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유함
  • 인덱스 루트 블록을 읽을 때
  • 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
  • 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
  • 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
  • 테이블 블록을 Full Scan 할 때
• 버퍼캐시에서 블록을 찾을때 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 엑세스하는 방식을 사용

 

 

ref : 친절한 SQL 튜닝