SQLP - SQL 파싱 부하

@markdown

# SQL 파싱 부하

</br>

## SQL 처리 과정

____

- 사용자는 구조화된 질의언어(SQL)을 통해 사용자가 원하는 결과집합을 정의

- DBMS는 사용자의 SQL을 SQL 옵티마이저를 통해 실행계획으로 작성해줌

<pre><code class="SQL" style="font-size:14px">Execution Plan 

---------------------------------------------------------- 

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=209 Card=5 Bytes=175) 

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=5 Bytes=85) 

2 1 NESTED LOOPS (Cost=209 Card=5 Bytes=175)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=207 Card=1 Bytes=18) 

4 3 INDEX (RANGE SCAN) OF 'DEPT_LOC_IDX'(NON-UNIQUE) (Cost=7 Card=1) 

5 2 INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX'(NON-UNIQUE) (Cost=1 Card=5) 

</code></pre>

</br>

### SQL 파싱

- 1) SQL을 실행하면 먼저 SQL Parser가 SQL 문장에 문법적 오류가 없는지 검사(Syntax 검사)

- 2) 문법적 오류가 없다면 의미상 오류가 없는지 검사(Semantic 검사, Object 존재유무 등)

- 3) 검사를 마치면, 사용자가 발생한 SQL과 그 실행계획이 라이브러리캐시에 캐싱되어 있는지 확인

- 4) 캐싱되어 있다면 소프트파싱, 캐싱되어 있지 않다면 하드파싱

#### 소프트파싱(Soft Parsing)

- SQL과 실행계획을 캐시에서 찾아 곧바로 실행단계로 넘어가는 경우

#### 하드 파싱(Harding Parsing)     

- SQL과 실행계획을 캐시에서 찾이 못해 최적화 과정을 거치고 나서 실행단계로 넘어가는 경우

### 최적화(Optimization)

- <b>SQL 최적화를 담당하는 옵티마이저는 사용자가 요청한 SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 선택해주는 DBMS의 핵심</b>

    - 1) 5개의 테이블을 조인한다면, 발생하는 경우의 수가 5!이고 120개의 실행계획을 평가

    - 2) 120가지 실행계획에 포함된 각 단계별 다양한 조인 방식을 고려

    - 3) 해당 테이블을 Full Scan 할지, Index를 사용할지 고려

    - 위와 같이 최적화 과정이 무거운 작업이므로 최적화된 SQL 실행계획을 한번만 사용한다면 비효율이 발생하므로 여러 사용자가 공유해서 재사용할 수 있도록 공유메모리에 캐싱해놓는다.

</br>

## 캐싱된 SQL 공유

____

### 실행계획 공유 조건

- 1) 문법적 오류와 의미상 오류가 없는지 검사

- 2) 해시 함수로부터 반환받은 해시 값으로 라이브러리 캐시 내 해시버킷 탐색

- 3) 찾아간 해시 버킷에 체인으로 연결된 엔트리를 차례로 스캔하면서 같은 SQL 문장 탐색

- 4) SQL 문장을 찾으면 함께 저장된 실행계획을 가지고 바로 실행

- 5) 찾아간 해시 버킷에 SQL 문장을 찾이 못하면 최적화를 수행

- 6) 최적화를 거친 SQL과 실행계획을 방금 탐색한 해시 버킷 체인에 연결

- 7) 위 과정을 거쳐 최적화된 실행계획을 가지고 실행

<b>하드파싱을 반복하지 않고 캐싱된 버전을 찾아 재사용하려면 SQL을 먼저 찾아가야 하며, 캐시에서 SQL을 찾기위해 사용되는 키값은 SQL 문장 그 자체</br>

→ 이 때문에 SQL 문장안의 작은 공백 하나로도 DBMS는 서로 다른 SQL 문장으로 인식할수 있으므로 주의 해야함</b>

</br>

### 실행계획을 공유하지 못한 경우

- 1) 공백 또는 줄바꿈

<pre><code class="SQL" style="font-size:14px">SELECT * FROM CUSTOMER;

SELECT *    FROM CUSTOMER;  

</code></pre>

- 2) 대문자 구분

<pre><code class="SQL" style="font-size:14px">SELECT * FROM CUSTOMER; 

SELECT * FROM Customer; 

</code></pre>

- 3) 주석

<pre><code class="SQL" style="font-size:14px">SELECT * FROM CUSTOMER; 

SELECT /* 주석문 */ * FROM CUSTOMER; 

</code></pre>

- 4) 테이블 Owner 명시

<pre><code class="SQL" style="font-size:14px">SELECT * FROM CUSTOMER; 

SELECT * FROM HR.CUSTOMER; 

</code></pre>

- 5) 옵티마이저 힌트 사용

<pre><code class="SQL" style="font-size:14px">SELECT * FROM CUSTOMER;

SELECT /*+ all_rows */ * FROM CUSTOMER;

</code></pre>

- 6) 조건절 비교값

<pre><code class="SQL" style="font-size:14px">SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'tommy'; 

SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'karajan'; 

SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'javaking'; 

SELECT * FROM CUSTOMER WHERE LOGIN_ID = 'oraking'; 

</code></pre>

- 위와 같은 비효율을 줄이고 공유 가능한 형태로 SQL을 작성하려면 개발 초기에 SQL 작성표준을 정해서 이를 준수해야함

- 6번 같은 경우 조건절 값을 문자열로 붙여가며 매번 다른 SQL로 실행되는 리터럴 SQL의 경우, 사용자가 많이 몰리는 시간대에는 장애상황으로 발생할 수 있으므로 바인드 변수의 사용을 고려해야함

</br>

## 바인드 변수 사용하기

____

### 바인드 변수의 중요성

- 사용자가 로그인을 하는 프로그램이 리터럴 SQL로 만들어져 있다면, 아래와 같이 로그인 사용자가 생길때마다 프로시저가 하나씩 만들어지게 된다.

<pre><code class="SQL" style="font-size:14px">procedure LOGIN_TOMMY() { ... } 

procedure LOGIN_KARAJAN() { ... } 

procedure LOGIN_JAVAKING() { ... } 

procedure LOGIN_ORAKING() { ... } 

. 

. 

. 

</code></pre>

- 위와 같은 경우 로그인 ID를 파라미터로 받아서 하나의 프로시저로 처리하도록 해야한다.

<pre><code class="SQL" style="font-size:14px">procedure LOGIN(login_id in varchar2) { ... } 

</code></pre>

- 이와 같은 Driven 방식으로 SQL을 작성하는 방법이 제공되면 이것이 곧 바인드 변수이며, 이렇게 바인드 변수를 사용하면 하나의 프로시저를 공유하면서 반복 재사용이 가능하다.

<pre><code class="SQL" style="font-size:14px">SELECT * FROM CUSTOMER WHERE LOGIN_ID = :LOGIN_ID;

</code></pre>

#### 바인드 변수를 사용했을떄의 효과

- SQL과 실행계획을 반복적으로 재사용함으로써 파싱 소요시간과 메모리 사용량을 줄여줌

- 궁극적으로 시스템 전반의 CPU와 메모리 사용률을 낮춰 데이터베이스 성능과 확장성을 높임

#### 바인드 변수를 사용하지 않아도 되는 예외상황

- 배치프로그램이나 DW, OLAP 등 정보계 시스템에서 사용되는 Long Running 쿼리

- 조건절의 컬럼의 값 종류가 소수일때

### 바인드 변수 사용 시 주의사항

- 옵티마이저는 나중에 반복 수행될 때 어떤 값이 입력될지 알 수 없기 때문에 옵티마이저는 조건절 컬럼의 데이터 분포가 균일하다는 가정을 세우고 최적화를 수행한다.

- 컬럼의 분포가 균일할때는 바인드 변수 처리가 나쁘지 않다.

- 컬럼의 분포가 균일하지 않을때에는 실행 시점에 바인딩되는 값에 따라 쿼리 성능이 다르게 나타날 수 있으므로 이럴때는 상수값을 사용하는것이 나을 수 있다.

### 바인드 변수 부작용을 극보하기 위한 노력

- Oracle 9i 부터 바인드 변수 `Peeking` 기능을 도입

- SQL이 첫 번째 수행될 때의 바인드 변수 값을 살짝 훔쳐 보고, 그 값에 대한 컬럼 분포를 이용하여 실행계획을 결정하는 기능이다.

- 하지만 이 `Peeking` 기능은 처음 실행될 때 입력된 값과 전혀 다른 분포를 갖는 값이 입력되면 쿼리 성능이 갑자기 나빠질 수 있기 때문에 위험한 기능이다.

- 위와 같은 위험요소를 줄이기 위해 Oracle 11g에서는 'Adaptive Cursor Sharing' 기능을 도입함으로써 입력된 변수 값의 분포에 따라 다른 실행계획이 사용되도록 처리하고 있다.

</br>

## Static SQL과 Dynamic SQL

____

### Static SQL

- String형 변수에 담지 않고 코드에 직접 기술한 SQL(Embedded SQL)

<pre><code class="C" style="font-size:14px">Proc*C 구분으로 Static SQL 작성한 예시

int main() 

{ 

  printf("사번을 입력하십시오 : "); 

  scanf("%d", &empno); 

  EXEC SQL WHENEVER NOT FOUND GOTO notfound; 

  EXEC SQL SELECT ENAME INTO :ename 

           FROM EMP 

           WHERE EMPNO = :empno; 

  printf("사원명 : %s.\n", ename); 

notfound: 

  printf("%d는 존재하지 않는 사번입니다. \n", empno);

} 

</code></pre>

- SQL문장을 String 변수에 담지 않고 `EXEC` 예약된 키워드 처럼 사용하여 기술한 것

- PreCompile 과정에서 Static SQL이 발견하면 이를 SQL 런타임 라이브러리에 포함된 함수를 호출하는 코드로 변환한다. 이 과정에서 String 변수에 담긴다.

- Static SQL은 PreCompile 단계에서 절대 변하지 않으므로 구문 분석, 유효 오브젝트 여부, 오브젝트 엑세스 권한 등의 체크가 가능하다.

### Dynamic SQL

- String형 변수에 담아서 기술하는 SQL문

- 조건에 따라 SQL문을 동적으로 바꿀 수 있고, 런타임 시에 사용자로부터 SQL문의 일부를 입력 받아 실행할 수 있다.

- PreCompile시 문법, 의미 오류 체크가 불가능

<pre><code class="C" style="font-size:14px">int main() 

{ 

   char select_stmt[50] = "SELECT ENAME FROM EMP WHERE EMPNO = :empno"; 

   // scanf("%c", &select_stmt); → SQL문을 동적으로 입력 받을 수도 있음

   EXEC SQL PREPARE sql_stmt FROM :select_stmt; 

   EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt; 

   EXEC SQL OPEN emp_cursor USING :empno; 

   EXEC SQL FETCH emp_cursor INTO :ename; 

   EXEC SQL CLOSE emp_cursor; 

   printf("사원명 : %s.\n", ename); 

} 

</code></pre>

</br>

### 바인드 변수의 중요성 재강조

- Static를 사용하든 Dynamic SQL을 사용하든 옵티마이저는 SQL 문장 자체만을 인식할 뿐이므로 성능에 영향을 주지는 않는다.

- 라이브러리 캐시 효율을 따질때에는 `Static`이냐 `Dynamic`이냐의 차이보다는 `바인드 변수` 사용 여부에 초점을 맞춰야 함

- 바인드 변수를 사용하지 않고 리터럴 값을 SQL 문자열에 결합하는 방식으로 개발했을 경우, 반복적인 하드파싱으로 심각한 성능 저하와 라이브러리 캐시에 심한 경합이 발생

</br>

## 애플리케이션 커서 캐싱

____

- 같은 SQL을 여러 번 반복해서 수행해야 할 때, 첫번째는하드파싱이 일어나겠지만 이후부터는 라이브러리 캐시에 공유되 버전을 찾아 가볍게 실행한다.

- 그렇더라도 SQL 문장의 문법적, 의미적 오류가 없는지 확인하고, 해시함수로부터 반환된 해시 값을 이용해 캐시에서 실행계획을 찾고, 수행에 필요한 메모리 공간을 할당하는 등의 작업을 매번 반복하는 것은 비효율적이다.

- 이러한 비효율을 줄이기 위한 방법이 `애플리케이션 커서 캐싱`이다.(개발언어마다 API 참조)

<pre><code class="C" style="font-size:14px">for(;;) { 

   EXEC ORACLE OPTION (HOLD_CURSOR=YES); 

   EXEC ORACLE OPTION (RELEASE_CURSOR=NO); 

   EXEC SQL INSERT ...... ; // SQL 수행

   EXEC ORACLE OPTION (RELEASE_CURSOR=YES); 

} 

</code></pre>

- SQL을 수행하는 부분을 두 개 옵션으로 감싸면, 커서를 해제하지 않고 루프 내에서 반복 재사용함

<pre><code class="SQL" style="font-size:14px">call count cpu elapsed disk query current rows 

----- ------ ----- ------- ----- ------- ------ ----- 

Parse      1  0.00    0.00     0      0       0     0 

Execute 5000  0.18    0.14     0      0       0     0 

Fetch   5000  0.17    0.23     0  10000       0  5000 

----- ------ ----- -------  ----- ------- ------  ----- 

total 10001 0.35 0.37 0 10000 0 5000 

Misses in library cache during parse: 1 

</code></pre>

- 위의 실행결과는 커서를 캐싱한 상태에서 같은 SQL을 5,000번 반복 수행했을 때이다.

- 일반적인 방법으로 같은 SQL을 반복 수행할 때는 `Parse Call` 횟수가 `Execute Call` 횟수와 같게 나타난다.

- 반면, 커서 캐싱을 사용한 결과 최초 `Parse Call` 1번만 발생했고, 이후에는 발생하지 않았음을 확인할 수 있다. 

#### Java에서의 묵시적 캐싱 옵션 사용 코드

<pre><code class="java" style="font-size:14px">public static void CursorCaching(Connection conn, int count) throws Exception{ 

    // 캐시 사이즈를 1로 지정 

    ((OracleConnection)conn).setStatementCacheSize(1); 

    // 묵시적 캐싱 기능을 활성화 ((OracleConnection)conn).setImplicitCachingEnabled(true); 

    for (int i = 1; i <= count; i++) { 

        // PreparedStatement를 루프문 안쪽에 선언 

        PreparedStatement stmt = conn.prepareStatement( 

          "SELECT ?,?,?,a.* FROM emp a WHERE a.ename LIKE 'W%'");

        stmt.setInt(1,i); 

        stmt.setInt(2,i); stmt.setString(3,"test"); 

        ResultSet rs=stmt.executeQuery(); 

        rs.close(); 

        // 커서를 닫더라도 묵시적 캐싱 기능을 활성화 했으므로 닫지 않고 캐시에 보관하게 됨 

        stmt.close(); 

    } 

} 

</code></pre>

</br>

#### Statement를 닫지 않고 재사용 할 수 있는 코드

<pre><code class="java" style="font-size:14px">public static void CursorHolding(Connection conn, int count) throws Exception{ 

    // PreparedStatement를 루프문 바깥에 선언 

    PreparedStatement stmt = conn.prepareStatement( 

    "SELECT ?,?,?,a.* FROM emp a WHERE a.ename LIKE 'W%'"); 

    ResultSet rs; 

    for (int i = 1; i <= count; i++) { 

        stmt.setInt(1,i); 

        stmt.setInt(2,i); 

        stmt.setString(3,"test");

        rs=stmt.executeQuery(); 

        rs.close(); 

    } // 루프를 빠져 나왔을 때 커서를 닫는다.

    stmt.close();

}

</code></pre>

> SQL 전문가 가이드 : 과목3. SQL 고급 활용 및 튜닝, 1장 SQL 아키텍처 기반 튜닝 원리 - 제2절 SQL 파싱 부하(p.488 ~ p.498)

> 사진 출처 : 구루비넷