08장. 바인드 변수의 사용

8장. Bind 변수의 사용

 

학습 목표

 

이번 장을 완료하면 다음과 같은 작업을 수행 할 수 있다.

①   Bind 변수를 사용하여 얻을 수 있는 이득을 나열 할 수 있다.

②   Bind 엿보기를 사용 할 수 있다.

③   적응형 커서 공유를 사용 할 수 있다.

 

커서 공유와 서로 다른 Literal

그림 8.1

 

만약, WHERE 절에 Literal이 포함 된 SQL 문장을 사용한다면 라이브러리 캐시에 저장된 거의 동일한

SQL의 여러 버전으로부터 결과를 얻을 수 있다. 각 SQL 문장이 서로 다른 값과 함께 제출 되고,

라이브러리 캐시 내에서 해당 문장이 발견되지 않으면 새로운 SQL 문장을 처리하기 위해 모든 절차를

반드시 수행하여야 한다. 이로 인하여 불필요한 문장 파싱이 발생 할 뿐만 아니라 유사한 문장들로

인하여 라이브러리 캐시가 빠르게 채워지게 된다.

 

이러한 방식으로 코딩을 하게 되면 커서 공유의 장점을 얻을 수 없다.

 

그러나, 제공된 문자 값에 따라 옵티마이저에 의해 서로 다른 실행 계획이 발생될 수도 있다. 예를 들어,

JOBS 테이블에서 MIN_SALARY가 12000을 초과하는 행들이 많은 반면에 18000을 초과 하는 행들은

극히 적을 수 있다. 이러한 데이터 분포의 차이는 쿼리에 제공되는 값에 따라 서로 다른 실행 계획이

사용될 수 있으므로 인덱스를 추가하는 이유를 정당화한다. 이것을 그림에서 설명하고 있다. 그림에서

보는 바와 같이 첫 번째와 세 번째 쿼리는 동일한 실행 계획을 사용하지만, 두 번째 쿼리는 다른 실행

계획을 사용하고 있다.

 

성능 상의 관점에서 보면, 각각 별도의 커서를 갖는 것이 좋다. 그러나, 이 예제에서는 첫 번째 쿼리와

마지막 쿼리의 경우 커서를 공유 할 수 있었기 때문에 경제적인 편은 아니다.

 

참고 : 그림의 예제의 경우, 첫 번째 쿼리와 세 번째 쿼리의 V$SQL.PLAN_HASH_VALUE가 동일하다.

 

커서 공유와 Bind 변수

그림 8.2

만약, 각 Literal에 대하여 서로 다른 문장을 실행하는 대신에 Bind 변수를 사용하면 추가적인 파싱 활동을

생략 할 수 있다. 옵티마이저는 해당 문장이 이미 파싱되었다는 사실을 알고 있으며, 동일한 문장을 다음

번에 실행 할 때는 Bind 변수에 다른 값을 지정하였다 하더라도 동일한 실행 계획을 재사용한다.

 

그림의 예제에서 Bind 변수는 min_sal이고, JOBS 테이블의 MIN_SALARY 컬럼과 비교된다. 세 개의 서로

다른 문장을 실행하는 대신 Bind 변수를 사용하는 단일 문장을 실행한다. 실행 시, 동일한 실행 계획이

사용되고, 변수에는 주어진 값이 입력된다.

 

그러나 성능 상의 관점에서 보면, 세 번의 문장 실행 중 두 번만 우수한 성능을 얻을 수 있기 때문에 최상은

아니지만, 세 개의 문장을 실행하는데 있어서 라이브러리 캐시 내에 하나의 공유 커서만 필요하므로 매우 경제적이다.

 

SQL*Plus내 Bind 변수

그림 8.3

 

SQL*Plus 세션에서도 Bind 변수를 사용 할 수 있다. SQL*Plus에서 Bind 변수를 정의하려면 VARIABLE 명령을 사용한다.

그 다음은 EXEC[UTE] 명령으로 대입 문장을 실행하여 변수에 값을 할당 할 수 있다. 이 후, 할당한 변수를 사용하여 변수를 참조 할 수 있다.

위 예제에서 첫 번째 집계는 변수에 SA_REP을 할당하여 30이고, 그 다음은 변수에 AD_VP를 할 당하여 2이다.

 

EM에서 Bind 변수

그림 8.4

 

EM의 SQL Worksheet 페이지(Database Home 페이지의 Related Links 영역 내에 SQL Worksheet 링크)에서

SQL 문장에 Bind 변수를 사용 할 수 있도록 지정 할 수 있다. 이를 위해서 Use bind variables for execution

체크 박스를 선택한다. 체크 박스를 선택하면 몇 개의 항목이 생성되며, 여기에 Bind 변수 값을 입력 할 수 있다.

SQL 문장에서 이 값들은 콜론으로 시작되는 변수 명으로 참조된다. 참조 할 변수의 순서는 어떤 변수가 어떤 값을

가져와야 하는지를 정의하는 것이다.

첫 번째 변수가 첫 번째 값을 갖고, 두 번째 변수가 두 번째 값을 갖는다. 만약, 문장 내에서 참조될 변수의 순서가

변경되면 순서에 맞추어 값의 목록도 변경 해 줄 필요가 있다.

 

Bind 변수 엿보기

그림 8.5

 

Literal이 쿼리에 사용되면, 해당 Literal은 최상의 실행 계획을 결정하기 위해 옵티마이저에 의해 서 사용 될 수 있다.

그러나, Bind 변수가 사용되더라도, 옵티마이저는 여전히 쿼리 내의 조건 에 기술된 값을 기반으로 최상의 실행 계획을

선택해야 하지만, SQL 문장 내에서 해당 값들을 읽을 수는 없다. SQL 문장이 파싱 될 때, 변수에 할당된 값에 합당한

우수한 실행 계획을 생성하려 면 시스템은 Bind 변수의 값을 엿볼 수 있어야 한다. 옵티마이저는 이를 위해 Bind 엿보기

(peeking)를 수행한다. SQL 문장이 하드 파싱되면 옵티마이저는 각 Bind 변수의 값을 평가하고 최상의 실행 계획을

결정하기 위해 해당 입력값을 사용한다. 쿼리를 최초로 파싱하고 실행 계획 이 결정되면, 동일한 문장을 실행 할 때,

Bind 변수에 사용한 값과는 무관하게 동일한 실행 계 획을 사용한다.

 

이 기능은 Oracle9i Database Release 2에서 처음으로 소개되었지만 Oracle Database 11g에서 동작 특성이 변경되었다.

그림 8.6

 

일부 조건 하에서 Bind 엿보기는 옵티마이저가 준 최적 실행 계획을 선택하도록 유발 할 수도 있다. 이러한 현상이

발생하는 이유는 Bind 변수의 최초 값이 실행 계획을 결정하는데 사용되며, 이후 해당 쿼리를 실행 할 때는 이미

작성된 실행 계획을 사용하기 때문이다. 다음 번에 해당 문장을 실행 할 때 다른 Bind 변수 값을 제공하더라도

동일한 실행 계획이 사용된다. 서로 다 른 Bind 변수 값을 갖는 문장을 실행 할 때는 서로 다른 실행 계획을

사용하는 것이 더 나을 수 있다. 예제는 특정 인덱스의 선택도가 컬럼 값에 따라 극단적으로 변화하는 경우이다.

선택도 가 낮을 때는 전체 테이블 스캔이 빠를 수 있지만, 높은 선택도에서는 인덱스 범위 스캔이 좀 더 적합

할 수 있다. 그림에서 보여지는 것처럼 실행 계획 A는 min_sal의 첫 번째와 세 번째 값에 대하여 적절하지만

두 번째 값에 대해서는 적합하지 않을 수 있다.

MIN_SALARY 값이 18000을 초과하는 행이 매우 적고, 실행 계획 A가 전체 테이블 스캔이라고 가정하자.

이 경우에 두 번째 문장 실행에 대하여 전체 테이블 스캔은 좋은 실행 계획이 될 수 없다.

 

Bind 변수는 커서 공유를 많이 발생시키고 SQL의 파싱을 감소시켜주는 장점이 있지만 특정 바인드 변수 값에

대해서는 준최적 실행 계획을 작성 할 가능성이 있다. DSS 환경에서는 쿼리를 실 행 했을 때, 수행해야 할 작업

중 쿼리의 파싱이 매우 작은 부분을 차지하기 때문에 Bind 변수 의 사용을 가능하면 제한하여야 한다. 파싱은

매우 짧은 시간이 소요되지만 문장의 실행은 수 분 에서 수 시간이 소요 될 수 있다. 수행 시간이 긴 실행 계획에

대하여 파싱 시간을 절약하는 것 은 큰 의미가 없다.

 

향상된 커서 공유

 

Oracle8i는 Literal만 다른 SQL 문장을 공유 할 수 있도록 하였다. 서로 다른 Literal을 가진 동일 한 문장이 실행 될 때마다

실행 계획을 새로 작성하는 대신, 옵티마이저는 공통의 실행 계획을 생성하여 해당 문장을 추후에 다시 실행하는 경우,

재사용 될 수 있도록 하였다.

 

오직 하나의 실행 계획이 잠재적인 다른 실행 계획 대신에 사용되므로 이 기능을 애플리케이션에 적용 할 때는 사전에 먼저

테스트하여 사용 여부를 결정하여야 한다.

이 후, Oracle9i에서는 이 기 능을 확장하여 유사한(similar) 문장으로 인식되는 문장들만 공유 되도록 하였다. 즉, 실행 계획이

사용된 Literal과 독립적이라는 것을 보장하는 경우에만 재사용된다. 예를 들어, EMPLOYEE_ID가 기본키인 쿼리를 가정하자

 

SQL> SELECT * FROM employees WHERE employee_id=153;

 

어떠한 Literal이 와도 위 문장은 동일한 실행 계획을 만들어낸다. 그러므로, 옵티마이저는 서로 다른 Literal을 갖는

동일한 문장이 여러 번 실행되더라도 오직 하나의 실행 계획을 생성한다.

 

한편, 동일한 EMPLOYEES 테이블이 DEPARTMENT_ID 컬럼에 넓은 범위의 값을 갖는다고 가정하 자. 예를 들어, 50번

부서에는 전체 사원의 1/3이 포함되고 70번 부서에는 오직 한 사람 혹은 두 사람만이 포함된다고 가정하고 다음 두 쿼리를 보자.

 

SQL> SELECT * FROM employees WHERE department_id = 50; SQL> SELECT * FROM employees WHERE department_id = 70;

 

만약, DEPARTMENT_ID 컬럼에 히스토그램 통계를 가지고 있다면 동일한 커서를 공유하는 하나의 실행 계획은 안전 할 수 없다.

이 경우에는 어떤 문장이 먼저 실행되느냐에 따라 실행 계획이 전 체 테이블 스캔(또는 고속 전체 인덱스 스캔)을 포함하거나

단순한 인덱스 범위 스캔을 사용 할 수 있다.

 

CURSOR_SHARING 파라메터

 

CURSOR_SHARING 초기화 파라메터의 값은 옵티마이저가 문장의 Literal을 Bind 변수로 처리하 는 방법을 결정한다.

①   EXACT : Literal 교체가 완전히 비활성화 된다.

②   FORCE : 모든 Literal에 대해 공유가 발생한다.

③   SIMILAR : 안전한 Literal에 대해서만 공유가 발생한다.

 

초기 버전에서는 오직 EXACT 또는 FORCE 옵션만 사용 할 수 있었다. 옵티마이저는 문장을 조사하여 Literal 교체가 안전한

경우에만 해당 교체가 발생하도록 보장한다. 이 과정에서 모든 사용 가능한 인덱스(고유 또는 비고유)와 인덱스 및 테이블에

수집된 통계 및 히스토그램에 관한 정보 를 사용 할 수 있다.

 

초기화 파일의 CURSOR_SHARING 값은 ALTER SYSTEM SET CURSOR_SHARING 명령 또는

ALTER SESSION SET CURSOR_SHARING 명령으로 덮어 쓰여 질 수 있다.

CURSOR_SHARING_EXACT 힌트를 사용하면 Literal을 Bind 변수로 교체하려는 어떠한 시도도 수행하지 않고 SQL 문장을 실행한다.

 

강제 커서 공유 : 예제

그림 8.7

 

ALTER SESSION 명령으로 커서 공유를 강제로 수행하면, Literal만 다른 모든 쿼리는 SYS_B_0라는

동일한 Bind 변수를 사용하는 문장으로 재 작성된다. 그 결과, 3개의 커서 대신 오직 하나의 커서를 사용하게 된다.

참고 : 적응형 커서 공유를 사용하면, 이 경우에 두 개의 커서가 작성 될 수 있다.

 

적응형 커서 공유 : 개요

 

Bind 변수는 오라클 데이터베이스가 여러 SQL 문장에 대하여 단일 커서를 공유하도록 해준다. 커서를

공유하는 이유는 SQL 문장을 파싱하는데 사용되는 공유 메모리의 양을 감소시키기 위한 것이다. 그러나,

커서 공유와 SQL 최적화는 그 목적이 서로 대치된다. Literal을 사용하여 SQL 문 장을 작성하는 것은

옵티마이저에게 더 많은 정보를 제공하여 더 나은 실행 계획을 이끌어 낼 수 있도록 하는 것이지만

한편으로는 과도한 하드 파스로 인하여 메모리와 CPU의 부담이 증가하게 된다. Oracle9i 데이터베이스는

서로 다른 Literal을 가진 유사한 SQL 문장이 공유 될 수 있도록 통합된 솔루션을 제공하였다. Bind 변수를

사용한 문장의 경우, Oracle9i에서는 Bind 엿보기 개념을 소개하였다. Bind 엿보기의 장점을 이용하려면

커서 공유를 수행하고 문장이 실행 될 때마다 동일한 실행 계획을 사용한다고 가정하여야 한다. 만약, 해당

문장이 실행될 때마다 서로 다른 실행 계획으로부터 매우 큰 이익을 얻을 수 있다면, Bind 엿보기는 좋은

실행 계획을 작 성하는데 전혀 유용하지 않게 된다.

가능한 이러한 문제를 해결하기 위해, Oracle Database 11g는 적응형 커서 공유를 소개하였다. 이 기능은

무조건 커서를 공유하지 않도록 정밀하게 설계된 전략이지만, Bind 변수를 포함한 하나 의 SQL 문장에

대하여 여러 개의 실행 계획을 작성한다. 단, 여러 실행 계획을 사용하여 얻을 수 있는 이득이 파싱 시간과

메모리 사용량 부담을 넘어서는 경우에만 한정된다. 그러나, Bind 변 수의 사용 목적이 메모리에서 커서를

공유하는 것이므로 생성해야 할 커서의 개수를 고려하여 적 당한 타협을 수행하여야 한다.

 

적응형 커서 공유 : 아키텍처

그림 8.8

 

적응형 커서 공유를 사용하면 그림과 같은 시나리오가 발생한다.

1.   커서는 평상 시와 같이 하드 파싱을 수행하기 시작한다. 만약, Bind 엿보기가 발생하고, Bind 변수가

포함된 조건절의 선택도를 계산하기 위해 히스토그램이 사용된다면, 커서 는 Bind 민감(bind-sensitive)

커서로 표시된다. 또한, Bind 변수를 포함하는 조건절과 조건절의 선택도 관련 일부 정보가 저장된다.

위 예제에서 저장 될 조건절 선택도는 (0.15, 0.0025)를 둘러싸는 입방체(cube)이다. 최초의 하드 파싱이므로

실행 계획은 Bind 엿보기를 사용한다. 커서가 실행된 후, Bind 값과 커서의 실행 통계가 해당 커서에 저장된다.

 

새로운 Bind 변수 집합이 사용되어 해당 문장이 다시 실행되는 동안, 시스템은 평상시와 같은 소프트 파싱을

수행하고 문장 실행을 위해 일치하는 커서를 검색한다. 문장 실행이 완료되면 실행 통계가 커서에 저장된

현재 것과 비교된다. 그 다음, 시스템은 모든 이전의 실행 결과 (V$SQL_CS_ .. 뷰)를 통해 통계의 패턴을 관찰하고

해당 커서를 Bind 인식(bind aware) 커서로 표시 할 것인지를 결정하게 된다.

 

2.   이 쿼리의 다음 소프트 파싱에서 커서가 Bind 인식 커서라면, Bind 인식 커서 일치 가 사용된다. 새로운

Bind 변수 집합으로 조건절의 선택도가 이제 (0.18, 0.003)이라고 가정하자. 선택도는 Bind 인식 커서 일치의

일부로 사용되고, 선택도는 기존 입방체 내에 존재하므로 해당 문장은 기존 커서의 실행 계획을 사용하여 실행 된다.

 

3.   이 쿼리의 다음 소프트 파싱에서 새로운 Bind 변수 집합으로 조건절의 선택도가 이제 (0.3, 0.009)라고 가정하자.

선택도는 기존 입방체 내부에 있지 않기 때문에 일치되는 커서도 존재하지 않는다. 시스템은 하드 파싱을 수행하고

두 번째 실행 계획을 갖는 새로 운 커서를 작성한다. 또한, 새로운 선택도 입방체는 새로운 커서의 일부로 저장된다.

새로운 커서가 실행 된 후, 시스템은 Bind 값과 실행 통계를 커서에 저장한다.

 

4.   이 쿼리의 다음 소프트 파싱에서 새로운 Bind 변수 집합으로 조건절의 선택도가 이제 (0.28, 0.004)라고 가정하자.

선택도는 기존 입방체에 존재하지 않기 때문에 시스템은 하드 파싱을 수행한다. 이 경우에 하드 파싱의 결과가 첫 번째

실행 계획과 동일하다고 가 정하자. 실행 계획이 첫 번째 커서와 동일하기 때문에 두 개의 커서는 머지된다. 즉, 두 개의

입방체는 머지되어 좀 더 큰 입방체가 되고, 커서 중 하나는 삭제된다. 만약, 다음 번에 조건절의 선택도가 새로운 입방체

내부에 해당된다면 소프트 파싱이 발생하고 커서 가 일치된다.

 

적응형 커서 공유 : 뷰

그림 8.9

 

이 뷰들은 쿼리가 Bind 인식 쿼리인지 여부를 결정하고 사용자의 간섭 없이 자동으로 처리된다.

그러나, 수행된 작업과 관련된 정보는 문제점을 진단 할 수 있도록 V$ 뷰를 통해서 제공된다.

V$SQL에 새로운 컬럼이 추가 되었다.

①   IS_BIND_SENSITIVE : 커서가 Bind 민감(bind sensitive)인지를 나타내며 YES | NO로 지정 한다.

옵티마이저가 조건절의 선택도를 계산 할 때, Bind 변수를 엿보고 Bind 변수 값이 변경되면 다른 실행

계획을 발생 시키는데, 이를 Bind 민감(bind sensitive)이라고 부른다.

②   IS_BIND_AWARE : 커서가 Bind 인식(bind aware)인지를 나타내며 YES | NO로 지정한다.

커시 내의 커서가 Bind 인식 커서 공유를 사용하도록 표시되어 있으면 Bind 인식(bind aware)이라고 부른다.

③   V$SQL_CS_HISTOGRAM : 3개의 버킷으로 구성된 실행 이력 히스토그램으로 실행 횟수 분포를 보여준다.

④   V$SQL_CS_SELECTIVITY : Bind 변수를 포함하는 모든 조건절의 커서에 저장 된 선택도 입방체 또는

범위를 보여주며, 이 선택도는 커서 공유를 결정하는데 사용된다. 여기에는 조건절의 구문과 선택도

범위의 최소 및 최대값이 포함된다.

⑤   V$SQL_CS_STATISTICS : 적응형 커서 공유는 쿼리의 실행을 모니터링하고 해당 실행과 관 련된 정보를

수집한 다음, 이 정보를 이용하여 쿼리에 Bind 인식 커서 공유를 사용 할 것인지를 결정한다. 이 뷰는 이러한

결정을 위해 수집된 정보를 요약해서 보여준다. 문장 의 실행으로부터 처리된 행, buffer gets, CPU 시간이

추적되며, Bind 변수가 커서를 구성하는데 사용되었다면 PEEKED 컬럼에 YES가 표시되며, 그렇지 않은 경우는 NO가 표시 된다.

 

적응형 커서 공유 : 예제

 

그림의 데이터를 가정하자. JOB_ID 컬럼에 히스토그램 통계가 있으며 AD_ASST보다 SA_REP이 수 천배 많이

저장되어 있다. 이 경우에 Bind 변수 대신 Literal이 사용되었다면, 쿼리 옵티마이저 는 AD_ASST 값이 전체

행에서 1% 이내라는 것을 알게 되며, SA_REP은 전체 행의 약 1/3이라는 것을 인식하게 된다. 먄약, 해당

테이블이 수백만 건의 행을 가지고 있다면,

이러한 값들에 대하여 실행 계획은 서로 다를 것이다. AD_ASST 쿼리는 해당 값을 가진 행들이 매우 적으므로

인덱스 범위 스캔을 수행 할 것이며 SA_REP 쿼리는 해당 값을 가진 행들이 매우 많으므로 전체 테이블 스캔을

수행하여 전체 테이블을 인는 것이 효율적일 것이다. 그러나, Bind 변수의 사용은 두 값에 대하여 같은 실행 계획을 사용하도록 만든다.

각 값에 대하여 서로 다르고 우수한 실행 계 획이 존재하지만, 각 쿼리는 동일한 실행 계획을 사용한다.

 

이 쿼리를 Bind 변수를 사용하여 여러 번 수행하면, 시스템은 해당 쿼리를 Bind 인식으로 가 정하며,

이 시점에서 Bind 값을 기반으로 실행 계획을 변경시킨다. 즉, Bind 변수 값에 따라 쿼리에 최상의

실행 계획이 사용됨을 의미한다.

 

적응형 커서 공유 제어

 

①   적응형 커서 공유는 CURSOR_SHARING 파라메터와 별개이다. 이 파라메터의 설정은 리터럴을 시스템이

생성한 Bind 변수로 교체할 것인지 여부를 결정한다. 만약, 사용자가 처음부터 Bind 변수를 제공하였다면,

적응형 커서 공유가 동작한다.

②   SPM(SQL Plan Management) 자동 실행 계획 캡처를 사용하면, Bind 변수를 포함한 SQL 문장에 대하여

캡처 된 첫 번째 실행 계획은 해당 SQL 실행 계획의 기준선으로 표시된다. 만약, 동일한 SQL 문장(적응형 커서

공유가 수행된 경우)에 대하여 다른 실행 계 획이 발견되면, SQL 문장 실행 계획 이력에 이 실행 계획을 추가하고

검증이 필요함을 표시한다. 즉, 이 실행 계획은 즉시 사용 될 수 없다. 새로운 Bind 변수 집합을 기반으로 적응형

커서 공유가 새로운 실행 계획을 작성할지라도 SPM은 해당 실행 계획의 검증 이 수행 될 때까지 해당 실행 계획을

사용하지 못하도록 설정한다. 10g에서의 동작으로 돌아가보면, 첫 번째 Bind 변수의 집합을 기반으로 생성된

오직 하나의 실행 계획이 다음 번에 수행되는 문장의 실행 계획에서 사용된다. 이를 방지하기 위한 방법 중에

하나는 때때로 자동 실행 계획 캡처를 false로 설정하고 시스템을 운영하는 것이며 커서 캐 시에 Bind 변수를

갖는 SQL 문장의 모든 실행 계획을 읽어온 다음, 커서 캐시로부터 직접 전체 실행 계획을 해당 SQL 실행 계획

기준선으로 로드하는 것이다. 이 과정을 수 행하면 단일 SQL 문장에 대한 모든 실행 계획은 기본적으로

SQL 기준선 실행 계획으로 표시된다.