PL/SQL w Oracle 11g: Function Result Cache

Jedną z nowości w systemie zarządzania bazą danych Oracle 11g jest “Function Result Cache”. Podczas tworzenia funkcji w języku PL/SQL można dodać klauzulę ‘RESULT_CACHE’ określającą, że wynik funkcji ma być przechowywany w pamięci podręcznej (dokładniej w Shared Global Area, SGA) w celu ponownego wykorzystania w przyszłości. Przy ponownym wywołaniu funkcji dla tych samych argumentów co poprzednio wynik będzie pobrany z pamięci zamiast wyliczany jeszcze raz. Ma to wpływ głównie na czas wykonywania skomplikowanych i czasochłonnych funkcji.

Umieszczenie wyniku funkcji w obszarze SGA umożliwia dostęp do wyników dla wszystkich sesji – nie tylko dla sesji, która wykonała funkcję z klauzulą ‘RESULT_CACHE’.

Przykład:

CREATE OR REPLACE FUNCTION test_result_cache(p_employee_id IN NUMBER) RETURN NUMBER AS
v_salary NUMBER;
BEGIN
sys.dbms_lock.sleep(10);
select salary into v_salary from EMPLOYEES where employee_id = p_employee_id;
RETURN(v_salary);
END;
/
SQL> set timing on;
SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
8200

Elapsed: 00:00:10.21
SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
8200

Elapsed: 00:00:10.02

Teraz zmienimy deklarację funkcji dodając ‘RESULT_CACHE’:

SQL> CREATE OR REPLACE FUNCTION test_result_cache(p_employee_id IN NUMBER) RETURN NUMBER RESULT_CACHE AS
v_salary NUMBER;
BEGIN
sys.dbms_lock.sleep(10);
select salary into v_salary from EMPLOYEES where employee_id = p_employee_id;
RETURN(v_salary);
END;
/

SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
8200

Elapsed: 00:00:10.10
SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
8200

Elapsed: 00:00:00.00

Widać, że pierwsze wywołanie nadal zajmuje 10 sekund, jednak przy kolejnym wartość funkcji pobierana jest z pamięci a nie wyliczana ponownie.

W przypadku, gdy funkcja podczas wyznaczania wartości korzysta z tabel lub perspektyw warto wykorzystać dodatkowo klauzulę ‘RELIES_ON’. Umożliwia ona uniknięcie błędnych wyników w przypadku, gdy na tabeli wykonywane są operacje DML. Kolejny przykład:

SQL> update EMPLOYEES set salary = 16000 where employee_id = 110;

1 row updated.

Elapsed: 00:00:00.20
SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
8200

Elapsed: 00:00:00.01

Aby usunąć błąd z niewłaściwą wartością dodajemy RELIES_ON (EMPLOYEES):

SQL> CREATE OR REPLACE FUNCTION test_result_cache(p_employee_id IN NUMBER) RETURN NUMBER RESULT_CACHE RELIES_ON (EMPLOYEES) AS
2 v_salary NUMBER;
BEGIN
sys.dbms_lock.sleep(10);
select salary into v_salary from EMPLOYEES where employee_id = p_employee_id;
RETURN(v_salary);
END;
/

SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
16000

Elapsed: 00:00:10.04
SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
16000

Elapsed: 00:00:00.01
SQL> update EMPLOYEES set salary = 20000 where employee_id = 110;

1 row updated.

Elapsed: 00:00:00.01
SQL> select test_result_cache(110) from dual;
TEST_RESULT_CACHE(110)
———————-
20000

Elapsed: 00:00:10.01

Innym ciekawym przykładem wykorzystania RESULT_CACHE jest funkcja wyznaczająca wartość n-tego wyrazu ciągu fibonnaciego:

CREATE OR REPLACE FUNCTION fibonacci (n NUMBER)
RETURN NUMBER RESULT_CACHE IS
BEGIN
IF (n =0) OR (n =1) THEN
RETURN 1;
ELSE
RETURN fibonacci(n - 1) + fibonacci(n - 2);
END IF;
END;
/

Dla funkcji z klauzula ‘RESULT_CACHE’ istnieją pewne ograniczenia – przede wszystkim nie może ona mieć parametrów typu OUT i IN OUT. Dodatkowo zarówno parametry jak i wartość wynikowa nie mogą być typu BLOB, CLOB, NCLOB, REF CURSOR, Collection (tylko parametry), Object, Record(w tym przypadku Record jest dopuszczalny dla wartości wynikowej pod warunkiem, że nie zawiera w sobie innych zabronionych typów).

Administrator Oracle 11g w celu zarządzania ‘RESULT_CACHE’ może korzystać:

* parametry inicjalizacyjne (RESULT_CACHE_MAX_SIZE, RESULT_CACHE_MAX_RESULT)
* pakiet DBMS_RESULT_CACHE
* perspektywy: [G]V$RESULT_CACHE_STATISTICS, [G]V$RESULT_CACHE_MEMORY, [G]V$RESULT_CACHE_OBJECTS, [G]V$RESULT_CACHE_DEPENDENCY

Korzystając z klauzuli ‘RESULT_CACHE’ musimy pamiętać, aby nie modyfikowała ona stanu bazy danych (brak operacji DML i DDL) i nie modyfikowała sesji – od wykonania lub niewykonania (czyli pobrania wartości zwracanej z SGA) tej funkcji nie mogą więc zależeć żadne procesy biznesowe.

Linki zewnętrzne:

Using the Cross-Session PL/SQL Function Result Cache