[친절한 SQL 튜닝] 라이브러리 캐시 / 하드 파싱 / 바인드 변수와 SQL 공유

라이브러리 캐시와 하드 파싱

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

이 공간에 프로시저가 저장되면 SQL 최적화와 로우 소스 생성과 같은 SQL 최적화 과정을 매번 진행할 필요 없이

SQL을 재사용할 수 있다.

사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱한 후에 이 SQL이 라이브러리 캐시에 존재하는지 먼저 확인하는데,

존재하면 곧바로 '실행' 단계로 넘어간다.

-> 소프트 파싱

그런데 SQL이 라이브러리 캐시에 존재하지 않으면 SQL최적화와 로우 소스 생성 단계까지 모두 거친 후에 '실행' 단계를 수행한다.

-> 하드 파싱

위 과정에 '하드 파싱'이라는 단어가 붙은 이유는 SQL 최적화 과정을 수행하는 것이 말 그대로 쉬운 과정이 아니기 때문이다.

쿼리문 1개를 최적화하겠다고 하면,

- 조인 순서 고려

- 조인 방식 고려 (NL 조인 / 소트 머지 조인 / 해시 조인 등..)

- 스캔 방식 (테이블 스캔 / 인덱스 스캔 - Index Range Sca, Index Unique Scan, Index Full Scan, Index Fast Full Scan, Index Skip Scan ..) -> 테이블 갯수만큼 고려.

그리고 이 과정을 거치는데 사용하는 정보들도 아래와 같이 많다.

- 기본 정보

- 오브젝트 통계 (테이블 통계, 인덱스 통계, 컬럼 통계)

- 시스템 통계 (CPU 속도, Single Block I/O 속도, Multiblock I/O 속도..)

- 옵티마이저 관련 파라미터

이 정보들을 이용해 효율성을 판단하는 것인데,

하나의 쿼리를 최적화하기 위해 CPU 를 많이 소비하게 된다.

이렇게 hard 한 작업을 거쳐서 내부 프로시저를 생성하기 때문에 이를 한번만 사용하고 버리는 것은 비효율적이다.

그래서 라이브러리 캐시에 실행된 프로시저를 저장하는 것이다.

사용자 정의 함수나 프로시저, 트리거, **패키지 는 한번 생성되면 영구적으로 보관되고 이름도 따로 있다.

그 이름으로 한번 실행되면 라이브러리 캐시에 바로 보관되고 여러 사용자들이 공유하면서 재사용한다.

그러나 SQL은 이름이 따로 없고, 전체 텍스트가 이름의 역할을 한다.

그 의미는 텍스트 일부가 바뀌더라도 다른 이름을 갖는 것이라고 볼 수 있다.

(DBMS는 SQL은 영구 저장하지 않는다. SQL은 프로그램을 개발하면서 수시로 바뀌기도 하고 일회성 SQL도 많은데, 이를 다 저장하게 되면 오히려 SQL 조회 속도가 느려지기 때문이다.)

SQL 전체 텍스트가 이름의 역할을 함으로써 아래와 같은 문제가 있다. 이는 바인드 변수가 중요한 이유이기도 한데 바인드 변수는 아래에서 알아본다.

아래 2개의 쿼리문은 동일한 역할을 수행하지만, 말그대로 텍스트가 다르기 때문에 다른 SQL로 여겨진다.

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

그래서 아래와 같이 SQL문이 수행되도록 개발했다고 한다면,

100만명이 로그인을 시도했을 때, 100만번의 하드 파싱이 발생되고 100만개의 SQL이 라이브러리 캐시에 존재하게 된다.

public void login (String login_id) throws Exception {
  String sqlStatement = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = '" + login_id + "'";
  Statement st = con.createStatement();
  ResultSet rs = st.executeQuery(sqlStatement);

  if (rs.next()) {
        // ..
  }
  rs.close();
  st.close();
}

위 프로시저의 내부 처리 루틴은 모두 같기 때문에

하나의 프로시저에서 로그인 ID를 파라미터로 받도록 하면, 라이브러리 캐시에는 프로시저 하나만 저장될 것이다.

이 방법이 바인딩 변수이다.

바인드 변수와 SQL 공유

파라미터 Driven 방식으로 SQL을 작성하는 방법인데, 바인드 변수라고 한다.

로그인 ID를 파라미터로 받는 프로시저 하나가 생성될 것이고, 하드 파싱도 한번만 발생하고 라이브러리 캐시에 적재되면

캐싱된 SQL을 100만명이 공유하면서 재사용하게 된다.

수정된 소스코드

public void login (String loginId) throws Exception {
  String sqlStatement = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?";
  PreparedStatement pst = con.prepareStatement(sqlStatement);
  pst.setString(1, loginId)
  ResultSet rs = pst.executeQuery();

  if (rs.next()) {
        // ..
  }
  rs.close();
  st.close();
}

**패키지란?
여러 함수와 프로시저가 묶여있는 말그대로 package!
하나의 패키지 안에는 여러 프로시저/함수가 포함되어 있을 수 있다.
선언부와 본문으로 구성되어있으며, 사용자에게는 선언부만 공개 및 사용된다.

저작자표시 (새창열림)

라이브러리 캐시와 하드 파싱

바인드 변수와 SQL 공유

티스토리툴바