You've got to find what you love.

# 바인드 변수를 사용하라

바인드 변수는 쿼리에 있어서 플레이스 홀더(역자 주_placeholder, 빠져 있는 것을 대신하는 기호)다.
예를 들어 직원 123을 조회하기 위해 다음과 같은 쿼리를 할 수 있다.

select * from emp where empno = 123 ;

다른 방식으로는 이렇게도 쿼리할 수 있다.

select * from emp where empno = :empno ;

일반적인 시스템에는 사원번호 123을 한 번 조회하고 끝나고, 다음으로 사원번호 456을 조회하고, 그 다음엔 789를 조회하는 식이다.(역자 주_ 세 개의 사원번호를 조회하기 위해 세 개의 서로 다른 Literal SQL을 실행한다)
아니면 위에 말한 SELECT 문장들처럼 인서트 문을 사용하는데, 바인드 변수를 사용하지 않으면 기본키 값(empno)이 문장 안에 하드 코딩될 것이다.
그리고 인서트 문장은 다시는 재사용될 수 없을 것이다.(역자 주_ 물론 누군가 세 개의 사원번호와 동일한 사원번호를 입력하여 조회한다면 재사용될 것이지만)
쿼리 안에 상수를 대입하여 사용하면, 데이터베이스는 모든 쿼리를 새로운 쿼리로 인식하여 파싱하고, 문법을 검사하고, 보안을 체크하고, 옵티마이징하는 등의 일을 할 것이다.
즉 모든 쿼리가 실행될 때마다 다시 컴파일되어야 한다는 말이다.

두 번째 쿼리는 :empno라는 바인드 변수를 사용했다.
:empno 값은 쿼리가 실행될 때마다 주어진다.
이 쿼리는 한 번 컴파일 된 후 shared pool(라이브러리 캐시)에 저장되며, 동일한 쿼리가 실행될 때 shared pool에서 가져와 재사용될 수 있다.
성능과 확장성의 관점에서 두 가지 방식의 차이는 극적일 정도로 엄청나다.

성능 관점에서 바인드 변수의 사용 여부가 결과에 얼마나 큰 차이를 가져올 수 있는지를 간단한 테스트를 통해 알아보자.

SCOTT@ora10g>create table t( x int );

Table created.

SCOTT@ora10g>create or replace procedure proc1
  2  as
  3  begin
  4      for i in 1 .. 10000
  5      loop
  6          execute immediate
  7          'insert into t values ( :x )' using i;
  8      end loop;
  9  end;
 10  /

Procedure created.

-- 두번째 프로시저는 입력되는 각 로우마다 각각 다른 SQL(하드파스)문이 실행되도록 하였다.

SCOTT@ora10g>create or replace procedure proc2
as
  2    3  begin
  4      for i in 1 .. 10000
  5      loop
  6          execute immediate
  7          'insert into t values ( '||i||')';
  8      end loop;
  9  end;
 10  /

Procedure created.

/*
    위 두 프로시저의 차이점은 바인드 변수를 사용한 것과 그렇지 않은 것과의 차이다.
    둘 다 동적 SQL을 사용했으며 로직은 동일하다.
    즉, 바인드 변수의 사용 여부에만 차이가 있다.

    두개의 결과물을 비교하기 위해 필자(토마스 카이트)가 개발한 runstats라는 툴을 사용할 것이다.
*/

SCOTT@ora10g>set serveroutput on
SCOTT@ora10g>
SCOTT@ora10g>exec runstats_pkg.rs_start

PL/SQL procedure successfully completed.

SCOTT@ora10g>exec proc1


PL/SQL procedure successfully completed.


SCOTT@ora10g>exec runstats_pkg.rs_middle

PL/SQL procedure successfully completed.

SCOTT@ora10g>exec proc2


PL/SQL procedure successfully completed.

SCOTT@ora10g>exec runstats_pkg.rs_stop(10000)
Run1 ran in 91 cpu hsecs
Run2 ran in 629 cpu hsecs
run 1 ran in 14.47% of the time

/*
    아래에서 제시할 결과는 CPU Time에 근거해서, 바인드 변수를 사용하지 않은 것이 바인드 변수를 
    사용한 것에 비해 10,000개의 로우를 입력하는 데 훨씬 긴 시간이 필요하고,
    훨씬 더 많은 자원을 차지한다는 것을 명백하게 보여 준다.
    바인드 변수 없이 로우들을 입력하면 CPU Time이 약 20배나 더 소모되었다.
*/

Name                                  Run1        Run2        Diff
STAT...parse count (hard)                5      10,012      10,007
STAT...parse count (total)              38      10,053      10,015
STAT...calls to get snapshot s          67      10,094      10,027
STAT...recursive calls              10,422      20,966      10,544
LATCH.simulator hash latch             194      11,336      11,142
LATCH.simulator lru latch              173      11,324      11,151
STAT...db block gets from cach      10,406      30,368      19,962
STAT...db block gets                10,406      30,368      19,962
LATCH.enqueues                         715      21,355      20,640
LATCH.enqueue hash chains              781      21,492      20,711
LATCH.session allocation               287      28,804      28,517
STAT...session logical reads        10,711      40,591      29,880
LATCH.library cache pin             40,589      75,113      34,524
LATCH.kks stats                          8      40,344      40,336
LATCH.library cache lock               559      62,594      62,035
LATCH.cache buffers chains          56,165     129,165      73,000
STAT...physical read bytes               0      81,920      81,920
STAT...physical read total byt           0      81,920      81,920
LATCH.row cache objects                494     128,030     127,536
STAT...session uga memory           65,464     -65,464    -130,928
STAT...session pga memory           65,536     -65,536    -131,072
STAT...session pga memory max      262,144      65,536    -196,608
LATCH.library cache                 41,215     239,696     198,481
STAT...session uga memory max      261,964      57,880    -204,084
LATCH.shared pool                   20,470     229,382     208,912

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
164,982   1,009,419     844,437     16.34%

PL/SQL procedure successfully completed.

    바인드 변수를 사용한 첫 번째 방법은 4번 하드 파스를 했고, 바인드 변수를 사용하지 않은 것은
    입력하는 문장마다 한 번씩 10,000번 하드 파스를 했다는 것을 알 수 있다.
    그러나 하드 파싱 항목의 차이는 단지 빙산의 일각일 뿐이다.
    여기서 바인드 변수를 사용하지 않는 방법은 바인드 변수를 사용하는 것보다
    거의 20배 정도의 래치를 사용했다는 것을 알 수 있다.



출처 : 전문가를 위한 오라클 데이터베이스 아키텍처 - 토마스 카이트