11 Static SQL 구현을 위한 기법들

• Dynamic SQL를 자주 사용하게 되는 첫 번째 이유를 앞에서 살펴 보았습니다.
  개발팀에 문의했을때 두 번째로 많이 나온 사례는 조건절에 IN-List 항목이 가변적으로 변할 때였습니다.
  이를 포함해 Dynamic SQL을 Static SQL로 바꾸는 기법을 몇가지 소개하려고 합니다.

• 여기서 소개하는 사례들은 Dynamic SQL로 작성하더라도 생성될 수 있는 최대 SQL 개수가 그다지 많지 않기 때문에 라이브러리 캐시에 그렇게 많은 부하를 주지는 않습니다.
• 앞에서 보았던 사용자가 선택적으로 입력한 검색조건에 따라 SQL이 다양하게 바뀌는 사례도 마찬가지였습니다.
  문제는, 이런저런 이유로 Dynamic SQL을 사용하는 순간 조건절 비교 값까지 습관적으로 Literal 상수 값을 사용하도록 개발한다는 데에 있습니다. 그런 뜻에서, 꼭 필요한 경우가 아니라면 가급적 Static SQL로 작성하는 습관과 능력을 기를수 있도록 몇 가지 사례를 독자와 공유하려는 것입니다. 누구나 쉽게 생각해 낼 수 있는 단순한 방법들이지만 이런 사례들을 접함으로써 SQL을 Static하게 구현하는 것이 결코 어려운 일이 아님을 전달 하고자 합니다.

(1) IN-List 항목이 가변적이지만 최대 경우 수가 적은 경우

• 
  
  
  
  
  
  
  
  
  
  
• LP회원을 선택하는 그림 4-15 팝업 창에서 사용자가 선택한 LP회원 목록을 선택하고자 할 때, Static 방식으로 SQL을 작성하려면 아래 7개 SQL을 미리 작성해 두어야 합니다.

  select * from LP회원 where 회원번호 in (01')
  select * from LP회원 where 회원번호 in (01', '02')
  select * from LP회원 where 회원번호 in (01', '03')
  select * from LP회원 where 회원번호 in (01', '02', '03')
  select * from LP회원 where 회원번호 in (02')
  select * from LP회원 where 회원번호 in (02', '03')
  select * from LP회원 where 회원번호 in (03')
  

• 선택 가능한 회원수가 4개로 늘어나면 15개, 5개면 31개의 SQL이 필요합니다. SQL을 미리 작성해 두고 경우에 따라 그 중 하나를 선택하도록 프로그래밍하는 것은 귀찮은 일이므로 Dynamic SQL을 사용하는 손쉬운 방법을 선택하게 됩니다.
  하지만 이를 Static SQL로 구현하기 위한 해법은 허무하게도 아래와 같이 간단합니다.

  select * from LP회원 where 회원번호 in ( :a, :b, :c )
  

• 사용자가 입력하지 안은 항목에 null 값을 입력하면 자동으로 결과집함에서 제외됩니다.
  그런데도 IN-List를 동적으로 생성하도록 Dynamic SQL을 사용하는 경우를 종종 보게 되므로 여기서 언급하는 것입니다.
  상황에 따라 아래처럼 decode문을 사용해야 할 때도 있습니다. 그림 4-15에서 4개의 체크박스에 각각 내부적으로 'all', '01', '02', '03' 값이 부여돼 있다고 가정하고 작성한 것입니다.

  select * from LP회원
  where 회원번호 in ( decode(:a, 'all', '01', :b)
  		 , decode(:a, 'all', '02', :c)
  		 , decode(:a, 'all', '03', :d)
  		 )
  

(2) IN-List 항목이 가변적이고 최대 경우 수가 아주 많은 경우

• 그림 4-16을 보면, IN-List 항목이 가변적이고 최대 개수도 고정적인 것은 앞 사례와 동일합니다.
  하지만 가능한 경우 수가 너무 많아 Static SQL로 일일이 작성해 두는 것은 불가능해 보입니다. 그렇다고 바인드 변수를 사용하는 것도 쉬운 일이 아닙니다. 실행 시 바인드 변수에 값을 입력하는 코딩을 그만큼 많이 해야 하기 때문입니다.그림 4-16은 화면에 보이는 부문만 표시했는데도 항목 개수가 이미 60여 개에 이릅니다. 좀 더 쉽게 구현하는 방법이 없을까?
  문제를 풀려면 발상의 전환이 필요합니다. SQL 조건절에는 대개 좌변에 컬럼을 두고 우변에는 그것과 비료할 상수 또는 변수를 위치시킵니다. 하지만 여기서는 생각을 바꿔 컬럼과 변수 위치를 서로 바꿔봅니다.

• select * from 수시공시내역
  where 공시일자 = :일자
  and :inlist like '%' || 분류코드 || '%'
  

• 조건절을 위와 같이 작성하고 사용자가 선택한 분류코드를 ',' 등 구분자로 연결해 아래처럼 String형 변수에 담아서 바인딩하고 실행시키면 됩니다.

  :inlist := '01,03,08,14,17,24,33,46,48,53'
  

• 참고로, 문자열을 처리하는 오라클 내부 알고리즘상 like 연산자보다 instr 함수를 사용하면 더 빠르므로 아래와 같이 작성할 것을 권고 합니다.

  select * from 수시공시내역
  where 공시일자 = :일자
  and INSTR(:inlist, 분류코드) > 0
  

• 인덱스 원리에 익숙한 독자라면 like, instr 둘 다 컬럼을 가공한 형태이므로 분류코드를 인덱스 액세스 조건으로 사용 못해 성능상 문제가 될 수 있음을 직감했을 것입니다. 이는 인덱스 구성에 따라 얘기가 달라집니다.

  1. IN-List를 사용할 때
  select * from 수시공시내역
  where 공시일자 = :일자
  and 분류코드 in ( ... )
  
  2. like 또는 instr 함수를 사용할 때
  select * from 수시공시내역
  where 공시일자 = :일자
  and INSTR(:inlist, 분류코드) > 0
  

• 인덱스 구성이 분류코드 + 공시일자일 때는 당연히 1번이 유리합니다. 2번 SQL문은 이 인덱스를 사용하지 못하거나 Index Full Scan 해야만 합니다.
  하지만 인덱스 구성이 공시일자 + 분류코드일 때는 상황이 따라 다릅니다. 사용자가 선택한 항목 개수가 소수일 때는 1번이 유리하지만 다수일 때는 인덱스를 그만큼 여러 번 탐침해야 하므로 2번이 유리할 수도 있습니다. 2번 SQL의 유 . 불리는 인덱스 깊이(루트에서 리프블록에 도달하기까지 거치는 블록 수)와 데이터 분포에 따라 결정될 것입니다. 하루치 데이터가 수십만건 이상 되는 경우가 아니라면 대개는 2번처럼 분류코드가 인덱스 필터 조건으로 사용되는 것만으로도 과도한 테이블 랜덤 액세스를 제거할 수 있어 만족할만한 성능을 얻을 수 있습니다.
• 어쨌든, 사용자가 대개 소수 항목만으로 조회하거나 인덱스 구성이 분류코드 + 공시일자일 때는 인덱스를 좀더 효율적으로 액세스할 수 있는 방법을 강구해야 합니다.

  < 방법1 >

  select /*+ ordered use_nl(B) */ B.*
  from ( select 분류코드
            from 수시공시분류
            where INSTR(:inlist, 분류코드) > 0 ) A
  	     , 수시공시내역 B
  where B.분류코드 = A.분류코드
  

• 위 체크 항목들을 화면에 뿌리기 위해 사용한 수시공시분류 테이블은 100개 이내의 작은 테이블일 것이므로 Full Scan으로 읽더라도 비효율이 없습니다. 작은 테이블을 Full Scan으로 읽으면서 NL 조인 방식으로 분류코드 값을 수시공시내역 테이블에 던져 줍니다. 그러면 수시공시내역 테이블에 있는 인덱스를 정상적으로 이용하면서 원하는 결과집합을 빠르게 얻을 수 있습니다.

  < 방법2 >

  select /*+ ordered use_nl(B) */ B.*
  from ( select substr(:inlist, (rownum-1)*2+1, 2))
            from 수시공시분류
  	  where rownum <= length(:inlist) / 2) A
  	     , 수시공시내역 B
  where B.분류코드 = A.분류코드
  

• 여기서도 드라이빙 집합으로서 수시공시분류 테이블을 사용했지만, 화면에 뿌려진 항목 개수 이상의 레코드를 갖는다면 어떤 테이블을 사용해도 무방합니다.
  이 SQL을 실행할 때는 사용자가 선택한 분류코드를 아래처럼 구분자 없이 연결해 String형 변수에 담아서 바인딩합니다.

  :inlist := '01030814172433464853'
  

• 인라인뷰 A만 먼저 실행시켜 보면, 아래 10개 레코드를 갖는 집합이 됩니다. 코드값은 대개 고정길이를 사용한다는 데에 착안한 것이며, 여기서는 2자리라고 가정하고 있습니다. 코드값 길이가 3자리면, 위 SQL문에서 숫자2를 모두 3으로 바꿔주기만 하면 됩니다.

          01
         03
         08
         14
         17
         24
         33
         46
         48
         53

• 이를 드라이빙 집합으로 삼아 수시공시내역 테이블을 NL 방식으로 조인한다면 < 방법1 >과 마찬가지로 수시공시내역 테이블에 있는 인덱스를 정상적으로 이용할 수 있습니다.

  < 방법3 >

  select /*+ ordered use_nl(B) */ B.*
  from (select substr(:inlist, (level-1)*2+1, 2)
  	from dual
  	connect by level <= length(:inlist) / 2) A
  	, 수시공시내역 B
  where B.분류코드 = A.분류코드
  

• < 방법2 >와 같은 방식이라고 할 수 있습니다. 다만, dual 테이블을 이용해 집합으르 동적으로 생성한다는 점만 다릅니다.

(3) 체크 조건 적용이 가변적인 경우

select 회원번호, SUM(체결건수), SUM(체결수량), SUM(거래대금)
from    일별거래실적 e
where  거래일자 = :trd_dd
and     시장구분 = '유가'
and exists (
	select 'x'
	from 종목
	where 종목코드 = e.종목코드
	and     코스피종목편입여부 = 'Y'
)
group by 회원번호

• 주식종목에 대한 회원사(=증권사)별 거래실적을 집계하는 쿼리입니다. 위에서의 Exists 서브쿼리는 코스피에 편입된 종목만을 대상으로 거래실적을 집계하고자 할 때만 동적으로 추가됩니다.
• 사실 이 케이스는 라이브러리 캐시 최적화와는 그다지 상관이 없습니다. 나올 수 있는 경우의 수가 두 개뿐이기 때문입니다. 그럼에도 Static SQL로 구현해 보려는 노력 속에 SQL을 아래와 같이 작성한 경우를 보았습니다. 문제는, 아이디어는 괜찮은데 성능이 오히려 이전보다 나빠진다는 데에 있습니다.

  select 회원번호, SUM(체결건수), SUM(체결수량), SUM(거래대금)
  from    일별거래실적 e
  where  거래일자 = :trd_dd
  and     시장구분 = '유가'
  and exists (
  	select 'x'
  	from 종목
  	where 종목코드 = e.종목코드
  	and   코스피종목편입여부 = decode(:check_yn, 'Y', 'Y', 코스피종목편입여부)
  )
  group by 회원번호
  

• 사용자가 코스피 종목 편입 여부와 상관없이 전 종목을 대상으로 집계하려고 할 때는 서브쿼리를 수행할 필요가 없습니다. 그럼에도 위와 같이 무리하게 SQL을 통합함으로써 항상 서브쿼리를 수행해야만 하는 비효율을 안게 된 것입니다. SQL트레이스 결과를 통해 확인해 보겠습니다.

  [ 전 종목을 대상으로 집계하고자 할 때 ]

• 아래에서 블록 I/O가 8,518개 발생한 것을 볼 수 있습니다. 지면관계상 exists절을 빼고 쿼리했을 때의 트레이스 결과를 보여주진 않았지만 600여 개에 불과했었습니다. 불필요한 서브쿼리를 수행함으로써 이전보다 성능이 더 떨어진 것입니다.

  :trd_dd := '20071228'
  :check_yn := 'N'
  
  Call         Count    CPU Time    Elapsed Time      Disk     Query    Current       Rows
  --------- ----------- ------------ --------------- -------- -------- --------- -----------
  Parse          1          0.000           0.000         0        0          0          0
  Execute        1          0.000           0.000         0        0          0          0
  Fetch        264          0.000           0.000         0     8518          0       2623
  --------- ----------- ------------ --------------- -------- -------- --------- -----------
  Total        266          0.000           0.000         0     8518          0       2623
  
  Rows      Row Source Operation
  ------   ------------------------------------------------
     0     STATEMENT
  2623       FILETER  (cr=8518 pr=0 pw=0 time=36781 us)
  2627         PARTITION RANGE SINGLE PARTITION: 20 20 (cr=641 pr=0 pw=0 time=10547 us)
  2627           TABLE ACCESS BY LOCAL INDEX ROWID 일별거래실적 PARTITION: 20 20 (cr=641 ...)
  2627             INDEX RANGE SCAN 일별거래실적 X01 PARTITION: 20 20 (cr=274 pr=0 ...)
  2623           TABLE ACCESS BY INDEX ROWID 종목 (cr=7877 pr=0 pw=0 time=25166 us)
  2623             INDEX RANGE SCAN 종목_PK (cr=5254 pr=0 pw=0 time=16450 us) (Object ID 79702)
  

          [ 사용자가 코스피 편입 종목만으로 집계하고자 할 때 ]

• 이 경우에는 쿼리를 통합했다고 손해를 보지는 않습니다. 통합하기 전과 블록 I/O가 같습니다.

  :trd_dd := '20071228'
  :check_yn := 'Y'
  
  Call         Count    CPU Time    Elapsed Time      Disk     Query    Current         Rows
  --------- ----------- ------------ --------------- -------- -------- --------- -----------
  Parse           1         0.000           0.000        0        0          0           0
  Execute         1         0.000           0.000        0        0          0           0
  Fetch          85         0.020           0.000        0     8208          0         839
  --------- ----------- ------------ --------------- -------- -------- --------- -----------
  Total          87         0.020           0.000        0     8208          0         839
  
  Rows      Row Source Operation
  ------   ------------------------------------------------
     0     STATEMENT
   839       FILETER  (cr=8208 pr=0 pw=0 time=13410 us)
  2627         PARTITION RANGE SINGLE PARTITION: 20 20 (cr=293 pr=0 pw=0 time=5296 us)
  2627           TABLE ACCESS BY LOCAL INDEX ROWID 일별거래실적 PARTITION: 20 20 (cr=293 ...)
  2627             INDEX RANGE SCAN 일별거래실적 X01 PARTITION: 20 20 (cr=97 pr=0 ...)
   839           TABLE ACCESS BY INDEX ROWID 종목 (cr=7915 pr=0 pw=0 time=27118 us)
  2623             INDEX RANGE SCAN 종목_PK (cr=5292 pr=0 pw=0 time=18069 us) (Object ID 79702)
  

• 위 수행 결과를 볼 때, 2개 SQL로 분리하는 편이 낫습니다.
  Static SQL 구현 능력을 향상시키는 차원에서 굳이 하나의 SQL로 통합하고 싶다면, 아래와 같이 함으로써 두 마리 토끼를 다 잡을 수 있습니다. 즉, I/O 효율과 라이브러리 캐시 효율을 모두 달성 가능합니다.

  select 회원번호, SUM(체결건수), SUM(체결수량), SUM(거래대금)
  from    일별거래실적 e
  where  거래일자 = :trd_dd
  and     시장구분 = '유가'
  and exists (
         select 'x' from dual where :check_yn = 'N'
  	   union all
  	   select 'x'
  	   from 종목
  	   where 종목코드 = e.종목코드
  	   and     코스피종목편입여부 = 'Y'
  	   and      :check_yn = 'Y'
  )
  group by 회원번호
  

• Exists 서브쿼리는 존재여부만 체크하는 것이므로 그 안에 union all을 사용하면 조인에 성공하는 첫 번째 레코드를 만나는 순간 더는 진행하지 않고 true를 리턴합니다. SQL트레이스를 통해 앞에서 수행했던 결과와 비교해 봅시다.

  [ 전 종목을 대상으로 집계하고자 할 때 ]

• 위 쿼리가 실행될 때 :check_yn 변수에 'N' 값이 들어오면, 즉 코스피에 편입되지 않은 종목까지 모두 읽고자 한다면 union all 아래쪽 종목 테이블과는 조인을 시도하지 않고 곧바로 Exists 서브쿼리를 빠져 나옵니다. 10g부터 DUAL 테이블은 PAST DUAL 방식으로 수행되기 때문에 블록 I/O는 발생하지 않습니다.
  따라서 위처럼 SQL을 통합하면 아래 트레이스 결과에서 보듯이 블록 I/O가 통합하기 이전과 똑같고, 개발자가 작성한 것보다는 7.5% 수준으로 감소했습니다.(차이를 느낄 수 없을 정도로 미미하긴 하지만 통합하기 전보다 필터링을 위해 CPU를 약간 더 소모한다는 사실은 알고 있도록 합니다.)

  :trd_dd := '20071228'
  :check_yn := 'N'
  
  Call         Count    CPU Time       Elapsed Time    Disk     Query    Current       Rows
  --------- ----------- ------------ --------------- -------- -------- --------- -----------
  Parse           1        0.000             0.000         0        0         0           0
  Execute         1        0.000             0.000         0        0         0           0
  Fetch         264        0.000             0.000         0      641         0        2627
  --------- ----------- ------------ --------------- -------- -------- --------- -----------
  Total         266        0.000             0.000         0      641         0        2627
  
  Rows      Row Source Operation
  ------   ------------------------------------------------
     0   STATEMENT
  2627     FILTER (cr=641 pr=0 pw=0 time=18443 us)
  2627       PARTITION RANGE SINGLE PARTITION: 20 20 (cr=641 pr=0 pw=0 time=7924 us)
  2627         TABLE ACCESS BY LOCAL INDEX ROWID 일별거래실적 PARTITION:20 20 (cr=641 ...)
  2627            INDEX RANGE SCAN 일별거래실적_X01 PARTITION: 20 20 (cr=274 pr=0 ...)
  2627         UNION-ALL  (cr=0 pr=0 pw=0 time=9061 us)
  2627           FILTER  (cr=0 pr=0 pw=0 time=4659 us)
  2627             FAST DUAL  (cr=0 pr=0 pw=0 time=1874 us)
     0           TABLE ACCESS BY INDEX ROWID 종목 (cr=0 pr=0 pw=0 time=0 us)
     0              INDEX RANGE SCAN 종목_PK  (cr=0 pr=0 pw=0 time=0 us)(Object ID 79702)
  

• 참고로, 9i 이전 버전이었다면 차라리 두 개 SQL로 분리하는 게 나을 수도 있습니다. 필터처리 시 캐싱 효과가 있긴 하지만 최소한 해당 일자에 거래가 있었던 종목코드 개수만큼 DUAL 테이블을 반복적으로 읽어야 하기 때문입니다. DAUL 테이블을 Full Scan 방식으로 일게 되므로 건건이 세그먼트 헤더를 포함해서 보통 2~4개 정도의 블록 I/O를 추가로 일으키게 됩니다.

  [ 사용자가 코스피 편입 종목만으로 집계하고자 할 때 ]

• 이 경우는 통합 전에도 어차피 서브쿼리를 수행했어야 하므로 동일한 성능을 보입니다.

  :trd_dd := '20071228'
  :check_yn := 'Y'
  
  Call         Count    CPU Time       Elapsed Time      Disk   Query    Current         Rows
  --------- ----------- ------------ --------------- -------- -------- --------- -----------
  Parse            1         0.000          0.000          0        0         0           0
  Execute          1         0.000          0.000          0        0         0           0
  Fetch           85         0.040          0.053          0     8208         0         839
  --------- ----------- ------------ --------------- -------- -------- --------- -----------
  Total           87         0.040          0.053          0     8208         0         839
  
  Rows      Row Source Operation
  ------   ------------------------------------------------
     0   STATEMENT
   839     FILTER (cr=8208 pr=0 pw=0 time=15177 us)
  2627       PARTITION RANGE SINGLE PARTITION: 20 20 (cr=293 pr=0 pw=0 time=7919 us)
  2627         TABLE ACCESS BY LOCAL INDEX ROWID 일별거래실적 PARTITION:20 20 (cr=293 ...)
  2627            INDEX RANGE SCAN 일별거래실적_X01 PARTITION: 20 20 (cr=97 pr=0 pw=0 ...)
   839         UNION-ALL  (cr=0 pr=0 pw=0 time=36922 us)
     0           FILTER  (cr=0 pr=0 pw=0 time=1817 us)
     0             FAST DUAL  (cr=0 pr=0 pw=0 time=0 us)
   839           TABLE ACCESS BY INDEX ROWID 종목 (cr=7915 pr=0 pw=0 time=26760 us)
  2623              INDEX RANGE SCAN 종목_PK  (cr=5292 pr=0 pw=0 time=17769 us)
  

(4) select-list가 동적으로 바뀌는 경우

• 사용자 선택에 따라 화면에 출력해야 할 항목이 달라지는 경우가 종종 있습니다. 그림 4-17은, 그런 요건 때문에 SQL을 동적으로 구성할 수 밖에 없었다면서 어떤 개발팀에서 가져온 사례입니다.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

• /* 1 : 평균 2: 합계 */
  if( pfmStrCmpTrim(INPUT->inData.gubun, "1", 1) == 0){
  	snprintf(..., " avg(계약수), avg(계약금액), avg(미결제약정금액) ");
  } else {
  	snprintf(..., " sum(계약수), sum(계약금액), sum(미결제약정금액) ");
  }
  

• 이것을 Static SQL로 바꾸는 것은 너무 쉽습니다. decode 함수 또는 case 구문을 활용하면 됩니다.

• /* 1 : 평균 2: 합계 */
  decode(:gubun, '1', avg(계약수), sum(계약수)),
  decode(:gubun, '1', avg(계약금액), sum(계약금액)),
  decode(:gubun, '1', avg(미결재약정금액), sum(미결재약정금액)),
  

(5) 연산자가 바뀌는 경우

• 조건절에 사용되는 입력항목이나 출력항목이 바뀌는 게 아니라 그림 4-18처럼 비교 연산자가 그때그때 달라지는 경우는 어떻게 Static SQL로 구현할 수 있을까요?

• 미만, 이하, 이상, 초과, 중 사용자가 선택하는 항목이 무엇이냐에 따라 <, <=, >, >= 4가지 중 하나의 연산자로 바꿔야 하므로 Dynamic SQL이 불가피하다고 생각할 수 있습니다. 하지만 누구나 조금만 고민해 보면 쉽게 해법을 찾을 수 있습니다. 아래처럼 SQL을 작성하고 바인딩하는 값을 바꾸면 됩니다.

• where 거래미형성률       between :min1 and :max1
  and    일평균거래량        between :min2 and :max2
  and    일평균거래대금     between :min3 and :max3
  and    호가스프레드비율  between :min4 and :max4
  and    가격연속성           between :min5 and :max5
  and    시장심도              between :min6 and :max6
  and    거래체결률           between :min7 and :max7
  

• 각 컬럼은 아래와 같은 도메인에 따라 표준화된 데이터 타입과 자릿수를 할당받습니다.

• 도메인	데이터 타입
  거래량	NUMBER(9)
  거래대금	NUMBER(15)
  가격연속성	NUMBER(5, 2)
  ......	......

• 일평균거래량을 예로 들면, 거래량 도메인은 9자리 숫자형이고 정수 값만 허용하므로 입력 가능한 최소값은 0, 최대값은 999,999,999입니다. 따라서 사용자가 1000주를 입력하여 사용자가 선택한 비교 연산자에 따라 아래와 같이 Between 시작값과 종료값을 바인딩 하면 됩니다.

• 구분	between 시작값(:min3)	between 종료값(:min3)
  이하	0	1000
  미만	0	999
  이상	1000	999999999
  초과	1001	999999999

• 정수형이 아니 가격연속성을 하나 더 예로 들면, 가격연속성 도메인은 소수점 이하 2자리를 갖는 총 5자리 숫자형이므로 입력 가능한 최소값은 0.00, 최대값은 999,99입니다. 따라서 사용자가 50%를 입력하면 사용자가 선택한 비교 연산자에 따라 아래와 같이 Between 시작값과 종료값을 바인딩하면 됩니다.

• 구분	between 시작값(:min5)	between 종료값(:min5)
  이하	0.00	50.00
  미만	0.00	49.99
  이상	50.00	999.99
  초과	50.01	999.99

참조 문서

이 자료는 (오라클 성능 고도화 원리와해법 I)을 참고 하여 작성했습니다.

10 Dynamic SQL 사용 기준

(1) Dynamic SQL 사용에 관한 기본 원칙

• 지금까지 설명한 라이브러리 캐시 최적화 원리와 ,Static, Dynamic SQL 정의에 비추어 Dynamic SQL 사용에 관한 기본 원칙을 다음과 같이 정리할 수 있습니다.

1. Static SQL을 지원하는 개발 환경이라면 Static SQL로 작성하는 것을 원칙으로 합니다.
     Static SQL은 PreCompile을 과정을 거치므로 런타임 시 안정적인 프로그램 Build가 가능하다는 장점이 있습니다. 그리고 Dynamic SQL,을 사용하면 애플리케이션 커서 캐싱 기능이 작동하지 않는 경우가 있는데, 이 기능이 필요한 상황(예를 들어, 루프 내에서 반복 수행되는 쿼리)에서  Dynamic SQL을 사용하면 성능이 나빠지기 때문입니다.
2. 아래 경우에는 Dynamic SQL를 사용해도 무방합니다.
   1. PreCompile 과정에서 컴파일 에러가 나는 구문을 사용할 때, 예를 들어, Pro*C에서 스칼라 서브쿼리, 분석함수, ANSI 조인 등
   2. 상황과 조건에 따라 생성될 수 있는 SQL 최대 개수가 많아 Static SQL로 일일이 나눠서 작성하려면 개발 생산성이 저하되고 유지보수 비용이 매우 커질 때
3. 2번 경우에 해당해서 Dynamic SQL를 사용하더라도 조건절에는 바인드 변수를 사용하는 것을 원칙으로 합니다. 특히, 사용빈도가 높고 조건절 컬럼의 값 종류가 매우 많을 때(예를 들어, 계좌번호, 상품번호, 회원번호 등)는 반드시 준수합니다.
4. 3번 바인드 변수 사용원칙을 준수하되 아래 경우는 예외적으로 인정합니다.
   1. 배치 프로그램이나 DW, OLAP등 정보계 시스템에서 사용되는 Long Running쿼리. 이들 쿼리는 파싱 소요시간이 쿼리 총 소요시간에서 차지하는 비중이 매우 낮고, 수행빈도가 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 적음.
   2. OLTP성 애플리케이션이더라도 사용빈도가 매우 낮아 하드파싱에 의한 라이브러리 캐시 부하를 유발할 가능성이 없을 때. 예외적으로 인정하는 것이므로 단순히 바인드 변수 정의하는 게 귀찮다고 그렇게 해서는 안됨.
   3. 조건절 컬럼의 값 종류(Distinct Value)가 소수일 때, 특히 값 분포가 균일하지 않아 옵티마이저가 컬럼 히스토그램 정보를 활용하도록 유도하고자 할 때.
   예) 증권시장구분코드 = ( '유가', '코스닥', '주식파생', '상품파생' )

• Static(=Embedded)SQL을 지원하지 않는 개발 환경이라면 모든 SQL이 Dynamic SQL이지만 런타임 시 SQL이 동적을 바뀌도록 개발하는 것만큼은 삼가야 합니다. 그런 환경에서는 Static과 Dynamic SQL을 편의상 아래와 같이 재정의하고, 위에서 제시한 기본 원칙을 동일하게 적용할 것을 권고합니다.

• Static SQL : SQL Repository 에 완성된 형태로 저장한 SQL
• Dynamic SQL : SQL Repository에 불완전한 형태로 저장한 후 런타임 시 상황과 조건에 따라 동적으로 생성되도록 작성한 SQL

(2) 기본 원칙이 잘 지켜지지 않는 첫 번째 이유, 선택적 검색 조건

• 이렇게 Dynamic SQL 사용 원칙을 정하고 개발을 시작해도, 중간에 점검해 보면 여전히 잘 지켜지지 않습니다. Static SQL을 지원하는 개발 환경에서조차 자주 Dynamic SQL를 사용해 조건절을 동적으로 구성합니다. 그 원인을 개발팀에 물어 보면, 가장 많은 비중을 차지하는 것이 아래 그림에서처럼 검색 조건이 다양해 사용자 선택에 따라 조건절이 동적으로 바뀌는 경우입니다.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• 크기가 작은 테이블이라면 상관없겠지만 대상 테이블이 대용량인데도 화면을 이처럼 설계하는 것은 참 무책임하다는 생각을 하게 됩니다. 이렇게 다양한 검색 조건을 한 화면에서 처리해 준다고 하면 업무 담당자 입장에서는 싫다고 마다할 리 없습니다. 심지어는 필수입력 항목도 전혀 없고, 검색기간도 무제한입니다. 그런데 성능은 누가 담보할 것인지... 조회버튼을 누를 때마다 10분씩 소요된다면 기능이 좋다고 해서 만족할 사용자는 아마 없을것입니다. 항상 개발 막바지에 가서 이런 화면들 때문에 성능 이슈가 불거지는 것을 많이 목격했습니다.
• 대용량 테이블에 대한 조회 요건이 이렇게 복잡하다면 적어도 현업과의 협의를 통해 필수입력 항목을 수렴해서 프로그램에 반영해야 합니다. 또한 기간 조건(Between, <, >, <=, >= 등)에 대해서는 입력 값 범위를 가능한 짧게 제한하려는 노력을 반드시 해야 합니다. 업무 담당자를 만나서 인터뷰해 보면 의외로 문제가 쉽게 풀리는 경우가 많습니다. 설계 단계에서부터 성능을 고려한 업무 요건 도출리 필요한 이유가 여기에 있습니다.
• 현업과의 협의를 거쳐 필수항목 위주로 화면을 구성하더라도 업무요건 상 조회 조건이 다양한 경우는 있기 마련입니다. 그럴 때 SQL을 효과적으로 작성하려면 어떻게 접근해야 하는지 같이 고민해 봅시다.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• 거래일자만 필수입력 조건이고, 나머지는 모두 선택적 입력 조건이라고 가정합니다.
  SQL 작성에 대한 가이드가 없는 상황에서 위와 같은 조회 프로그램을 구현할 때면 개발자는 십중팔구 아래와 같이 SQL을 작성할 것입니다.

• select 거래일자, 종목코드, 투자자유형코드, 주문매체코드
       , 체결건수, 체결수량, 거래대금
  from 일별종목거래
  where 거래일자 between  :시작일자 and  :종료일자
  %option
  

• 그러고 나서 사용자의 선택이나 입력 값에 따라 %option 부분에 조건절을 아래와 같이 동적으로 붙여나갑니다.

  %option = " and 종목코드 = 'KR123456' and 투자자유형코드 = '1000'"
  

• SQL 작성 표준이 존재하는 대형 개발 프로젝트라면 당연히 Dynamic SQL 사용을 제한하므로 표준을 준수하려고 아래와 같은 방법을 사용하게 됩니다. 참고로, NVL을 사용하는 것 외에 몇 가지 방법이 더 있는데, 각각의 장단점에 대해서는 뒤에서 다시 다루기로 하겠습니다.

  select 거래일자, 종목코드, 투자자유형코드, 주문매체코드
       , 체결건수, 체결수량, 거래대금
  from 일별종목거래
  where 거래일자 between :시작일자 and :종료일자
  and 종목코드 = nvl(:종목코드, 종목코드)
  and 투자자유형코드 = nvl(:투자자유형코드, 투자자유형코드)
  and 주문매체구분코드 = nvl(:주문매체구분코드, 주문매체구분코드)
  

• 이렇게 코딩하면 사용자가 어떻게 값을 입력하더라도 단 한 개의 실행계획을 공유하면서 반복 재사용하게 되므로 라이브러리 캐시 효율 측면에서는 최상의 선택입니다. 하지만 쿼리 수행 속도 측면에서는 어떨까요? 인덱스를 이용한 액세스가 효과적인 상황에서 낭패를 보게 됩니다. 인덱스를 전혀 사용하지 못하거나(거래일자가 인덱스 선두 컬럼이 아닐 때) 사용하더라도 비효율적으로 사용하기 때문입니다.
• *라이브러리 캐시 효율과 I/O 효율을 모두 고려하면서 SQL을 개발하려면, 남아 있는 방법은 한 가지뿐입니다. 종목코드, 투자자유형코드, 주문매체구분코드 입력여부에 따라 SQL을 모두 분리해서 개발하는 것입니다. 그럴 경우 이 3가지 선택적 입력 조건을 처리하는 데에만 모두 8개의 SQL을 따로 작성해야 합니다.  

          1. 거래일자 between
         2. 거래일자 between, 종목코드 =
         3. 거래일자 between, 종목코드 =, 투자자유형코드 =
         4. 거래일자 between, 종목코드 =, 투자자유형코드 =, 주문매체구분코드 =
         5. 거래일자 between, 종목코드 =, 주문매체구분코드 =
         6. 거래일자 between, 투자자유형코드 =
         7. 거래일자 between, 투자자유형코드 =, 주문매체구분코드 =
         8. 거래일자 between, 주문매체구분코드 =

• 여기서는 SQL이 간단하므로 8개쯤 작성하는 게 대수롭지 않을 수 있지만 실무에서 작성되는 SQL은 대개 짧으면 수십 라인, 길면 수백 라인에 이릅니다.
  빠듯한 개발 일정에 쫓기는 프로젝트에서 이렇게 개발하라고 하는 것은 현실성이 떨어지며 불가능에 가깝습니다.
• 인덱스 원리를 잘 아는 SQL튜닝 전문가 시각에서 보면, 위 요건을 만족하려고 SQL을 8개나 만들 필요는 없습니다. 변별력이 좋지 않은 컬럼은 인덱스 엑세스 효율에 도움이 되지 않으므로, 인덱스 구성을 고려해 변별력이 좋은 컬럼 중심으로 2~3개의 SQL로 분기하면 됩니다.
  애플리케이션에서 IF문을 이용해 분기하거나 아래처럼 union all을 사용하는 방법이 있습니다.

        < 인덱스 구성 >
        INDEX01 : 종목코드 + 거래일자
        INDEX02 : 투자자유형코드 + 거래일자 + 주문매체구분코드
        INDEX03 : 거래일자 + 주문매체구분코드

select 거래일자, 투자자유형코드, 회원번호
     , 체결건수, 체결수량, 거래대금
from  일별종목거래
where :종목코드 is not null
and   거래일자 between :시작일자 and :종료일자
and   종목코드 = :종목코드
and   투자자유형코드 = nvl(:투자자유형, 투자자유형코드)
and   주문매체구분코드 = nvl(:주문매체, 주문매체코드)
union all
select 거래일자, 투자자유형코드, 회원번호
     , 체결건수, 체결수량, 거래대금
from  일별종목거래
where :종목코드 is null and : 투자자유형 is not null
and   거래일자 between :시작일자 and :종료일자
and   투자자유형코드 = nvl(:투자자유형, 투자자유형코드)
and   주문매체구분코드 = nvl(:주문매체, 주문매체코드

union all
select 거래일자, 투자자유형코드, 회원번호
     , 체결건수, 체결수량, 거래대금
from  일별종목거래
where :종목코드 is null and :투자자유형 is null
and 거래일자 between :시작일자 and :종료일자
and 주문매체구분코드 = nvl(:주문매체, 주문매체구분코드)

• 하지만 개발 기간 내내 SQL마다 이런 식으로 최적의 인덱스 구성전략을 고민하면서 개발한다는게 결코 쉬운 일은 아닙니다. 그리고 데이터 분포와 인덱스 구성 등을 고려해 이와 같은 형태로 SQL을 최적화할 수 있는 고급 개발자가 그리 많지 않다는 현실도 인정해야 합니다.
• 위와 같은 union all로 분기하는 기법은, 일반적인 SQL 작성 표준보다는 튜닝차원에서 접근하고 필요에 따라 적절히 활용하도록 하는 것이 타당합니다.

(3) 선택적 검색 조건에 대한 현실적인 대안

• 그렇다면 현실적인 대안은? 앞 절에서 이미 정리했듯이 Static SQL 사용을 원칙으로 하되 사용자 입력 조건에 따라 생성될 수 있는 SQL 최대 개수가 너무 많을 때는 Dynamic SQL 사용을 허용하는 것입니다. 조건절에 따를 SQL 개수가 많더라도 그 중 일부만 주로 사용되므로 실질적인 하드 파싱 부하는 거의 없습니다. 다만, 라이브러리 캐시 효율화의 핵심인 바인드 변수 사용 원칙만큼은 준수하도록 해야 합니다.
• 개발 언어나 툴에서 Dynamic Method를 제공하는 목적에 맞게, 부하를 최소화하는 수준에서 잘 활용하는 것이 최선이라고 생각합니다. 아래는 PL/SQL에서 조건절을 동적으로 구성하되 바인드 변수를 사용하도록 코딩한 예제입니다. JAVA, Proc*C 등에서도 활용 가능한 패턴입니다.(JAVA에서 아래와 같은 패턴을 사용하지 않으면 if...else 구문을 두 번씩 작성하게 됩니다. SQL문을 위해 한 번, 바인드 변수를 위해 한 번)

  SQLStmt := 'SELECT 거래일자, 종목코드, 투자자유형코드, '
          || '주문매체코드, 체결건수, 체결수량, 거래대금 '
          || 'FROM 일별종목거래 '
          || 'WHERE 거래일자 BETWEEN :1 AND :2 ';
  IF :종목코드 IS NULL Then
  SQLStmt := SQLStmt || 'AND :3 IS NULL ';
  Else
  SQLStmt := SQLStmt || 'AND 종목코드 = :3 ';
  End If;
  
  If :투자자유형 IS NULL Then
    SQLStmt := SQLStmt || 'AND :4 IS NULL ';
  Else
  SQLStmt := SQLStmt || 'AND 투자자유형코드 =:4 ';
  End If;
  
  EXECUTE IMMEDIATE SQLStmt
  INTO :A, :B, :C, :D, :E, :F, :G
  USING :시작일자, :종료일자, :종목코드, :투자자유형코드;
  

• 이밖에 Visual Basic 스타일로 변환한 것으로서, Delphi등에서도 활용 가능한 패턴은 교제의 322 페이지를 참고 바랍니다.
• 이처럼 SQL을 Dynamic하게 구성하면, 인덱스를 설계할 때 다소 불편하다는 단점이 있습니다. SQL Repository에서 SQL을 수집해 테이블별 액세스 유형을 분석하면서 인덱스 설계를 해야 하는데, 조건절이 프로그램 수행 도중에 동적으로 바뀌기 때문입니다. 그리고 옵티마이저 힌트를 사용해 튜닝하기도 곤란합니다.
  이런 단점이 있긴 하지만 개발 생산성도 무시할 수 없으므로 Dynamic SQL을 적재적소에 잘 활용하라고 권고 아니 권고를 하는 것입니다. 튜닝은 말 그대로 튜닝입니다. 개발이 튜닝 관점에서 필요한 조치들을 취하면 된다고 생각합니다.
• 인덱스 설계 문제에 대해 얘기하자면, 완성된 형태의 SQL들은 sQL Repository에 저장된 것을 참조하고 그렇지 않은 것들은 수행된 최종 SQL들을 수집(SQL 트레이스 또는 v$sql 등 활용)해서 자주 나타나는 액세스 유형을 기준으로 인덱스 설계를 진행하면 됩니다.(솔직히 인덱스 설계를 위해 SQL Repository나 프로그램 소스를 직접 열어가며 작업하는 경우는 거의 없으며, 대부분 후자의 방식을 따릅니다.)
• Pro*C, PL/SQL, Visual Basic, Delphi, 어떤 프로그램에서 수행했든 위와 같이 Dynamic 패턴으로 SQL을 던지면 오라클 서버를 통해 수집되는 최종 SQL은 아래와 같은 형태입니다.

         SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드
                   , 체결건수, 체결수량, 거래대금
        FROM 일별종목거래
        WHERE 거래일자 BETWEEN ? AND ?

         SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드
                  , 체결건수, 체결수량, 거래대금
        FROM 일별종목거래
        WHERE 거래일자 BETWEEN ? AND ?
        AND 종목코드 = ?

         SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드
                  , 체결건수, 체결수량, 거래대금
        FROM 일별종목거래
        WHERE 거래일자 BETWEEN ? AND ?
        AND 투자자우형코드 = ?

         SELECT 거래일자, 종목코드, 투자자유형코드, 주문매체코드
                 , 체결건수, 체결수량, 거래대금
        FROM 일별종목거래
        WHERE 거래일자 BETWEEN ? AND ?
        AND 종목코드 = ?
        AND 투자자유형코드 = ?

• Static SQL로 작성했을 때와 차이가 없습니다. 따라서 이들 액세스 경로 기준으로 인덱스 설계를 하면 되고, 이것을 Static SQL로 작성했다고 해서 인덱스 구성전략이 달라지지는 않습니다.
  인덱스 구성전략만으로 튜닝이 되지 않을 때는 옵티마이져 힌트를 사용해야 하는데, 조건절이ㅣ 위와 같이 동적으로 바뀐다면 힌트를 함부로 사용할 수 없습니다. 그때는 할 수 없이 Static SQL을 사용해야 하며, 인덱스 구성과 컬럼 분포, 자주 사용되는 액세스 유형들을 고려해 SQL을 통합하고 힌트를 기술할 수 있는 형태로 재작성해야만 합니다.
• 이렇게 설명해 놓고 한가지 걱정이 생기는데, 여기 설명한 내용을 근거로 Dynamic SQL를 무분별하게 사용하려는 개발자가 생겨나지 않을까 싶습니다. 다시 한번 강조하지만, 원칙은 Static SQL로 작성하는 것이며, 방법이 없거나 SQL이 너무 복잡할 때만 Dynamic SQL을 꺼내 들려고 노력해야 합니다. 그런 뜻에서 Dynamic SQL을 Static SQL로 바꿔서 구현한 사례들을 다음 절에서 소개하려고 합니다.
  그전에, 선택적 입력 조건을 처리할 때 NVL 대신 사용할수 있는 몇 가지 방법들이 있다고 했는데, 여기서 각각의 특징을 살펴보고 넘어가겠습니다.

(4) 선택적 검색 조건에 사용할 수 있는 기법 성능 비교

      A. OR 조건을 사용하는 경우

select * from 일별종목거래
where (:isu_cd is null or isu_cd = :siu_cd)

Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS
1  0  TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)

     - 항상 Table Full Scan으로 처리되므로 인덱스 활용이 필요할 때는 이 방식을 사용해선 안 됩니다.

      B. LIKE 연산자를 사용하는 경우

select * from 일별종목거래
where isu_cd like :isu_cd || '%'

Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS
1  0  TABLE ACCESS (BY LOCAL INDEX ROWID) OF '일별종목거래' (TABLE)
2  1   INDEX (RANGE SCAN) OF '일별 종목거래_PK' (INDEX (UNIQUE))

    - 인덱스 사용이 가능하지만 사용자가 :isu_cd 값을 입력하지 않았을 때 Table Full Scan이 유리한데도 인덱스를 사용하게 되므로 성능이 나빠질 수 있습니다.

      C. NVL 함수를 사용하는 경우

select * from 일별종목거래
where isu_cd = nvl(:isu_cd, isu_cd)

Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS
1  0  CONCATENATION
2  1   FILTER
3  2    TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
4  1   FILTER
5  4    TABLE ACCCESS (BY LOCAL INDEX ROWID) OF '일별종목거래' (TABLE)
6  5     INDEX (RANGE SCAN) OF '일별종목거래_PK' (INDEX (UNIQUE))

      D. DECODE 함수를 사용하는 경우

select * from 일별종목거래
where isu_cd = decode(:isu_cd, null, isu_cd, :isu_cd)

Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS
1  0  CONCATENATION
2  1   FILTER
3  2    TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
4  1   FILTER
5  4    TABLE ACCESS (BY LOCAL INDEX ROWID) OF '일별종목거래' (TABLE)
6  5     INDEX (RANGE SCAN) OF '일별종목거래_PK' (INDEX (UNIQUE))

    - C와 D 방식은, 사용자의 :isu_cd 입력 여부에 따라 Full Table Scan과 Index Scan으로 실행계획이 자동 분기 됩니다.  단, nvl 또는 decode 함수를 사용할 때는 해당 컬럼이 not null 컬럼이어야 하며, null   컬럼이어야 하며, null 허용 컬럼일 때는 결과 집합이 달라지므로 주의해야 합니다. 사용자가 :isu_cd 값을 입력하지 않았을 때는 조건절이 isu_cd = isu_cd가 되는데, isu_cd 컬럼 값이 null일 때 오라클은 false를 리턴하기 때문입니다. 참고로, null = null 비교가 가능한 DBMS도 있기는 합니다. 잘 이해가 되지 않는 다면 아래 결과를 참고하시길.

SQL> select * from dual
 2   where NULL = NULL;

선택된 레코드가 없습니다.

SQL> select * from dual
  2  where NULL IS NULL ;
DU
--
X

1 개의 행이 선택되었습니다

     - nvl 또는 decode를 여러 컬럼에 대해 사용했을 때는 그 중 변별력이 가장 좋은 컬럼 기준으로 한번만 분기가 일어난다는 사실도 기억할 필요가 있고,

      복잡한 옵션 조건을 처리 할 때 이 방식에만 의존하기 어려운 이유가 여기에 있습니다.

      E. union all를 사용하는 경우

select * from 일별종목거래
where :isu_cd is null
union all
select * from 일별종목거래
where :isu_cd is not null
and isu_cd = :isu_cd

Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS
1  0  UNION-ALL
2  1   FILTER
3  2    TABLE ACCESS (FULL) OF '일별종목거래' (TABLE)
4  1   FILTER
5  4    TABLE ACCESS (BY LOCAL INDEX ROWID) OF '일별종목거래' (TABLE)
6  5     INDEX (RANGE SCAN) OF '일별종목거래_PK' (INDEX (UNIQUE))

• 이 방식에 대해서는 따로 설명이 필요 없을 것 같습니다. 5가지 방식에 대한 선택 기준을 정리해 보면 아래와 같습니다.

         1. not null 컬럼일 때는 nvl, decode를 사용(C와 D)하는 것이 편하다.
        2. null 값을 허용하고 인덱스 액세스 조건으로 의미있는 컬럼이라면 union all을 사용(E)해 명시적으로 분기해야한다.
        3. 인덱스 액세스 조건으로 참여하지 않는 경우, 즉 인덱스 필터 또는 테이블 필터 조건으로만 사용되는 컬럼이라면 (:c isnull or cao = :c) 또는 (c like :c || '%')어떤 방식을 사용(A와 B) 해도 무방합니다.

참조 문서

이 자료는 (오라클 성능 고도화 원리와해법 I)을 참고 하여 작성했습니다.

08 Static vs. Dynamic SQL

• 하드 파싱 부하를 최소화하기 위해 Dynamic SQL 대신 Static SQL을 사용하라는 표현을 흔히 사용하는데, 용어를 제대로 사용하고 있는지 확인해 볼 필요가 있습니다.
• 그런뜻 에서 라이브러리 캐시 최적화를 위한 Static SQL 작성 기법들을 소개하기 전에 Static과 Dynamic SQL 용어를 명확히 정의 하고자 합니다.
• 먼저 아래 그림의 두 쿼리 툴에서 작성한 SQL문이 Static SQL인지, 아니면 Dynamic SQL인지 생각해 보기 바랍니다.
  

• 왼쪽은 Dynamic이고 오른쪽은 Static이라고 답했다면 지금부터 설명하는 내용을 대충 읽고 넘어가서는 안됩니다.
• 한 가지 예를 더 살펴보겠습니다. 아래는 JAVA에서 SQL문을 수행하는 예제인데, 이에 대해서는 이견 없이 Dynamaic SQL이라고 답할 것입니다.

  SQLState = "select count(*) from emp where deptno = " + p_deptno;
  stmt = conn.prepareStatement(SQLState);
  rs = stmt.executeQuery();
  

• 그렇다면, 아래는 어떤가요?

  if(p_deptno == 10){
     SQLState = "select count(*) from emp where deptno = 10";
  }else if(p_deptno == 20){
     SQLState = "select count(*) from emp where deptno = 20";
  }else if(p_deptno == 30){
     SQLState = "select count(*) from emp where deptno = 30";
  }else if(p_deptno == 40){
     SQLState = "select count(*) from emp where deptno = 40";
  }
  stmt = conn.prepareStatement(SQLState);
  rs = stmt.executeQuery();
  

• Static인가? 아니면 Dynamic인가? 튜닝 교육을 할때 직접 질문을 던져보면 수강생마다 각기 다르게 대답합니다.
  그런데 대부분 개발 프로젝트에 가서 SQL 작성 표준 가이드 문서를 보면 Dynamic SQL을 사용하지 말라는 문구는 반드시 들어가 있습니다.
  용어의 의미조차 명확하지 않은데, 이 표준이 잘 지켜질 리가 만무합니다.
  조건절에 바인드 변수를 사용하면 Static SQL, Literal 상수 값을 사용하면 Dynamic SQL로 분류하는 튜닝 교재들이 있어 이런 혼선이 빚어졌다고 생각합니다.

(1) Static SQL

• Static SQL이란, String형 변수에 담지 않고 코드 사이에 직접 기술한 SQL문을 말합니다.
  다른 말로 `Embedded SQL`이라고도 합니다.
• 아래는 Pro*C 구문으로 Static SQL을 작성한 예시입니다.

   int main()
  {
     printf("사번을 입력하십시오 : ");
     scanf("%d", &empno);
     EXEC SQL WHENAVER NOT FOUND GOTO notfound;
     EXEC SQL SELECT ENAME INTO :ename
              FROM   EMP
              WHERE  EMPNO = :empno;
     printf("사원명 : %s.\n", ename);
  
  notfound:
     printf("%d는 존재하지 않는 사번입니다. \n", empno);
  }
  

• 여기서 보듯이 SQL문을 String 변수에 담지 않고 마치 예약된 키워드처럼 C/C++ 코드 사이에 섞어서 기술하고 있습니다.

(2) Dynamic SQL

• Dynamic SQL이란, String형 변수에 담아서 기술하는 SQL문을 말합니다.
  String 변수를 사용하므로 조건에 따라 SQL문을 동적으로 바꿀 수 있고, 또는 런타임 시에 사용자로부터 SQL문의 일부 또는 전부를 입력 받아서 실행할 수도 있습니다.
  따라서 PreCompile 시 Syntax, Semantics 체크가 불가능 합니다.
• Dynamic SQL을 만나면 PreCompiler는 그 내용을 확인하지 않고 그대로 통과시킵니다.
  Pro*C환경에서 개발해 본 독자라면 스칼라 서브쿼리, 분석 함수, ANSI 조인문 등을 사용했을 때 PreComile 과정에서 에러가 나는 경험을 했을 것입니다.
  Semantic 체크는 DB 접속을 통해 이루어지지만 Syntax 체크만큼은 PreCompiler에 내장되 SQL 파서를 이용하는데, 위 구문들을 사용하면 현재 사용 중인 PreCompiler가 그것들을 인식하지 못해 에러를 던지는 것입니다.
  해결 방법은 Dynamic SQL을 사용하면 됩니다.
• 아래는 Pro*C에서 Dynamic SQL을 작성한 사례다. SQL을 String형 변수에 담아 실행하는 것에 주목하기 바랍니다.
  바로 아래 주석 처리한 부분은 SQL을 런타임 시 입력 받는 방법을 예시합니다.

  int main()
  {
     char select_stmt[50] = "SELECT ENAME FROM EMP WHERE EMPNO = :empno";
     // scanf("%c", &select_stmt); --> SQL문을 동적으로 입력 받을 수도 있음
  
     EXEC SQL PREPARE sql_stmt FROM :select_stmt;
     EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt;
     EXEC SQL OPEN emp_cursor USING :empno;
     EXEC SQL PETCH emp_cursor INTO :ename;
     EXEC SQL CLOSE emp_cursor;
     printf("사원명 : %s.\n", ename);
  }
  

• Pro*C에서 제공하는 Dynamic Method에는 4가지가 있고, 간단히 요약하면 아래와 같습니다.

• Method 1 : 입력 Host 변수 없는 Non-Query
  'DELETE FROM EMP WHERE DEPTNO = 20' 'GRANT SELECT ON EMP TO scott'

• Method 2 : 입력 Host 변수 개수가 고정적인  Non-Query
  'INSERT INTO EMP (ENAME, JOB) VALUES (:ename, :job)' 'DELETE FROM EMP WHERE EMPNO = :empno'

• Method 3 : select-list 컬럼 개수와 입력 Host 변수 개수가 고정적인 Query
  'SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO' 'SELECT DNAME, LOC FROM DEPT WHERE DEPTNO = 20' 'SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO = :deptno'

• Method 4 : select-list 컬럼 개수와 입력 Host 변수 개수가 가변적인 Query
  'INSERT INTO EMP (<unknown>) values (<unknown>)' 'SELECT <unknown> FROM EMP WHERE DEPTNO = :deptno'

(3) 일반 프로그램 언어에서 SQL 작성법

• 지금까지 Pro*C 위주로 Static과 Dynamic SQL을 설명했는데, 가장 인기있는 개발언어 중 하나인 JAVA에서는 SQL을 어떻게 작성하는지 살펴겠습니다.

  PreparedStatement stmt;
  ResultSet rs;
  StrongBuffer SQLStmt = new StringBuffer();
  SQLStmt.append("SELECT ENAME, SAL FROM EMP ");
  SQLStmt.append("WHERE EMPNO = ?");
  
  stmt = conn.prepareStatement(SQLStmt.toString());
  stmt.setLong(1, txtEmpno.value);
  rs = stmt.executeQuery();
  
  // do anything
  
  rs.close();
  stmt.close();
  

• 다음은 Delphi에서 SQL을 작성하는 예시입니다.

  begin
    Query1.Close;
    Query1.Sql.Clear;
    Query1.Sql.Add('SELECT ENAME, SAL FROM EMP ');
    Query1.Sql.Add('WHERE EMPNO = :empno');
    Query1.ParamByuName('empno').AsString := txtEmpno.Text;
    Query1.Open;
  end;
  

• 마지막으로, Visual Basic에서 SQL문 작성 예시를 보겠습니다.

  Dim comm As new ADODB.Command
  Dim rs As ADODB.Recordset
  Dim SQLStmt as String
  
  SQLStmt = "SELECT ENAME, SAL FROM EMP "
  SQLStmt = SQLStmt & "WHERE EMPNO = ?"
  comm.CommandText = SQLStmt
  comm.Parameters.Append comm.CreateParameter("empno", adNumeric, adparamInput)
  comm.Parameters("empno").Value = txtEmpno.Text
  Set rs = comm.Execute
  
  ' do anything
  
  rs.Close
  Set rs = Nothing
  Set comm = Nothing
  

• 여기 3가지 사례에서 보듯이 Static SQL을 작성할 수 있는 방법이 제공되지 않습니다.
  모두 String 변수에 담아서 실행하는 것입니다.
  따라서 이들 언어에서 작성된 SQL로 작성하지 말라고 한다면 어불성설입니다.
  그리고 Toad, Orange, SQL*Plus과 같은 Ad-hoc 쿼리 툴에서 작성하는 SQL도 모두 Dynamic SQL이라고 보면 틀림없습니다.
  이들 툴이 컴파일되는 시점에서 SQL이 확정되지 않았으며, 사용자가 던지는 SQL을 런타임 시에 받아서 그대로 DBMS에 던지는 역할만 할뿐입니다.
• Static(=Embedded) SQL을 지원하는 개발 언어로는 PowerBuilder, PL/SQL, Pro*C, SQLJ 정도가 있습니다.

(4) 문제의 본질은 바인드 변수 사용 여부

• 지금까지 설명한 Static, Dynamic SQL은 애플리케이션 개발 측면에서의 구분일 뿐이며, 데이터베이스 입장에서는 차이가 없습니다.
  Static SQL을 사용하든 Dynamic SQL을 사용하든 오라클 입장에서는 던져진 SQL문 그 자체만 인식할 뿐이며, PL/SQL, Pro*C 등에서 애플리케이션 커서 캐싱 기능을 활용하고자 하는 경우 외에는 성능에도 전혀 영향이 없습니다.
  애플리케이션 커서 캐싱 기능을 사용하지 않는다면 Dynamic, Static 구분은 라이브러리 캐시 효율과도 전혀 무관합니다.
• 그러므로 라이브러리 캐시 효율을 논할 때 초점은 바인드 변수 사용 여부에 맞춰져야 합니다.
  Dynamic SQL을 사용해 문제가 되는 것이 아니라 바인드 변수를 사용하지 않았을때 문제가 되는 것입니다.
  바인드 변수를 사용하지 않고 Literal 값을 SQL 문자열에 결합하는 방식으로 개발했을 때, 반복적인 하드 파싱으로 성능이 얼마나 저하되는지, 그리고 그때문에 라이브러리 캐시에 얼마나 심한 경합이 발생하는지, 그 원리에 대해서는 앞에서 충분히 설명했습니다.
• 다시 한번 강조하지만, 바인드 변수 사용 여부로 Static과 Dynamic을 구분하는 것은 잘못된 것이므로 용어 사용에 주의 하길 바랍니다.

참조 문서

이 자료는 (오라클 성능 고도화 원리와해법 I)을 참고 하여 작성했습니다.

08 애플리케이션 커서 캐싱

   애플리케이션 커서 캐싱이란?

• 오라클 공식 용어는 아니며, Parse Call을 발생시키지 않고 SQL을 반복 수행하는 기능에 대해 필자(조시형)가 붙인 이름입니다.
  1. 세션 커서를 캐싱하면 SGA의 공유 커서를 빠르게 찾아서 커서를 오픈할수 있습니다.
  2. 세션 커서 캐시에 있는 SQL을 수행 하더라도 공유 커서 힙을 Pin하고 실행에 필요한 메모리
     공간을 PGA에 할당하는 등의 작업은 반복하게 됩니다.
  3. 이 과정마저 생략하고 빠르게 SQL을 수행하는 방법이 있는데, 이를 `애플리케이션 커서 캐싱`이라고 합니다.
  4. 개발 언어마다 구현 방법이 다르므로 이 기능을 활용하려면 API를 잘 살펴봐야합니다.

• Pro*C에서는 SQL을 수행하는 부분을 아래처럼 두 개 옵션으로 감싸면, 커서를 놓지 않고 반복 재사용합니다.

   for(;;){
      EXEC ORACLE OPTION (HOLD_CURSOR=YES);
      EXEC ORACLE OPTION (RELEASE_CURSOR=NO);
      EXEC SQL INSERT ...... ;      // SQL 수행
      EXEC ORACLE OPTION (RELEASE_CURSOR=YES);
  }
  

• HOLD_CURSOR 옵션은 애플리케이션 커서와 세션 커서와의 관계를 다루는 반면
  RELEASE_CURSOR 옵션은 공유커서와 세션 커서와의 관계를 다룹니다.
  일반적으로는 Execute Call 횟수만큼 Parse Call이 반복 되지만, 애플리케이션 커서 캐싱 기능을 이용하면
  공유 커서를 Pin한 채 반복 수행하므로 Parse Call이 최초 한번만 발생하고 이후로는 발생하지 않습니다.
  아래는 애플리케이션에서 커서를 캐싱한 채 같은 SQL을 5,000번 반복 수행했을 때의 SQL 트레이스 결과입니다.

   call        count      cpu    elapsed     disk     query      current     rows
  --------- -------- -------- ---------- -------- --------- ------------ --------
  Parse            1     0.00       0.00        0         0            0        0
  Execute       5000     0.18       0.14        0         0            0        0
  Fetch         5000     0.17       0.23        0     10000            0     5000
  --------- -------- -------- ---------- -------- --------- ------------ --------
  total        10001     0.35       0.37        0     10000            0     5000
  
  Misses in library cache during parse: 1
  

  • Parse Call이 한번만 발생했고, 이후 4,999번 수행할 때는 Parse Call이 전혀 발생하지 않았음을 알수 있습니다.
    (최초 Parse Call이 발생한 시점에 라이브러리 캐시에서 커서를 찾지 못해 하드 파싱을 수행한 사실도 라이브러리 캐시 Miss 항목을 통해 읽을 수 있어야 합니다.)

• JAVA에서 이를 구현하려면 묵시적 캐싱(Implicit Caching) 옵션을 사용하거나 Statement를 닫지 않고 재사용하면 됩니다.
  SQL을 아래 4가지 패턴으로 작성하고, 각각에 대한 수행속도를 비교해 보겠습니다.
  • 패턴1 : 바인드 변수를 사용하지 않을때
  • 패턴2 : 바인드 변수를 사용하지만, 커서를 캐싱하지 않을 때
  • 패턴3 : 커서를 닫지 않고 재사용할 때
  • 패턴4 : 묵시적 캐싱 기능을 사용할 때
• 아래는 각 패턴별로 작성된 메서드를 호출하는 main() 메서드 부분입니다.

   public static void main(String[] arr) throws SQLException, Exception{
  	// (1) Bind 변수를 사용하지 않을 때
  	noBinding(connMgr.getConnectionMethod(), 5000);
  
  	// (2) Bind 변수를 사용하지만, Caching 옵션을 사용하지 않을 때
  	noCaching(connMgr.getConnectionMethod(), 5000);
  
  	// (3) Cursor를 닫지 않고 반복적으로 재사용할 때
  	cursorHolding(connMgr.getConnectionMethod(), 5000);
  
  	// (4) Caching 옵션을 사용할 때
  	cursorCaching(connMgr.getConnectionMethod(), 5000);
  }
  

(1) 바인드 변수를 사용하지 않을 때

• 아래부터 나오는 JAVA 소스는 교재의 내용을 그대로 표기 했으며, 실제 테스트를 위한 전체 소스는 첨부파일을 확인하여 테스트가 가능합니다.

   public static void noBinding(Connection conn, int count)throws Exception{
  	PreparedStatement stmt;
  	ResultSet rs;
  
  	for(int i = 1; i <= count; i++){
  		stmt = conn.prepareStatement(
  			"SELECT /* no_binding */" + i + ", " + i + ", 'test', a.* " +
  			"FROM emp a WHERE a.ename LIKE 'W%'");
  		rs = stmt.executeQuery();
  
  		rs.close();
  		stmt.close();
  	}
  }
  

• 바인드 변수를 사용하지 않았으므로 매번 하드 파싱을 반복 수행하게 됩니다.
  아래는 SQL 트레이스에서 처음 2번 실행한것과 맨 마지막 것만을 추출한 것입니다.
• 테스트를 해보니 첫번째 Parse시에 elapsed 값이 0.01을 보였던거 외에는 5000번을 수행할때까지 동일한 결과가 나왔음을 확인 했습니다.
  한가지 더 비교 하자면 교재에서는 2로 표기되었던 Fetch시에 query 부분의 값이 제가 테스트를 했을때는 모두 7로 나오는걸 확인했습니다.

  ********************************************************************************
  
  SELECT /* no_binding */1, 1, 'test', a.*
  FROM
   emp a WHERE a.ename LIKE 'W%'
  
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        1      0.00       0.01          0          0          0           0
  Execute      1      0.00       0.00          0          0          0           0
  Fetch        1      0.00       0.00          0          7          0           1
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total        3      0.00       0.01          0          7          0           1
  
  Misses in library cache during parse: 1
  ********************************************************************************
  
  SELECT /* no_binding */2, 2, 'test', a.*
  FROM
   emp a WHERE a.ename LIKE 'W%'
  
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        1      0.00       0.00          0          0          0           0
  Execute      1      0.00       0.00          0          0          0           0
  Fetch        1      0.00       0.00          0          7          0           1
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total        3      0.00       0.00          0          7          0           1
  
  Misses in library cache during parse: 1
  ********************************************************************************
  .
  .
  . 5000번까지 동일
  ********************************************************************************
  
  SELECT /* no_binding */5000, 5000, 'test', a.*
  FROM
   emp a WHERE a.ename LIKE 'W%'
  
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        1      0.00       0.00          0          0          0           0
  Execute      1      0.00       0.00          0          0          0           0
  Fetch        1      0.00       0.00          0          7          0           1
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total        3      0.00       0.00          0          7          0           1
  
  Misses in library cache during parse: 1
  ********************************************************************************
  

(2) 바인드 변수를 사용하지만, 커서를 캐싱하지 않을 때

• 아래는 바인드 변수를 사용했지만 본 절에서 설명하고 있는 애플리케이션 커서 캐싱 기법은 사용하지 않았습니다.

   public static void noCaching(Connection conn, int count)throws Exception{
  	PreparedStatement stmt;
  	ResultSet rs;
  
  	for(int i = 1; i <= count; i++){
  		stmt = conn.prepareStatement(
  			"SELECT /* no_caching */ ?, ?, ?, a.* " +
  			"FROM emp a WHERE a.ename LIKE 'W%'");
  		stmt.setInt(1, i);
  		stmt.setInt(2, i);
  		stmt.setString(3, "test");
  		rs = stmt.executeQuery();
  
  		rs.close();
  		stmt.close();
  	}
  }
  

• 따라서 Parse Call이 Execute Call 횟수만큼 발생하게 됩니다.
  하지만 하드파싱은 전혀 발생하지 않거나 한번쯤 발생합니다.

  ********************************************************************************
  
  SELECT /* no_caching */ :1, :2, :3, a.*
  FROM
   emp a WHERE a.ename LIKE 'W%'
  
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse     5000      0.00       0.28          0          0          0           0
  Execute   5000      0.01       0.84          0          0          0           0
  Fetch     5000      0.00       1.11          0      35000          0        5000
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total    15000      0.01       2.24          0      35000          0        5000
  
  Misses in library cache during parse: 1
  ********************************************************************************
  

(3) 커서를 닫지 않고 재사용할 때

• 이번에는 아래처럼 PreparedStatement를 루프문 바깥에 선언하고 루프 내에서 반복 사용하다가 루프를 빠져 나왔을 때 닫습니다.
  JAVA PreparedStatement 객체가 앞에서 설명했던 `애플리케이션 커서`에 해당합니다.

  public static void cursorHolding(Connection conn, int count)throws Exception{
  	// PreparedStatement를 루프문 바깥에 선언.
  	PreparedStatement stmt = conn.prepareStatement(
  		"SELECT /* cursor_holding */ ?, ?, ?, a.* " +
  		"FROM emp a WHERE a.ename LIKE 'W%'");
  	ResultSet rs;
  
  	for(int i = 1; i <= count; i++){
  		stmt.setInt(1, i);
  		stmt.setInt(2, i);
  		stmt.setString(3, "test");
  		rs = stmt.executeQuery();
  
  		rs.close();
  	}
  
  	// 루프를 빠져 나왔을 때 커서를 닫는다.
  	stmt.close();
  }
  

• 아래 트레이스 결과를 보면, 앞에서 HOLD_CURSOR와 RELEASE_CURSOR 옵션을 사용한 Pro*C 사례에서 보았듯이 Parse Call이 한번만 실행됩니다.

  ********************************************************************************
  
  SELECT /* cursor_holding */ :1, :2, :3, a.*
  FROM
   emp a WHERE a.ename LIKE 'W%'
  
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        1      0.00       0.00          0          0          0           0
  Execute   5000      0.01       1.43          0          0          0           0
  Fetch     5000      0.01       0.70          0      35000          0        5000
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total    10001      0.02       2.15          0      35000          0        5000
  
  Misses in library cache during parse: 1
  ********************************************************************************
  

(4) 묵시적 캐싱 기능을 사용할 때

• 마지막으로, PreparedStatement를 루프문 안쪽에 선언하고 루프 내에서 쿼리를 수행 하자마자 곧바로 닫습니다.
  하지만 setStatementCacheSize를 0보다 크게 설정하고, setImplicitCachingenabled 메소드를 true로 설정하였습니다.

  public static void cursorCaching(Connection conn, int count)throws Exception{
  
  	// 캐시 사이즈를 1로 지정
  	((OracleConnection)conn).setStatementCacheSize(1);
  
  	// 묵시적 캐싱 기능을 활성화
  	((OracleConnection)conn).setImplicitCachingEnabled(true);
  
  	for(int i = 1; i <= count; i++){
  		PreparedStatement stmt = conn.prepareStatement(
  			"SELECT /* implicit_caching */ ?, ?, ?, a.* " +
  			"FROM emp a WHERE a.ename LIKE 'W%'");
  		stmt.setInt(1, i);
  		stmt.setInt(2, i);
  		stmt.setString(3, "test");
  		ResultSet rs = stmt.executeQuery();
  
  		rs.close();
  
  		// 커서를 닫지만 내부적으로는 닫히지 않은 채 캐시에 보관
  		stmt.close();
  	}
  }
  

• 루프 내에서 PreparedStatement를 매번 닫았지만 아래 트레이스 결과에서 보듯 Parse Call은 단 한번만 발행합니다.
  묵시적 캐싱(Implicit Caching) 옵션을 활성화 했기 때문입니다.

  ********************************************************************************
  
  SELECT /* implicit_caching */ :1, :2, :3, a.*
  FROM
   emp a WHERE a.ename LIKE 'W%'
  
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        1      0.00       0.04          0          0          0           0
  Execute   5000      0.01       1.35          0          0          0           0
  Fetch     5000      0.00       0.68          0      35000          0        5000
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total    10001      0.01       2.08          0      35000          0        5000
  
  Misses in library cache during parse: 1
  ********************************************************************************
  

• 단지 프로그램 개발 패턴만 바꿨을 뿐인데 뚜렷한 성능차이를 보이고 있습니다.
  java 프로그램으로 테스트했기 때문에 네트워크를 통한 Roundtrip 영향이 커,
  파싱에 의한 부하는 상대적으로 작게 나타난 점을 감안해야합니다.
  같은 테스트를 PL/SQL에서 수행한다면 성능 차이는 더 확연히 드러납니다.
• PL/SQL에서는 위와 같은 옵션을 별도로 적용하지 않더라도 자동적으로 커서를 캐싱합니다.
  단, Static SQL을 사용할 때만 그렇습니다. Dynamic SQL을 사용하거나 Cursor Variable(=Ref Cursor)을 사용할 때는
  커서를 자동으로 캐싱하는 효과가 사라진다는 사실을 명심하기 바랍니다.
• 그렇다면, PL/SQL에서는 최대 몇 개 SQL을 내부적으로 캐싱할까? 오라클 9i까지는 open_cursors 파라미터에 의해 결정됩니다.
  따라서 세션 커서 캐싱 기능을 비활성화하면(session_cached_cursor=0) PL/SQL의 자동 커서 캐싱 기능까지 비활성화되므로 주의 해야 합니다.
• 테스트 내용을 확인해 봅니다.

  Dynamic SQL 사용 시, 커서 캐싱(10g 이후)

  10g 이후부터 같은 SQL문을 연속적으로 실행하면, Dynamic SQL 이더라도 바로 직전 커서를 캐싱합니다.(Ref Cursor일 때는 제외) Static SQL과 마찬가지로, 이 기능도 session_cached_cursors 파리미터가 0보다 클 때만 작동합니다. # 아래는 system 계정으로 테스트를 했습니다. SQL> alter session set session_cached_cursors = 100; Session altered. SQL> create table t ( x number); Table created. SQL> alter system flush shared_pool; System altered. SQL> declare   2  i number;   3  begin   4    for i in 1..100   5    loop   6      execute immediate 'insert into t values(' || mod(i, 10) || ')';   7    end loop;   8    9  commit; 10  end; 11  / PL/SQL procedure successfully completed. SQL> select count(distinct sql_text) sql_cnt   2     , sum(parse_calls) parse_calls   3     , sum(executions) executions   4  from v$sql   5  where sql_text like 'insert into t values%';   SQL_CNT PARSE_CALLS EXECUTIONS ---------- --------- ----------        10         100        100 > 0부터 9까지의 값을 입력하는 10개 SQL을 불연속적으로 실행했더니 Parse Call이 SQL 수행횟수만큼 발생했습니다. SQL> alter system flush shared_pool; System altered. SQL> declare   2     i number;   3  begin   4     for i in 1..100   5     loop   6             execute immediate 'insert into t values(' || ceil(i/10) || ')';   7     end loop;   8    9     commit; 10  end; 11  / PL/SQL procedure successfully completed. SQL> select count(distinct sql_text) sql_cnt   2     , sum(parse_calls) parse_calls   3     , sum(executions) executions   4  from v$sql   5  where sql_text like 'insert into t values%';   SQL_CNT PARSE_CALLS EXECUTIONS ---------- --------- ----------        10          10        100 > 1부터 10개까지의 값을 입력하는 10개 SQL을 연속적으로 입력했더니 Parse Call이 SQL 개수만큼 발생했습니다. SQL> alter session set session_cached_cursors = 0; Session altered. SQL> alter system flush shared_pool; System altered. SQL> declare   2     i number;   3  begin   4     for i in 1..100   5     loop   6             execute immediate 'insert into t values(' || ceil(i/10) || ')';   7     end loop;   8    9     commit; 10  end; 11  / PL/SQL procedure successfully completed. SQL> select count(distinct sql_text) sql_cnt   2     , sum(parse_calls) parse_calls   3     , sum(executions) executions   4  from v$sql   5  where sql_text like 'insert into t values%';   SQL_CNT PARSE_CALLS EXECUTIONS ---------- --------- ----------        10         100        100 > 1부터 10가지의 값을 입력하는 10개 SQL을 연속적으로 입력했지만 세션 커서 캐싱 기능을 비활성화시켰더니     Parse Call이 SQL 수행 횟수만큼 발생했습니다.

•   아래는 어떤 회사에서 수집한 AWR 리포트 중 Instance Efficiency 부분만을 발췌한 것입니다.

   Instance Efficiency Percentages (Target 100%)
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
           Buffer Nowait %:  99.98    Redo NoWait %:   100.00
           Buffer   Hit  %:  97.93 In-memory Sort %:   100.00
           Library  Hit  %:  99.55     Soft-Parse %:    98.34
        Execute to Parse %:  89.31      Latch Hit %:    98.67
  Parse CPU to Parse Elapsd %: 61.11  % Non-Parse CPU:  97.58
  

• 이 중 Execute to Parse 항목을 주목하기 바랍니다. 이는 아래 공식으로 구해진 값입니다.(3장에서 이미 다룬 바가 있습니다. )
  Execute to Parse = (1 - parse/execute)*100

• 즉, Parse Call 없이 SQL을 수행한 횟수를 의미합니다.
  그런데 n-Tier 환경에서는 DB Connection을 사용하고 나서 곧바로 커넥션 풀에 반환 하므로 애플리케이션에 커서 캐싱 기법을 제대로 활용하기가 쉽지 않습니다.
  따라서 요즘 같은 웹 애플리케이션 환경에서는 대개 이 항목이 50% 미만의 낮은 수치를 보이기 마련입니다.
  그런데도 위처럼 89.31%로 비교적 높게 나타난 이유는 PL/SQL로 작성한 함수/프로시저를 적극적으로 많이 사용한 시스템에서 자주 나타나는 현상입니다.
  이처럼 애플리케이션 커서 캐싱 기법을 잘 활용하면 라이브러리 캐시 효율에 매우 긍정적인 효과를 가져다 줍니다.

참조 문서

이 자료는 (오라클 성능 고도화 원리와해법 I)을 참고 하여 작성했습니다.

첨부 자료

Parse Call 테스트(JAVA)

# 이 문서는 오라클클럽에서 작성하였습니다.
# 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
# 출처 : http://wiki.oracleclub.com/pages/viewpage.action?pageId=4948589&