Oracle11g Adaptive Cursor Sharing

SQL 실행계획의 공유와 재사용에 관하여 Oracle11g에서 새로운 기능이 나왔다.
Adaptive Cursor Sharing 이란 기능이 새로 나왔는데 특별히 사용자가 이기능을 쓰기위하여 설정해야 되는
값들이 있는것도 아니고 자동으로 기능이 사용된다고 보면 된다.
이 기능이 하는 역활에 대해 알아보자.

우선 커서공유를 위해 설정된 파라메터 값을 확인해보니... EXACT 로 설정되어 있다.

cursor_sharing의 값에는 3개의 값이 있다.

EXACT : 기본 값으로서 서로 완전히 일치하는 SQL문장에 대해서만 커서가 공유되는 기본적인 특성을 그대로 유지하게 된다. 이상태에서는 동일하게 작성된 SQL 문장들만이 공유될 것이고, 더 많은 SQL들이 공유되도록 하기 위해서는 사용자가 SQL 문장을 작성할때 바인드 변수를 사용해야 한다.

SIMILAR : SQL 문장에 사용된 리터럴 값이 다른 값으로 대체되더라도 SQL의 의미 자체에 아무 변화가 없고 실행 계획에 대한 최적화 내용에 변화가 없다면, 사용된 리터럴 값을 Oracle이 자동으로 생성한 바인드 변수로 대체하여 공유할 수 있는 상태로 실행하게 된다.

FORCE : SQL문장에 사용된 리터럴 값이 다른 값으로 대체되는 경우 SQL의 의미 자체에 아무 변화가 없다면 사용된 리터럴 값을 Oracle이 자동으로 생성한 바인드 변수로 대체하여 공유할 수 있는 상태로 실행하게 된다.

 

Adaptive Cursor Sharing

만약 emp테이블에 부서번호 10번인 사원이 12명이 있고 부서번호 30번인 사원이 1000명이 있고 인덱스가 있따고 가정하자.
이럴경우 최초 실행한 SQL에서 바인드변수를 써서 부서번호 10으로 조회를 하게 되면 index_range_scan을 하게 될것이다. 그리고 커서가 공유하게 됨으로 다음번에 부서번호 30으로 조회를 하게 되면 1000건인데도 불구하고 full_scan을 하지 않고 index_range_scan을 할것이다. 이러한 오차가 더욱 커질수록 문제가 야기된다. 반대도 마찬가지 경우가 생기게 된다.
이러한 바인드 변수에 의한 Cursor Sharing 문제점을 해결한게 바로 Adaptive Cursor Sharing이다.
v$SQL 뷰의 새로 생긴 IS_BIND_SENSITIVE 컬럼이 'Y'이라는 것은 옵티마이저가 해당 SQL 문장의 최적화된 실행 계획은 바인드 변수의 값에 따라 달라질 수 있다는 것을 알고 있음을 의미한다.
'N'으로 되어 있는것은 현재 Child Cursor가 바인드 변수의 값을 확인하여 새롭게 실행계획을 생성하지는 않았다는 것을 의미한다.

즉 최초 부서번호 10으로 조회하여 실행계획이 세워질때 v$SQL의 buffer_gets의 수와 두번째 부서번호 30으로 해서 조회했을때 buffer_gets의 수가 갑자기 차이가 많이 나게 되면 옵티마이저가 자동으로 실행계획을 하나더 새성 시킨다.

이렇게 Oracle11g 에서는 바인드 변수를 사용하여 수행되는 SQL에 대해, 바인드 변수의 값과  SQL의 수행 일량을 모니터링하고 있다가, 기존에 파악된 실행 계획을 사용했음에도 일량이 기존의 것에 비해 급격한 차이를 보이는것이 감지되면 해당 SQL의 실행 계획을 바인드 변수의 값에 따라 재 생성하여, 데이터의 분포도에 따른 성능 변화를 막고 항상 일정한 시스템 성능을 발휘할 수 있도록 지원한다.
이것을  Adaptive Cursor Sharing이라고 한다.