Oracle Cursors 는 library cache (shared SQL area) 에 할당된 memory 공간으로 LRU 알고리즘에 의해서 관리된다.
< Cursor 정보 >
– 구문 분석된 명령문 ( 정적 , 동적 및 순환 SQL, 프로시저, 데이터베이스 트리거 등 의 프로그램 단위) : P-Code
– Execution-Plan
– 참조 객체 목록 (원본 TEXT)
————– Session cached cursors Hit Ratio ————
select round((hit.value/tot.value)*100, 5) session_cache_hit_ratio
from v$sysstat tot
, v$sysstat hit
, v$sysstat cnt
where tot.name = ‘parse count (total)’
and hit.name = ‘session cursor cache hits’
and cnt.name = ‘session cursor cache count’ ;
session_cache_hit_ratio 는 최소 50% 이상 이어야 합니다.
select gethitratio, pinhitratio
from v$librarycache
where namespace=’SQL AREA’ ;
gethitratio, pinhitratio > 90% 이어야 하고, sum(pins) / sum(reloads) <= 1% 이어야 한다.
————– Session cached cursors Hit Ratio ————
1. open_cursors
: 한 세션이 열 수 있는 최대 cursor 개수
2. session_cached_cursors
: 열려있는 세션이 가질 수 있는 최대 Cursors 개수
SESSION_CACHED_CURSORS 파라미터는 동일한 SQL을 반복수행(3회이상) 하는 경우에 유리하며, 보통 softer parse 라고 한다.
모듈별로 특정 SQL 들을 반복 수행하는 세션들에 설정시 SOFT 파싱부하를 감소 시켜 준다.
시스템 이 내부 수행하는 ReCursive SQL 도 포함되므로 최소 30 이하로 설정하는것은 효과가 없으며 보 통 50 이상을 권장한다.
동일한 SQL이 동일세션에서 3회 이상 수행 시 PGA 에 해당 SQL 의 Handle Address 를 Caching 하게 되며, Caching 정보를 토대로 해당 Bucket의 모든 Handle을 모두 검색하지 않고,
Caching 되어 있는 Handle Address를 가지고 해당 Handle 의 LCO에 Direct 하게 탐색을 하게 되어 일반적인 Soft Parsing 보다 개선의 효과가 더 크다.
단, parse count 가 자체가 줄어드는 것은 아니며, 탐색 하는 시간 즉 parsing time 이 절감하는 효과가 있다.
Session_cached_cursors 의 설정은 PGA 에 해당 세션의 SQL(3회 이상 수행)을 Caching 하는 것으로, Library cache object 를 pinned 하지 않기 때문에 Soft Parsing 이 발생하게 되는 것이다.
반면에 PL/SQL에서 사용되는 Hold Cursor(Static SQL) 의 경우에는 library cache object 를 Pinned 한 상태에서 반복 수행되므로, soft parsing 이 발생 하지 않게 되는 것이다.
3. cursor_space_for_time = true[false]
: 세션에서 사용된 Cursor를 세션이 닫힐 때까지 SGA에 남겨놓는다.
4. cursor_shaing = [ EXACT, FORCE, SIMILAR ]
: cursor_sharing(FORCE, SIMILAR) 을 설정 시 처음 수행되는 literal value를 bind value로 대체를 하게 되는데, 해당 cursor가 Memory 에서 Aging out 되지 않을 경우 이후 수행되는 literal value 에 대해서는 peek at the bind 로 수행되게 된다.
peek at the bind로 해석되는 것은 “_optim_peek_user_bind”=TRUE로 해석되는 것과 동일하게 해 석되는것이다.
위와 같이 해석될 경우 system level 에서 cursor_sharing 를 설정하는 것은 상당히 위험해 질 수 있으므로, 해당 파라미터 세팅 (FORCE,SIMILAR) 시에는 필히 SESSION LEVEL OR SQL LEVEL 에서 제어를 할 필요가 있음.
부가적으로, cursor_sharing = force 로 설정할 경우에는 rownum 사용에 주의를 하여야 한다.
rownum = 1 과 같이 프로그램 작성시 cursor_sharing=force를 설정할 경우 rownum = :b1 과 같이 Oracle 내부적으로 변경되므로 전체범위 처리 후에 해당 1건의 row 를 추출하게 된다.
이 때 에는 rownum <= 1 과 같이 변경 후 cursor_sharing=force 를 설정하여야 한다.