[친절한 SQL 튜닝] 1장 SQL 처리 과정과 I/O - 1.2 SQL 공유 및 재사용

1.1 SQL 파싱과 최적화

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

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

• SQL 최적화를 위해 옵티마이저가 고려하는 정보

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

→ 이처럼 복잡한 작업을 겨쳐 생성한 내부 프로시저를 한번만 사용하고 버린다면 비효율적. 라이브러리 캐시가 필요한 이유.

1.2.2 바인드 변수의 중요성

이름없는 SQL 문제

• 사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름 가짐
• SQL은 이름이 없고 전체 SQL 텍스트가 이름 역할을 함.
  - 저장하지 않는 이유: 사용자 정의 함수/프로시저는 내용을 수정해도 이름이 변하지 않음. 그런데 SQL은 자체가 이름이므로 텍스트 중 한 부분만 수정돼도 새로운 객체가 탄생함.

공유 가능 SQL

• SQL은 의미가 같다고 하더라도 텍스트가 조금만 달라지면(예를 들어 대소문자만 달라져도) 다른 SQL문으로 인식한다.

# 서로 다른 SQL문임.
SELECT * FROM emp WHERE empno = 7900;
select * from EMP where EMPNO = 7900;

• 파라미터만 다르고 내부 처리 루틴이 같은 프로시저의 경우에는 바인드 변수를 사용해야 라이브러리 캐시에 하나의 SQL문만 저장해서 과도한 라이브러리 캐시들이 경합을 벌이지 않게 해야 한다.

• 변경 전

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

# 내부 프로시저
create procedure LOGIN_ORAKING( ) { ... }
create procedure LOGIN_JAVAKING( ) { ... }

• 변경 후

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

# 내부 프로시저
create procedure LOGIN (login_id in varchar2) { ... }

• 변경 전에는 SQL이 실행될 때마다 전체 SQL문에 대한 새로운 라이브러리 캐시가 생성되지만, 변경 후에는 파라미터만 변경되고 동일한 SQL문을 재사용하게 된다.
• 즉, 수많은 로그인 사용자가 발생하더라도 하드파싱은 최초에 한번만 일어나고 이후에 캐싱된 SQL을 공유하면서 재사용한다.