dybyte

backend developer

3 minute read

바인드 변수의 중요성

이름 없는 SQL 문제

라이브러리 캐시에 적재할 때, SQL은 전체 텍스트가 이름 역할을 한다. 처음 실행할 때 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다. 캐시 공간이 부족하면 버려졌다가 다시 실행할 때 똑같은 최적화 과정을 거쳐 캐시에 적재된다.

SQL은 SQL 자체가 이름이기 때문에 텍스트 중 작은 부분이라도 수정되면 그 순간 다른 객체가 새로 탄생하는 구조다. → SQL 텍스트가 변하면 SQL ID 도 변한다.

공유가능 SQL

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

SELECT * FROM emp WHERE empno = 7900;
select * from emp where empno = 7900;
select * from emp where empno = 7900 ;
...

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.executeQuery(SQLStmt);
	if(rs.next()){
		// do anything
	}
	rs.close();
	st.close();
}

할인 이벤트에 참여하기 위해 500만 명 중 20%에 해당하는 100만 고객이 동시에 시스템 접속을 시도할 경우 어떤 일이 발생할까?

DBMS에 발생하는 부하는 대개 과도한 I/O가 원인인데, 이날은 I/O가 거의 발생하지 않음에도 불구하고 CPU사용률은 급격히 올라가고, 라이브러리 캐시에 발생하는 여러 종류의 경합때문에 로그인이 제대로 처리되지 않을 것이다. 각 고객에 대해 동시다발적으로 발생하는 SQL 하드파싱 때문이다.

이 순간 라이브러리 캐시를 조회해보면, 아래와 같은 SQL로 가득 차 있다.

select * from customer where login_id = 'oraking'
select * from customer where login_id = 'javaking'
...

로그인 프로그램을 이렇게 작성하면, 고객이 로그인할 때마다 아래와 같이 DBMS 내부 프로시저를 하나씩 만들어서 라이브러리 캐시에 적재하는 셈이다.

create procedure LOGIN_ORAKING() {...}
create procedure LOGIN_JAVAKING() {...}
...

위 프로시저의 내부 처리 루틴은 모두 같다. 그렇다면 프로시저를 여러 개 생성할 것이 아니라 아래처럼 로그인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 = ?";
	Statement st = con.createStatement();
	ResultSet rs = st.executeQuery(SQLStmt);
	if(rs.next()){
		// do anything
	}
	rs.close();
	st.close();
}

라이브러리 캐시는 다음과 같다.

select * from customer where login_id = :1

이 SQL에 대한 하드파싱은 최초 한 번만 일어나고, 캐싱된 SQL을 100만 고객이 공유하면서 재사용한다.

바인드 변수는 입력값을 쿼리문에서 분리하여 처리하므로, SQL Injection 도 예방할 수 있다.

라이브러리 캐시

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

라이브러리 캐시는 SGA 구성요소다. SGA(System Global Area)는 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간이다.

Image

사용자가 SQL 문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지 확인한다. 캐시에서 찾은 경우 곧바로 실행 단계로 넘어가지만, 찾지 못하면 최적화 단계를 거친다. SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 ‘소프트 파싱(Soft parsing)’이라 하고, 찾는 데 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 ‘하드 파싱(Hard parsing)’이라 한다.

하드 파싱은 CPU를 많이 소비하는 몇 안되는 작업 중 하나다. 이러한 작업을 거쳐 생성한 내부 프로시저를 한 번만 사용하고 버리는 것은 비효율적이다. 따라서 라이브러리 캐시가 필요하다.

SQL 최적화 과정

SQL을 실행하기 전 최적화 과정을 세분화하면 아래와 같다.

  1. SQL 파싱

    사용자로부터 SQL을 전달받으면 가장 먼저 SQL Parser가 파싱을 진행한다. SQL 파싱을 요약하면 아래와 같다.

    • 파싱 트리 생성: SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
    • Syntax 체크: 문법적 오류가 없는지 확인
    • Semantic 체크: 의미상 오류가 없는지 확인

  2. SQL 최적화

    SQL 옵티마이저는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 하나를 선택한다. SQL 옵티마이저는 데이터베이스 성능을 결정하는 가장 핵심적인 엔진이다.

  3. 로우 소스 생성

    SQL 옵티마이저가 선택한 실행경로를 실제 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계다. 로우 소스 생성기가 그 역할을 맡는다.

Image

소프트 파싱 → 1단계를 거친다. 하드 파싱 → 1~3단계를 거친다.

SQL 옵티마이저

SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해주는 DBMS의 핵심 엔진이다.

  1. 사용자로부터 전달받은 쿼리를 수행하는데 후보군이 될만한 실행계획들을 찾아낸다.
  2. 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
  3. 최저 비용을 나타내는 실행계획을 선택한다.

Updated:

You may also enjoy