[ORACLE] 바인드 변수의 중요성

1. 개요

바인드 변수를 사용하게 되면, 커서를 많이 생성하지 않고 하나를 반복 재사용하므로 메모리 사용량과 파싱 소요시간을 줄여준다.궁극적으로, 시스템 전반의 메모리와 CPU 사용률을 낮춰 데이터베이스 성능과 확장성을 높이는게 기여하고, 특히 동시 사용자 접속이 많을 때는 그 영향력이 절대적이다.아래 간단한 실습을 통해 바인드 변수에 대해 알아보자.

2. 실습 환경 설정

-- 테이블 생성
create table t
as
select * from all_objects;

update t set object_id = rownum;

-- 인덱스 생성
create unique index t_idx on t(object_id);

-- 통계정보 수집
analyze table t compute statistics;

-- 인덱스를 잘 타는지 실행계획 확인 
set autotrace traceonly explain; -- 실행계획을 보기위한 sql plus 문법
select object_name from t where object_id = 1000;
set autotrace off; -- 실행계획을 안보기 위한 sql plus 문법

alter system flush shared_pool;

3. 실습

첫 번째, 바인드 변수 사용

-- SQL 트레이스 수집 시작
alter session set sql_trace = true;

-- 바인드 변수 방식으로 20000회 실행
declare
	type rc is ref cursor;
	l_rc rc;
	l_object_name t.object_name%type;
BEGIN
	for i in 1..20000
	loop
		open l_rc for
			'select /* test1 */ object_name
			 from   t
			 where  object_id = :x' using i;
		fetch l_rc into l_object_name;
		close l_rc;
	end loop;
end;
/

--PL/SQL 처리가 정상적으로 완료되었습니다.
--경   과: 00:00:03.91

select * from dual;

-- SQL 트레이스 수집 종료
alter session set sql_trace = false;

V$SQL의 결과를 확인해보면, 2만번의 쿼리 콜이 있었지만 하드파싱은 1번뿐 인것을 알 수 있다.

select sql_text, loads, parse_calls, executions, fetches from v$sql
where  sql_text like '%test1%'
and    sql_text not like '%v$sql%'
and    sql_text not like '%declare%';

*LOADS =1 (하드파싱 1번)

 

 

아래 SQL TRACE에도 위와 동일한 결과를 볼 수 있다. 

*Misses in library cache during parse: 1  (하드파싱 1번)

 

 

두 번째, 바인드 변수 미사용(즉, 상수 사용)

-- SQL 트레이스 수집 시작
alter session set sql_trace = true;

-- 상수값 입력 방식으로 20000회 실행
declare
	type rc is ref cursor;
	l_rc rc;
	l_object_name t.object_name%type;
BEGIN
	for i in 1..20000
	loop
		open l_rc for
			'select /* test2 */ object_name
			 from   t
			 where  object_id = ' || i;
		fetch l_rc into l_object_name;
		close l_rc;
	end loop;
end;
/	

--PL/SQL 처리가 정상적으로 완료되었습니다.
--경   과: 00:00:53.91

select * from dual;

-- SQL 트레이스 수집 종료
alter session set sql_trace = false;

 

아래 V$SQL의 결과를 보면 WHERE절에 새로운 조건절이 대입 될때마다 캐시에 새로운 커서가 생성된 것을 볼 수 있다.

또한 매번 하드파싱을 하게된 꼴이고, 처리 시간 또한 기존에 비해 몇배가 더 걸리게 되었다.

"제일 중요한 사실은 20,000번 커서를 생성했지만, 캐시에 남아있는건 3400여개 뿐이였다.

나머지는 이미 캐시에서 밀려나고 없기 때문이다."

select substr(sql_text, 61), loads, parse_calls, executions, fetches from v$sql
where  sql_text like '%test2%'
and    sql_text not like '%v$sql%'
and    sql_text not like '%declare%';