[📕 친절한 SQL 튜닝] 1-2. SQL 공유 및 재사용

이번에는 소프트 파싱과 하드 파싱의 차이점 알아보기.

 

 

 

소프트 파싱  vs 하드 파싱

사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱하고 해당 SQL이 라이브러리 캐시에 존재하는지부터 확인
캐시에서 찾으면 곧바로 실행 단계로 넘어가지만, 찾지 못하면 최적화 단계를 거침

✨ 라이브러리 캐시란 ?

SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간으로, SGA 구성 요소임

✨ SGA (System Global Area) 란 ?

서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간

---

🔹 소프트 파싱

• SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것

 

🔹 하드 파싱

• 캐시에서 찾는 걸 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것

 

 

 

 

 

바인드 변수의 중요성

🔹 이름없는 SQL 문제 

SQL은 사용자 정의 함수/프로시저, 트리거, 패키지 등처럼 이름이 따로 존재하지 않으므로 딕셔너리에 저장하지도 않는다.

SQL 자체가 이름이기 때문에 텍스트 중 작은 부분이라도 수정되면 그 순간 다른 객체가 새로 탄생하는 구조다.

 

SQL ID는 SQL 전체 텍스트를 간략히 표현하기 위해 오라클이 내부 함수를 이용하영 생성한 값이므로 SQL 전체 텍스트와 1:1 대응 관계를 갖는다.

그러므로 SQL 텍스트가 변하면 SQL ID도 변한다.

DBMS에서 수행되는 SQL이 모두 완성된 것이 아니며, 개발 과정에는 수시로 변경된다.

일회성도 많고 무효화되는 것도 많으므로 SQL을 모두 저장하려면 많은 공간이 필요하고, 그만큼 SQL을 찾는 속도도 느려진다. 

이러한 이유로 DBMS가 SQL을 영구 저장하지 않는다. 

 

---

🔹 공유 가능 SQL

public void login(String login_id) throws Exception {

	String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = '" + login_id + "'";
    Statement st = con.createStatement();
	ResultSet rs = st.executeQuery(SQLStmt);
    
    if(rs.next()) {
    	// do anything
    }
    
    rs.close();
    st.close();
}

 

500만 고객을 보유한 쇼핑몰에서 로그인 모듈 담당자가 위와 같은 프로그램을 작성했다 하고, 쇼핑몰에서 12시 정각부터 30분간 할인 이벤트를 하기로 했다.

 

500만 명 중 20%에 해당하는 100만 고객이 이벤트 당일 12시를 전후해 동시에 시스템을 접속한다면 

각 고객에 대한 동시다발적인 SQL 하드파싱 때문에 I/O가 거의 발생하지 않음에도 CPU 사용률은 급격히 올라갈 것이다. 

 

아마 라이브러리 캐시를 조회하면 SQLStmt에 해당하는 쿼리문으로 가득 차 있을 것이다. 

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

create procedure LOGIN_GAMJA() {...}
create procedure LOGIN_GOGUMA() {...}
create procedure LOGIN_GAJI() {...}
create procedure LOGIN_CARROT() {...}
create procedure LOGIN_PEA() {...}
...
...
...

위 프로시저의 내부 처리 루틴은 모두 같다. 

아래와 같이 로그인ID를 파라미터로 받는 프로시저 하나를 공유하면서 재사용하는 것이 적합할 것이다. 

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

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

---

public void login(String login_id) throws Exception {

	String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?";
    Statement st = con.createStatement();
	ResultSet rs = st.executeQuery(SQLStmt);
    
    if(rs.next()) {
    	// do anything
    }
    
    rs.close();
    st.close();
}

로그인 프로그램을 위와 같이 수정하면 라이브러리 캐시에 로그인과 관련해서 아래 SQL 하나만 발견된다.

SELECT * FROM CUSTOMER WHERE LOGIN_ID = :1

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

:1은 바인드 변수를 의미하고, 실제 실행될 때 :1 자리에 특정 값이 바인딩되어 실행된다.