[Oracle] 바인드 변수의 부작용과 해법

바인드 변수의 부작용과 해법

1. 커서란

• 바인드변수 SQL 수행절차

- 최초 수행시 최적화
- 실행계획 라이브러리 캐시 적재
- 실행시 바인딩

• 최초 수행할 때 최적화를 거친 실행계획을 라이브러리 캐시에 적재하고, 실행시점에는 그것을 그대로 가져와 값만 다르게 바인딩하여서 반복 재사용
• SQL의 최적화시점에서는 조건절 컬럼의 데이터 분포도를 활용하지 못하는 문제점을 가짐
  • 바인드 변수를 사용할 때는 평균 분포를 가정한 실행계획을 생성
  • 아래의 기준으로 선택도를 구하게 된다.

1	번호 > : NO	5	번호 between :NO and :NO
2	번호 < : NO	6	번호 > :NO and 번호 <= :NO2
3	번호 >= : NO	7	번호 >= :NO and 번호 < :NO2
4	번호 <= : NO	8	번호 > :NO and 번호 < :NO2

1~4번은 선택도 5%로 계산하고, 5~8번 같은 =조건이 아닌 부등호나 Between 같은 범위 기반 검색은 0.25%
로 계산한다.

 

조건절에 따른 바인드변수

SCOTT@orcl > create table t as
  2  select rownum no from dual connect by level <= 1000;

테이블이 생성되었습니다.

SCOTT@orcl > analyze table t compute statistics for table for all columns;

테이블이 분석되었습니다.

SCOTT@orcl > explain plan for
  2  select * from t where no <= :NO;

해석되었습니다.

SCOTT@orcl > select * from table
  2  (dbms_xplan.display(null,null,'basic rows'));

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |    50 |
|   1 |  TABLE ACCESS FULL| T    |    50 |
------------------------------------------
8 개의 행이 선택되었습니다.


SCOTT@orcl > explain plan for
  2  select * from t where no between :no1 and :no2;

해석되었습니다.



SCOTT@orcl > select * from table
  2  (dbms_xplan.display(null,null,'basic rows'));

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |
|   1 |  FILTER            |      |       |
|   2 |   TABLE ACCESS FULL| T    |     3 |
-------------------------------------------

9 개의 행이 선택되었습니다.

위의 테스트 결과로 바인드 변수는 정해진 기준에 따라 선택도를 구하고 전체 레코드 수를 곱하여 비용을 계산함을 알 수 있다.

 

바인드 변수가 아닌 상수 조건식

SCOTT@orcl > explain plan for select * from t where no <= 100;

해석되었습니다.

SCOTT@orcl > select * from table(dbms_xplan.display(null,null,'basic rows'));

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |    99 |
|   1 |  TABLE ACCESS FULL| T    |    99 |
------------------------------------------

8 개의 행이 선택되었습니다.

SCOTT@orcl > explain plan for select * from t where no between 500 and 600;

해석되었습니다.

SCOTT@orcl > select * from table(dbms_xplan.display(null,null,'basic rows'));

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |    99 |
|   1 |  TABLE ACCESS FULL| T    |    99 |
------------------------------------------

8 개의 행이 선택되었습니다.

바인드변수를 사용할 때는 정확한 컬럼 히스토그램에 근거하지 않고 카디널리티를 구하는 정해진 계산식을 기초해 비용을 계산하므로 최적이 아닌 실행계획을 수립 할 가능성이 높다. 또한 바인드 변수를 사용하면 파티션테이블을 쿼리 할때 파티션 레벨 통계 정보를 이용하지 못하게 되므로 가끔 악성 실행계획을 수립하는 경우 발생.

(1) 바인드 변수 Peeking

Peeking이란? 단어가 의미하듯이 SQL이 첫 번째 수행되면서 하드파싱될 때 함께 딸려 온 바인드 변수 값을 살짝 훔쳐보고, 그 값에 대한 컬럼 분포를 이용하여 실행계획을 결정하는 것

• 장점도 있는 반면 단점 또한 존재
  • 히스토그램으로 첫번째 최초 생성한 SQL에 관해서는 최적의 실행계획을 생성하지만, 캐시에 밀려나기 전까지는 기존의 실행계획을 사용한다.
  • 즉, 쿼리가 최초 수행시 분포도가 좋지 않은 컬럼인 경우 Full Scan을 수립하지만 반대인 경우 즉 분포도가 좋은 경우에도 실행계획이 캐시에서 밀려나기 전까지는 실행계획은 Full Scan하는 실행계획을 수립
  • 만약 해당 쿼리의 수행 빈도가 낮아 자주 캐시에 밀린다면 실행계획이 수시로 바뀔 수 있어 문제의 소지가 됨
• 오라클 버전별 문제점
  • 9i인 경우 dbms_stats(통계정보 수집할 때 사용) 패키지의 기본 설정으로 히스토그램을 생성하지않은체로 운영하기 때문에 Peeking이 큰문제 를 일으키지 않았다.
  • 10g에서는 dbms_stats 패키지의 기본 설정이 히스토그램 생성 여부를 오라클이 판단하기 때문에 더 많은 히스토그램이 생성됨으로 Peeking 바인드 변수에 대한 문제가 많이 발생

  

-이런저런 이유로 바인드 변수 Peeking 기능을 사용하지 않는다.

SQL> alter system set "_optim_peek_user_binds" = false;  <-- 비활성화
SQL> alter system set "_optim_peek_user_binds" = true;   <-- 활성화

(2) 적응적 커서 공유

적응적 커서 공유란? Peeking 기능의 문제를 해결하기 위해 11g에서 한번 더 개선된 기능이 나온것 입력된 바인드 변수 값의 분포에 따라 다른 실행계획이 사용되도록 하는 것이다.

• 적응적 커서 공유 기능을 사용하기 위해서는 먼저 조건 컬럼에 히스토그램이 생성돼 있어야 한다.
• 옵티마이저가 바인드 변수 값에 따라 실행계획을 달리 가져야 할 필요가 있다고 판단되는 SQL 커서에 대하여
  적응적 커서 기능을 활성화한다.
  • 이러한 조건을 만족하는 커서를 Bind Sensitive 커서
  • v$sql을 조회하면 is_Bind_sensitive 컬럼이 'Y'로 표시
  • is_Bind_sensitive 모드는 현재 SQL커서가 적응적 커서 공유 기능을 사용 할 수 있다라고 표시 할 뿐 Bind Sensitive 모드에서는 바인드 값에 따라 실행계획이 바뀌지 않는다.
• 실행계획이 바뀌기 위해서는 바인드 변수를 실행했을 때 이전에 비해 많은 일량을 처리하는 것으로 판단 되는 순간 해당 커서를 Bind Aware모드로 전환
  • Bind Aware모드 전환하게 되면 기존 커서는 사용 중지가 되고 새로운 커서가 생성되기 시작한다.
  • Bind Aware 모드에서 생성된 커서를 Bind Aware커서라고 부르며, v$sql에서 is_bind_aware 컬럼이 'Y'로 표시
  • 밀려난 기존 커서는 v$sql의 is_shareable 컬럼이 'N'으로 설정되며, 라이브러리 캐시 공간이 필요할 때 가장 먼저 밀려난다.
• 그렇다고 다른 값이 계속 입력 될 때 마다 계속 해서 새로운 커서가 생성되는 것은 아니고 선택도가 비슷한 것끼리 같은 커서를 공유하게 된다.
  .
  .

.
.

• 위의 예를 통해 분포도가 다른 값이 오더라도 한번은 기존의 커서를 실행해야 하는 단점이 발생

(3) 입력 값에 따라 SQL 분리

SELECT /*+ FULL(a) */ *
FROM 아파트매물 a
WHERE :CITY IN ('서울시', '경기도')
AND 도시 = :CITY
UNION ALL
SELECT /*+ INDEX(a idx01) */ *
FROM 아파트매물 a
WHERE :CITY NOT IN ('서울시', '경기도')
AND 도시 = :CITY;

• union all을 사용함으로 생기는 문제점
  • 라이브러리 캐쉬 효율 하락: 10개의 SQL을 결합하면 옵티마이저 또한 10개를 다 최적화해야함 그만큼 shared_pool에서 많은 공간이 낭비가 됨
  • Parse, Execute 단계의 CPU 사용률 을 높아져서 시스템부하: 파싱 단계에서 많은 시간을 소비하므로 cpu사용률이 파싱 단계에서 거의 소비가 된다. 실행분기 조건에서 제외되는 부분까지 실제 실행이 되므로 실행단계에서도 cpu사용률을 높인다.
  • 네트워크를 통한 메시지 전송량도 증가

IF :CITY IN ('서울시', '경기도') THEN
SELECT /*+ FULL(a) */ *
    FROM 아파트매물 a
   WHERE 도시 = :CITY;
ELSE
  SELECT /*+ INDEX(a idx01) */ *
    FROM 아파트매물 a
   WHERE 도시 = :CITY;
END IF;

• union all을 사용하지 않고 조건에 따라 분기하는 것이 바람직함

(4) 예외적으로, Literal 상수값 사용

바인드 변수를 사용하게 되면 cursor의 재활용 측면에서 좋기는 하지만 꼭 바인드 변수가 최적화라는 해법은 되지 못한다.

• 값이 얼마 없는 경우에는 옵티마이저가 Literal 값을 미리 알고 있기 때문에 더 좋은 실행계획을 생성 할 수 있음
• 파싱 소요시간이 쿼리 총 소요시간에서 차지하는 비중이 적은 배치 프로그램에서는 캐시 부하를 크게 염려할 필요가 없으므로 Literal 상수를 사용하는 것이 최적일 수 있다.

출처

http://wiki.gurubee.net/pages/viewpage.action?pageId=26741319 

1st_바인드 변수의 부작용과 해법 - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루