제2절_SQL_파싱_부하

조회 수 5284 추천 수 0 2013.09.06 23:29:01
ljw *.148.176.25

1. SQL 처리과정

구조화된 질의언어(SQL, Structured Query Language)를 통해 사용자가 원하는 결과집합만 정의,

그것을 얻는 데 필요한 처리절차를 직접 기술하진 않음. 

우리 대신 프로그래밍 해 주는 엔진이 DBMS에 내장돼 있기 때문이며, SQL 옵티마이저가 바로 그런 역할을 해 준다. 옵티마이저에 의해 생성된 처리절차를 실행계획(Execution Plan)이라고 함

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

| Id  | Operation       | Name   | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |   106 |  7632 |     3   (0)| 00:00:01 |

|   1 |  NESTED LOOPS      |             |  106 |  7632 |     3   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  7276 |     3   (0)| 00:00:01 |

|*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |    4 |     0   (0)| 00:00:01 |

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

위 실행계획이 실제 실행 가능한 형태는 아니므로 코드(또는 프로시저) 형태로 변환하는 과정을 거치고 나서 SQL 엔진에 의해 수행

가. SQL 파싱(Parsing)

사용자가 SQL을 실행하면 제일 먼저 SQL 파서(Parser)가 SQL 문장에 문법적 오류가 없는지를 검사(→Syntax 검사)한다. 

문법적으로 오류가 없으면 의미상 오류가 없는지를 검사(→Semantic 검사)하는데, (예를 들어 존재하지 않거나 권한이 없는 객체를 사용했는지, 또는 존재하지 않는 칼럼을 사용했는지 등)

이런 검사를 마치면 사용자가 발행한 SQL과 그 실행계획이 라이브러리 캐시에 캐싱됐는지를 확인한다. 만약 캐싱돼 있다면, 무거운 최적화 과정을 거치지 않고 곧바로 실행할 수 있다.

  • 소프트 파싱(Soft Parsing) : SQL과 실행계획을 캐시에서 찾아 곧바로 실행단계로 넘어가는 경우를 말함
  • 하드 파싱(Hard Parsing) : SQL과 실행계획을 캐시에서 찾지 못해 최적화 과정을 거치고 나서 실행단계로 넘어가는 경우를 말함

라이브러리 캐시는 해시구조로 관리되기 때문에 SQL마다 해시 값에 따라 여러 해시 버킷으로 나누어 저장된다. SQL을 찾을 때는 SQL 문장을 해시 함수에 입력해서 반환된 해시 값을 이용해 해당 해시버킷을 탐색하면 된다.

나. SQL 최적화(Optimization)

옵티마이저 : 사용자가 요청한 SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리경로를 선택해 주는 DBMS의 핵심엔진. 

앞서 옵티마이저의 최적화 과정을 거치는 경우를 ‘하드(Hard) 파싱’이라고 표현했는데, 최적화 과정은 그만큼 무거운 작업을 수반. 

(예를 들어, 5개의 테이블을 조인하려면 조인 순서만 고려해도 5!(=120)개의 실행계획을 평가, 120가지 실행계획에 포함된 각 조인 단계별로 NL JOIN, Sort Merge Join, Hash Join 등 다양한 조인 방식까지 고려하면 경우의 수는 기하급수적으로 늘어남 )

파싱 과정을 거친 SQL과 실행계획을 여러 사용자가 공유하면서 재사용할 수 있도록 공유 메모리에 캐싱

2. 캐싱된 SQL 공유

가. 실행계획 공유 조건

방금 설명한 SQL 수행 절차

  • 문법적 오류와 의미상 오류가 없는지 검사한다.
  • 해시 함수로부터 반환된 해시 값으로 라이브러리 캐시 내 해시버킷을 찾아간다.
  • 찾아간 해시버킷에 체인으로 연결된 엔트리를 차례로 스캔하면서 같은 SQL문장을 찾는다.
  • SQL 문장을 찾으면 함께 저장된 실행계획을 가지고 바로 실행한다.
  • 찾아간 해시버킷에서 SQL 문장을 찾지 못하면 최적화를 수행한다.
  • 최적화를 거친 SQL과 실행계획을 방금 탐색한 해시버킷 체인에 연결한다.
  • 방금 최적화한 실행계획을 가지고 실행한다.

하드 파싱을 반복하지 않고 캐싱된 버전을 찾아 재사용하려면 캐시에서 SQL을 먼저 찾아야 하는데, 캐시에서 SQL을 찾기 위해 사용되는 키 값이 “SQL 문장 그 자체”.

나. 실행계획을 공유하지 못하는 경우

예를 들어, 아래 6가지 경우에 옵티마이저는 각각 다른 SQL로 인식해 별도의 실행계획을 수립한다.

공백 문자 또는 줄바꿈

SELECT * FROM CUSTOMER;SELECT * FROM CUSTOMER;

대소문자 구분

SELECT * FROM CUSTOMER; SELECT * FROM Customer;

주석(Comment)

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

테이블 Owner 명시

SELECT * FROM CUSTOMER; SELECT * FROM HR.CUSTOMER;

옵티마이져 힌트 사용

SELECT * FROM CUSTOMER; SELECT /*+ all_rows */ * FROM CUSTOMER;

조건절 비교 값

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';

라이브러리 캐시 효율과 직접 관련 있는 패턴은 6번. 

사용자가 입력한 값을 조건절에 문자열로 붙여가며 매번 다른 SQL로 실행하는 경우. 

3. 바인드 변수 사용하기

가. 바인드 변수의 중요성

사용자 로그인을 처리하는 프로그램에 SQL을 위 6번과 같은 식으로 작성하면, 아래 처럼 프로시저가 로그인 사용자마다 하나씩 만들어지게 된다.

procedure LOGIN_TOMMY() { … } 
procedure LOGIN_KARAJAN() { … }
procedure LOGIN_JAVAKING() { … }
procedure LOGIN_ORAKING() { … } . . .

위 프로시저의 내부 처리 루틴은 모두 같을 것이다. 그것이 가장 큰 문제인데, 모든 프로시저의 처리 루틴이 같다면 여러 개 생성하기보다 아래 처럼 로그인 ID를 파라미터로 받아 하나의 프로시저로 처리하도록 하는 것이 효율적.

procedure LOGIN(login_id in varchar2) { … }

이처럼 파라미터 Driven 방식으로 SQL을 작성하는 방법이 제공되는데, 그것이 곧 바인드 변수(Bind Variable)다. 바인드 변수를 사용하면 하나의 프로시저를 공유하면서 반복 재사용할 수 있게 된다.

SELECT * FROM CUSTOMER WHERE LOGIN_ID = :LOGIN_ID;

위 SQL과 같이 바인드 변수를 사용하면 이를 처음 수행한 세션이 하드파싱을 통해 실행계획을 생성한다.

그 실행계획을 한번 사용하고 버리는 것이 아니라 라이브러리에 캐싱해 둠으로써 같은 SQL을 수행하는 다른 세션들이 반복 재사용할 수 있도록 한다.

즉, 이후 세션들은 캐시에서 실행계획을 얻어 입력 값만 새롭게 바인딩하면서 바로 실행하게 된다. 아래는 바인드 변수를 사용한 SQL을 20,000번 수행할 때의 SQL 트레이스 결과다.

call count cpu elapsed disk query current rows 

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

Parse 20000 0.16 0.17 0 0 0 0

Execute 20000 0.22 0.42 0 0 0

Fetch 20000 0.45 0.47 0   60000 0   20000 

total 60000 1.23 1.07 0   60000 0   20000

Misses in library cache during parse: 1

Parse Call : SQL 문장을 캐시에서 찾으려고 시도한 횟수

Execute Call : 쿼리 실행 횟수 (실행 횟수만큼 Parse Call이 반복됨을 확인)

최초 Parse Call이 발생한 시점에 라이브러리 캐시에서 커서를 찾지 못해 하드 파싱 수행(라이브러리 캐시 Miss 항목)

바인드 변수 사용시 SQL과 실행계획을 여러 개 캐싱하지 않고 하나를 반복 재사용하므로 파싱 소요시간과 메모리 사용량을 줄여준다. 궁극적으로, 시스템 전반의 CPU와 메모리 사용률을 낮춰 데이터베이스 성능과 확장성을 높이고, 특히 동시 사용자 접속이 많을 때는 그 효과성이 절대적이다. 


단 다음의 경우 바인드 변수를 쓰지 않아도 무방하다.

  • 배치 프로그램이나 DW, OLAP 등 정보계 시스템에서 사용되는 Long Running 쿼리
    이들 쿼리는 파싱 소요시간이 쿼리 총 소요시간에서 차지하는 비중이 매우 낮고, 수행빈도도 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 낮음. 그러므로 바인드 변수 대신 상수 조건절을 사용함으로써 옵티마이저가 칼럼 히스토그램을 활용할 수 있도록 하는 것이 유리
  • 조건절 칼럼의 값 종류(Distinct Value)가 소수일 때
    특히 값 분포가 균일하지 않아 옵티마이저가 칼럼 히스토그램 정보를 활용하도록 유도하고자 할 때

위 경우가 아니라면, 특히 OLTP 환경에선 반드시 바인드 변수를 사용할 것을 권고.

각 DBMS는 조건절 비교 값이 리터럴 상수일 때 이를 자동으로 변수화 시켜주는 기능을 제공. 

SQL Server : ‘단순 매개 변수화(simple parameterization)’ 기본적으로 활성화된다. 

Oracle : cursor_sharing 파라미터를 시스템 또는 세션 레벨에서 FORCE나 SIMILAR로 설정(기본 값은 EXACT)하면 된다.


나. 바인드 변수 사용 시 주의사항

변수를 바인딩하는 시점이 최적화 이후라는 사실. 

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


다. 바인드 변수 부작용을 극복하기 위한 노력

oracle ‘바인드 변수 Peeking’  / SQL Server ‘Parameter Sniffing’ 

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

(실행계획이 수시로 바뀔 수 있기 때문에 위험한 기능.)


쿼리 수행 전에 확인하는 실행계획은 바인드 변수 Peeking 기능이 적용되지 않은 실행계획.

사용자가 쿼리 수행 전에 실행계획을 확인할 때는 변수에 값을 바인딩하지 않으므로 옵티마이저는 변수 값을 Peeking 할 수 없다. 

따라서 사용자는 평균 분포에 의한 실행계획을 확인하고 프로그램을 배포하게 되는데, 그 SQL이 실제 실행될 때는 바인드 변수 Peeking을 일으켜 다른 방식으로 수행될 수 있다. 이런 이유로, 현재 대부분의 운영 시스템에서는 아래 처럼 바인드 변수 Peeking 기능을 비활성화시킨 상태에서 운영 중이다.

alter system set "_optim_peek_user_binds" = FALSE ;


‘적응적 커서 공유(Adaptive Cursor Sharing) : Oracle11g에서 도입된 기능. 입력된 변수 값의 분포에 따라 다른 실행계획이 사용되도록 처리하고 있다. 이 기능도 아직 완전하지 못하기 때문에 부작용이 완전히 해소될 때까진 개발자의 노력이 필요.

예를 들어, 아래 쿼리로 아파트매물 테이블을 읽을 때 서울시와 경기도처럼 선택도(Selectivity, 3장 1절 4항 참조)가 높은 값이 입력될 때는 Full Table Scan이 유리하고, 강원도나 제주도처럼 선택도가 낮은 값이 입력될 때는 인덱스를 경유해 테이블을 액세스하는 것이 유리하다.


select * 

 from 아파트매물 

where 도시 = :CITY ;

그럴 때 위 쿼리에서 바인딩 되는 값에 따라 실행계획을 아래와 같이 분리하는 방안을 고려할 수 있다.

select /*+ FULL(a) */ *

  from 아파트매물 a 

where :CITY in ('서울시', '경기도') 

  and 도시 = :CITY 

union all 

select /*+ INDEX(a IDX01) */ * 

  from 아파트매물 a 

where :CITY not in ('서울시', '경기도') 

 and 도시 = :CITY;


4. Static SQL과 Dynamic SQL

가. Static SQL

Static SQL : String형 변수에 담지 않고 코드 사이에 직접 기술한 SQL문을 말한다. (‘Embedded 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); 

}

SQL문을 String 변수에 담지 않고 마치 예약된 키워드처럼 C/C++ 코드 사이에 섞어서 기술한 것을 볼 수 있다. 

Pro*C에서 소스 프로그램(.pc)을 작성해서 PreCompiler로 PreCompile하면 순수 C/C++ 코드가 만들어진다. 

이를 다시 C/C++ Compiler로 Compile해 실행파일이 만들어지면 그것을 실행한다. PreCompiler가 PreCompile 과정에서 Static(=Embedded) SQL을 발견하면 이를 SQL 런타임 라이브러리에 포함된 함수를 호출하는 코드로 변환. 

이 과정에서 결국은 String형 변수에 담긴다. Static SQL이든 Dynamic SQL이든 PreCompile 단계를 거치고 나면 String 변수에 담기기는 마찬가지지만 Static SQL은 런타임 시에 절대 변하지 않으므로 PreCompile 단계에서 구문 분석, 유효 오브젝트 여부, 오브젝트 액세스 권한 등을 체크하는 것이 가능.

나. Dynamic SQL

Dynamic SQL이란, String형 변수에 담아서 기술하는 SQL문. 

String 변수를 사용하므로 조건에 따라 SQL문을 동적으로 바꿀 수 있고, 또는 런타임 시에 사용자로부터 SQL문의 일부 또는 전부를 입력 받아서 실행할 수도 있음. 

따라서 PreCompile 시 Syntax, Semantics 체크가 불가능하므로 Dynamic SQL에 대해선 PreCompiler는 내용을 확인하지 않고 그대로 DBMS에 전달한다. 아래는 Pro*C에서 Dynamic SQL을 작성한 사례다. 


int main() { 

char select_stmt50 = "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); 

}

Static(=Embedded) SQL을 지원하는 개발 언어는 많지 않으며, PowerBuilder, PL/SQL, Pro*C, SQLJ 정도가 있다. 

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

지금까지 설명한 Static, Dynamic SQL은 애플리케이션 개발 측면에서의 구분일 뿐이며, 데이터베이스 입장에선 차이가 없다.

Static SQL을 사용하든 Dynamic SQL을 사용하든 옵티마이저는 SQL 문장 자체만 인식할 뿐이므로 성능에도 영향을 주지 않는다. (단, Static SQL일 때만 애플리케이션 커서 캐싱 기능이 작동하는 개발 언어도 있으므로 그때는 성능에 영향을 줄 수 있다.) 

따라서 라이브러리 캐시 효율을 논할 때 Static이냐 Dynamic이냐의 차이보다는 바인드 변수 사용 여부에 초점을 맞춰야 한다. 

Dynamic으로 개발하더라도 바인드 변수만 잘 사용했다면 라이브러리 캐시 효율을 떨어뜨리지 않음. 

5. 애플리케이션 커서 캐싱

‘애플리케이션 커서 캐싱’ : soft parsing을 일으키지 않고 빠르게 sql을 수행할수 있는 방법. 

(soft 파싱이 되더라도 SQL 문장의 문법적, 의미적 오류가 없는지 확인하고, 해시함수로부터 반환된 해시 값을 이용해 캐시에서 실행계획을 찾고, 수행에 필요한 메모리 공간(Persistent Area와 Runtime Area)을 할당하는 등의 작업을 매번 반복해야 하므로 비효율 발생)

pro*C

for(;;) {

EXEC ORACLE OPTION (HOLD_CURSOR=YES);

EXEC ORACLE OPTION (RELEASE_CURSOR=NO);

EXEC SQL INSERT …… ; // SQL 수행 EXEC ORACLE OPTION (RELEASE_CURSOR=YES); }


아래는 애플리케이션에서 커서를 캐싱한 상태에서 같은 SQL을 5,000번 반복 수행했을 때의 SQL 트레이스 결과다.

call count cpu elapsed disk query current rows 

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

Parse 1 0.00 0.00 0 0 0

Execute    5000 0.18 0.14 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


일반적인 방식으로 같은 SQL을 반복 수행할 때는 Parse Call 횟수가 Execute Call 횟수와 같음. 

반면, 위 트레이스 결과에선 Parse Call이 한 번만 발생했고, 이후 4,999번 수행할 때는 Parse Call이 전혀 발생하지 않았음을 알 수 있다. 최초 Parse Call이 발생한 시점에 하드 파싱을 수행한 사실도 아래쪽 라이브러리 캐시 Miss 항목을 보고 알 수 있다. JAVA에서 이 기능을 구현하려면 아래와 같이 묵시적 캐싱(Implicit Caching) 옵션을 사용하면 된다.


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(); 

}//end for

}

또는 아래처럼 Statement를 닫지 않고 재사용해도 같은 효과를 얻을 수 있다.

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();

}

// 루프를 빠져 나왔을 때 커서는 위와 같은 옵션을 별도로 적용하지 않더라도 자동적으로 커서를 캐싱.

//단, Static SQL을 사용할 때만 그렇다. 

Dynamic SQL을 사용하거나 Cursor Variable(=Ref Cursor)을 사용할 때는 커서를 자동으로 캐싱하는 효과가 사라진다는 사실을 명심하기 바란다