[SQL] SQL 공유 및 재사용

in Study on Database, Sql
[SQL] SQL 공유 및 재사용

SQL의 파싱에 있어서 소프트 파시과 하드 파싱의 차이점을 알아보고 SQL 공유 및 재사용의 중요성을 알아봅니다.



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


라이브러리 캐시란 SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용 할 수 있도록 캐싱해두는 메모리 공간입니다. 라이브러리 캐시는 DB 버퍼 캐시, Redo 로그 버퍼와 같이 System Global Area를 구성하는 요소 중 하나입니다.
사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지 확인합니다. 캐시에 SQL이 존재할 경우 곧바로 실행 단계로 넘어가지만 찾지 못할 경우 최적화 단계를 거친 뒤 로우 소스를 생성한 후 실행 단계를 거칩니다.
SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 소프트 파싱이라 하고, 찾는데 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 하드 파싱이라고 합니다.

2. SQL 최적화 과정의 어려움


옵티마이저가 SQL을 최적화 할 때, 옵티마이저는 많은 일을 수행합니다. 테이블의 조인을 수행하는 쿼리문 하나를 최적화 하기 위해서 무수히 많은 경우의 수가 존재하기 때문입니다. 조인 순서만 고려해도 테이블 개수의 팩토리얼만큼 탐색해야 하며 테이블들의 스캔 방식 또한 고려해야 합니다. 게다가 사용할 수 있는 인덱스는 보통 테이블당 여러개가 존재하기 때문에 러프하게 계산해도 수십만 가지의 경우의 수가 존재한다고 볼 수 있습니다.
옵티마이저는 이러한 과정에서 다음과 같은 정보들을 사용합니다.

  1. 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
  2. 오브젝트 통계: 테이블 통계, 인덱스 통계, 컬럼 통계
  3. 시스템 통계: CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
  4. 옵티마이저 관련 파라미터
    이 과정은 CPU를 많이 소비하는 어려운 작업입니다. 이러한 작업을 통해 생성한 프로시저를 한번만 사용하고 버린다면 비효율적이기 때문에 얻어낸 결과를 라이브러리 캐시에 저장해야 합니다.

3. 이름없는 SQL


사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때 부터 이름을 가지며 컴파일한 상태로 딕셔너리에 저장되고, 사용자가 삭제하지 않는 한 영구적으로 보관됩니다. 때문에 라이브러리 캐시에 적재하며 재사용 할 수 있습니다. 반면, SQL의 경우 이름이 따로 없으며 딕셔너리에 저장하지 않고 오로지 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하며 재사용합니다. 캐시 공간이 부족할 경우 버려졌다가 다음에 다시 실행할 때 또 하드 파싱을 거쳐 캐시에 적재됩니다.
SQL은 자체가 이름이기 때문에 텍스트 중 작은 부분이라도 수정된다면 그 순간 다른 객체가 새로 탄생하는 구조입니다. 개발 과정에서 수시로 변경이 일어나고 일회성 SQL도 많기 때문에 SQL은 영구 저장하지 않습니다.

SELECT * FROM emp WHERE empno = 7900;
select * from emp where empno = 7900;
select * from scott.emp where empno = 7900;
위의 SQL은 의미적으로 모두 같지만, 라이브러리 캐시에서 SQL을 찾기 위해 사용하는 키 값이 SQL 문 그 자체이기 떄문에 모두 다른 SQL문이 됩니다. 따라서 실행할 때 각각 최적화를 진행하고 라이브러리 캐시에서 별도의 공간을 사용합니다.

4. 바인드 변수의 중요성


개발자가 다음과 같은 프로그램을 작성했다고 생각해봅시다.

public void login(String ID) throws Exception {
    String SQLStmt = "SELECT * FROM STUDENT WHERE ID = '" + ID + "'";
    ...
}

다수의 고객이 위의 시스템에 접근할 경우, 동시다발적으로 발생하는 SQL의 하드 파싱으로 인해 CPU 사용률이 급격히 올라가고, 라이브러리 캐시에 트랜젝션이 몰려 급격한 성능 저하를 겪을 수 있습니다.
이 때 라이브러리 캐시는 아래와 같이 SQL로 가득 차 있을 것입니다.

SELECT * FROM STUDENT WHRER ID = '123'
SELECT * FROM STUDENT WHRER ID = '23521'
SELECT * FROM STUDENT WHRER ID = '3632'
SELECT * FROM STUDENT WHRER ID = '4384'
SELECT * FROM STUDENT WHRER ID = '54654'

위 SQL을 바탕으로 SQL 옵티마이저와 로우 소스 생성기는 아래와 같은 내부 프로시저를 만듭니다.

create procedure LOGIN_123() { ... }
create procedure LOGIN_23521() { ... }
create procedure LOGIN_3632() { ... }
create procedure LOGIN_4384() { ... }
create procedure LOGIN_54654() { ... }

위 프로시저의 경우 내부 처리 루틴은 모두 같습니다. 동일한 프로시저를 여러 개 생성할 경우 비효율적이며 성능 저하가 나타날 것입니다. 따라서 아래와 같이 프로시저 하나를 공유하면서 재사용하는 것이 타당합니다.

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

이처럼 파라미터 Driven 방식으로 SQL을 작성하는 방법이 제공되는데, 바인드 변수가 바로 그것입니다.

바인드 변수 사용을 위해 앞서 예를 들었던 프로그램을 수정해봅시다.

public void login(String ID) throws Exception {
    String SQLStmt = "SELECT * FROM STUDENT WHERE ID = ?";
    ...
}

위의 코드에 고객들이 접근할 경우, 앞서 라이브러리 캐시와는 다르게 해당 SQL과 관련해 하나의 SQL만 발견할 것입니다.

SELECT * FROM STUDENT WHRER ID = :1

이 SQL에 대해서 하드 파싱은 최초 한 번만 일어나고, 캐싱된 SQL을 여러 사용자가 공유하며 재사용 할 것입니다.

저는 자바를 통한 쿼리문 작성을 배울 때 당연하게도 파라미터 Driven 방식으로 배웠습니다. 때문에 최적화 요소 중 하나라고 인지조차 하지 못했습니다만, 상당한 최적화가 적용된 기술인 것을 알 수 있는 기회였습니다.

© 2022. All rights reserved.