alter session set cursor_sharing를 왜 사용하는 걸까?
SAP PO에서 JDBC Receiver채널을 사용할때 바인딩, 배치모드를 사용하지 않는 이상 리터널(Literal)SQL로 실행하는걸로 알고 있습니다. 리터널 SQL은 WHERE절에 비교되는 상수값을 하드코딩해서 사용하는 것을 말하며 그 반대로 바인딩 모드가 있는데 WHERE의 조건에 :A 형태로 값들이 바인딩 되는 변수를 이야기합니다. 예를 들면 일반적으로 사용하는 쿼리 select * from dual where id = 1 와 다르게 id = 1을 id = ? 로 사용하여 변하는 조건만 바인드변수에 치환만 하여 사용하는 것입니다. 자바에서는 Statement와 PreparedStatement로 비교가 되겠네요.
현상&원인
일반적으로 사용되는 리터널SQL이지만 사용빈도가 높은경우 하드파싱(Hard Parsing)으로 인한 성능저하가 일어날수 있다고 합니다.
파싱종류에는 하드파싱과 소프트파싱(Soft Parsing)으로 나눠지는데 하드파싱은 처음 실행되는 SQL에 Shared Pool과 딕셔너리 캐시에 새로 파싱을 하면서 문법이나 테이블, 컬럼, 권한 등 체크를 한다고 합니다.
소프트파싱은 실행하려고 하는 SQL문이 이미 Shared Pool과 캐시에 있으므로 관련정보(파싱트리, 실행계획) 자원을 그대로 재사용할수 있다고 하며 하드보다 소프트파싱이 속도가 빠르다고 합니다.
하드파싱이 되는 대상이 되는 SQL은 고백, 라인이 다른경우 동일한 WHERE절의 동일한 값이지만 대소문자가 다른경우, 주석, 오너, 힌트 등이 있을것 같습니다.
이런쿼리가 많아 진다면 Shared Pool에 할당되면서 공간이 부족해져 'ORA-04031 Shared Pool 공간이 부족...' 이런에러가 발생될수 있다고 하며 하드파싱이 되는 리터널SQL은 오라클에서는 체크를 사전에 문제점을 잡는게 좋을것 같습니다.
해결책
이렇게 리터널SQL로 대량으로 처리되는 경우에는 바인드 변수를 사용하는걸 권장하며 이걸 사용할수 없는 경우에는 오라클은 CURSOR_SHARING파라메터, MS-SQL은 AUTO-PARAMETERIZATION이 조절로 활성화된다고 합니다.
바인딩 변수를 사용하는것보다 성능은 좋지 않지만 리터널 SQL문을 사용하는것보다는 성능향상이 있다는게 검증되었다고 합니다. CURSOR_SHARING의 설정값은 FORCE와 SIMILAR이 있으며 옵션을 사용하게 되면 오라클에서 임의로 바인드 변수를 사용하게 만들게 해준다고 하는데 부작용도 있는것 같습니다.
순간에 실행계획으로 인한 부하나 SQL에서 의도적으로 사용한 상수까지 전부다 변수로 변경될수 있는 문제가 있었다고 하는데 이런내용들은 예전 버젼이 낮은 오라클9i는 PEEKING기능, 오라클 11g에서는 Adaptive Cursor Sharing이라는 기능이 도입됐다고 하는데 그래도 바인드변수를 사용하는걸 권장하는것 같습니다. 오라클12C에서는 EXACT(기본값)나 FORCE를 사용가능한데 SIMILAR은 Deprecated되었다고 합니다.
결론
SAP PO와 연계하는 오라클 DB에서 'CURSOR_SHARING'을 사용하는 경우 쿼리를 실행하기전 'alter session set cursor_sharing = force 이나 similar'을 사용하면 될것 같습니다. 그전에 오라클 버젼에 따라 이 옵션이 주는 영향을 확인해 봐야겠죠? 실제 적용 후에 해당인터페이스에 대한 모니터링이나 타겟 DB에 대한 모니터링은 필요한것 같습니다.
출처:
Literal SQL & Bind Variable SQL(Soft Parsing/Hard Parsing), 소프트파싱, 하드파싱...
SQL 파싱처리과정과 성능 향상 포인트 최적화!(하드파싱/소프트파싱 차이 SQL쿼리 파싱 최적화 과정, 바인드 변수 사용)
댓글 없음:
댓글 쓰기