꿈꾸는 개발자, DBA 커뮤니티 구루비

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

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

바인드 변수를 사용하면 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재하고, 실행시점에는 그것을 그대로 가져와 값을 다르게 바인딩하면서 반복 재사용하게된다.

즉, 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

등치(=) 조건이 아닌 부등호나 Between 같은 범위 기반 검색조건일 때는 고정된 규칙을 사용하므로 더 부정확한 예측에 기반한 실행계획이 만들어진다.
위의 1~4번은 선택도 5%로 계산, 5~8번까지는 -.25%로 계산


카디널리티 = 선택도 + 전체 레코드 수

조건절에 따른 바인드변수 테스트



4, 5번으로 테스트

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

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

SQL> analyze table t compute statistics for table for all columns;

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

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

해석되었습니다.

SQL> 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 |
------------------------------------------

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

해석되었습니다.


SQL> 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 |
-------------------------------------------

SQL> explain plan for
  2  select * from t where no <= 100;

해석되었습니다.


-상수 조건식 ( no <=100, no between 500 and 600)

SQL> select * from table

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

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

SQL> explain plan for
  2  select * from t where no between 500 and 600;

해석되었습니다.

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

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

위 테스트 결과로
바인드 변수는 정해진 계산식으로 비용을 계산.
파티션테이블을 쿼리할 땐 파티션 레벨 통계정보를 이용하지못함.
파티션보다 부정확한 테이블 레벨 통계를 이용해 악성 실행계획을 수립,

바인드 변수 Peeking

오라클 9i부터 바인드 변수의 부작용을 극복하려고 도입.
SQL이 하드파싱될때 바인드 변수값을 훔쳐보고 그값에 대한 실행계획 결정.

하단과 같은 선택도가 높은 데이터를 인덱스를 경유해 액세스할 ?면 성능이 더 느려진다.
예시로 들면 , 제주도로 검색하여서 , 실행계획을 Range Scan으로 판단하게된다.(몇건이되지않기에 빠르게 나올것이다)
다음에 조회시 서울로 조회하였을때 Range Scan이라면 많은데이타이므로 기존 Full Scan(원래대로라면) 을 하였을 때보다 성능저하는 느낄것이다.

9i

1) dbms_stats 패키지의 기본 설정으로 히스토그램을 생성하지않은체로 운영

10g

1) dbms_stats 패키지의 기본 설정으로 히스토그램을 생성 여부를 오라클에서 판단

위와 같은 내용으로 히스토그램에 대한 정보를 좀더 많은 컬럼에 대해서 생성하므로 Peeking 바인드 변수에 대한 문제가 많이 발생.


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

시스템이 변경되었습니다.


SQL> select * from v$ses_optimizer_env
  2  where sid =userenv('sid')
  3  and name like '_optim_peek%';

       SID         ID NAME                                     ISD VALUE
---------- ---------- ---------------------------------------- --- -------------------------
       146         98 _optim_peek_user_binds                   NO  false

적응적 커서 공유

오라클에서 10g Peeking 기능의 문제를 해결하기위해 11g에서 나온기능이다.
입력된 바인드 변수 값의 분포에 따라 다른 실행계획이 사용되도록 하는것이다.

적응적 커서 공유 수행 통계를 관찰할수있는 뷰

v$sql_cs_statistics
v$sql_cs_histogram
v$sql_cs_selectivity

이 기능을 작동하려면 기본적으로 조건절 컬럼에 히스토그램이 생성돼 있어야 한다.
그 상타에서, 옵티마이저가 바인드 변수 값에 따라 실행계획을 달리 가져갈 필요가있다고 판단되는 SQL 커서에 대해서 이 기능을 활성화된다.
그런 커서를 'Bind Sensitive 커서' 라고 부르며, v$sql을 조회해 보면 is_bind_sensitive 컬럼이 'Y' 로 표시돼 있다.
이것은 아직 바인드 값에 따라 실행 계획이 바뀌지 않는다. 그냥 마킹 했다고 보면된다.
그러면 오라클은 이커서에 대해 내부적으로 별도의 히스토그램과 수행 통계를 관리하며, 특정 값으로 실행 했을 때
이전에 비해 많은 일량을 처리 한것으로 판단 되는 순간 해당 커서를 Bind Aware 모드로 전환한다.
이때부터 기존 커서는 사용이 중지 되고, 새로운 커서가 생성되는 것이다.
커서는 값별로 생성되지않고 선택도가 비슷한 것 끼리 커서를 공유하는 방식이다.

위 그림으로 보면

1) 일단 처음에 실행되는 쿼리에서 분포도가 좋은 서울시로 조회하여 Fuul Scan하는 실행계획을 수립힌다.(어떠한값이 들어와도)
2) 처음에 입력되었던 서울시가 아니라 제주도로 입력되면 컬럼히스토그램을 확인후 새로운 실행계획을 만든다. 이때 Bind Aware모드로 전환.
3) 서울시와 비슷한 분포도를 가진 값이 입력되면 비슷한 서울시의 커서를 사용한다. 서울시를 입력하면 기존(실행계획)을 버리고 새로운 실행계획을 만든다.
- v$sql 에서 is_sharedable 컬럼이 N으로 설정되고, 라이브러리 캐시에 공간이 필요할 때는 가장 먼저 밀러남.
4) 제주도와 비슷한 분포도를 가진 값이 입력되면 비슷한 강원도의 커서를 사용한다.

is_bind_sensitive : http://hoonsik69.tistory.com/277

단점은 성능이 분포도가 좋은 검색조건을 준다고해도 분포도가 높은 쿼리를 선행으로 읽어버린다는 단점이 존재한다.
말그대로 옵티마이저가 스스로 판단하는 자동시스템이지만, 문제가될여지는 충분하다.

입력 값에 따라 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;

sql을 분리하기위해 union all 을 사용하여 생긴문제

1) 라이브러리 캐쉬 효율 하락
2) Parse, Execute 단계의 CPU 사용률 을 높아져서 시스템부하
3) 네트워크를 통한 메시지 전송량도 증가

OLTP 시스템에서 개발자들은 DBMS을 도우기 위한 SQl작성법


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

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

부등호나 Between 같은 범위 검색조건일때
배치프로그램이나 DW, OLAP등 정보시스템
OLTP성 애플리케이션(그래도 왠만해서는 바인드변수를 사용권장)
조건절 컬럼의 값 종류가 소수 일때