Techdata VINA 지식 공유

Oracle9i Online Document에서 New Feature를 조회해 보면 아래와 같이 Bind Variable Peeking에 대해 소개 합니다.

Peeking of User-Defined Bind Variables
The CBO peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.

When bind variables are used in a statement, it is assumed that cursor sharing is intended and that different invocations are supposed to use the same execution plan. If different invocations of the cursor would significantly benefit from different execution plans, then bind variables may have been used inappropriately in the SQL statement.

이 기능을 이해하기 전에 우선 알아야 할 사항이 있습니다. 

Bind Variable을 사용한 SQL은 먼저 파싱(Parsing)과 최적화(Optimization)가 이루어진 후에 바인드 변수의 바인딩이 이루어진다는 사실입니다.

따라서 최적화가 이루어지는 시점에는 변수로 제공되는 컬럼의 분포가 균일하다는 가정을 세운 후에 최적화를 수행하게 되고 분포가 균일하지 못한 컬럼(highly skewed column)에 바인드 변수를 사용하게 되면 최악의 실행계획을 생성하게 됩니다.

그래서, 우리가 Hard Parsing의 부하를 줄이기 위해 Bind Variable을 사용하는 것이 좋다는 것을 알면서도 사실상 데이터의 분포가 고르지 못한 경우가 많기 때문에 현실적으로 사용하기 어려운 경우가 종종 있었습니다.

이러한 한계를 극복하기 위해 Oracle 9i부터 제공되기 시작한 것이 "Peeking"이라는 기능입니다.
'Peek'라는 용어를 사전에서 찾아보면 "몰래 엿보다"라는 뜻을 갖는데, 위 원문을 요약해 보면 이렇습니다. Bind Variable을 사용한 SQL이 첫번째 실행될 때 SQL 옵티마이저는 사용자가 지정한 바인드 변수의 값을 "살짝 컨닝"함으로써, 조건절의 컬럼값이 상수로 제공될 때와 마찬가지로 해당 조건의 선택성(selectivity, ※ Unique 인덱스를 갖는 컬럼의 선택성이 가장 높다.)을 확인한 후에 최적화를 수행한다는 얘기이다. 그리고 나서 커서가 다음 번에 계속 실행될 때에는 변수를 다른 값으로 바인딩하더라도 더 이상 peeking이 발생하지 않으며 앞에서 생성된 실행계획을 그대로 사용한다는 것이다. 즉, 최초 Hard Parsing이 일어날 때 단 한번만 Bind Variable을 Peeking한다는 설명이고, 다시 말해서 첫번째 Binding 되는 값에 따라 SQL Plan이 고정된다는 것인데, 이는 애플리케이션을 개발하는 입장에서 매우 중요한 특징입니다.

--- 테스트

어떤 학교의 학점 테이블이 아래와 같이 돼 있다고 가정하면

SQL> create table 학점테이블
  2  as
  3  select 'A' 학점, 91 시작점수, 100 종료점수 from dual union all
  4  select 'B', 81, 90 from dual union all
  5  select 'C', 71, 80 from dual union all
  6  select 'D', 61, 70 from dual union all
  7  select 'F',  0, 60 from dual
  8  /

테이블이 생성되었습니다.

그리고 '학생' 테이블은 학번, 이름, 학점으로 구성되어 있는데, 이 중 대부분의 학생이 'B'학점에 속한다고 가정하고 아래와 같이 가상으로 테이블을 생성

SQL> select object_type, count(*)
  2  from all_objects
  3  group by object_type
  4  order by 2
  5  /

OBJECT_TYPE          COUNT(*)
------------------ ----------
EVALUATION CONTEXT          1  ----> 'A'
CONSUMER GROUP              2  ----> 'C'
SEQUENCE                    2  ----> 'D'
INDEX                       5  ----> 'F'
INDEXTYPE                   9  -+
LIBRARY                    14   |
PROCEDURE                  14   |
OPERATOR                   28   |
TABLE                      53   |
FUNCTION                   90   |--> 'B'
JAVA RESOURCE             184   |
PACKAGE                   267   |
TYPE                      536   |
VIEW                     1102   |
JAVA CLASS               9654   |
SYNONYM                 11540  -+

16 개의 행이 선택되었습니다.

SQL> create table 학생
  2  as
  3  select object_id    학번
  4       , object_name  이름
  5       , decode( object_type
  6           , 'EVALUATION CONTEXT', 'A'
  7           , 'CONSUMER GROUP',     'C'
  8           , 'SEQUENCE',           'D'
  9           , 'INDEX',              'F', 'B' ) 학점
 10  from all_objects
 11  /

테이블이 생성되었습니다.

SQL> alter table 학생 add
  2  constraint pk_학생 primary key (학번)
  3  /

테이블이 변경되었습니다.

SQL> create index 학생_학점_IDX on 학생(학점);

인덱스가 생성되었습니다.

SQL> analyze table 학생 compute statistics
  2  for table for all indexes for all indexed columns
  3  /

테이블이 분석되었습니다.

테스트를 계속 진행하기에 앞서 결과를 명확히 하기 위해 우선 shared pool을 비워야 합니다.

SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL> alter session set sql_trace = true;

세션이 변경되었습니다.

이제 학점 'A'에 대해 아래와 같이 SQL을 실행한 후 Trace 결과를 확인해 보면 인덱스를 통한 테이블 액세스가 발생한 것을 확인할 수 있다.

SQL> select count(*), min(학번)
  2  from   학생
  3  where  학점 = 'A';

  COUNT(*)  MIN(학번)
---------- ----------
         1       5847

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
      1   TABLE ACCESS BY INDEX ROWID 학생
      1    INDEX RANGE SCAN 학생_학점_IDX (object id 30510)

이제 데이터 밀도가 굉장히 높은 즉, 선택성이 매우 낮은 학점 'B'에 대해 아래와 같이 SQL을 실행한 후 Trace 결과를 확인해 보면 Table Full Scan으로 처리된 것으로 확인

SQL> select count(*), min(학번)
  2  from   학생
  3  where  학점 = 'B';

  COUNT(*)  MIN(학번)
---------- ----------
     23491          4

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
  23491   TABLE ACCESS FULL 학생

이렇게 상수조건을 사용할 경우에는 우리가 예상했던 바대로, 주어지는 값의 종류에 따라 실행계획이 바뀌면서 효율적으로 처리되는 것을 알 수 있습니다.

이제, Bind Variable을 사용할 경우에는 어떻게 실행계획이 만들어지는지 확인

SQL> variable x varchar2(1)
SQL> exec :x := 'A'

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> declare
  2    l_학생수  number;
  3    l_학번    학생.학번%type;
  4  begin
  5    select count(*), min(학번) into l_학생수, l_학번
  6    from   학생
  7    where  학점 = :x;
  8  end;
  9  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> exec :x := 'B'

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> declare
  2    l_학생수  number;
  3    l_학번    학생.학번%type;
  4  begin
  5    select count(*), min(학번) into l_학생수, l_학번
  6    from   학생
  7    where  학점 = :x;
  8  end;
  9  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL Trace 결과
********************************************************************************

SELECT count(*), min(학번) from   학생
  where  학점 = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          5          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
      1   TABLE ACCESS BY INDEX ROWID 학생
      1    INDEX RANGE SCAN 학생_학점_IDX (object id 30510)
    
********************************************************************************
    
SELECT count(*), min(학번) from   학생
  where  학점 = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.08       0.08          0        195          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.09       0.08          0        195          0           1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 61     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
  23491   TABLE ACCESS BY INDEX ROWID 학생
  23491    INDEX RANGE SCAN 학생_학점_IDX (object id 30510)
    
********************************************************************************

바인드 변수를 'B'로 재할당한 후 두번째 실행한 경우에도 최초 'A'를 실행할 때와 똑같이 인덱스를 사용하여 처리되었습니다. 

주목해서 보아야 할 점은 첫번째 실행시에는 Hard Parse 횟수가 1이고 두번째 실행시에는 0이라는 것인데, 이를 통해 옵티마이저는 Hard Parse시에만 peeking을 수행하는 것을 알 수 있습니다.참고로, tkprof 사용시 aggregate=no 옵션을 사용해야 이렇게 별도로 분리된 Trace 결과를 얻을 수 있다.

다시 Shared Pool을 비우고, 이번에는 'B'-->'A' 순으로 바인딩변수의 값을 바꾸면서 같은 SQL을 실행시켜 보자.

SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL> exec :x := 'B'

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> declare
  2    l_학생수  number;
  3    l_학번    학생.학번%type;
  4  begin
  5    select count(*), min(학번) into l_학생수, l_학번
  6    from   학생
  7    where  학점 = :x;
  8  end;
  9  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> exec :x := 'A'

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> declare
  2    l_학생수  number;
  3    l_학번    학생.학번%type;
  4  begin
  5    select count(*), min(학번) into l_학생수, l_학번
  6    from   학생
  7    where  학점 = :x;
  8  end;
  9  /

PL/SQL 처리가 정상적으로 완료되었습니다.

********************************************************************************

SELECT count(*), min(학번) from   학생
  where  학점 = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        1      0.04       0.03          0        147          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.06       0.05          0        147          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
  23491   TABLE ACCESS FULL 학생

********************************************************************************

SELECT count(*), min(학번) from   학생
  where  학점 = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.02       0.02          0        147          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.02       0.02          0        147          0           1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 61     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
      1   TABLE ACCESS FULL 학생

********************************************************************************

이번에는 앞에서의 테스트와 반대로 첫번째 실행에 'B'를 사용하였기 때문에 Table Full Scan으로 처리되었고 이후에 어떤 값으로 바인딩되더라도 같은 실행계획으로 처리된 것을 확인할 수 있다.

이처럼 Bind Variable Peeking 기술이 도입됨으로써, 컬럼의 실질적인 분포를 고려하지 않고 평균값을 이용하는 기존 최적화 방식의 한계를 일부 극복했다고 볼 수 있으나 비효율의 가능성을 여전히 안고 있다는 사실을 눈으로 확인할 수 있었다.

이제, 이러한 기능을 어떻게 잘 사용해서 가장 효과적이고 효율적인 애플리케이션을 개발할지는 오라클 사용자 즉, 개발자의 몫인 것이다.
옵티마이저 원리에 대해 조금이라도 관심을 가지고 공부한 개발자라면 누구나 알고 있는 사실이 하나 있다. 이전에 실행된 SQL과 일점일획이라도 다른 부분이 있다면 SQL 옵티마이저는 이것을 다른 SQL로 간주하고 새로운 실행계획을 작성한다는 것인데, 이점을 이용해 Bind Variable Peeking이 갖는 한계를 극복해 보자.

SQL> create or replace function getdata( p_학점 varchar2 ) return varchar2
  2  as
  3    l_학번    학생.학번%type;
  4    l_학생수  number;
  5  begin
  6    if(p_학점 = 'B')
  7    then
  8      select count(*), min(학번) into l_학생수, l_학번
  9      from   학생 B_TYPE
 10      where  학점 = p_학점;
 11    else
 12      select count(*), min(학번) into l_학생수, l_학번
 13      from   학생 OTHER_TYPE
 14      where  학점 = p_학점;
 15    end if;
 16    return l_학생수 || '명 , 최소학번' || l_학번;
 17  end;
 18  /

함수가 생성되었습니다.

SQL> begin
  2    for l_cursor in (select 학점 from 학점테이블)
  3    loop
  4      dbms_output.put_line( l_cursor.학점 || ' : ' ||
  5                            getdata(l_cursor.학점) );
  6    end loop;
  7  end;
  8  /
A : 1명 , 최소학번5847
B : 23491명 , 최소학번4
C : 2명 , 최소학번3844
D : 2명 , 최소학번140
F : 5명 , 최소학번3

PL/SQL 처리가 정상적으로 완료되었습니다.

********************************************************************************

SELECT count(*), min(학번) from   학생 OTHER_TYPE
    where  학점 = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        4      0.01       0.00          0         60          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.01       0.00          0         60          0           4

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
      5   TABLE ACCESS BY INDEX ROWID 학생
      5    INDEX RANGE SCAN 학생_학점_IDX (object id 30510)

********************************************************************************

SELECT count(*), min(학번) from   학생 B_TYPE
    where  학점 = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        1      0.04       0.04          0        147          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.05       0.04          0        147          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
  23491   TABLE ACCESS FULL 학생

********************************************************************************

참고로, 여기서는 테이블 Alias을 이용해서 실행을 분리했는데 주석( /* */ )을 이용해도 같은 효과를 얻을 수 있습니다.

한가지 더 알아 두어야 할 점은, EXPLAIN PLAN FOR ... 명령어를 통해 Bind Variable의 실행계획을 사전에 확인하려고 할 때에는 옵티마이저가 peeking 해 볼 수 있는 변수값이 제공되지 않기 때문에 할 수 없이 평균 분포를 가정한 실행계획을 제시하게 됩니다. 따라서 Bind Variable Peeking이 사용되는지 여부에 대한 실질적인 결과를 확인할 수 없고, 반드시 SQL Trace 결과를 통해서만 정확한 결과를 확인할 수 있습니다.