[4] 적응적 커서 공유

• Adaptive Cursor Sharing
• 10g에서 Peeking의 부작을을 해소하기 위해 11g에서 도입된 기능이다.

(1) 적응적 커서 공유 수행 매커니즘

• 기본적으로 이기능이 사용되려면 컬럼에 대한 히스토그램이 생성되어 있어야 한다.
• 옵티마이져가 바인드변수값에 따라 실행계획이 달라져야 한다는 가정을 두고 Bind Sensitive한 커서를 실현한다.

[예제로 보는 적응적 커서 공유 수행 매커니즘]

1. '서울시' 조회시 풀스캔 실행계획으로 커서를 캐싱하고 이를 1번커서라 칭한다.
2. '제주도' 조회시 1번커서를 그대로 사용하면 비효율이 발생한다.
3. 이번엔 커서의 모드를 Bind Aware 모드로 전환한다. 이 때 커서 공유는 불가능해진다.
4. 다시 '제주도' 조회시 인덱스 스캔용 새 커서를 만든다. 이를 2번 커서라 칭한다.
5. 만약 이때 '제주도'가 아닌 '경기도'가 입력된다면 이 때도 공유커서가 없으므로 새로운 실행계획이 수립된다.
6. 이 때 만들어진 커서는 Bind Aware 모드로 있던 커서와 동일하다.
   즉 , 같은 커서가 불필요하게 많이 만들어지는것을 방지하기 위해 그 중 하나만 사용하고 나머지는 버린다.
   또한 , 입력되는 자료에 따라 그때 그때 적절한 커서를 공유하는 것은 아니다.
   👉 비효율이 한번 발생한후 커서 공유를 중단하고, 그 다음 실행시 다시 실행계획을 수립하는 방식이다.

(2) 적응적 커서 공유를 확인할 수 있는 뷰

• v$sql_cs_statistics
• v$sql_cs_histogram
• v$sql_cs_selectivity

[5] 입력값에 따라 SQL 분리하기

• 바인드 변수의 부작용을 해소하기 위한 DBMS의 노력 VS 개발자의 노력
• Sql 분리의 단점 : 값의 종류가 늘거나 줄때 소스를 일일이 변경해 주어야 한다.

(1) 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 사용시 주의 사항

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

(2) Sql 분리 - 어플리케이션 단계

[예시]

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

[6] ⭐️ 예외적으로, Literal 상수값 사용하는 경우 ⭐️

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