ch1. SQL파싱과 최적화

1.1 SQL 파싱과 최적화

1.1.1  SQL구조적, 집합적, 선언적 질의 언어

• SQL(structured query language) 
• SQL은 기본적으로 구조적(structural)이고 집합적(set-based)이고 선언적(declarative)인 질의 언어다.
• 그러나 원하는 결과집합을 만드는 과정은 절차적일 수밖에 없다. 즉, 프로시저가 필요하다.
• 그러한 프로시저를 만드는 DBMS 내부엔진이 바로 SQL 옵티마이저다.
• DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행가능한 상태로 만드는 전 과정을 SQL최적화라고 한다.

 

*프로시저: 일종의 함수. 일련의 쿼리를 하나의 함수처럼 실행하기 위한 쿼리의 집합.

 

1.1.2 SQL최적화 과정

1) SQL 파싱

사용자로부터 SQL을 전달받으면 가장 먼저 SQL 파서가 파싱을 진행한다.

 

- 파싱트리 생성 : SQL문을 구성요소별로 분석하여 파싱트리 생성

- Syntax 체크 : 문법적 오류 확인. (사용불가 키워드?순서?누락?)

- Semantic 체크 : 의미상 오류 확인.(존재하지 않는 테이블or 컬럼?, 오브젝트에 대한 권한 여부?)

 

2) SQL 최적화

옵티마이저가 SQL 최적화 돌입.

- 옵타마이저가 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로 생성

- 경로들을 비교한 수 가장 효율적인 경로 하나를 선택

* SQL을 실행하려면 파싱과 최적화를 미리 거친다. 세부적인 sql처리 과정을 설명할 목적이 아니라면 굳이 구분할 필요는 없다.

 

3) 로우 소스 생성

SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅 한다. 이 작업은 로우 소스 생성기(Row-soucre generator)가 담당한다.

 

 

 

1.1.3 SQL 옵티마이저

SQL옵티마이저는 쿼리를 수행할 수 있는 가장 최적의 데이터 액세스 경로를 선택해 주는 DBMS의 핵심 엔진이다. 옵티마이저의 최적화 단계는 아래와 같이 요약될 수 있다.

 

1) 쿼리를 수행하는 데 후보군이 될 만한 실행계획을 찾아낸다.

2) 데이터 딕셔너리(Data Dictionary)에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.

3) 비용이 가장 낮은 실행계획을 선택한다.

 

*서버 프로세스가 SQL을 전달하면 옵티마이저가 최적화해서 실행계획을 반환한다고 생각하기 쉽다. 그러나 옵티마이저는 백그라운드 프로세스가 아니다. 옵티마이저는 별도 프로세스가 아니라 서버 프로세스가 가진 기능일 뿐이다. SQL 파서와 로우 소스 생성기도 마찬가지다.

 

1.1.4 실행계획과 비용

'실행계획(Excution Plan)'은 SQL 옵티마이저가 생성한 처리 절차를 사용자가 확인할 수 있게 트리구조로 표현한 것이다. p.21

- 실행계획은 p.531를 참고.

- 미리보기 기능을 통해 자신이 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스인지를 확인할 수 있고, 예상과 다른 방식으로 처리된다면 실행경로를 변경할 수 있다.

 

1.1.5 옵티마이저 힌트 p.24~28

SQL 옵티마이저가 대체로 최적의 선택을 하지만 완벽하지 않다. SQL이 복잡해지면 실수의 가능성이 높아진다. 최적의 경로가 아닌 경우 옵티마이저 힌트를 이용해 데이터 액세스 경로를 변경할 수 있다. 

옵티마이저 힌트는 주석 기호에 +를 붙이면 된다. /*+ INDEX(A_고객_PK) */

 

---

 

1.2 SQL 공유 및 재사용

 

1.2.1 소프트 파싱 vs. 하드 파싱

라이브러리 캐시 :

Library Cache는 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간이다.

이러한 프로시저는 아래의 절차를 거쳐 생성된다.

• SQL 파싱 => 최적화 => 로우 소스 생성)

System Global Area (SGA) :

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

SGA는 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스 하는 데이터 제어 구조를 캐싱하는 메모리 공간이다.

 

System Global Area의 구조

1. 사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지 확인한다.
2. 라이브러리 캐시에서 SQL을 찾으면 곧바로 실행 단계로 넘어간다. 이를 소프트파싱(Soft Parsing)이라고 한다.
3. 찾지 못하면 쿼리 최적화와 로우 소스 생성 단계를 거친다. 이를 하드 파싱(Hard Parsing)이라고 한다.

SQL 최적화 과정을 Hard Parsing이라고 부르는 이유. 

실제로 hard하기 때문이다.

옵티마이저가 SQL을 최적화 할 때 많은 연산을 수행한다.

다섯 개 테이블을 조인하는 쿼리문 하나를 최적화 할 때, 조인 순서만 고려해도 5!(120)가지이다. 조인에도 여러 방식이 있다.

테이블 전체를 스캔할지, 인덱스를 이용할지 결정해야 한다. 인덱스 스캔에도 여러 방식이 있다. 게다가 사용할 수 인덱스가 테이블마다 여러 개가 될 수 있다.

 

즉, 엄청나게 큰 경우의 수가 존재하고 거대한 연산을 수행한다. 이 최적화 과정에서 옵티마이저가 사용하는 정보는 아래와 같다.

• 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
• 오브젝트 통계: 테이블 통계, 인덱스 통계, (히스토그램을 포함한) 컬럼 통계
• 시스템 통계: CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
• 옵티마이저 관련 파라미터

 

이러한 정보를 참고하고 수많은 실행경로를 도출한다. 더불어 딕셔너리와 통계정보를 읽어 각각의 효율성을 판단하는 과정을 거친다. 이 과정은 절대 soft하지  않다.

 

DB에서 이루어지는 처리과정은 주로 I/O  작업에 집중된다. 그러나 하드 파싱은 CPU를 많이 소비하는 작업이다. 이렇게 하드한 과정을 거쳐 생성한 내부 프로시저(stored procedure)를 일회성으로 사용한다면 비효율적이다. 따라서 최적화 한 쿼리를 저장하는 공간이 필요하다. 이 공간이 바로 Library Cache이다.

 

 

1.2.2 바인드 변수의 중요성

이름없는 SQL 문제:

 

사용자 정의 함수/프로시저/ 트리거/ 패키지 등은 생성할 때부터 이름을 갖는다.

이것들은 컴파일한 상태로 딕셔너리에 저장되며 사용자가 삭제하지 않는 한 영구보관된다.

이것들은 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하며 재사용된다.

 

SQL은?

SQL은 이름이 없다. 전체 SQL문이 이름 역할을 한다. 딕셔너리에 저장되지도 않는다. SQL은 처음 실행될 때 최적화 과정을 거쳐 동적으로 생성되며 내부 프로시저를 라이브러리 캐시에 적재함으로써 여러 사용자가 공유/재사용한다. 캐시 공간이 부족하면 제거됐다가 다음에 다시 같은 과정을 거쳐 캐시에 적재된다.

 

사용자 정의 함수/프로시저처럼 SQL을 영구저장하는 DBMS도 있다. DB2가 그러하다. Oracle, SQL Server는 그러지 않는다. 그 이유는 아래와 같다.

 

: SQL변경에 따른 수많은 객체 생성으로 인한 성능 저하

 

SQL은 이름이 따로 없다. 따라서 텍스트의 일부분이 수정되면 그 순간 새로운 객체가 생성된다.

오라클 10g에서 등장한 SQL ID는 SQL 이름으로 사용해도 마찬가지다. SQL ID는 SQL 전체 텍스트를 간략히 표현하려고 오라클이 내부 함수를 이용해 생성한 값이다. 즉, SQL 텍스트와 1:1 관계를 갖는다. 따라서 텍스트 부분이 변하면 SQL ID도 변한다.

 

DBMS에서 수행되는 SQL이 모두 완성된 SQL은 아니다. 개발과정에선 SQL이 수시로 변경된다.  일회성(ad hoc)도 많다. 일회성, 무효화된 SQL까지 모두 저장하려면 많은 공간이 필요하다. 그만큼 그 안에서 SQL을 찾는 속도도 느려진다.

 

 

공유 가능 SQL

 

라이브러리 캐시에서 SQL을 찾기 위해 사용하는 키 값이 'SQL문 그자체'이므로 아래는 모두 다른 SQL이다. 의미는 같지만 각각 최적화를 진행하며 라이브러리 캐시에서 별도 공간을 사용한다.

 

SELECT * FROM emp WHERE empno = 8900;
select * from EMP where EMPNO = 8900;
select * from emp where empno = 8900;
select * from emp where empno = 8900 ;
select * from emp where empno = 8900  ;
select * from scott.emp where empno = 8900;
select /* comment */ * from emp where empno = 8900;
select /*+ first_rows */ * from emp where empno = 8900;

 

아래의 자바 코드를 보자. 고객이 500만명인 쇼핑몰의 로그인 모듈의 데이터 접근 코드이다.

public void login(String login_id) throws Exception {
	String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = '" + login_id + "'";
    Statement st = con.createStatement();
    ResultSet rs = st.excuteQuery(SQLStmt);
    
    if(rs.next()){
    	//some logic 
    }
    rs.close();
    st.close();
}

 

만약 일시에 100만 고객이 동시접속을 시도한다면 로그인 처리가 제대로 되지 않을 것이다. DBMS에 발생하는 부하는 대개 과도한 I/O 떄문인데, 이 경우는 I/O가 거의 발생하지 않았음에도 문제가 발생한다. CPU 사용률이 급등하고 라이브러리 캐시에 경합이 발생할 것이다. 이는 개별 고객에 대해 동시다발적으로 발생하는 SQL 하드파싱 때문이다. 

 

라이브러리 캐시를 확인(V$SQL)해 보면 아래와 같은 SQL이 가득하다.

SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'kim';
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'lee';
SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'park';

이는 고객별로 내부 프로시저를 만들어서 라이브러리 캐시에 저장하고 있다는 뜻이다.  이러한 내부 프로시저를 만드는 역할을 SQL 옵티마이저와 로우 소스 생성기가 담당한다.

 

create procedure LOGIN_kim() {...}
create procedure LOGIN_lee() {...}
create procedure LOGIN_park() {...}
create procedure LOGIN_john() {...}

라이브러리 캐시에 적재되는 프로시저. 그러나 동일한 기능을 하는 프로시저가 개별 생성된다는 것은 비효율적이다. 만약 하나를 만들어서 공유한다면 효율적일 것이다. 아래처럼 로그인ID를 파라미터로 받는 프로시저 하나를 공유하는 것이다.

 

create procedure LOGIN (login_id in varchar2) {...}

이를 위해 파라미터 Driven 방식으로 SQL을 작성하는 방법이 제공된다. 그것이 바인드 변수다.

 

바인드 변수를 이용하여 코드 수정

//바인드 변수를 이용하여 코드 수정

public void login(String login_id) throws Exception {
	String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?";	// '?'가 바인드 변수
    
    PreparedStatement st = con.prepareStatement(SQLStmt);	//preparedStatement
    st.setString(1, login_id);	//첫 번째 ?(bind variable)에 login_id 변수값을 넣는다.
    ResultSet rs = st.excuteQuery();	//실행
    
    if(rs.next()){
    	//some logic 
    }
    rs.close();
    st.close();
}

 

이처럼 바인드변수를 사용함으로써 개별 프로시저가 생성되지 않으며, 하나의 프로시저가 공유/재사용 될 것이다. 아까처럼 수 많은 사람들이 몰릴 때의 라이브러리 캐시를 조회해 보면, 로그인과 관련해서 하나의 SQL만 발견된다.

 

SELECT * FROM CUSTOMER WHERE LOGIN_ID = :1

이 SQL에 대한 하드 파싱은 최초 한 번만 일어난다. 캐싱된 SQL을 100만 명의 고객이 공유하며 재사용 된다.

 

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

I/O (Input/Output)튜닝이 곧 SQL 튜닝이라고 봐도 과언이 아니다.

SQL 튜닝에 앞서 데이터 저장 구조와 데이터를 디스크와 메모리에서 읽어오는 메커니즘을 이해할 필요가 있다.

 

1.3.1 SQL이 느린 이유

스토리지에서 DB로 데이터를 보내는 I/O Channel에서 병목현상이 일어난다.

 

I/O = SLEEP

: OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 대기(waiting)한다.

 

프로세스

• 프로세스는 '실행 중인 프로그램'이다.
• 생성(new)부터 종료(terminated)까지 준비(ready), 실행(running), 대기(waiting) 상태를 반복한다.
• 실행 중인 프로세스는 interrupt에 의해 수시로 실행 준비 상태(Runnable Queue)로 전환했다가 다시 실행 상태로 전환한다.
• 여러 프로세스가 하나의 CPU를 공유할 수 있지만 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있기 때문에 아래와 같은 메커니즘이 필요하다.

프로세스 생명주기(process lifecycle)

 

프로세스가 디스크에서 데이터를 읽어야 할 경우

• 프로세스는 CPU를 OS에 반환하고 waiting 상태에서 I/O가 완료되기를 기다린다.
• 정해진 OS함수를 호출(I/O call)하고 CPU를 반환한 채 알람을 설정하고 waiting queue에서 잠을 잔다.
• 이러한 이유로 I/O가 많으면 성능이 느릴 수밖에 없다.

process =(I/O Call)=> [OS 함수] ==> I/O 서브시스템
^
정해진 OS함수를 I/O call 하고 CPU 반환(SLEEP)

 

• I/O call 속도는 Single Block I/O 기준으로 평균 10ms 정도이다. 초당 100블록쯤 읽는 셈이다. 10000블록을 읽어야 한다면 10초를 기다려야 한다.
• I/O 튜닝이 안 된 시스템이라면 수 많은 프로세스에 의해 동시다발적으로 발생하는 I/O call 때문에 디스크 경합이 심해지고 대기 시간 역시 증가한다. 

 

1.3.2 데이터베이스 저장 구조

1. 데이터를 저장하려면 테이블  스페이스를 생성해야 한다.

 

- 테이블 스페이스

: 테이블 스페이스는 세그먼트를 담는 콘테이너로서, 다수의 데이터 파일(디스크 상의 물리적인 OS파일)로 구성된다.

 

테이블 스페이스의 구조

2. 테이블 스페이스 생성 이후에 세그먼트를 생성한다.

 

- 세그먼트

: 세그먼트는 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트다.(테이블, 인덱스, 파티션, LOB 등)

테이블, 인덱스를 생성할 때 데이터를 어떤 테이블 스페이스에 저장할지를 지정한다. 

세그먼트는 여러 익스텐트로 구성된다. 파티션 구조가 아니라면 테이블도 하나의 세그먼트이고, 인덱스도 하나의 세그먼트이다.  LOB 컬럼은 그 자체가 하나의 세그먼트를 구성하므로 자신이 속한 테이블과 다른 별도 공간에 값을 저장한다.

 

- 익스텐트

: 익스텐트는 공간을 확장하는 단위다. 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블 스페이스로부터 익스텐트를 추가로 할당받는다.

 

- 블록

: 블록은 데이터를 읽고 쓰는 단위이다. 익스텐트는 연속된 블록들의 집합이기도 하다. 일부 DBMS에서는 블록 대신 page라는 용어를 사용하기도 한다.

 

- 데이터 파일

: 디스크 상의 물리적인 OS 파일

 

(테이블 스페이스 (세그먼트 (익스텐트(블록) ) ) )

 

 

테이블 스페이스와 데이터 파일 간의 관계

세그먼트 공간이 부족해지면 테이블스페이스로부터 익스텐트를 추가로 할당 받는다. 이 때 할당된 익스텐트가 서로 다른 데이터 파일에 위치할 가능성이 높다. 파일 경합을 줄이기 위해 DBMS가 데이터를 가능한 한 여러 데이터 파일로 분산해서 저장하기 때문이다.

 

익스텐트 내 블록은 서로 인접한 연속된 공간이다. 그러나 익스텐트끼리는 연속된 공간이 아니다.

 

아래와 같은 방식으로 오라클에서 세그먼트에 할당된 익스텐트 목록 조회 가능

SQL> select segment_type, tablespace_name, extent_id, file_id, block_id, blocks
from dba_extents
where owner = USER
and segment_name = 'MY_SEGMENT'
order by extent_id;

 

Data Block Address (DBA, 데이터 파일에서의 데이터 블록의 주소값) p.42 참고

모든 데이터 블록은 디스크 상에서 몇 번 데이터 파일의 몇 번째 블록인지를 나타내는 자신만의 고유 주소값을 갖는다. 이 주소값을 DBA라고 한다. 블록은 데이터를 읽고 쓰는 단위이므로 데이터를 읽기 위해서는 DBA를 알아야 한다. 

 

 

 

1.3.3 블록단위 I/O

데이터베이스에서 데이터를 읽고 쓰는 단위는 블록이다. 데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 전부 읽는다.  오라클은 기본적으로 8KB^5 크기의 블록을 사용한다. 따라서 1Byte를 읽기위해 8KB를 읽는 셈이다. (2, 4, 16KB로 사용할 수도 있음. ms sql Server는 8KB블록 (=페이지)만 지원)

 

테이블뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다. p46참고.

 

 

1.3.4 시퀀셜 액세스 vs. 랜덤 액세스

테이블 또는 인덱스 블록을 액세스(=읽는) 하는 방식은 두 가지다.

• 시퀀셜 액세스 (Sequential Access)
• 랜덤 액세스 (Random Access)

 

시퀀셜 액세스

: 시퀀셜 액세스는 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식이다. 인덱스 리프 블록(index leaf block)은 앞 뒤를 가리키는 주소값을 통해 논리적으로 서로 연결돼 있다. 이 주소값에 따라 앞 또는 뒤로 순차적으로 스캔한다.

 

테이블 블록 간에는 서로 논리적인 연결고리가 없다. 따라서 오라클은 세그먼트에 할당된 익스텐트의 목록을 세그먼트 헤더에 map으로 관리한다. 익스텐트 맵은 각 익스텐트의 첫 번째 블록 주소 값을 갖는다. 읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면 그것이 곧 Full Table Scan이다.

 

랜덤 액세스

: 랜덤 엑세스는 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근(=touch)하는 방식이다.

 

 

 

 

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

DB 버퍼캐시

DB 버퍼캐시는 디스크에서 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄인다.  따라서 매번 SQL을 수행할 때마다 블록을 새로 읽지 않아도 된다. 

 

라이브러리 캐시가 SQL, 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 '코드 캐시'라고 한다면, DB 버퍼캐시는 '데이터 캐시'라고도 할 수 있다.

서버 프로세스와 데이터파일 사이에 버퍼캐시가 존재하며, 블록을 직접 읽기 전에 버퍼캐시를 탐색한다. 블록이 버퍼캐시에 존재한다면 프로세스가 I/O call 할 필요 없으므로 시간과 부하가 감소한다. 버퍼캐시는 공유 메모리 영역이므로 같은 블록을 읽는 다른 프로세스도 득을 본다.

 

아래와 같이 SGA영역을 확인할 수 있다.

show sga

 

 

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

 

논리적 블록 I/O

: 논리적 블록 I/O는 SQL을 처리하는 과정에 발생한 총 블록 I/O를 의미한다. 일반적으로는 메모리상의 버퍼 캐시를 경유하므로 메모리 I/O가 곧 논리적 I/O라고 여겨도 무방하다. (정확히는, 논리적 I/O = 메모리I/O + Direct Path I/O)

 

물리적 블록 I/O

: 물리적 블록 I/O는 디스크에서 발생한 총 블록 I/O를 말한다. SQL처리 중 읽어야 할 블록을 버퍼캐시에서 찾지 못할 때만 디스크를 액세스 하므로, 논리적 블록 I/O 중 일부를 물리적으로 I/O한다. (버퍼 캐시에 블록이 없어서 디스크에서 찾는 경우 그걸 물리적 블록I/O라고 한다.)

메모리 I/O가 전기적 신호인 데 반해, 디스크 I/O는 액세스 arm을 통해 물리적으로 작용하므로 메모리 I/O에 비해 보통 10,000배쯤 느리다. 디스크 경합이 심하면 더욱 느리다.

 

• SQL이 참조하는 테이블에 데이터 수량이 다르지 않은 상태에서 조건절에 같은 변수가 입력되면 매번 읽는 블록 수는 같다. SQL을 수행하면서 읽은 총 블록 I/O가 논리적 I/O이다.
• Direct Path Read 방식으로 읽는 경우를 제외하면 모든 블록은 DB 버퍼캐시를 경유해서 읽는다. 따라서 논리적 I/O횟수는 일반적으로 DB 버퍼캐시에서 블록을 읽은 횟수와 일치한다. 논리적 I/O가 메모리 I/O와 같은 개념은 아니지만 결과적으로 수치는 같다.
• DB가 버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 I/O가 물리적 I/O다. 데이터 입력이나 삭제가 없어도 물리적 I/O는 SQL 실행 시마다 달라진다. 실행을 거듭할수록 줄어든다. DB 버퍼캐시에서 해당 테이블 블록의 점유율이 증가하기 때문이다. 버퍼캐시에서 삭제되면 다시 증가한다.

 

블록 I/O 적정량

: SQL을 수행하는 데 필요한 논리적 일량은 모두 다르다. 해당 SQL의 목적에 따라 블록 I/O 발생량은 달라진다. 이러한 논리적 일량은 검색 범위, 조인 테이블 개수, 대상 테이블 크기, 인덱스 구조 등에 의해 결정된다.

 

--

 

버퍼캐시 히트율(Buffer Cache Hit Ratio)

: 읽은 전체 블록 중 물리적인 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율을 나타낸다. 버퍼캐시의 효율을 측정하는 데 가장 일반적으로 사용하는 지표이며, 일반적으로 높을 수록 효율이 좋다는 의미이다.

 

온라인 트랜젝션을 처리하는 애플리케이션이라면 시스템 레벨에서 평균 99%의 히트율을 달성해야 한다. 즉, 디스크에서 직접 I/O를 하는 일이 거의 없어야 한다는 뜻이다.

 

BCHR = (캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) * 100	--전체에서 버퍼의 비율을 구한다.--
	= ( (논리적I/O - 물리적I/O) / 논리적I/O ) * 100
	= ( 1 - ( 물리적I/O / 논리적I/O ) ) * 100	 --전체(1)에서 물리적 비율을 뺀 값에 100을 곱함--
    

--식 변형--

물리적I/O = 논리적I/O * (100% -BCHR)

 

논리적I/O를 줄여야 하는 이유.

 

물리적I/O가 적을수록 성능은 향상된다. 그러나 BCHR은 시스템 상황에 따라 달라지므로(버퍼에 항상 내가 찾는 블록이 존재 하지 않을 수 있으므로), 물리적I/O는 시스템 상황에 따라 달라지는 외생변수이다. 통제가 실제적으로 어렵다는 의미이다. (메모리를 증설해서 DB버퍼캐시를 증가시키는 방법 외에는 통제 불가하다.)

 

그러나 논리적I/O는 절대적이며 일정하다. 만약 논리적I/O의 개수를 줄인다면 그에 따라 물리적I/O의 개수도 절대적으로 감소하므로, 논리적 I/O는 통제가능하며 통제해야 할 내생변수이다. 결국 SQL성능을 높이기 위해서는 논리적 I/O를 줄일 수밖에 없다.

 

e.g. 시스템의 BCHR가 70/%인 경우, 특정 SQL의 논리적 I/O가 10,000개면 물리적I/O는 3,000개 쯤 발생할 것으로 예상할 수 있다.

--물리적I/O = 논리적I/O * (100% -BCHR)--
물리적I/O = 10000 * (100 - 70)% = 3000

-- 논리적I/O 감소 ==> 물리적I/O 감소 ==> 성능향상 --

 

만약 논리적 I/O를 1,000개로 줄인다면 물리적 I/O도 300개로 감소한다. 성능은 10배 향상된다.

 

논리적 I/O를 줄이는 방법

SQL을 튜닝해서 읽어야 하는 총 블록 개수를 줄이면 된다. 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL튜닝이다.

 

예시는 p.54~55 참고/

• query = 1,351,677
• current = 12,367
• disk = 601,458

총 논리적 I/O는 1,965,502(=query + current + disk)가 아니다. 디스크를 조회하기 전에 우선 버퍼캐시에 읽어야 할 블록량을 적재하기 때문이다. 따라서 DB 버퍼캐시에서 읽은 1,364,044(= query + current)에 디스크에서 읽은 물리적 I/O(=601,458) 블록이 이미 포함되어 있다.

 

BCHR = (1 - (Disk / (Query + Current))) * 100
	= (1 - (601,458 / (1,351,677 + 12,367))) * 100 --버퍼캐시에서 물리적I/O를 뺀 부분의 비율을 계산--
  	= 55.9%

 

높은 BCHR가 반드시 높은 효율의 SQL을 의미하지는 않는다. 버퍼에 블록이 존재하는 비율이 높다는 것이지, 블록에 도달하는 경로가 효율적이라는 의미는 아니기 때문이다.

 

 

 

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

메모리 캐시가 클수록 좋다. 그러나 비용과 기술의 한계 때문에 전체 데이터 중 일부만 메모리에 올릴 수밖에 없다.

캐시에서 찾지 못한 데이터 블록은 I/O call을 통해 디스크에서 DB 버퍼 캐시로 적재하고서 읽는다(물리적I/O).

 

Single Block I/O

: I/O call 할 때 한 번에 하나의 블록씩 요청하여 메모리에 적재하는 방식

 

Multiblock I/O

: I/O call 할 때 한 번에 여러 블록씩 요청하여 적재하는 방식

 

 

인덱스를 이용하는 경우, 기본적으로 인덱스와 테이블 블록 모두 Single Block I/O 방식을 사용한다. 인덱스는 소량 데이터를 일는 경우 주로 사용하므로 Single Block I/O 방식이 효율적이다.

 

Single Block I/O가 사용되는 오퍼레이션:

• 인덱스 루트 블록을 읽을 때
• 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

많은 데이터 블록을 읽는 경우는 Multiblock I/O 방식이 효율적이다. 인덱스를 사용하지 않고 테이블 전체를 탐색(full scan)할 때 이 방식을 이용한다. 테이블이 클수록 Multiblock I/O 단위도 크면 좋다.

(갖다오는 횟수 감소 -> waiting 횟수와 시간 감소)

I/O call을 하고 물리적I/O를 하는 동안 프로세스는 wating queue에 들어가므로.

 

Multiblock I/O는 캐시에서 찾지 못한 특정 블록을 디스크에서 읽으려고 I/O call을 할 때 디스크 상에 그 블록과 인접한 블록들을 한꺼번에 읽어 캐시에 미리 적재하는 기능이다. OS단에서는 보통 1MB 단위로 I/O를 수행한다. 여기서 인접한 블록은 같은 익스텐트에 속한 블록을 의미한다. multiblock I/O를 사용해도 익스텐트의 경계를 넘지 못한다. 익스텐트의 단위가 20이고 multiblock I/O의 단위가 8이라고 가정할 때, 총 3회에 걸쳐 20블록(8+8+4)만 가져올 수 있다.

 

이 두 방식은 각각 따로 사용되지 않는다. multiblock I/O를 사용하여 큰 단위로 블록을 읽고, 남은 소수의 블록을 singleblock I/O를 사용하여 읽는다.(Full scan 중 chain이 발생한 로우를 읽을 때도 사용)

 

 

1.3.7 Table Full Scan vs. Index Range Scan

 

Table Full Scan

: 테이블 전체를 스캔해서 읽는 방식

Table Full Scan은 테이블에 속한 블록 전체를 읽어서 사용자가 원하는 데이터를 찾는 방식이다.

 

Index Range Scan

: 인덱스를 이용하여 테이블에 액세스에 접근하여 읽는 방식

인덱스에서 일정량을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식. ROWID는 테이블 레코드가 디스크 상에 저장된 위치를 가리키는 위치 정보이다.

 

p.60 참고

---

 

Table Full Scan을 지양하고 인덱스 스캔을 사용해야 효율적라는 인식이 일반적이지만, 인덱스가 SQL성능을 저하시키는 경우도 상당하다.(특히 집계용 SQL과 배치 프로그램. 이들은 대부분 Table Full Scan으로 유도하면 성능이 향상된다. 조인을 포함한 SQL이면 조인 메소드로 해시 조인을 선택해 주면 된다.)

 

인덱스 사용 시 오히려 성능이 떨어지는 이유

: Table Full Scan은 시퀀셜 액세스와 Multiblock I/O 방식으로 디스크 블록을 읽는다. 한 블록에 속한 모든 레코드를 한 번에 읽고, 캐시에서 블록을 찾지 못하면 한 번의 수면(I/O call)을 통해 인접한 수십~수백 블록을 한 번에 I/O한다. 이 방식을 사용하는 SQL은 스토리지 스캔 성능이 향상될수록 성능도 향상된다.

 

하지만 소량의 블록을 읽기 위해서 거대한 테이블을 Table Full Scan을 사용하는 것은 비효율적. 데이터가 소량일 때는 반드시 인덱스를 사용해야 한다.

 

인덱스 맹신은 금물

Index Range Scan는 Single Block I/O 방식으로 디스크 블록을 읽는다. 캐시에서 블록을 못 찾으면 디스크에서 I/O call 매번한다. 따라서 다량의 데이터를 읽을 때는 Table Full Scan보다 불리하다. 다회의 I/O call 때문에 프로세스의 waiting이 늘어나 성능이 저하되는 것이기 때문에, storage의 스캔성능 향상되는 만큼 향상되지는 않는다.

+ 이 방식은 읽었던 블록을 반복해서 읽는다. 다량의 데이터를 읽을 때 물리적I/O 뿐만 아니라 논리적I/O 측면에서도 불리하다.

e.g. 한 블록에 평균 500개 레코드가 존재하는 경우. 인덱스를 사용해 전체 레코드를 읽으면 모든 블록을 최대 500번씩 읽는다. Table Full Scan 방식은 한 블록을 한 번만 읽는다. 따라서 데이터량이 일정 수준을 넘어가면 Table Full Scan이 유리하다. p.62 참고

 

 

1.3.8 캐시 탐색 메커니즘

Direct Path I/O를 제외한 모든 블폭 I/O는 메모리 버퍼 캐시를 경유한다.

버퍼캐시를 탐색하는 오퍼레이션

• 인덱스 루트 블록 읽을 때(인덱스 루트 블록 주소는 SQL을 파싱하고 최적화 하는 시점에 SQL 커서에 담김)
• 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
• 테이블 블록을 Full Scan 할 때

(인덱스 루트 블록 => 인덱스 브랜치 블록 => 인덱스 리프 블록 => 테이블 블록)

(루트 => 브랜치 => 리프 => 테이블)

 

버퍼 캐시에서 블록을 찾는 방법

버퍼캐시에서 블록을 찾을 때 해시 알고리즘으로 버퍼 헤더를 찾고 거기서 얻은 포인터로 버퍼 블록을 액세스 하는 방식을 사용한다. 

 

해시 구조의 특징

• 같은 입력 값은 항상 동일한 해시 체인(=버킷)에 연결됨
• 다른 입력값(4와 9)가 동일한 해시 체인에 연결될 수 있음.
• 해시체인 내에는 정렬이 보장되지 않음.

 

버퍼 캐시 구조

 

 

위의 예제는 모듈러 함수를 사용하는 경우 표현.

(5로 나누었을 때 나머지 값을 반환하는 모듈러 함수로 탐색 메카니즘 예시)

 

버퍼 캐시에서 20번 블록을 찾으려고 할 때

: 20%5==0이다. 따라서 만약 이 블록이 버퍼에 캐시되어 있다면, 버퍼 헤더가 첫 번째 해시 체인(해시값=0)에 연결돼 있을 것이다. 따라서 항상 첫 번째 해시 체인만 탐색하면 된다.

 

버퍼 캐시에서 27번 블록을 찾으려고 할 때

: 27%5==2이다. 그 다음부터 무슨 말일지 모르겠음. 예시가 이해되지 않음.

 

 

메모리 공유자원에 대한 액세스 직렬화

버퍼캐시는 SGA구성요소이므로 버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원이다.(누구나 접근 가능)

 

하나의 버퍼 블록에 두 개 이상의 프로세스가 동시에 접근하려고 할 때 문제 발생.

동시접근 시 블록 정합성에 문제 발생 가능.

 

따라서 실제 내부에서는 순차적으로 접근하도록 구현해야 하며 이를 위해 직렬화(serialization, 줄세우기)메커니즘이 필요하다. 이러한 직렬화 메커니즘이 래치(Latch, 자물쇠, 걸쇠)다.

 

캐시 버퍼 체인 래치(cache buffer chain latch)

대량의 블록을 읽는 경우 모든 블록에 대한 해시 체인을 탐색한다. DBA(Data Block Address)를  해시 함수에 입력하고 반환값으로 스캔해야 할 해시를 찾는다. 이 때 해시 체인을 스캔하는 동안 다른 프로세스가 체인 구조를 변경해서는 안 된다. 

이를 예방하기 위해 해시 체인 래치가 존재한다. 위의 도표에서 0부터 4까지 다섯 개 체인 앞쪽에 자물쇠가 있다고 생각하면 된다. 자물쇠를 열 수 있는 key를 획득한 프로세스만이 체인으로 진입할 수 있다.

 

 

SGA를 구성하는 서브 캐시마다 별도의 래치가 존재한다. 버퍼캐시에는 캐시버퍼 체인 래치, 캐시버퍼 LRU 체인 래치 등이 작동한다.

빠른 DB를 구현하려면 BCHR을 높여야 한다. 그러나 래치들의 경합 때문에 캐시I/O도 생각만큼 빠르지 않을 수도 있다.

 

캐시버퍼 체인 뿐만 아니라 버퍼블록 자체에도 직렬화 메커니즘이 존재한다. Buffer Lock이다. 이런 직렬화 메커니즘에 의한 캐시 경합을 줄이려면 SQL튜닝을 통해 쿼리 일량(논리적I/O) 자체를 줄여야 한다.

 

버퍼 락(buffuer lock)이 필요한 이유 p.66
읽고자 하는 블록 확인 후 => 캐시버퍼 체인 래치 해제 필요. 그래야 기다리던 다른 프로세스들이 작업 재개 가능.

그런데 래치를 해제한 상태로 버퍼블록 데이터를 읽고 쓰는(I/O) 중에 후행 프로세스가 같은 블록에 접근해서 데이터를 읽고 쓴다면 정합성에 문제 발생 가능.

따라서 캐시버퍼 체인 래치를 해제하기 전에 버퍼 헤더에 Lock을 설정함으로써 버퍼블록 자체에 대한 직렬화 문제 해결

 

 

 

 

 

 

 

 

 

 

 

 

 

https://product.kyobobook.co.kr/detail/S000001975837

친절한 SQL 튜닝 | 조시형 - 교보문고