* 아래 테스트는 oracle11g R2에서 진행했음을 밝힙니다.

 

Result Cache

 

1. 개요
Oracle11g의 성능 분야의 신 기능으로서 SQL query 또는 PL/SQL function의 결과를 메모리 내에 cache할 수 있는 기능.
결과가 cache 되어 있는 query를 수행하거나 PL/SQL function을 호출하면 해당 query 또는 function을 실행하는 대신
cache로부터 바로 결과를 호출.
- 메모리를 조금 더 사용하는 대신 응답 시간의 현저한 향상을 기대.

Result cache 기능 분류
SQL Query Result Cache
- Server-side
- Client-side

PL/SQL Function Result Cache
- Server-side

 

서버 단의 result caching과 클라이언트 단의 result caching은 어느 정도의 관련은 있지만 서로 독립적인 기능.

 

2. SQL Query Result Cache의 개념
Query Result
통상의 SQL query는 물론 특정 시점에 대한 flashback query를 수행하였을 때에도 그 결과가 cache 될 수 있다.
- query 결과가 read-consistent한 snapshot이라면 cache 대상.

 

result caching의 bypass가 발생하는 경우
- 특정 테이블에 대한 변경과 조회가 하나의 transaction에서 발생하는 경우 그 조회 결과는 read-consistent한
snapshot이 아니므로 result caching으로부터 제외.

 

cache 되는 단위에 어느 정도의 융통성이 있다. top level query 외에도 view 또는 inline view 형태의
query block에 대해서는 그 결과가 단독으로 cache되는 것.

 

두 가지 주의 사항.(동전의 양면)
- subquery의 결과는 단독으로 cache되지 않는다.
- View/inline view에 대한 optimization이 disable된다.

 

둘 이상의 query block이 있는 SQL에 대해서 optimizing의 기본적인 원칙은 전체를 하나로 다룬다는 것.
- Sub-query un-nesting, view merging, predicate pushing 등의 optimization 기법.

 

query block 단위로 result caching을 하기 위해서는 그러한 optimization이 희생되어야 한다.
따라서 result cache 기능이 sub-query 단위로 적용되지 않는 것을 꼭 단점이라고는 볼 수 없을 것이다.

 

3. Cache(Result cache memory는 shared pool의 component)
Default sizing(메모리 관리 정책에 따라 기본값으로 shared pool 내에 할당.)

MEMORY_TARGET 사용 시: MEMORY_TARGET의 0.25%
SGA_TARGET 사용 시: SGA_TARGET의 0.5%
SHARED_POOL_SIZE 사용 시: SHARED_POOL_SIZE의 1%

 

RESULT_CACHE_MAX_SIZE
result cache의 크기를 명시적으로 지정하고자 한다면 이 parameter를 사용.
- 최소값은 0(result caching이 disable)
- 최대값은 shared pool 크기의 75%.

 

이 parameter는 원래 동적으로 변경이 가능하지만, 일단 0으로 설정하여 instance를 시작한 후에는 alter system 명령에 의해 수정 불가.
- result caching을 다시 enable하기 위해서는 instance restart가 필요.

현 버전에서 result cache 크기에 대한 advisory 기능 없음.

shared pool advisory 기능이 result cache 사이즈에 대한 권고를 제시하지 않음.

 

RESULT_CACHE_MAX_RESULT
result cache 자체의 sizing을 정하는 것은 아님.
전체 result cache memory 내에서 하나의 result가 차지할 수 있는 메모리의 최대 크기를 result cache memory 전체

크기에 대한 %로 표시.
- 디폴트 값: 5%

 

4. SQL Query Result Cache의 동작
Cache-in(이를 결정하는 1차적인 요소는 시스템/세션 레벨 parameter인 RESULT_CACHE_MODE의 설정.)
MANUAL: result caching를 원하는 SQL 마다 개별적으로 /*+ result_cache */ 힌트를 사용.(default)
FORCE: 모든 SQL이 일괄적으로 result caching의 대상. /*+ no_result_cache */ 힌트를 사용.
- 특정 SQL에 대해서는 result caching을 하지 않도록 설정.

 

result caching의 대상이 되는 SQL이 실행된다면, cache-in 발생 두 조건.
- 현재 그 결과가 cache되어 있지 않았을 때
- 결과의 크기가 RESULT_CACHE_MAX_RESULT 이하 일때

 

제약 사항(query들은 result caching이 미적용되며, /*+ result_cache */ 힌트 무시.)
- Dictionary 및 temporary 테이블에 대한 query
- 시퀀스의 CURRVAL/NEXTVAL에 대한 query
- current_date, current_timestamp, local_timestamp, userenv/sys_context(with non-constant variables),
sys_guid, sysdate, sys_timestamp 등의 함수 호출이 포함된 query
- Non-deterministic PL/SQL 함수를 호출하는 query

 

Cache-hit
- 실제로 실행하였을 때와 동일한 결과를 result cache로부터 얻었음을 의미.
- 동일한 SQL이 수행되어야 하며, parameter도 동일.

 

parameter란 무엇인가?
Query result는 SQL 문장 내에서 사용되는 다음과 같은 요소들에 의해 parameter 화 된다.
- Bind 변수
- dbtimezone, sessiontimezone, userenv/sys_context(with constant variables),uid, user 등의 보다 정적인 함수
- 호출 결과
- NLS 등의 환경 parameter
(SQL 문장 뿐만 아니라 parameter들이 모두 일치할 때 cache-hit이 발생.)

 

Note.
RAC 환경에서 result cache의 cache-hit은 어디까지나 local event이다.
각 instance의 result cache memory는 각자의 내용을 담고 있을 뿐이다.
Result cache에 대해서는 통상의 cache fusion과 같은 기능이 제공되지 않는다고도 볼 수 있을 것이다.

 

Cache-out
Cache된 result는 다음의 경우에 result cache로부터 "out"된다.
- Age-out. Result cache 역시 LRU cache이기 때문.
- Invalidation.(해당 query가 참조하는 object에 DML 등의 변경이 일어나는 경우.)

 

Note.
RAC 환경에서 result cache의 cache-hit은 local event, invalidation은 global event.
read consistency의 문제이기 때문이다, 어떤 의미에서 부분적인 cache fusion이 제공된다고도 볼 수 있겠다.

 

5. SQL Query Result Cache의 모니터링

DBMS_RESULT_CACHE Package
result cache에 대한 정보나 통계는 물론 각종 관리 작업도 수행할 수 있도록 하는 다양한 프로시저들을 제공.

RESULT_CACHE Views
- (G)V$RESULT_CACHE_STATISTICS
- (G)V$RESULT_CACHE_MEMORY
- (G)V$RESULT_CACHE_OBJECTS
- (G)V$RESULT_CACHE_DEPENDENCY

 

6. SQL Query Result Cache 사용 예
환경 점검 및 초기화
관련 parameter 값들을 점검하고, result cache를 초기화한 후 그 내역 확인.
Result cache의 초기화와 그 현황 파악은 DBMS_RESULT_CACHE 패키지를 사용.

 

- sys user로 접속
conn / as sysdba
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 150
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 1000
SET SERVEROUTPUT ON
- parameter 조회
show parameter result_cache_mode
show parameter memory_target
show parameter result_cache_max_result
- result cache를 flush
execute dbms_result_cache.flush;
- shared_pool을 flush
alter system flush shared_pool;
- result cache 현황 파악
execute dbms_result_cache.memory_report;

 

사전 확인
Result caching이 지정된 SQL이 어떻게 실행될 것인지를 execution plan으로 확인.
- RESULT_CACHE_MODE = MANUAL이므로 해당 SQL에 result_cache 힌트 사용.

 

실습(두 query를 실제로 실행)

 

--  hr 유저로 접속
conn hr/hr

-- 다음 실행
select /*+ result_cache */ * from departments;
select department_name, emp_count
from (select /*+ result_cache */ department_id, count(*) emp_count
from employees
group by department_id) e, departments d
where e.department_id = d.department_id;

--result cache 관련 통계 정보는 V$RESULT_CACHE_STATISTICS 에서 확인
conn / as sysdba
col value for a15
col name format a55
select * from v$result_cache_statistics;

 

5번째 항목 Create Count Success의 값이 2이다.

위 두 SQL은 처음 수행되는 것이었으므로 실행과 동시에 새롭게 result cache에 기록되었을 것이다. 이
결과는 위의 조회와 같이 "Create Count Success = 2"로 나타난다.

동일한 두 SQL을 다시 한번 실행.

 

conn hr/hr
select /*+ result_cache */ * from departments;
select department_name, emp_count
from (select /*+ result_cache */ department_id, count(*) emp_count
from employees
group by department_id) e, departments d
where e.department_id = d.department_id;

 

당연히 동일한 결과를 얻지만 그 elapsed time을 보면, 처음 실행하였을 때보다 약 5 배가 좋아졌다.
이는 query들이 다시 실행된 것이 아니라 앞서 cache된 결과를 result cache에서 가져온 것이다.
- V$RESULT_CACHE_STATISTICS에서 확인 가능.

 

conn / as sysdba
col name format a55
select * from v$result_cache_statistics;

 

7번 항목의 Find Count 값이 2이다.

일반적으로 result cache의 효용이 좋다는 것은 "Find Count"가 상대적으로 높은 값을 보이는 것을 의미.
result cache의 sizing이 적절하지 못할 때 발생하는 "Create Count Failure"와
"Delete Count Valid"의 값은 상대적으로 낮아야 할 것이다.

 

7. PL/SQL Function Result Cache
SQL query result cache 기능과 메커니즘을 공유한다. Cache 자체도 shared pool 내의 result cache memory 영역이
SQL query 용과 PL/SQL function 용으로 나눈다.

 

Cache-in
설정 방법
Query result cache가 힌트를 사용하는 반면, PL/SQL function은 다음의 서명을 사용하여 작성함으로써
result caching을 지정.(실제 사용법은 예제에서 확인.)

RESULT_CACHE 절
RELIES_ON 절: dependent object 명시
Recursive function도 cache 가능.
cache된 recursive function이 호출된다면 일체의 재귀적 호출이 생략.

 

PL/SQL function의 caching 제약 사항.

데이터 타입 상의 제한
- IN parameter(BLOB, CLOB, NCLOB, REF CURSOR, Collection, Object, Record 타입)
- Return 타입이 BLOB, CLOB, NCLOB, REF CURSOR, Object 이거나 BLOB, CLOB, NCLOB, REF CURSOR, Object 타입을
포함하는 Record 또는 Collection인 경우
- OUT/IN OUT parameter를 가진 경우.
- Invoker’s right으로 정의되거나, anonymous block 내에서 정의된 경우
위에 해당하는 함수를 RESULT_CACHE 절과 함쎄 생성하면 오류가 발생.
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/39 PLS-00999: implementation restriction (may be temporary)
RESULT_CACHE is disallowed on subprograms with OUT or IN OUT parameters

 

주의 사항
result caching을 사용하지 말거나, 보다 신중하게 함수를 작성.
Side-effect를 가진 function
- 그 결과만을 cache한다는 것은 무의미한 일일 수 있기 때문.
- 해당 session에 specific한 설정/application context에 의존하는 함수.
(날짜를 반환하는 함수가 있는데 다음과 같이 코딩. - return to_char(some_date); 이 경우 해당 세션의
NLS_DATE_FORMAT에 따라 그 결과가 계속 달라질 수 있으므로 cache에 그리 적합한 경우는 아니다.)

 

노트
주의 사항과 제약 사항은 다루어지는 방법이 다르기는 하지만 맥락은 비슷하다고 볼 수 있다. 예를 들어
OUT parameter를 갖는 함수는 어떤 의미에서 side-effect를 갖고 있다고 말할 수 있다. 그렇다면 왜
이것은 주의 사항이 아닌 제약 사항인가? 그 이유는 그 경우 함수 서명만으로 간단히 체크할 수 있기
때문이라고 볼 수 있을 것이다.

이와 같은 논의는 query result cache와 비교해보면 더욱 분명해진다. 앞서 기술했듯이 query result
cache에서는 너무 “일시적인” 결과는 cache되지 않으며, 또 그렇게 하는 것이 어렵지 않다. 예를 들어
SQL 문장 내에 sysdate 함수에 대한 호출이 있느냐만을 점검하면 된다. 하지만 PL/SQL 코드의
symantics 체크는 SQL에 비해 훨씬 어렵다.

따라서 PL/SQL function의 result caching은 SQL query의 경우에 비해 보다 미묘한 측면이 있다. 위에
예로 든 날짜 구하기 함수의 경우, 날짜를 그냥 DATE로 반환하도록 하던지, 아니면 parameter로 포맷
문자열을 받도록 하는 등의 코딩을 신중하게 구사해야 하는 것이다.

 

Cache-hit
"동일한 함수, 동일한 parameter"가 cache-hit의 조건.
- parameter는 직접적으로는 function의 parameter를 의미.
- 주의점은 parameter의 동일성이 '=' 연산자에 비해 보다 엄격.
(예를 들어 PL/SQL 코드 내에서는 'A' = 'A ' 이지만 두 값이 parameter로 들어왔을 때에는 동일한 parameter로 취급되지 않는다.
두 parameter가 동일하기 위해서는 bit for bit로 동일해야 한다.)

 

8. PL/SQL Function Result Cache 사용 예

환경 점검 및 초기화

 

conn / as sysdba
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 150
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 1000
SET SERVEROUTPUT ON
show parameter result_cache_mode
show parameter result_cache_max_size
show parameter result_cache_max_result
execute dbms_result_cache.flush;
alter system flush shared_pool;
execute dbms_result_cache.memory_report;

 

함수 생성

conn hr/hr
create or replace function EMP_COUNT(dept_no number)
return number
result_cache relies_on (employees) –- result cache를 지정하는 syntax
is
v_count number;
begin
select count(*) into v_count
from employees
where department_id = dept_no;
return v_count;
end;
/

 

함수 호출 – 1차

 

conn hr/hr
select department_name, emp_count(department_id) no_of_emps
from departments
where department_name = 'Accounting'
/
DBMS_RESULT_CACHE.MEMORY_REPORT를 이용하여 현황을 파악.
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 150
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 1000
conn / as sysdba
-- Establish the cache content
set serveroutput on
execute dbms_result_cache.memory_report;

 

 

위 결과에서 총 3 block의 cache가 할당되었다.
왜 cache entry가 3개일까?
- V$RESULT_CACHE_OBJECTS로부터 확인.

 

conn / as sysdba
col name format a55
select type, namespace,status, scan_count,name
from v$result_cache_objects
/

 

 

하나는 함수 실행 결과이고, 두 개는 dependent object에 대한 정보.
Dependency는 RELIES_ON으로 명시한 HR.EMPLOYEES 테이블과 함수의 정의 자체도 포함.
SCAN_COUNT = 0 임은 지금 처음 cache되었으며 아직 참조되지 않았음을 의미.

- V$RESULT_CACHE_STATISTICS로 cross-check를 하자.

 

conn / as sysdba
col name format a55
select * from v$result_cache_statistics;

 

함수 호출 – 2차

 

conn hr/hr
select department_name, emp_count(department_id) no_of_emps
from departments
where department_name = 'Accounting'
/

 

동일한 함수를 다시 실행시켰더니 elapsed time이 크게 감소되었음을 확인할 수 있다.
다른 정보를 통해 cross check를 해보자.

 

conn / as sysdba
col name format a55
select * from v$result_cache_statistics;
conn / as sysdba
col name format a55
select type, namespace,status, scan_count,name
from v$result_cache_objects;

 

 

9. Client-side SQL Query Result Cache
Orale11g의 result cache 기능은 서버 단의 result caching 와 클라이언트 단의 result caching 기능을 포함.
클라이언트 단의 result caching 기능은 클라이언트-서버 사이의 round trip을 제거하는 효과도 있으므로 응답 시간이

크게 향상되고, 서버 단의 CPU 나 메모리 사용량을 낮출 수 있다는 장점도 있다.

 

서버 단의 result cache와 클라이언트 단의 result cache는 어느 정도의 연관성은 갖고 있지만 그 구체적인 구현은 서로

독립적이다. 가장 분명한 차이는 서버 단의 경우 result cache가 SGA 내의 shared pool 내에서 관리되고,
클라이언트 단의 경우에는 클라이언트의 메모리를 사용한다는 것이다.

 

클라이언트란?
JDBC-OCI, ODP.Net, OCCI, Pro*C/C++, Pro*COBOL, ODBC 등의 OCI 클라이언트를 의미.
클라이언트 단의 SQL query result cache 기능과 관련된 기능
- Database Change Notification(DCN)
- 10gR2부터 오브젝트의 변화 및 결과 집합, 즉 데이터의 변화에 대한 notification을 지원.

 

이전 버전에서도 일부 사용자들로부터 클라이언트 단의 result cache 기능에 대한 요구가 있었지만,
해당 사용자가 원하는 기능을 custom하게 구현하는 것이 유일한 방법이었다.
이것은 쉽지 않은 일이며, 특히 cache의 일관성을 유지하는 측면은 더욱 그렇다.
10gR2의 DCN은 custom 구현을 상대적으로 쉽게 해줄 수 있는 API를 제공한 것이며,
11g는 굳이 DCN 기능을 이용하여 직접 구현하지 않아도, 바로 사용할 수 있는 클라이언트 단 SQL query result cache 기능을 제공한다.

 

클라이언트 단의 SQL query result cache를 사용하기 위해 필요한 사항.

RESULT_CACHE_MODE 파라미터 & RESULT_CACHE 힌트
paramter의 사용법은 서버 단의 result cache의 경우와 동일하며,
/*+ result_cahce / 및 /*+no_result_cache */의 두 힌트의 사용법도 동일.

제약사항(서버 단의 query resut cache에 비해 제약 사항이 많다.)

다음 query들은 cache되지 않는다.
- Views
- Remote objects
- Complex types in the select list
- Snapshot-based or flashback queries
- Queries executed in a serializable, read-only transaction, or inside a flashback session
- Queries that have PL/SQL functions in them
- Queries that have VPD policies enabled on the tables

 

CLIENT_RESULT_CACHE_SIZE
하나의 클라이언트 프로세스가 가질 수 있는 result cache의 최대 크기를 지정.
- 0으로 지정하면 클라이언트 단의 result caching이 disable
- Sqlnet.ora 파라미터인 OCI_RESULT_CACHE_MAX_SIZE를 통해 override할 수 있다.

 

CLIENT_RESULT_CACHE_LAG
클라이언트 result cache가 서버 단의 변경에 대해 가질 수 있는 lag의 최대값: 디폴트는 3초
OCI 캐쉬의 일관성은 기본적으로 OCIStmtExecute() call에 의한 check를 통해 이루어진다.
만일 서버 호출이 한동안 없다 하더라도 이 파라미터에 의해 주기적으로 sync를 맞추게 되는 것이다.

 

OCI Statement Caching
Client-side SQL query result cache를 사용하기 위해서는 OCI statement caching이 enable로 설정.

CLIENT_RESULT_CACHE_STATS$
- 클라이언트 단의 result cache에 대한 통계를 보여주는 view
- OCI 클라이언트가 주기적으로 이 view를 update.
- V$RESULT_CACHE_STATISTICS와 거의 흡사.

 

Client-side query result cache 사용 예
$ORACLE_HOME/rdbms/demo/cdemoqc.sql 및 $ORACLE_HOME/rdbms/demo/cdemoqc.c에 예제 사용.