• 이 문서는 구루비에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4948370&
• 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

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

• 바인드 변수 사용 시 Sql의 수행 절차
  1. 최초수행시점에 최적화를 거친 실행계획이 캐시에 적재된다.
  2. 실행시점에 공유커서를 가져와 조건값만 다르게 바인딩 하면서 반복 재사용하게 된다.

• 바인드 변수 사용 시 문재점
  1. 평균 분포를 가정한 실행계획 수립
  2. 컬럼 분포가 균일할 경우 문제될것 없다.
  3. 컬럼 분포가 균일하지 않을 경우 실행계획은 바인딩되는 값에 따라 최적일수도 최악일수도 있다.
  4. 등치(=)조건이 아닌 부등호나 범위검색일 경우 고정된 규칙을 사용하여 실행계획을 수립한다.

     번호	바인딩 조건 형태	선택도(Selectivity)
     1	번호 > :no	5%
     2	번호 < :no	5%
     3	번호 >= :no	5%
     4	번호 <= :no	5%
     5	번호 BETWEEN :no1 AND :no2	0.25%
     6	번호 > :no1 AND 번호 <= :no2	0.25%
     7	번호 >= :no1 AND 번호 < :no2	0.25%
     8	번호 > :no1 AND 번호 < :no2	0.25%

• Cardinality : 비용계산시 특정 엑세스 단계를 거치고 출력될것으로 예상되는 건수
• Cardinality = 선택도(Selectivity) * 전체레코드수

• 바인딩 조건형태에 따른 카디널리티 예측 테스트

  SQL> CREATE TABLE t 2 AS 3 SELECT ROWNUM no 4 FROM dual 5 CONNECT BY LEVEL <= 1000 6 ; 테이블이 생성되었습니다. SQL> ANALYZE TABLE t COMPUTE STATISTICS FOR TABLE FOR ALL COLUMNS; 테이블이 분석되었습니다. SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE no <= :no; 해석되었습니다. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic rows')); Plan hash value: 1601196873 ------------------------------------------ | Id | Operation | Name | Rows | ------------------------------------------ | 0 | SELECT STATEMENT | | 50 | | 1 | TABLE ACCESS FULL| T | 50 | ------------------------------------------ 8 개의 행이 선택되었습니다. SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE no BETWEEN :no1 AND :no2; 해석되었습니다. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic rows')); Plan hash value: 1322348184 ------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | 3 | | 1 | FILTER | | | | 2 | TABLE ACCESS FULL| T | 3 | ------------------------------------------- 9 개의 행이 선택되었습니다.

• 검색 Row 수 예측 : 실제와 많이 다를 수 있다.
  1. 부등호 검색 카디널리티 = 1000 * 5 / 100 = 50
  2. 범 위 검색 카디널리티 = 1000 * 0.25 / 100 = 2.5 = 3

• 리터럴 상수 조건에 따른 카디널리티 예측 테스트 : 거의 정확한 로우수 예측

  SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE no <= 100; 해석되었습니다. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic rows')); Plan hash value: 1601196873 ------------------------------------------ | Id | Operation | Name | Rows | ------------------------------------------ | 0 | SELECT STATEMENT | | 99 | | 1 | TABLE ACCESS FULL| T | 99 | ------------------------------------------ 8 개의 행이 선택되었습니다. SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE no BETWEEN 500 AND 600; 해석되었습니다. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic rows')); Plan hash value: 1601196873 ------------------------------------------ | Id | Operation | Name | Rows | ------------------------------------------ | 0 | SELECT STATEMENT | | 99 | | 1 | TABLE ACCESS FULL| T | 99 | ------------------------------------------ 8 개의 행이 선택되었습니다.

• 바인드 변수를 사용하면 최적이 아닌 실행계획을 수립할 가능성이 높다.
  1. 바인드 변수를 사용할때 정확한 컬럼히스토그램에 근거하지 않고 정해진 계산식에 기초해 비용을 계산
  2. 파티션 테이블 쿼리 시 파티션 레벨 통계정보를 이용하지 못함, 테이블 레벨 통계정보 이용

(1) 바인드 변수 Peeking

• 바인드 변수 Peeking
  1. 바인드 변수의 부작용을 극복하기 위해 9i 부터 peeking 기능 도입
  2. Sql의 첫번째 수행시 입력된 바인딩값을 살짝 훔쳐보고 그값에 대한 분포도를 이용해 실행계획 수립
  3. SQL Server 의 Parameter Sniffing

• 바인드 변수 Peeking의 부작용
  • 예) 아파트매물과 같이 분포도가 고르지 못한 자료 조회
    
    SELECT * FROM 아파트매물 WHERE 도시 = :City;
  1. 최초 Sql 실행시 '서울'로 조회하면
     => 넓은 범위를 인덱스 스캔하는 것보다 풀스캔이 유리하므로 풀스캔으로 실행계획을 세움
     => 이후 어떤 값이 들어오든지 무조건 풀스캔으로 실행계획을 공유하게 됨
     => 제주도나 강원도는 인덱스 스캔을 통해 빠른 성능을 발휘할 수 있음에도 풀스캔을 하게 됨
  2. 최초 Sql 실행시 '강원도'로 조회하면
     => 인덱스 스캔으로 실행계획을 세움
     => 이후 어떤 값이 들어오든지 무조건 인덱스 스캔으로 실행계획을 공유하게 됨
     => 서울 조회시 인덱스 스캔으로 넓은 범위를 조회하게 되어 성능 저하 발생

• 10G에서의 Peeking
  1. 10G부터는 dbms_stats의 기본설정이 히스토그램을 설정할지 여부를 오라클이 판단하게끔 바뀌었다.
  2. 이전에 히스토그램이 있는지도 모르고 사용을 안해왔던 사용자들이 대부분
  3. 컬럼에 대한 히스토그램이 더 많이 생성되면서 Peeking의 부작용이 더 심각하게 나타나게 된다.
  4. 이로 인해 10g에서는 바인드 변수를 쓰지 말고 리터럴 상수로 회귀하자는 움직임도 나타남.
  5. Peeking 기능 비 활성화
     ALTER SYSTEM SET "_optim_peek_user_binds" = FALSE;
  6. EXPLAIN PLAN 을 통해 확인하는 사전 실행계획은 바인드 변수값이 주어지지 않은 상태에서의 평균분포도 실행계획
     실제 수행시 변수값이 바인딩 되면서 Peeking 기능으로 인해 확인했던 실행계획과 다른 실행계획으로 풀릴 수 있다.

(2) 적응적 커서 공유(Adaptive Cursor Sharing)

• 10g에서 Peeking의 부작을을 해소하기 위해 11g에서 도입된 기능

• 적응적 커서 공유 수행 매카니즘
  1. 기본적으로 이기능이 사용되려면 컬럼에 대한 히스토그램이 생성되어 있어야 한다.
  2. 옵티마이져가 바인드변수값에 따라 실행계획이 달라져야 한다고 판단
  3. 이러한 커서를 Bind Sensitive 커서라고 부른다.
  4. '서울시' 조회시 풀스캔 실행계획으로 커서 캐시(이를 1번커서라 칭하자)
  5. '제주도' 조회시 1번커서를 그대로 사용(비효율 발생)
  6. 이전에 비해 많은 일량을 처리한 것으로 판단
  7. 이커서의 모드를 Bind Aware 모드로 전환, 커서 공유 불가
  8. 다시 '제주도' 조회시 인덱스스캔용 새 커서를 만든다(2번 커서)
  9. 만약 이때 '제주도'가 아닌 '경기도'가 입력된다면
  10. 이때도 공유커서가 없으므로 새로운 실행계획 수립
  11. 다만 이때 만들어진 커서는 Bind Aware 모드로 있던 커서와 동일하므로
      그중 하나만 사용하고 나머지는 버린다.(같은 커서가 불필요하게 많이 만들어지는것을 방지)
  12. 결국, 입력되는 자료에 따라 그때 그때 적절한 커서를 공유하는 것은 아니다.
      비효율이 한번 발생한후 커서 공유 중단하고, 그 다음 실행시 다시 실행계획을 수립하는 방식

• 적응적 커서 공유 확인 뷰
  1. v$sql_cs_statistics
  2. v$sql_cs_histogram
  3. v$sql_cs_selectivity

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

• 바인드 변수의 부작용을 해소하기 위한 DBMS의 노력 VS 개발자의 노력
• Sql 분리 - Union all

  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 사용시 주의 사항
  1. 지나치게 긴 Sql은 오히려 라이브러리 캐시 효율을 떨어뜨린다.
  2. 10개의 Sql이 결합된 형태라면
     => 하드파싱 시점에 10개의 sql을 각각 최적화 해야 한다.
     => 그만큼 shared pool 공간 많이 차지, cpu에도 부하 발생
  3. 실행 단계에서도 CPU 사용률을 높인다.
     => 리턴된 결과집합이 0이고 IO가 없다 할지라도 sql이 아예 실행되지 않는 것은 아니다.

• Sql 분리 - 어플리케이션 단계

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

• Sql 분리의 단점
  1. 값의 종류가 늘거나 줄때 소스를 일일이 변경해 주어야 함

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

• Literal 상수값 사용
  1. 컬럼값의 종류가 적을 때
     => 하드 파싱의 부하가 미미
  2. 배치프로그램이나 DW, OLAP 등 정보계 시스템
     => 더 나은 실행계획을 수립
     => 쿼리 수행시간 자체가 워낙 길기 때문에 하드파싱 부하가 차지하는 비율 낮음
  3. OLTP에서는 바인드 변수 사용 권장
     => 사용빈도가 낮은 Sql의 경우 예외적으로 사용 가능성 검토

문서정보

• 이 문서는 구루비에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4948370&
• 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.