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 속성 설정
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% 이상의 성능 개선이 있었음을 확인하실 수 있을 것입니다.
