SQL 공유 및 재사용

이전 글 - SQL이란? 및 SQL 처리 과정

 

먼저 해당 포스팅은 오라클(Oracle) 데이터 베이스를 기준으로 작었습니다.^^

 

하지만 다른 데이터 베이스(mysql, postgresql, ms...) 또한 아래와 비슷한 구성과 원칙으로 시스템이 설계되어 있으며 각 요소들의 명칭은 다를 수 있습니다.(예를 들어 SGA 같은거?)

 

---

라이브러리 캐시 in Shared Pool

 

오라클 데이터 베이스 시스템은 SGA(System Global Area)라는 매우 중요한 메모리 구성 요소를 포함한다.

SGA - 데이터베이스 인스턴스가 작동하는 동안 사용하는 모든  데이터를 포함하고 있다.

 

SGA 메모리 영역은 다음과 같은 요소로 구분되어 있다.

1. Database Buffer Cache
2. Shared Pool(Library Cache)
3. Redo Log Buffer
4. Large Pool
5. ...
6. ...
...
더 있다. 그렇지만 여기까지만 나열하겠다. 다 써봐야 기억도 못 한다.ㅋㅋ

 

 

위 요소 중 Shared Pool 내부에 있는 라이브러리 캐시에 대한 이야기를 해 볼것이다.

라이브러리 캐시
키: SQL문 자체
값: 최종 실행 계획(옵티마이저가 선택한 최저 비용의 실행 계획)

데이터베이스는 요청받은 SQL 문의 내용을 키로 사용하고, 해당 SQL의 최종 실행 계획을 값으로 하여 라이브러리 캐시에 저장한다.

데이터베이스에서 SQL문 자체가 캐시의 키로 사용되기 때문에, SQL문이 한 글자라도 다르면 해당 SQL문은 새로운 키로 인식되어 캐시 히트가 발생하지 않게된다.

 

라이브러리 캐시란 SQL Cache이다. 즉, 데이터베이스는 SQL 최적화를 통해 얻은 최종 실행계획을 한 번만 사용하고 버리지 않으며 해당 실행계획을 라이브러리 캐시에 저장해 둔다.

 

다음에 쿼리문의 내용이 일치하는 요청이 오면 복잡한 SQL 최적화 작업을 생략하는 대신 라이브러리 캐시에 저장된 실행계획을 곧 바로 실행 시킨다.

 

---

Hard Parsing vs Soft Parsing

 

위 설명을 있어 보이게 표현하면 Soft parsing과 Hard Parsing 이라는 개념이 된다.

우선 기억하기 쉽게 개념을 카테고라이징 하자(기억하지 못하면 의미가 없는법!)

Soft parsing과 Hard parsing

Soft parsing is Good

VS

Hard parsing is Bad

 

일단 soft가 좋은거다. 기억하자 ㅋ

 

두 개념에 대해서 간략히 설명하겠다.

 

옵티마이저는 복수의 실행계획을 만들어 낸다.

이때 실행 계획의 수는 우리가 상상하는것보다 훨신 많을 수 있다.

예를 들어 어느 select 문에 관여하는 테이블의 개수가 7개라고 가정해보라. 그러면 테이블의 조인 순서를 정하는 7!(7 * 6 * 5 * ... * 1)개의 실행 계획이 고려되어 질 수 있다.
여기에 이것 저것 곱의 연산이 붙는다면 정말 어마어마한 수의 실행 계획이 만들어지고 이 중 가장 최적의 계획 하나만을 선택하게 될것이다. 

 

Start
  |
  v
Receive SQL Statement
  |
  v
Check SQL Cache = [SHARED POOL]
  |
  +------------------+
  |                  |
  v                  v
SQL Exists in       SQL Not in
Cache (Yes)         Cache (No)
  |                  |
  v                  v
Use Cached          Perform
Execution Plan      Hard Parsing
  |                  |
  v                  v
Execute SQL         Parse SQL Statement
  |                  |
  v                  v
End                 Optimize SQL Statement
                     |
                     v
                 Generate Execution Plan
                     |
                     v
                  Store Plan in Cache
                     |
                     v
                  Execute SQL
                     |
                     v
                     End

Soft Parsing

• 정의: 데이터베이스가 SQL 캐시에 저장된 실행 계획을 통해 쿼리를 곧바로 처리하는 과정.
• 설명: 동일한 SQL 문이 이미 캐시에 존재하는 경우, 데이터베이스는 저장된 실행 계획을 사용하여 쿼리를 빠르게 실행합니다.
• 장점: 성능 향상, 쿼리 처리 시간 단축, CPU 자원 절약.

Hard Parsing

• 정의: SQL 캐시에 저장되지 않은 새로운 SQL 요청을 처리하기 위해 데이터베이스가 전체 SQL 최적화 과정을 처음부터 수행하는 과정.
• 설명: 새로운 SQL 문이 캐시에 없는 경우, 데이터베이스는 SQL 문을 파싱하고 최적화하여 실행 계획을 생성하고 이를 캐시에 저장합니다.
• 단점: 성능 저하, 더 많은 CPU 자원 사용, 쿼리 처리 시간 증가.

 

---

실제 코드 예시

 

Hard Parsing이 Soft Parsing에 비해 시간적 그리고 메모리적으로 더 많은 자원을 필요로 하는것을 알 수 있다.

 

이제 우리는 새로운 개념에 대해 알게 되었다. 

필자는 개발자이므로 곧 바로 다음과 같은 질문을 하게 된다!

 

그래서? 이 사실을 코딩 할 때 어떻게 써먹쥬? 

질문에 대한 답은 매우 간단했다. Soft Parsing을 할 수 있는 상황에서 Hard Parsing이 일어나게 하지 말자.

그렇다면 우리가 언제 위와 같은 실수를 하게 되는걸까? 아래 예시를 통해 

 

- 잘못된 예시(쓸모 없는 하드 파싱)

public void login(String login_id) throws Exception {
    // 로그인 하는 유저마다 logn_id값이 다르다.
    // 새로운 유저가 로그인 할때마다 아래 sql문은 db로부터 하드파싱 될것이다.
    String SQLSmt = "SECECT * FROM CUSTOMER WHERE LOGIN_ID = `" + login_id + "'";
    Statemewnt st = con.createStatment();
    ResultSet rs = st.executeQuery(SQLStmt);
    if(rs.next()) {
    	//do anything
    }
    rs.close();
    st.close();
}


// DB가 받는 실제 SQL
// SELECT * FROM CUSTOMER WHERE LOGIN_+ID = 'HAEIN'
// SELECT * FROM CUSTOMER WHERE LOGIN_+ID = 'DONGMIN'
// SELECT * FROM CUSTOMER WHERE LOGIN_+ID = 'SOLAR'
// SELECT * FROM CUSTOMER WHERE LOGIN_+ID = 'SEUNGMIN'
// SELECT * FROM CUSTOMER WHERE LOGIN_+ID = 'JISU'

 

- 옳은 예시, 바인드 변수 사용(소프트 파싱)

public void logion(String login_id) throws Exception {
    // 바인드 변수 사용
    String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?";
    PreparedStatement st = con.prepareStatement(SQLStmt);
    st.setString(1, login_id);
    resultSet rs = st.executeQuery();
    if(rs.next()) {
    	// do anything
    }
    rs.close();
    st.close();
}


// 해당 SQL 요청 시 db의 라이브러리 캐시에 저장되는 값은 아래와 같다.
// SELECT * FROM CUSTOMER WHERE LOGIN_ID = :1

 

위 예시코드에서 알 수 있듯이, 바인드 변수를 사용하면 SQL에 대한 하드파싱은 최초 한 번만 일어나고, 캐싱된 SQL을 공유하면서 재 사용하게 된다.