Oracle - 바인드 변수에 대하여(테스트)
Posted 05 6, 2012 10:42, Filed under: DataBase/Oracle
# 한번의 광고 클릭으로, 당신을 대신해서 불우이웃을 도울 기회가 많아집니다.
# 바인드 변수를 사용하라
바인드 변수는 쿼리에 있어서 플레이스 홀더(역자 주_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배 정도의 래치를 사용했다는 것을 알 수 있다.
출처 : 전문가를 위한 오라클 데이터베이스 아키텍처 - 토마스 카이트
"DataBase / Oracle" 분류의 다른 글
| Oracle - 디폴트 롤, DBA, CONNECT, RESOURCE (0) | 2012/04/27 |
| Oracle - 권한 및 롤 관리 (0) | 2012/04/27 |
| Oracle - SQL*PLUS의 SYSDBA 접근 제어 (0) | 2012/04/27 |
| Oracle - PFILE, SPFILE 에 관하여 (0) | 2012/04/27 |
| Oracle - Listener 포트 변경 (0) | 2012/04/27 |
| Oracle - 사용자 패스워드 정책 변경 (0) | 2012/04/03 |
| Oracle - SYS_CONTEXT 함수를 이용하여 접속 세션 정보 추출 (2) | 2011/12/13 |
| Oracle - ASSM(Automatic Segment Space Management) (0) | 2011/09/18 |
| Oracle - 사용자의 테이블 스페이스 검색및 이동 (0) | 2011/09/18 |
| Oracle - 9i 설치관련 패치(Requirements for Installing Oracle 9iR2 on RHEL 4) (0) | 2011/09/01 |
# 한번의 광고 클릭으로, 당신을 대신해서 불우이웃을 도울 기회가 많아집니다.
Response :
0 Trackback
,
0 Comment
Trackback URL : http://develop.sunshiny.co.kr/trackback/738