Result cache 기능은 Oracle11g의 성능 분야의 신 기능으로서 SQL query 또는 PL/SQL function의 결과를 메모리 내에 cache할 수 있는 기능이다. 결과가 cache 되어 있는 query를 수행하거나 PL/SQL function을 호출하면 해당 query 또는 function을 실행하는 대신 cache로부터 바로 결과를 가져올 수 있다. 다시 말해 result cache 기능을 사용하면 메모리를 조금 더 사용하는 대신 응답 시간의 현저한 향상을 기대할 수 있는 것이다.
Result cache 기능은 “result”가 무엇의 결과이냐에 따라, 그리고 cache가 어디에 존재하며 어떻게 관리되느냐에 따라 다음과 같이 분류된다:
SQL Query Result Cache
– Server-side
– Client-side
PL/SQL Function Result Cache
– Server-side
서버 단의 result caching은 그것이 SQL query를 위한 것이건 PL/SQL function을 위한 것이건 동일한 메커니즘을 사용한다. 하지만 서버 단의 result caching과 클라이언트 단의 result caching은 어느 정도의 관련은 있지만 서로 독립적인 기능이다. 이하 서버 단의 SQL query result cache로부터 시작하여 각각의 내용을 살펴보도록 한다.
편의상 “클라이언트 단 (client-side)”이라고 명시적으로 수식하지 않는 한 서버 단의 result cache를 의미하는 것으로 한다.
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 단위로 적용되지 않는 것을 꼭 단점이라고는 볼 수 없을 것이다.
Cache
Result cache memory는 shared pool의 component이다. 그 sizing은 다음과 같이 이루어진다.
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
이 parameter는 비록 result cache 자체의 sizing을 정하는 것은 아니지만, 전체 result cache memory 내에서 하나의 result가 차지할 수 있는 메모리의 최대 크기를 result cache memory 전체 크기에 대한 %로 나타낸다. 디폴트 값은 5%이다.
SQL Query Result Cache의 동작
다음과 같이 크게 세가지로 나누어 볼 수 있다:
Cache-in: query 결과는 어떻게 cache되는가?
Cache-hit: 원하는 query 결과를 cache로부터 어떻게 찾는가?
Cache-out: 한번 cache된 결과는 어떻게 cache로부터 “out” 되는가?
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하기에 적절하지 않은 경우임을 알 수 있다. 예를 들어 시퀀스에 대한 query 결과는 지극히 “일시적”이다; 즉, 매번 query를 할 때마다 결과값이 달라진다. 이 경우 cache의 실익이 있다고 보기 어려울 뿐더러, 괜히 result cache memory만 차지함으로써, 정작 필요한 query 결과의 caching에 방해가 될 수 있다.
Cache-hit
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이 발생하는 것이다.
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 등의 변경이 일어나는 경우이다.
RAC 환경에서 result cache의 cache-hit은 local event이지만, invalidation은 global event이다. 이는 read consistency의 문제이기 때문이다. 어떤 의미에서 부분적인 cache fusion이 제공된다고도 볼 수 있겠다.
SQL Query Result Cache의 모니터링
DBMS_RESULT_CACHE
DBMS_RESULT_CACHE 패키지는 result cache에 대한 정보나 통계는 물론 각종 관리 작업도 수행할 수 있도록 하는 다양한 프로시저들을 제공한다. 사용 예는 예제에서 다루기로 한다.
RESULT_CACHE Views
다음의 View들이 제공된다. 역시 사용 예는 예제에서 다루기로 한다.
(G)V$RESULT_CACHE_STATISTICS
(G)V$RESULT_CACHE_MEMORY
(G)V$RESULT_CACHE_OBJECTS
(G)V$RESULT_CACHE_DEPENDENCY
SQL Query Result Cache 사용 예
환경 점검 및 초기화
먼저 관련 parameter 값들을 점검해 보고, result cache를 초기화한 후 그 내역을 살펴본다. Result cache의 초기화와 그 현황 파악은 DBMS_RESULT_CACHE 패키지를 사용한다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SQL> connect / as sysdba Connected. SQL> SET FEEDBACK 1 SQL> SET NUMWIDTH 10 SQL> SET LINESIZE 150 SQL> SET TRIMSPOOL ON SQL> SET TAB OFF SQL> SET PAGESIZE 1000 SQL> SET SERVEROUTPUT ON SQL> SQL> show parameter result_cache_mode NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ result_cache_mode string MANUAL SQL> show parameter memory_target NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ memory_target big integer 412M SQL> show parameter result_cache_max_size NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ result_cache_max_size big integer 1056K – default; memory_target 의 약0.25% SQL> show parameter result_cache_max_result NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ result_cache_max_result integer 5 SQL> SQL> execute dbms_result_cache.flush; -- result cache 를 flush PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> alter system flush shared_pool; System altered. Elapsed: 00:00:00.34 SQL> execute dbms_result_cache.memory_report; -- result cache 현황 파악 R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1K bytes -- 내부적으로 1KB 단위로 관리 (not configurable) Maximum Cache Size = 1056K bytes (1056 blocks) -- result_cache_max_size 에 대응 Maximum Result Size = 52K bytes (52 blocks) -- result_cache_max_result 에 대응 [Memory] Total Memory = 5132 bytes [0.003% of the Shared Pool] ... Fixed Memory = 5132 bytes [0.003% of the Shared Pool] ... Dynamic Memory = 0 bytes [0.000% of the Shared Pool] -- 0; 현재 cache 된 result 가 없음 PL/SQL procedure successfully completed. Elapsed: 00:00:00.05 SQL> |
사전 확인
Result caching이 지정된 SQL이 어떻게 실행될 것인지를 execution plan을 통해 미리 알아보자. 현재 RESULT_CACHE_MODE = MANUAL이므로 해당 SQL에 result_cache 힌트를 주도록 한다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SQL> connect hr/hr Connected. SQL> SQL> explain plan for 2 select /*+ result_cache */ * from departments; Explained. Elapsed: 00:00:00.30 SQL> SQL> set echo off PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------- Plan hash value: 4167016233 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 27 | 540 | 3 (0)| 00:00:01 | | 1 | RESULT CACHE | ggq8ztnxqw8ft4ucg3art21sjm | | | | | | 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 540 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=4; dependencies=(HR.DEPARTMENTS); name="select /*+ result_cache */ * from departments" 14 rows selected. Elapsed: 00:00:02.17 SQL> |
“RESULT CACHE”라는 새로운 row source operation이 실제 operation의 상위에 position됨을 볼 수 있다. 또한 explain plan 명령이 result cache에 대한 추가적인 정보를 제공함을 확인할 수 있다. 아래는 또 다른 query이다. 이번에는 최상위 레벨이 아닌 inline view query block에 result caching을 지정해 본다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SQL> connect hr/hr Connected. SQL> SQL> explain plan for 2 select department_name, emp_count 3 from (select /*+ result_cache */ department_id, count(*) emp_count from employees group by department_id) e, 4 departments d 5 where e.department_id = d.department_id; Explained. Elapsed: 00:00:00.12 SQL> SQL> set echo off PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------- Plan hash value: 523547400 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 462 | 7 (29)| 00:00:01 | | 1 | MERGE JOIN | | 11 | 462 | 7 (29)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 11 | 286 | 5 (40)| 00:00:01 | | 5 | VIEW | | 11 | 286 | 4 (25)| 00:00:01 | | 6 | RESULT CACHE | 7n7dsf9ukwqcv7cwbupmmdntaj | | | || | 7 | HASH GROUP BY | | 11 | 33 | 4 (25)| 00:00:01 | | 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 321 | 3 (0)|00:00:01 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") Result Cache Information (identified by operation id): ------------------------------------------------------ 6 - column-count=2; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache */ department_id, count(*) emp_count from employees group by departm ent_id" 26 rows selected. Elapsed: 00:00:00.09 SQL> |
Inline view 레벨로도 result caching이 적용됨을 확인할 수 있다.
실제 수행
이제는 위의 두 query를 실제로 실행해 보자.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
SQL> connect hr/hr Connected. SQL> SQL> select /*+ result_cache */ * from departments; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------------------------------------ ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 100 Finance 108 1700 110 Accounting 205 1700 120 Treasury 1700 130 Corporate Tax 1700 140 Control And Credit 1700 150 Shareholder Services 1700 160 Benefits 1700 170 Manufacturing 1700 180 Construction 1700 190 Contracting 1700 200 Operations 1700 210 IT Support 1700 220 NOC 1700 230 IT Helpdesk 1700 240 Government Sales 1700 250 Retail Sales 1700 260 Recruiting 1700 270 Payroll 1700 27 rows selected. Elapsed: 00:00:00.05 SQL> SQL> select department_name, emp_count 2 from (select /*+ result_cache */ department_id, count(*) emp_count 3 from employees 4 group by department_id) e, departments d 5 where e.department_id = d.department_id; DEPARTMENT_NAME EMP_COUNT ------------------------------------------------------------ ---------- Administration 1 Marketing 2 Purchasing 6 Human Resources 1 Shipping 45 IT 5 Public Relations 1 Sales 34 Executive 3 Finance 6 Accounting 2 11 rows selected. Elapsed: 00:00:00.05 SQL> SQL> set echo off SQL> |
위의 실행에 관련하여 기록되는 result cache 관련 통계 정보는 V$RESULT_CACHE_STATISTICS으로부터 확인할 수 있다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> connect / as sysdba Connected. SQL> col name format a55 SQL> select * from v$result_cache_statistics; ID NAME VALUE ---------- ------------------------------------------------------- ---------- 1 Block Size (Bytes) 1024 2 Block Count Maximum 1056 3 Block Count Current 32 4 Result Size Maximum (Blocks) 52 5 Create Count Success 2 6 Create Count Failure 0 7 Find Count 0 8 Invalidation Count 0 9 Delete Count Invalid 0 10 Delete Count Valid 0 10 rows selected. Elapsed: 00:00:00.02 SQL> |
위 두 SQL은 처음 수행되는 것이었으므로 실행과 동시에 새롭게 result cache에 기록되었을 것이다. 이 결과는 위의 조회와 같이 “Create Count Success = 2”로 나타난다. 이번에는 동일한 두 SQL을 다시 한번 실행시켜 본다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
SQL> connect hr/hr Connected. SQL> SQL> select /*+ result_cache */ * from departments; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------------------------------------ ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 100 Finance 108 1700 110 Accounting 205 1700 120 Treasury 1700 130 Corporate Tax 1700 140 Control And Credit 1700 150 Shareholder Services 1700 160 Benefits 1700 170 Manufacturing 1700 180 Construction 1700 190 Contracting 1700 200 Operations 1700 210 IT Support 1700 220 NOC 1700 230 IT Helpdesk 1700 240 Government Sales 1700 250 Retail Sales 1700 260 Recruiting 1700 270 Payroll 1700 27 rows selected. Elapsed: 00:00:00.01 SQL> SQL> select department_name, emp_count 2 from (select /*+ result_cache */ department_id, count(*) emp_count 3 from employees 4 group by department_id) e, departments d 5 where e.department_id = d.department_id; DEPARTMENT_NAME EMP_COUNT ------------------------------------------------------------ ---------- Administration 1 Marketing 2 Purchasing 6 Human Resources 1 Shipping 45 IT 5 Public Relations 1 Sales 34 Executive 3 Finance 6 Accounting 2 11 rows selected. Elapsed: 00:00:00.01 SQL> SQL> set echo off SQL> |
당연히 동일한 결과를 얻지만 그 elapsed time을 보면, 처음 실행하였을 때보다 약 5 배가 좋아졌음을 확인할 수 있다. 이는 query들이 다시 실행된 것이 아니라 앞서 cache된 결과를 result cache로부터 바로 가져올 수 있었음을 의미한다. 이는 V$RESULT_CACHE_STATISTICS로부터도 확인할 수 있다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> connect / as sysdba Connected. SQL> col name format a55 SQL> select * from v$result_cache_statistics; ID NAME VALUE ---------- ------------------------------------------------------- ---------- 1 Block Size (Bytes) 1024 2 Block Count Maximum 1056 3 Block Count Current 32 4 Result Size Maximum (Blocks) 52 5 Create Count Success 2 6 Create Count Failure 0 7 Find Count 2 -- cache-hit! 8 Invalidation Count 0 9 Delete Count Invalid 0 10 Delete Count Valid 0 10 rows selected. Elapsed: 00:00:00.00 SQL> |
예상대로 2 번의 cache-hit가 기록되었음을 알 수 있다. 일반적으로 result cache의 효용이 좋다는 것은 “Find Count”가 상대적으로 높은 값을 보이는 것을 의미한다. 동시에 result cache의 sizing이 적절하지 못할 때 발생하는 “Create Count Failure”와 “Delete Count Valid”의 값은 상대적으로 낮아야 할 것이다.