[친절한 SQL 튜닝] 하드파싱 VS 소프트파싱

SQL 공유 및 재사용

 

라이브러리 캐시

• SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용 할 수 있도록 캐싱해 두는 메모리 공간.
• SGA 구성요소이다.
  • SGA(System Global Area) 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간
• SQL이 전달되면 -> DBMS 는 해당 SQL이 라이브러리 캐시에 존재하는지 확인
  • 캐시에서 찾으면 곧바로 실행 단계로 넘어감 = 소프트 파싱
  • 캐시에서 찾지 못하면 최적화 단계를 거침 = 하드 파싱

 

하드파싱

옵티마이저는 SQL을 최적화할때 아래 정보를 사용한다.

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

데이터베이스에서 이루어지는 처리 과정은 대부분 I/O 작업에 집중되는 반면, 하드 파싱은 CPU 를 많이 소비하는 몇 안 되는 작업 중 하나다. 어려운(=hard) 작업을 거쳐 생성한 내부 프로시저를 한 번만 사용하고 버린다면 이만저만한 비효율이 아니다. 라이브러리 캐시가 필요한 이유가 여기 있다. 

 

이름 없는 SQL 문제

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

• 컴파일한 상태로 딕셔너리에 저장된다. 
• 사용자가 삭제하지 않는 한 영구적으로 보관된다.
• 실행할 때 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다. 

 

SQL 은 이름이 따로 없다. 전체 텍스트가 이름 역할을 한다.

• 딕셔너리에 저장하지 않는다.
• 처음 실행할 때 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다. 
• 캐시 공간이 부족하면 버려졌다가 다음에 다시 실행할 때 똑같은 최적화 과정을 거쳐 캐시에 적재된다.

 

사용자 정의 함수/프로시저는 내용을 수정해도 이름이 변하지 않으므로 같은 프로그램이 무한 생성되지 않지만, SQL은 자체가 이름이기 때문에 텍스트 중 작은 부분이라도 수정되면 그 순간 다른 객체가 새로 탄생하는 구조이다. 

 

공유가능 SQL

라이브러리 캐시에서 SQL 을 찾기 위해 사용하는 키 값이 'SQL 문 그 자체' 이므로 아래는 모두 다른 SQL 이다.

SELECT * FROM EMP WHERE EMPNO = 7900;
SELECT * FROM emp WHERE EMPNO = 7900;
SELECT * FROM emp WHERE empno = 7900;

 

 

많은 고객을 보유한 쇼핑몰에서 로그인 담당 개발자가 프로그램을 아래와 같이 작성했다고 했을 때

String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = '" + login_id + "'";

 

접속량이 많아지면 CPU 사용률은 급격히 올라가고, 라이브러리 캐시에 발생하는 여러 종류의 경합 때문에 로그인이 제대로 처리되지 않을 것이다. 동시다발적으로 발생하는 SQL 하드파싱 때문이다. 이때 라이브러리 캐시를 조회해 보면, 아래와 같은 SQL 로 가득 차 있다. 

SELECT * FROM CUSTOMER WHERE LOGIN_ID ='sian'
SELECT * FROM CUSTOMER WHERE LOGIN_ID ='rosa'
SELECT * FROM CUSTOMER WHERE LOGIN_ID ='woogie'

고객이 로그인 할 때마다 DBMS 내부 프로시저를 하나씩 만들어서 라이브러리 캐시에 적재하는 셈이다. 

이때 로그인 프로시저의 내부 루틴은 모두 같기 때문에 프로시저를 여러 개 생성할 것이 아니라 로그인ID 를 파라미터로 받는 프로시저 하나를 공유하면서 재사용하는 것이 바람직하다. 

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

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

아래와 같이 로그인 프로그램을 수정하고, 

String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID =?";

다음날 접속량이 많아졌을 때 라이브러리 캐시를 조회해 보면, 로그인과 관련해서 아래 SQL 하나만 발견된다. 

SELECT * FROM CUSTOMER WHERE LOGIN_ID = :1

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