Базы данных Oracle - статьи
Кэш результатов PL/SQL функций
Предположим, что вместо SQL-запроса существует PL/SQL-функция, которая возвращает значения. Это обычной прием: использовать функцию для возвращения значения, чтобы сделать код более модульным. Рассмотрим случай двух таблиц: CUSTOMERS, которая хранит информацию обо всех клиентах вместе с state_code. Другая таблица TAX_RATE хранит налоговую ставку каждого штата. Для получения налоговой ставки для клиента необходимо в запросе соединить таблицы. Поэтому, чтобы упростить задачу, можно написать функцию, показанную ниже, которая принимает ID клиента в качестве параметра и возвращает налоговую ставку в зависимости от state_code:
create or replace function get_tax_rate ( p_cust_id customers.cust_id%type ) return sales_tax_rate.tax_rate%type is l_ret sales_tax_rate.tax_rate%type; begin select tax_rate into l_ret from sales_tax_rate t, customers c where c.cust_id = p_cust_id and t.state_code = c.state_code; -- simulate some time consuming -- processing by sleeping for 1 sec dbms_lock.sleep (1); return l_ret; exception when NO_DATA_FOUND then return NULL; when others then raise; end; /
Выполним функцию несколько раз, как показано ниже. Не забудьте установить параметр timing в значение on, чтобы записать время выполнения в каждом случае.
SQL> select get_tax_rate(1) from dual;
GET_TAX_RATE(1) --------------- 6
1 row selected.
Elapsed: 00:00:01.23 SQL> select get_tax_rate(1) from dual;
GET_TAX_RATE(1) --------------- 6
1 row selected.
Elapsed: 00:00:01.17
Последовательно выдается примерно одинаковое время для каждого выполнения. (Я сознательно добавил оператор sleep, чтобы увеличить время выполнения функции; иначе значение бы возвращалось слишком быстро.) Если рассмотреть код, можно заметить, что функция, вероятно, будет возвращать одно и то же значение при каждом вызове. Клиент не часто меняет штат, и налоговая ставка для штата меняется редко, поэтому весьма вероятно, что для данного клиента налоговая ставка будет одинаковой при всех выполнениях функции. Ставка изменится только, если изменится налоговая ставка штата, или клиент переедет в другой штат. Итак, как насчет кэширования результатов этой функции?
База данных Oracle 11g позволяет сделать именно это. Разрешить кэширование результатов функции можно, просто добавив предложение result_cache. Но как насчет случая, когда в штате меняется налоговая ставка или когда клиент переезжает в другой штат? Есть возможность определить зависимость от базовых таблиц, так что любые данные, изменяемые в этих таблицах, будут вызывать недействительность и последующее перестроение кэша результатов функции. Вот та же функция с добавленным предложением result cache (выделено жирным шрифтом):
create or replace function get_tax_rate ( p_cust_id customers.cust_id%type ) return sales_tax_rate.tax_rate%type result_cache relies_on (sales_tax_rate, customers)
is l_ret sales_tax_rate.tax_rate%type; begin select tax_rate into l_ret from sales_tax_rate t, customers c where c.cust_id = p_cust_id and t.state_code = c.state_code; -- simulate some time consuming -- processing by sleeping for 1 sec dbms_lock.sleep (1); return l_ret; exception when NO_DATA_FOUND then return NULL; when others then raise; end; /
После этого изменения создайте и выполните функцию следующим образом:
SQL> select get_tax_rate(1) from dual;
GET_TAX_RATE(1) --------------- 6
1 row selected.
Elapsed: 00:00:01.21
Время выполнения - 1.21 секунды, как и раньше, но давайте посмотрим на следующее выполнение:
SQL> select get_tax_rate(1) from dual;
GET_TAX_RATE(1) --------------- 6
1 row selected.
Elapsed: 00:00:00.01
Время выполнения – всего лишь 0.01 секунды! Что произошло? Функция в первый раз нормально выполнилась за 1.21 секунду. Но важное отличие на этот раз состоит в том, что при её выполнении результат кэшировался. При последующих вызовах функция не выполняется, результат просто берется из кэша. Поэтому нет ожидания в 1 секунду, как это было в коде функции.
Кэширование выполнялось только для клиента с идентификатором (customer_id) равным 1. Что если попытаться выполнить функцию для другого клиента?
SQL> select get_tax_rate(&n) from dual; Enter value for n: 5 old 1: select get_tax_rate(&n) from dual new 1: select get_tax_rate(5) from dual
GET_TAX_RATE(5) --------------- 6
1 row selected.
Elapsed: 00:00:01.18 SQL> / Enter value for n: 5 old 1: select get_tax_rate(&n) from dual new 1: select get_tax_rate(5) from dual
GET_TAX_RATE(5) --------------- 6
1 row selected.
Elapsed: 00:00:00.00 SQL> / Enter value for n: 6 old 1: select get_tax_rate(&n) from dual new 1: select get_tax_rate(6) from dual
GET_TAX_RATE(6) --------------- 6
1 row selected.
Elapsed: 00:00:01.17
Как можно видеть, при первом выполнении с каждым значением параметра результат кэшируется. Последующие вызовы выбирают значение из кэша. Когда вы продолжаете выполнять функцию для каждого клиента, кэш увеличивается.
Обратите внимание на предложение "relies on" в коде функции. Оно говорит функции, что кэш зависит от двух таблиц: customers и tax_rate. Если данные в этих таблицах изменятся, кэш должен быть обновлен. Обновление происходит автоматически без вашего вмешательства. Если данные не менялись, кэш продолжает предоставлять кэшированные значения настолько быстро, насколько это возможно.
Если по каким-то причинам необходимо обойти кэш, можно вызвать процедуру поставляемого пакета DBMS_RESULT_CACHE:
SQL> exec dbms_result_cache.bypass(true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> select get_tax_rate(&n) from dual; Enter value for n: 6 old 1: select get_tax_rate(&n) from dual new 1: select get_tax_rate(6) from dual
GET_TAX_RATE(6) --------------- 6
1 row selected.
Elapsed: 00:00:01.18
Как видно из времени выполнения, кэш не использовался.
