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에도 역시 제약 사항들이 있다.
다음은 result 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로 동일해야 한다.
PL/SQL Function 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 |
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 result_cache_max_size NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ result_cache_max_size big integer 1056K SQL> show parameter result_cache_max_result NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ result_cache_max_result integer 5 SQL> SQL> execute dbms_result_cache.flush; PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 SQL> alter system flush shared_pool; System altered. Elapsed: 00:00:00.67 SQL> execute dbms_result_cache.memory_report; 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 Maximum Cache Size = 1056K bytes (1056 blocks) Maximum Result Size = 52K bytes (52 blocks) [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] –- 아직 cache 된 것이 없음 PL/SQL procedure successfully completed. Elapsed: 00:00:00.05 SQL> |
함수 생성
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> connect hr/hr Connected. SQL> SQL> create or replace function EMP_COUNT(dept_no number) 2 return number 3 result_cache relies_on (employees) –- result cache 를 지정하는 syntax 4 is 5 v_count number; 6 begin 7 select count(*) into v_count 8 from employees 9 where department_id = dept_no; 10 11 return v_count; 12 end; 13 / Function created. Elapsed: 00:00:00.89 SQL> |
함수 호출 – 1 차
먼저 다음과 같이 호출하고,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> connect hr/hr Connected. SQL> SQL> select department_name, emp_count(department_id) no_of_emps 2 from departments 3 where department_name = 'Accounting' 4 / DEPARTMENT_NAME NO_OF_EMPS ------------------------------------------------------------ ---------- Accounting 2 1 row selected. Elapsed: 00:00:00.09 SQL> |
DBMS_RESULT_CACHE. MEMORY_REPORT를 이용하여 현황을 파악해본다.
|
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 |
SQL> SET ECHO ON 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> SQL> connect / as sysdba Connected. SQL> SQL> --- Establish the cache content SQL> set serveroutput on SQL> execute dbms_result_cache.memory_report 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 Maximum Cache Size = 1056K bytes (1056 blocks) Maximum Result Size = 52K bytes (52 blocks) [Memory] Total Memory = 103528 bytes [0.054% of the Shared Pool] ... Fixed Memory = 5132 bytes [0.003% of the Shared Pool] ... Dynamic Memory = 98396 bytes [0.051% of the Shared Pool] ....... Overhead = 65628 bytes ....... Cache Memory = 32K bytes (32 blocks) ........... Unused Memory = 29 blocks ........... Used Memory = 3 blocks ............... Dependencies = 2 blocks (2 count) ............... Results = 1 blocks ................... PLSQL = 1 blocks (1 count) PL/SQL procedure successfully completed. Elapsed: 00:00:00.06 SQL> |
위 실행에 의해 총 3 block의 cache가 할당되었음을 볼 수 있다. 왜 cache entry가 3개일까? 이는 V$RESULT_CACHE_OBJECTS로부터 확인할 수 있다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> connect / as sysdba Connected. SQL> SQL> col name format a55 SQL> SQL> select type, namespace,status, scan_count,name 2 from v$result_cache_objects 3 / TYPE NAMESPACE STATUS SCAN_COUNT NAME -------------------- ---------- ------------------ ---------- ------------------------------------------ ------------- Dependency Published 0 HR.EMP_COUNT Dependency Published 0 HR.EMPLOYEES Result PLSQL Published 0 "HR"."EMP_COUNT"::8."EMP_COUNT"#fac892c7867b54c6 #1 3 rows selected. Elapsed: 00:00:00.01 SQL> |
하나는 함수 실행 결과이고, 나머지 두 개는 dependent object에 대한 정보이다. Dependency는 RELIES_ON으로 명시한 HR.EMPLOYEES 테이블은 물론, 함수의 정의 자체도 포함한다. SCAN_COUNT = 0 임은 지금 처음 cache되었으며 아직 참조되지 않았음을 의미한다.
이번에는 V$RESULT_CACHE_STATISTICS로 cross-check를 해보자.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> connect / as sysdba Connected. SQL> SQL> col name format a55 SQL> 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 1 –- Cache-in! 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.00 SQL> |
함수 호출 – 2 차
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> connect hr/hr Connected. SQL> SQL> select department_name, emp_count(department_id) no_of_emps 2 from departments 3 where department_name = 'Accounting' 4 / DEPARTMENT_NAME NO_OF_EMPS ------------------------------------------------------------ ---------- Accounting 2 1 row selected. Elapsed: 00:00:00.00 SQL> |
동일한 함수를 다시 실행시켰더니 elapsed time이 크게 감소되었음을 확인할 수 있다. 이제 다시 다른 정보를 통해 cross check를 해보자.
|
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 |
SQL> connect / as sysdba Connected. SQL> SQL> col name format a55 SQL> 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 1 6 Create Count Failure 0 7 Find Count 1 –- 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> SQL> connect / as sysdba Connected. SQL> SQL> col name format a55 SQL> SQL> select type, namespace,status, scan_count,name 2 from v$result_cache_objects 3 / TYPE NAMESPACE STATUS SCAN_COUNT NAME -------------------- ---------- ------------------ ---------- ------------------------------------------ ------------- Dependency Published 0 HR.EMP_COUNT Dependency Published 0 HR.EMPLOYEES Result PLSQL Published 1 –- cache-hit! "HR"."EMP_COUNT"::8."EMP_COUNT"#fac892c7867b54c6 #1 3 rows selected. Elapsed: 00:00:00.01 SQL> |
결론 및 활용 방안
Result cache 에 의해 성능 향상 효과를 누리기 위해서는 SQL query 또는 PL/SQL function이 다음의 성격을 모두 가지고 있어야 한다.
결과가 비교적 변치 않을 것.
다시 말해 dependent object 가 주로 read-only이거나, read-mostly일 것.
자주 수행될 것.
한 가지를 덧붙인다면 해당 query/function이 많은 row들을 access하되 결과의 크기는 그리 크지 않아야 할 것이다.
이는 결국 result cache의 효과는 application에 따라 크게 달라질 수 있음을 의미한다.
따라서 result cache를 이용하는 튜닝 역시 보통의 튜닝과 마찬가지로 application 분석으로부터 시작되어야 할 것이다.
또 한가지 이슈는 usability이다.
RESULT_CACHE_MODE = MANUAL인 경우에는 각각의 SQL에 result_cache 힌트를 주어야 한다.
다시 말해 application의 수정이 필요한 것이다. 하지만 이는 BMT 등의 상황에서는 종종 허용되지 않는다.
따라서 이 기능의 활용을 위해서는 사용자와의 충분한 협의가 있어야 할 것이다.
한편 그렇다고 RESULT_CACHE_MODE = FORCE로 주는 것은 또 다른 의미에서 부담스럽다.
모든 query/function들이 무조건, 즉 dependent object에 대한 DML 빈도를 고려하지 않고, 자기의 result를 cache하고자 한다고 상상해보자. 이 경우 성능이 좋아지는 것보다는 오히려 안 좋아질 소지가 많을 것이다.
결론을 내리면 잘 사용한다면 성능 향상에 크게 기여할 수 있는 기능이라는 것이다.