Bind variable(바인드 변수) 정의 및 사용법 예제

: 변수 는 바인드 변수 입니다.

db 프로그래밍을 짜실 때 꼭 한가지를 기억하세요.. 저는 자바로 설명하겠지만 이건 꼭 언어를 따지는 건 아닙니다. 

가령 이런 SQL문이 있다고 하죠. 

select * from tab where id = 1; 

그리고 시스템에서 id 를 입력받아서 매번 각 id에 대해 위의 쿼리를 실행한다고 하죠. 

그러면 다음과 같이 쿼리를 생성해서는 안됩니다. 

Connection con = null; 
Statement stmt = null; 

String query = "select * from tab where id = " + id; 

stmt = con.createStatement(stmt); 
ResultSet rs = stmt.executeQuery(); 

이와같이 코딩하면 SQL문장이 DBMS에 개별적으로 들어갑니다. 
즉, 
select * from tab where id = 1; 
select * from tab where id = 2; 
select * from tab where id = 3; 
select * from tab where id = 4; 
select * from tab where id = 5; 

가 전부다 다른 문장으로 처리된다는 말이죠. 다른 문장으로 처리된다는 말은 매번 저 문장이 
파싱되야한다는 말입니다. 

그러나 쿼리를 

select * from tab where id = ? 

와 같이 만들고 ? 자리에 값을 넣는 방법이 어떤 언어든지 있습니다. 

이럴때 ? 를 바인드 변수라고 하는데, 바인드 변수를 사용하면 위의쿼리는 

select * from tab where id = :id_val; 

이렇게 치환되죠.. 

그리고 개별 쿼리시마다 동일한 sql문을 수행하되 id_val의 값이 바뀌게 됩니다. 

자바의 경우는 이것이 PreparedStatement입니다. 또 어떤분은 PreparedStatement를 쓰긴 
하는데 정말 이상하게 씁니다. 가령 다음과 같은 식이죠. 

String query = "select * from tab where id = " + id; 
PreparedStatement pstmt = con.prepareStatement(query); 

이런식이죠... 이렇게 해서야 바인드 변수가 쓰이지 않습니다. 

String query = "select * from tab where id = ?" 
PreparedStatement pstmt = con.prepareStatement(query); 
pstmt.setInt(1, id); 

이런식으로 코딩해야합니다.. 

그러면 DBMS에는 하나의 쿼리인 select * from tab where id = ? 만 들어가고 
DBMS는 이 문장만 파싱한뒤에 바인드 변수값 바뀌는 처리는 파싱을 제외하고 해줄 수 있습니다.. 

파싱이 뭐 대단하겠냐 하겠지만 파싱이 많으면 CPU점유율이 높아집니다.. 매일 CPU한계치에 
도달해서 뺑뺑이 치는 DBMS에서 이와같이 PreparedStatement를 쓰고 안쓰고의 차이는 
심지어 CPU 차지율을 20% 이상 차이나게 합니다.. 

(말이 20% 지 평상시에 40% 의 CPU가 사용되는 시스템은 60% CPU를 먹게 만든단 뜻입니다..) 

Statement로만 도배를 해놓은 경우 급하면 급한대로 오라클의 init{SID}.ora파일에서
CURSOR_SHARING=FORCE로 선언해주면 급하게 해결을 볼 수 있습니다..

그러나 오라클에서는 DSS나 QUERY REWRITE를 사용하는 환경에서는
이 매개변수를 FORCE로 고치지 말라고 경고하고 있습니다.

얼핏 생각해봐도 아시겠지만, CURSOR_SHARING=FORCE로 해주면 대부분의 쿼리를 오라클이 임의로 수정하여
바인드 변수를 쓰게 만들어 실행계획이 매번 동일하게 돌아가는 효과를 낳습니다.
따라서 QUERY REWRITE와 DSS에서 필요한 그때 그때에 맞는 정확한 플랜이 안나오게
될 수 있다는 것이죠.

QUERY REWRITE를 쓰는지 안쓰는지 모르겠다고 그러시면 안쓰는 것입니다. 제가 알기로는
QUERY REWRITE가 8i 이하에서는 Materialized View외에서는 사용되지 않습니다..
9i 부터는 모르겠군요. 

만약 자신의 데이터베이스에서 얼마나 많은 재파싱이 일어나고 싶은지를 알고 싶다면,
다음 스크립트를 sys로 접근한뒤 실행해보세요.

prompt
prompt * SQL문 parsing time 구하기
prompt
SELECT NAME, 
 VALUE 
FROM V$SYSSTAT
WHERE NAME = 'parse time cpu'
or NAME = 'parse time elapsed'
or NAME like 'parse count%';

결과는 대략 다음과 같이 나타납니다.

NAME VALUE
------------------------------ ------------
parse time cpu 0
parse time elapsed 0
parse count (total) 322,128
parse count (hard) 662

SQL> 

여기서 total 은 전체 파싱된 sql문장의 수 이며 (hard)는 하드 파싱, 즉 새로
파싱된 문장의 수를 의미합니다.

이 비율과 parse time cpu 에 나타난 parsing 에 소요된 cpu 시간을 사용해
접근하시면 됩니다. 

from:
http://javastudy.co.kr/bbs/read.jsp?bbs=lecadvancebbs&id=70

 

 

----------------------------------------------------------------------------------------

 

EXAMPLE  2.:

 

----------------------------------------------------------------------------------------
-- subject : 바인드변수를 사용하라
-- summary : pl/sql 또는 sql로직에서 바인드변수를 사용한 로직과 사용하지않은
  로직을 각각돌려보면 실행시간이 확실히 차이가난다.
  이 원인은, 바인드변수를 사용하면 shared pool내에서 최초 한번 실행한 sql은
  다음의 똑같은 sql이 왔을때 다시 hard parse를 하지 않고 이미 parse된 sql을 사용하게된다.
----------------------------------------------------------------------------------------

SQL> alter system flush shared_pool;

-- shared_pool내용을 비우는 역활을 한다. 
-- 이렇게 하는 이유는 이후 실행할 쿼리에 대한 parse결과를 메모리상에서 없애기 위해서이다.

SQL> declare
 type rc is ref cursor;
 l_rc rc;
 l_dummy all_objects.object_name%type;
 l_start number default dbms_utility.get_time;
begin
 for i in 1 .. 1000
 loop
  open l_rc for
  'select object_name from all_objects where object_id = ' || i;
  fetch l_rc into l_dummy;
  close l_rc;
 end loop;
 dbms_output.put_line ( round( (dbms_utility.get_time - l_start)/100, 2) || 'seconds');
end;
/

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

 

SQL> declare
 type rc is ref cursor;
 l_rc rc;
 l_dummy all_objects.object_name%type;
 l_start number default dbms_utility.get_time;
begin
 for i in 1 .. 1000
 loop
  open l_rc for
  'select object_name from all_objects where object_id = :x' using i; --바인드변수를 사용함
  fetch l_rc into l_dummy;
  close l_rc;
 end loop;
 dbms_output.put_line ( round ( (dbms_utility.get_time - l_start) / 100, 2) || 'seconds..');
end;
/

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

 

 pl/sql 또는 sql로직에서 바인드변수를 사용한 로직과 사용하지않은 로직을 각각돌려보명 실행시간이  확실히 차이가난다.
  

이 원인은, 바인드변수를 사용하면 shared pool내에서 최초 한버실행한 sql은 다음의 똑같은 sql이 왔을때 다시 hard parse를 하지 않고 이미 parse된 쿼리로 사용하게 되기 때문이다.

 

고로 하드 파싱을 줄이는것이 핵심이라고 할수 있다.

 

 

----------------------------------------------------------------------------------------

EXAMPLE  3.:

 

바인드 변수의 사용시 조건절의 컬럼에 인덱스가 있더라도 varchar2형에 숫자형을 바인드변수로 비교하게되면
인덱스가 있는 컬럼쪽이 to_number()로 형변환이 일어나게 된다. 따라서 인덱스를 사용할수 없고 테이블
풀스캔으로 시도한다.

ex) select empno, ename
from emp
where empno=:v1;


empno를 number로 타입을 바꾸거나,

차선책으로 v1 바인드 변수를 to_char()로 형변환하여 인덱스 스캔이 가능하도록 한다.

 

----------------------------------------------------------------------------------------

EXAMPLE  4.:

 

 

바인드의 가치 

Mark A. Williams

 

 바인드 변수와 ODP.NET을 사용한 .NET 애플리케이션의 성능 향상

 

.NET 애플리케이션에서 Oracle Database의 데이터에 액세스하는 방법에는 여러 가지가 있습니다. 그러나 기능과 성능의 관점에서 볼 때 .NET 애플리케이션을 Oracle Database와 연결하는 방법으로는 Oracle Data Provider for .NET(ODP.NET)가 가장 좋은 선택입니다.

본 컬럼에서는 ODP.NET 10g의 핵심 기능을 통해 .NET 애플리케이션을 향상시킬 수 있는 방법을 중심으로 살펴보기로 하겠습니다. 첫 번째 주제는 ODP.NET 10g와 함께 바인드 변수를 사용하는 방법에 대한 것입니다.

바인드 변수 사용

ODP.NET 10g가 효율적으로 작동하고 확장되도록 만들기 위한 가장 간편하고 효과적인 방법 중 하나는 바인드 변수를 적절히 사용하는 것입니다. 바인드 변수는 다양한 유형의 애플리케이션 성능 향상에 도움이 되지만 여기서는 ODP.NET 10g와 함께 사용하는 경우에 대해 살펴보겠습니다.

바인드 변수는 SQL 문의 위치 표시자입니다. 예를 들어 Oracle Database와 함께 제공되는 HR 샘플 스키마를 사용할 경우, 바인드 변수를 사용하지 않는 일반적인 SELECT 문은 다음과 같습니다.

select country_name from hr.countries where country_id = 'UK' 

보는 것처럼 문의 WHERE 절에 리터럴 텍스트를 ‘UK’로 지정하였습니다. 간단히 한 가지를 변경하여 바인드 변수를 사용하도록 문을 변환할 수 있습니다.

select country_name from hr.countries where country_id = :country_id 

바인드 변수를 사용하는 문으로 변환하기 위해 리터럴 텍스트‘UK’를 :country_id로 식별되는 위치 표시자로 바꾸었습니다. 이 바인드 변수 식별자는 단일 콜론(":")으로 시작되는데 이것은 바인드 변수가 SQL 문에서 어떻게 나타나는지를 보여줍니다.

여기서는 SELECT 문을 사용하여 바인드 변수의 용도를 예시하였으나 바인드 변수는 UPDATE, INSERT, DELETE 문에서도 사용될 수 있으며 또 사용해야 합니다. 다음은 같은 countries 테이블을 사용하여UPDATE 문에서 바인드 변수를 사용한 예입니다.

update hr.countries 

set country_name = :country_name 

where country_id = :country_id 

바인드 변수가 중요한 이유

Oracle Database 10g가 SQL 문으로 제시되면 메모리 영역인 공유 풀을 확인하여 해당 문이 이미 존재하는지, 그 문이 메모리에 저장되어 있는지를 확인합니다. 해당 문이 메모리에 이미 존재하고 Oracle Database 10g가 해당 문을 재사용할 수 있으면 데이터베이스는 이 문의 구문 분석과 최적화 작업을 생략하고 건너뛸 수 있습니다. 바인드 변수를 사용하는 경우에는 SQL 문이 메모리에 저장될 가능성이 매우 높아지므로 SQL 문을 필요로 하는 후속 작업에서 이 문을 신속하게 사용할 수 있습니다.

공유 풀에 해당 문이 존재하지 않는 경우, 데이터베이스는 해당 문에 대한 구문 분석과 최적화 작업을 수행해야 하므로 성능이 저하될 수 있습니다. 문의 구문 분석과 최적화 작업에는 CPU 주기가 소모되며 소모되는 CPU 주기가 많을수록 작업은 더 느려지게 됩니다. 구문 분석과 최적화 작업은 또한 래치라는 메커니즘을 통해 공유 풀의 다양한 부분을 잠금 상태로 두게 됩니다. 한 번에 하나의 프로세스만 래치를 유지할 수 있으므로 공유 풀에 래칭이 증가하면 데이터베이스 안에서 경합이 벌어지게 됩니다.

단일 사용자 시스템에서는 래치가 발생하는 양과 문의 구문 분석 및 최적화에 사용되는 시간이 별로 중요하지 않게 보일 수 있습니다. 그러나 시스템에 사용자가 계속 추가되거나 애플리케이션의 추가 복사본이 실행되면 이러한 이벤트는 순식간에 늘어날 수 있으며 심지어는 시스템을 사용불능 상태로 만들 수도 있습니다.

ODP.NET 10g에서 바인드 변수 구현

ODP.NET 10g 프로그램에서 바인드 변수를 사용하려면 OracleParameter 클래스를 사용하여 .NET 코드로 각 바인드 변수를 표시합니다. OracleParameterCollection 클래스는 그 이름으로 알 수 있듯이 각 문에 대한 OracleCommand 객체와 관련된 OracleParameter 객체를 포함하는 수집 클래스입니다.OracleCommand 클래스는 SQL 문을 데이터베이스에 전달하고 그 결과를 애플리케이션에 반환합니다.

ODP.NET 10g에서는 bind by position(기본값) 또는 bind by name이라는 바인드 변수를 사용하는 두 가지 모드를 사용할 수 있습니다. OracleCommand의 부울 속성 BindByName(기본값은 false)이 모드를 설정합니다. bind by position 모드를 사용할 경우 SQL 문에 보이는 것과 같은 순서로OracleParameterCollection 모음에 이 매개변수를 추가해야 합니다. 명령어 객체에 대한 모음에 매개변수를 추가하려면 Parameters 속성에서 Add 메소드를 사용합니다. bind by name 모드를 사용할 경우는 이 모음에 어떤 순서로 매개변수를 추가해도 관계 없으나, 해당 매개변수 객체에 대한ParameterName 속성을 SQL 문의 바인드 변수 식별자와 같은 값으로 설정해야 합니다.

바인딩 모드(by position 또는 by name) 외에도 일반적으로 각 매개변수 객체에 대해 다음과 같은 몇 가지 속성을 설정합니다.

• Direction
• OracleDbType
• Size
• Value

바인드 변수는 출력, 입력 또는 입/출력 매개변수로 사용될 수 있습니다. 각 매개변수에 대해 적절한 방향을 표시하려면 Direction 속성을 사용합니다. Direction 속성의 기본값은 Input.입니다. 이 매개변수가 숫자, 날짜, VARCHAR2 등 가운데 어떤 것인지 표시하려면 OracleDbType 속성을 사용합니다. VARCHAR2데이터 유형과 같은 변수 길이 데이터 유형을 사용할 경우, 매개변수가 가지고 있는 데이터의 최대 크기를 표시하려면 Size 속성을 사용합니다. Value 속성에는 문 실행 전(입력 매개변수의 경우), 실행 후(출력 매개변수의 경우), 또는 전후(입/출력 매개변수의 경우)의 매개변수 값이 들어있습니다.

목록 1의 Main 메소드는 이러한 개념들을 하나로 묶어주며 SELECT 문에서 바인드 변수를 사용합니다. 다음은 Main 메소드의 핵심 부분입니다.

OracleCommand cmd = new OracleCommand(); 

?명령 객체(OracleCommand) 생성

OracleParameter p_country_id = new OracleParameter(); 

?매개변수 객체(OracleParameter) 생성

p_country_id.OracleDbType = OracleDbType.Varchar2; p_country_id.Value = "UK"; 

?매개변수 객체의 OracleDbType 및 Value 속성 설정

다음 단계 ODP.NET에 대한 추가 정보 Oracle Data Provider for .NET 개발자 가이드 다운로드 이 컬럼에 대한 샘플 애플리케이션  ODP.NET 10g  Oracle Developer Tools for Visual Studio .NET

Direction 속성이 기본값 Input을 사용하며 Size 속성은 설정되어 있지 않은 점에 주의하십시오. 이 객체는 입력 매개변수이므로 속성을 설정할 필요가 없습니다. Data Provider가 해당 값의 크기를 결정할 수 있기 때문입니다.

cmd.Parameters.Add(p_country_id); 

?모음에 매개변수 추가

목록 1의 샘플 애플리케이션을 실행하려면 .NET 콘솔 애플리케이션을 생성하고 메소드의 본문으로서 목록 1 코드를 사용하여 Main 메소드를 생성합니다. 프로젝트에 ODP.NET 10g 어셈블리에 대한 참조를 추가하고 코드 모듈 상단에 Oracle .DataAccess.Client 이름 공간을 포함시켜야 합니다. 참조를 추가하려면 Microsoft Visual Studio .NET 2003 메뉴 표시줄에서 Project -> Add Reference...를 선택한 다음 Add Reference 대화상자에서 Oracle .DataAccess.dll을 선택합니다. 이름 공간을 포함시키려면 using Oracle.DataAccess.Client;를 코드 모듈 상단에 추가합니다. 출력 결과는 다음과 같습니다.

C:\VTB\bin\Release>ValuesThatBind.exe Country Name: United Kingdom 

코드 목록 1: 바인드 변수 사용

static void Main(string[] args) { 

 string constr = "User Id=hr; 

 Password=hr; Data Source=oramag"; 

 OracleConnection con = new OracleConnection(constr); 

 con.Open(); 

 StringBuilder sbSQL = new StringBuilder(); 

 sbSQL.Append("select country_name "); 

 sbSQL.Append("from countries "); 

 sbSQL.Append("where country_id = :country_id"); 

 OracleCommand cmd = new OracleCommand(); 

 cmd.Connection = con; cmd.CommandText = sbSQL.ToString(); 

 OracleParameter p_country_id = new OracleParameter(); 

 p_country_id.OracleDbType = OracleDbType.Varchar2; 

 p_country_id.Value = "UK"; cmd.Parameters.Add(p_country_id); 

 OracleDataReader dr = cmd.ExecuteReader(); 

 if (dr.Read()) 
 { 
 Console.WriteLine("Country Name: {0}", dr.GetOracleString(0)); 
 } 
 
 dr.Dispose(); 
 p_country_id.Dispose(); 
 cmd.Dispose(); 
 con.Dispose(); 
} 
바인드 변수: 계속 사용 권장
본 컬럼에서는 .NET 애플리케이션에서 ODP.NET 10g와 함께 간단한 바인드 변수를 사용하는 방법에 대해 설명했습니다. 바인드 변수를 사용할 때와 그렇지 않은 경우에 대해 각 애플리케이션을 테스트하여 바인드 변수의 이점을 확인해 보시기 바랍니다..
 
 
----------------------------------------------------------------------------------------
EXAMPLE  5.:
 
 
Bind 변수를 활용하자
다음과 같은 SQL 구문에 대해 생각해 봅시다.
SELECT * FROM customer WHERE custid=1 
SELECT * FROM customer WHERE custid=249 
SELECT * FROM customer WHERE custid=6380 
오라클은 기본적으로 각각의 구문 별로 다른 실행 계획을 사용합니다. 이 쿼리들이 모두 동일한 구문을 기반으로 하는 것임을 감안하면, 이러한 방법은 시간과 리소스 면에서 낭비일 수 있습니다. 그 대신, 쿼리에 바인드 변수(bind variable; “:var”)를 적용하고 1, 249, 6380의 값을 적용하는 방법을 사용할 수 있습니다. 이렇게 하면 모든 SQL 구문이 동일한 실행 계획을 공유하도록 할 수 있습니다:
변수에 바인드 된 값
SQL
1
249
SELECT * FROM customer WHERE custid=:var
6380
$value를 :var 에 바인딩 PHP 코드 예제가 다음과 같습니다:
OCIParse($conn, 'SELECT * FROM customer WHERE custid=:var'); 
OciBindByName($stmt, ":var", $value,32); 
# 32 is an arbitrary large size $value = 1; 
OCIExecute($stmt); 
DisplayResults($stmt); 
$value = 249; 
OCIExecute($stmt); 
DisplayResults($stmt); 
$value = 6380; OCIExecute($stmt); 
DisplayResults($stmt); 
바인딩은 “SQL injection” 공격의 피해를 줄일 수 있다는 또 다른 장점이 있습니다 (공격자들은 SQL 구문을 마음대로 조작하기 어렵게 됩니다).
Cursor sharing. 바인딩을 사용하지 않는 SQL 구문이 이미 많이 사용되고 있다면, Oracle8 이후 버전에서 제공되는 CURSOR_SHARING 매개변수를 사용하여 컴파일 과정에 수반되는 오버헤드를 줄일 수 있습니다. 아래 SQL 구문을 실행하는 경우 오라클은 모든 쿼리를 바인드 변수를 사용하도록 재작성합니다:
ALTER SESSION SET CURSOR_SHARING='FORCE' 
따라서 아래 구문은 파서(parser)에 전달되기 전에 바인딩이 가능한 구문으로 자동 변환됩니다:
변경 전: SELECT * FROM customer WHERE custid=6380 
변경 후: SELECT * FROM customer WHERE custid=:SYS_B_0 
CURSOR_SHARING을 이용하면 실행 계획의 공유 및 재활용 수준을 개선할 수 있습니다. 하지만 그 효과는 제한적입니다. 각 구문 별로 직접 바인드 변수를 적용하는 것이 더 효과적입니다. 또 SQL injection 공격의 방지 효과를 기대할 수 없다는 문제도 있습니다.
Oracle9i 이후 버전에서는 CURSOR_SHARING='SIMILAR'를 활용할 수 있습니다. 이 매개변수는 보다 지능적인 알고리즘을 사용하며, 바인드 변수의 적용여부를 결정하기 전에 테이블 통계정보를 참고합니다.
ALTER SESSION 명령은 현재 세션에만 영향을 미칩니다. 따라서 데이타베이스에 연결을 설정할 때마다 다시 호출되어야 합니다. 영구적으로 변경 사항을 적용하려면 DBA에게 요청하여 데이타베이스 매개변수 파일(init.ora 또는 spfile)을 변경해야 합니다.
다음 섹션에서는, 벤치마크 테스트를 통해 바인드 변수 적용에 따른 성능의 차이를 비교해 보도록 하겠습니다. 예제로 사용된 INSERT 구문에서 100% 이상의 성능 개선이 있었음을 확인하실 수 있을 것입니다. 
[출처] Bind variable(바인드 변수) 정의 및 사용법 예제[펌]|작성자 마사국쑥
[출처] 오라클 바인드(bind) 변수|작성자 실바

[출처] 오라클 바인드(bind) 변수|작성자 실바

[출처] 오라클 바인드(bind) 변수|작성자 실바