04.라이브러리 캐시 최적화 원리 - 05.바인드 변수의 중요성, 06.바인드 변수의 부작용과 해법

05.바인드 변수의 중요성

바인드 변수 사용에 따른 효과는 아주 분명하게 나타납니다. 커서를 많이 생성하지 않고 하나를 반복 재사용하므로 메모리 사용량과 파싱 소요시간을 줄여줍니다. 궁극적으로 시스템 전반의 메모리와 cpu 사용률을 낮춰 데이터베이스 성능과 확장성을 높이는데 기여하고, 특히 동시 사용자 접속이 많을 때는 그 영향력이 절대적입니다.
바인드 변수 사용 원칙을 잘 지키지 않으면 라이브러리 캐시 경합 때문에 시스템 정상가동이 어려운 상황에 직면할 수 있습니다. 그럴때 cursor_sharing 파라미터를 변경하는 것을 고려해 볼 수 있는데 이는 응급처방으로 사용해야지 절대 영구 적용할 목적으로 사용해서는 안됩니다.

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

바인드 변수를 사용하면 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재하고, 실행시점에는 그것을 그대로 가져와 값을 다르게 바인딩하면서 반복 재사용하게 됩니다. 여기서 변수를 바인딩하는 시점이 최적화 시점보다 나중인 실행시점이라는 사실을 아는 것이 중요합니다. 즉 SQL을 최적화하는 시점에 조건절 컬럼의 데이터 분포도를 활용하지 못하는 문제점을 갖습니다.(바인드 변수를 사용하면 통계정보를 사용하지 못한다고 흔히 말하는데 정확히 표현하면 컬럼 히스토그램 정보를 사용하지 못하는 것이고 그것을 제외한 다른 통계정보들은 충분히 활용할 수 있습니다)
따라서 바인드 변수를 사용할 때 옵티마이저는 평균 분포를 가정한 실행계획을 생성합니다. 컬럼 분포가 균일할 때는 문제될 것이 없지만 그렇지 않을 때는 실행시점에 바인딩 되는 값에 따라 최적이 아닌 실행계획일 수 있어 문제일 수 있습니다.
특히 등치(=)조건이 아닌 부등호나 between같은 범위 기반 검색조건일 때는 고정된 규칙을 사용하므로 더 부정확한 예측에 기반한 실행계획이 만들어집니다. 좀 더 구체적으로 말하자면 1~4번은 선택도를 5%로 계산하고 5~8번까지는 0.25로 계산합니다.
1.번호 > :NO
2.번호 < :NO
3. 번호 >= :NO
4. 번호 <= :NO
5. 번호 between :NO1 and :NO2
6.번호 > :NO1 and 번호 <= :NO2
7.번호 >= :NO1 and 번호 < :NO2
8. 번호 > :NO1 and 번호 < :NO2
비용계산에 기초가 되는 카디널리티는 전체 레코드수에 선택도를 곱해서 구합니다.
따라서 테이블에 1000개의 로우가 있을 때 옵티마이저는 1~4번과 같은 조건절에 대해서는 50개의 로우가 출력될 것으로 예상하고, 5~8번과 같은 조건절에 대해서는 3개 로우가 출력될 것으로 예상합니다.
사용자가 실제 입력한 값에 따라 전혀 결과가 달라질 수 있지만 옵티마이저로서는 어쩔수 없는 선택을 한것으로 볼 수 있습니다.
이처럼 바인드 변수를 사용할 때는 정확한 컬럼 히스토그램에 근거하지 않고 카디널리티를 구하는 정해진 계산식에 기초해 비용을 계산하므로 최적이 아닌 실행계획을 수립할 가능성이 높습니다.
바인드 변수를 사용하면 컬럼 히스토그램을 제대로 활용하지 못할 뿐 아니라 파티션 테이블을 쿼리할 때 파티션 레벨 통계정보를 이용하지 못하게 되는 것도 바인드 변수의 대표적인 부작용입니다. 파티션 레벨 통계보다 다소 부정확한 테이블 레벨 통계를 이용함으로써 옵티마이저가 가끔 악성 실행계획을 수립합니다.

(1)바인드 변수 Peeking
바인드 변수의 부작용을 극복하려고 오라클은 9i부터 바인드 변수 Peeking기능을 도입하였습니다. 이 기능은 SQL이 첫 번째 수행되면서 하드파싱될 때 함께 딸려온 바인드 변수 값을 살짝 훔쳐보고, 그 값에 대한 컬럼 분포를 이용해 실행계획을 결정하는 것입니다.(다른 dbms도 같은 기능을 제공합니다)
이것은 위험할 수 있습니다. 어제까지 잘 돌던 프로그램이 어느날 갑자기 느려지는 현상이 발생할 수 있습니다.
10g부터 dbms_Stats의 기본설정이, 히스토그램을 생정할지 여부를 오라클이 판단하는 쪽으로 바뀌었습니다. 이전에는 히스토그램을 전혀 생성하지 않거나 몇몇개만 생성하던 것이 10g부터 더 많은 컬럼에 히스토그램이 생성됨으로 말미암아 바인드 변수 peeking에 의한 폐해가 더 도드라져 보이게 되었습니다.
시스템 운영자 입장에서는 자주 실행계획이 바뀌어 어제와 오늘의 수행속도가 급격히 달라지는 현상을 민감하게 받아들이고 차라리 항상 느린 애플리케이션을 튜닝하자는 마인드가 더 낫다고 생각할지도 모릅니다. 그래서 대부분 운영시스템에는 아래처럼 이 기능을 비활성화시킨 상태에서 운영중입니다.
alter system set “_optim_peek_user_bings”=FALSE;

(2)적응적 커서 공유
11g에서 나온 기능인데 입력된 바인드 변수 값의 분포에 따라 다른 실행계획이 사용되도록 하는 것입니다.
오라클은 적응적 커서 공유와 관련한 수행 통계를 관찰할 수 있도록 세가지 뷰를 제공합니다.
v$sql_cs_statistics
v$sql_cs_histogram
v$sql_cs_selectivity
이 기능이 작동하려면 기본적으로 조건절 컬럼에 히스토그램이 생성돼 있어야 합니다. 그 상태에서 옵티마이저가 바인드 변수 값에 따라 실행계획을 달리 가져갈 필요가 있다고 판단되는 SQL커서에 대해 이 기능을 활성화합니다. 그런 커서를 bind sensitive 커서라고 부르며 v$sql을 조회해보면 is_bind_sensitive컬럼이 Y로 표시되어 있습니다.
하지만 bind sensitibe모드에서는 아직 바인드 값에 따라 실행계획이 바뀌지는 않으며 우선 bind aware모드로의 전환이 필요합니다. 오라클은 bind sensitive커서에 대해 내부적으로 별도의 히스토그램과 수행 통계를 관리하며, 특정 값으로 실행했을 때 이전에 비해 많은 일량을 처리한 것으로 판단되는 순간 해당 커서를 bind aware모드로 전환합니다. 이때부터 기존커서는 사용이 중지되고 새로운 커서가 생성되기 시작합니다. 커서가 바인드 값별로 따로 만들어지는 것은 아니며, 선택도가 비슷한 것끼리는 같은 커서를 공유합니다. bind aware모드에서 생성된 커서를 bind aware라고 부르며 v$sql에서 is_bind_aware컬럼이 Y로 표시됩니다.

(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

여기서도 주의사항이 있는데 oltp시스템에서 union all을 이용해 SQL을 지나치게 길게 작성하면 오히려 라이브러리 캐시 효율을 떨어뜨리게 된다는 사실입니다. union all을 사용해 10개의 sql을 결합했다고 가정하겠습니다. 그러면 하드파싱 시점에 옵티마이저는 10개의 sql을 최적화해야 합니다. 그만큼 shared pool에서 많은 공간을 차지하게 되고, 10개가 골고루 사용된다면 모르지만 그 중 한 두개만 사용된다면 나머지는 불필요하게 공간만 낭비하는 결과를 초래합니다. 더욱이 매번 수행할 때마다 긴 텍스트를 파싱하면서 syntax를 체크하고 파싱트리를 만들어 semantic체크하는 것을 반복한다고 생각한다면 parse단계에서 cpu를 과도하게 소비할 것임은 분명하게 되는 것입니다.
따라서 배치 프로그램이나 DSS 시스템이라면 상관없겠지만 oltp시스템이라면 union all을 이용하는 것보다 아래처럼 애플리케이션 단에서 조건에 따라 SQL을 분기하는것이 바람직합니다.

(4)예외적으로, literal상수값 사용
조건절 컬럼의 값 종류가 소수일 때는 바인드 변수보다 오히려 literal상수를 사용하는게 나은 선택일 수 있습니다.
왜냐하면 입력 값 종류가 몇 개에 불과하다면 하드파싱 부하가 미미할 테고, literal상수를 사용함으로써 옵티마이저가 더 나은 선택을 할 가능성이 커지기 때문입니다.