Кэширование данных в Oracle для программиста PL/SQL

Просмотров: 7856

Кэширование данных в Oracle для PL/SQLЧрезвычайно распространенный метод повышения производительности заключается в промежуточном кэшировании для данных, к которым программа часто обращается и которые могут (по крайней мере какое-то время) оставаться статическими, то есть неизменными.

Область SGA базы данных Oracle является «начальником всех кэшей». Это (обычно) очень большая и (всегда) очень сложная область памяти, которая служит посредником между базой данных (файлами на диске) и программами, работающими с этой базой данных.

Оглавление статьи[Показать]

В SGA кэшируется следующая информация (а также много другой информации, но эта наиболее актуальна для программистов PL/SQL):

В общем и целом база данных не использует SGA для кэширования данных программ. Когда вы объявляете переменную в своей программе, память для этих данных выделяется из PGA (для выделенного сервера). Каждое подключение к базе данных имеет собственную область PGA; таким образом, память, необходимая для хранения данных программы, копируется в каждом подключении, в котором эта программа вызывается. К счастью, у использования памяти PGA имеется преимущество: программа PL/SQL может получать информацию из PGA намного быстрее, чем из SGA. Следовательно, кэширование в PGA открывает интересные возможности для повышения производительности. Oracle также предоставляет другие механизмы кэширования, специфические для PL/SQL и предназначенные для повышения производительности ваших программ. В этом разделе будут рассмотрены три типа кэширования PL/SQL (еще один механизм, на который также стоит обратить внимание, использует контексты приложений):

При использовании пакетного кэширования в системе сохраняется копия данных. Вы должны быть твердо уверены в том, что эта копия актуальна. Любой из методов кэширования может использоваться неправильно, в результате чего пользователи начнут получать некорректные данные.

 

Пакетное кэширование

Пакетный кэш состоит из одной или нескольких переменных, объявленных на уровне пакета (а не в подпрограмме этого пакета). Пакетное кэширование является одним из первых кандидатов на организацию кэширования, потому что такие данные сохраняются в сеансе, даже если программы этого сеанса в настоящий момент не используют данные и не вызывают никакие подпрограммы пакета. Иначе говоря, если вы объявляете переменную на уровне пакета, то присвоенное этой переменной значение будет сохраняться до отключения, перекомпиляции пакета или изменения значения.

Наше знакомство с пакетным кэшированием начнется с описания типичных ситуаций, в которых уместно применение этого способа кэширования. Затем будет рассмотрен простой пример с кэшированием одного значения. В завершение я покажу, как кэшировать в пакете реляционную таблицу (частично или полностью), с существенным ускорением доступа к данным этой таблицы.

 

Когда используется пакетное кэширование

Пакетное кэширование уместно использовать при выполнении следующих условий:

И наоборот, пакетное кэширование не следует применять при выполнении хотя бы одного из следующих условий:

 

Простой пример пакетного кэширования

Как известно, функция USER возвращает имя текущего сеанса. Oracle реализует эту функцию в пакете STANDARD следующим образом:

 

function USER return varchar2 is
c varchar2(255);
begin
      select user into c from sys.dual; 
      return c;
end;

Таким образом, при каждом вызове USER выполняется запрос. Конечно, он выполняется быстро, но такой запрос никогда не должен выполняться более одного раза за сеанс, потому что возвращаемое значение остается неизменным. Возможно, вы спросите: ну и что? Не только запрос SELECT FROM dual выполняется очень эффективно, но и база данных Oracle также кэширует разобранный запрос и возвращенное значение, так что происходящее уже оптимизировано. Разве пакетное кэширование на что-нибудь повлияет? Еще как!

Возьмем следующий пакет:


PACKAGE thisuser 
IS
   cname CONSTANT VARCHAR2(30) := USER;
   FUNCTION name RETURN VARCHAR2;
END;

PACKAGE BODY thisuser 
IS
   g_user VARCHAR2(30) := USER;

   FUNCTION name RETURN VARCHAR2 IS BEGIN RETURN g_user; END;
END;

Я кэширую значение, возвращаемое USER, двумя разными способами:

Итак, кэши созданы, и теперь мы можем проверить, стоит ли игра свеч. Будет ли какая-либо из реализаций работать значительно быстрее по сравнению с многократным вызовом высокооптимизированной функции USER?

Следующий сценарий использует пакет sf_timer для сравнения производительности:


PROCEDURE test_thisuser (count_in IN PLS_INTEGER)
IS
   l_name all_users.username%TYPE;
BEGIN
   sf_timer.start_timer;
   FOR indx IN 1 .. count_in LOOP l_name := thisuser.NAME; END LOOP; 
   sf_timer.show_elapsed_time ('Packaged Function');
   --
   sf_timer.start_timer;
   FOR indx IN 1 .. count_in LOOP l_name := thisuser.cname; END LOOP; 
   sf_timer.show_elapsed_time ('Packaged Constant');
   --
   sf_timer.start_timer;
   FOR indx IN 1 .. count_in LOOP l_name := USER; END LOOP; 
   sf_timer.show_elapsed_time ('USER Function');
END test_thisuser;

Если запустить его сначала для 100, а затем для 1 000 000 итераций, будут получены следующие результаты:

Packaged Function Elapsed: 0 seconds.
Packaged Constant Elapsed: 0 seconds.
USER Function Elapsed: 0 seconds.

Packaged Function Elapsed: .48 seconds.
Packaged Constant Elapsed: .06 seconds.
USER Function Elapsed: 32.6 seconds.

Результат очевиден: при малом количестве итераций преимущества кэширования не очевидны. Однако с увеличением количества итераций пакетное кэширование работает значительно быстрее, чем вызов через уровень SQL и SGA.

Кроме того, обращение к константе происходит значительно быстрее, чем вызов функции, возвращающий значение. Тогда зачем использовать функцию? Версия с функцией обладает важным преимуществом перед константой: она скрывает значение. Таким образом, если по какой-либо причине значение потребуется изменить (неприменимо к нашему сценарию), это можно сделать без перекомпиляции спецификации пакета, которая бы потребовала перекомпиляции всех программ, зависящих от пакета.

И хотя вам вряд ли когда-нибудь потребуется кэшировать значение, возвращаемое функцией USER, надеюсь, вы убедились в том, что пакетное кэширование является весьма эффективным способом хранения и выборки данных. А теперь рассмотрим менее тривиальный пример.

 

Кэширование содержимого таблицы в пакете

Если приложение содержит таблицу, которая никогда не изменяется в нормальное рабочее время (то есть остается статической во время работы с ней), вы можете легко создать пакет, кэширующий все содержимое таблицы. Скорость обработки запросов при этом вырастет на порядок и более.

Допустим, имеется статическая таблица products, которая определяется следующим образом:


TABLE products (
   product_number INTEGER PRIMARY KEY 
 , description VARCHAR2(1000))

Ниже приведено тело пакета, предоставляющего два механизма получения данных из таблицы — с выполнением запроса и кэшированием данных с последующей выборкой из кэша:

1   PACKAGE BODY products_cache
2   IS
3      TYPE cache_t IS TABLE OF products%ROWTYPE INDEX BY PLS_INTEGER;
4      g_cache cache_t;
5
6      FUNCTION with_sql (product_number_in IN products.product_number%TYPE)
7         RETURN products%ROWTYPE
8      IS
9         l_row products%ROWTYPE;
10      BEGIN
11         SELECT * INTO l_row FROM products
12         WHERE product_number = product_number_in;
13         RETURN l_row;
14      END with_sql;
15
16      FUNCTION from_cache (product_number_in IN products.product_number%TYPE)
17         RETURN products%ROWTYPE
18      IS
19      BEGIN
20         RETURN g_cache (product_number_in);
21      END from_cache;
22   BEGIN
23      FOR product_rec IN (SELECT * FROM products) LOOP
24         g_cache (product_rec.product_number) := product_rec;
25      END LOOP;
26   END products_cache;

В следующей таблице описаны основные аспекты этого пакета.

from_cache

 

 

Когда пользователь впервые вызовет функцию from_cache (или with_sql), база данных сначала выполнит этот код.

Затем я конструирую и выполняю блок кода для сравнения производительности двух решений:

DECLARE
   l_row products%ROWTYPE;
BEGIN
   sf_timer.start_timer;
   FOR indx IN 1 .. 100000 
   LOOP
   l_row := products_cache.from_cache (5000);
   END LOOP;
   sf_timer.show_elapsed_time ('Cache table');
   --
   sf_timer.start_timer;
   FOR indx IN 1 .. 100000 
   LOOP
   l_row := products_cache.with_sql (5000);
   END LOOP;
   sf_timer.show_elapsed_time ('Run query every time');
END;

Результаты выполнения:

Cache table Elapsed: .14 seconds.
Run query every time Elapsed: 4.7 seconds.

И снова предельно очевидно, что пакетное кэширование работает намного, намного быстрее, чем многократное повторение запроса — даже если запрос полностью оптимизирован со всей мощностью и сложностью SGA.

 

Избирательное кэширование табличных данных

Допустим, я нашел статическую таблицу, к которой хотелось бы применить этот способ кэширования. Однако возникает одна проблема: таблица содержит 100 000 строк данных. Можно создать пакет наподобие products_cache из предыдущего раздела, но он будет использовать 5 Мбайт памяти в PGA каждого сеанса. С 500 параллельными подключениями кэш займет 2,5 Гбайт, а это неприемлемо. К счастью, я замечаю, что каждый пользователь обычно работает примерно с 50 строками данных. Следовательно, кэширование всей таблицы в каждом сеансе расточительно в отношении как вычислительных ресурсов (исходная загрузка 100 000 записей), так и памяти.

Если содержимое таблицы статично, но вам не нужны все данные из этой таблицы, рассмотрите возможность применения избирательного кэширования. Это означает, что при инициализации пакета содержимое таблицы не загружается в кэш. Если запрашиваемая пользователем строка находится в кэше, она возвращается немедленно, а если нет — вы запрашиваете эту одну строку из таблицы, добавляете ее в кэш, а затем возвращаете данные.

Когда пользователь в следующий раз запросит ту же строку, она будет извлечена из кэша. Следующий фрагмент демонстрирует этот способ:


FUNCTION jit_from_cache (product_number_in IN products.product_number%TYPE)
   RETURN products%ROWTYPE 
IS
   l_row products%ROWTYPE;
BEGIN
   IF g_cache.EXISTS (product_number_in)
   THEN
      /* Строка уже находится в кэше, возвращаем ее. */ 
      l_row := g_cache (product_number_in);
   ELSE
      /* Первый запрос, извлекаем строку из базы данных 
         и добавляем ее в кэш. */ 
      l_row := with_sql (product_number_in); 
      g_cache (product_number_in) := l_row;
   END IF;
   RETURN l_row;
END jit_from_cache;

В общем случае избирательное кэширование работает медленнее одноразовой загрузки всех данных в кэш. Тем не менее этот способ все равно значительно превосходит по скорости повторное обращение к базе данных.

 

Кэширование детерминированных функций

Функция называется детерминированной, если она возвращает одинаковый результат при всех вызовах с одинаковыми значениями аргументов IN и IN OUT. Также можно рассматривать детерминированные программы с точки зрения отсутствия побочных эффектов: все изменения, вносимые программой, отражаются в списке параметров. За дополнительной информацией о детерминированных функциях обращайтесь к этому блогу.

Именно из-за однозначности поведения детерминированных функций Oracle может построить кэш с входными данными и результатами функции. В конце концов, если для одного набора входных данных всегда выдается одинаковый результат, вызывать функцию повторно для того же набора параметров неразумно.

Рассмотрим еще один пример кэширования на базе детерминированных функций. Допустим, я определил следующую инкапсуляцию для функции SUBSTR (возвращает строку между заданной начальной и конечной позициями) как детерминированную функцию:


FUNCTION betwnstr (
   string_in IN VARCHAR2, start_in IN PLS_INTEGER, end_in IN PLS_INTEGER)
   RETURN VARCHAR2 DETERMINISTIC 
IS
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END betwnstr;

Затем эта функция вызывается внутри запроса (она не изменяет никакие таблицы базы данных, в противном случае ее нельзя было бы использовать подобным образом):

SELECT betwnstr (last_name, 1, 5) first_five 
   FROM employees

При таком вызове betwnstr база данных создает кэш с входными данными и возвращаемыми значениями. Затем, если я снова вызову функцию с теми же данными, база данных сразу вернет значение без вызова функции. Чтобы продемонстрировать эффект этой оптимизации, я изменю реализацию betwnstr:

FUNCTION betwnstr (
   string_in IN VARCHAR2, start_in IN PLS_INTEGER, end_in IN PLS_INTEGER)
   RETURN VARCHAR2 DETERMINISTIC 
IS
BEGIN
   DBMS_LOCK.sleep (.01);
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END betwnstr;

Подпрограмма sleep из пакета DBMS_LOCK приостанавливает betwnstr на сотую долю секунды.

Если вызвать эту функцию из блока кода PL/SQL (не из запроса), база данных не будет кэшировать возвращаемые значения, поэтому запрос 107 строк таблицы employees займет более секунды:

DECLARE
   l_string employees.last_name%TYPE;
BEGIN
   sf_timer.start_timer;
   FOR rec IN (SELECT * FROM employees)
   LOOP
      l_string := betwnstr ('FEUERSTEIN', 1, 5);
   END LOOP;

   sf_timer.show_elapsed_time ('Deterministic function in block');
END;
/

Результат:

Deterministic function in block Elapsed: 1.67 seconds.

Если теперь выполнить ту же логику, но переместить вызов betwnstr в запрос, ситуация существенно изменяется:

BEGIN
   sf_timer.start_timer;
   FOR rec IN (SELECT betwnstr ('FEUERSTEIN', 1, 5) FROM employees)
   LOOP
      NULL;
   END LOOP;
   sf_timer.show_elapsed_time ('Deterministic function in query');
END;
/

Результат:

Deterministic function in query Elapsed: .05 seconds.

Как видите, кэширование детерминированных функций является очень эффективным способом оптимизации. Главное — убедитесь в следующем:

 

Кэширование результатов функций (Oracle Database 11g)

До выхода Oracle Database 11g пакетное кэширование было самым лучшим и гибким способом кэширования данных для использования в программах PL/SQL. К сожалению, обстоятельства, в которых оно может использоваться, ограничены — источник данных должен быть статическим, а затраты памяти растут с каждым новым сеансом, подключенным к базе данных Oracle.

Так как преимущества этого вида кэширования (а также кэширования, реализованного для детерминированных функций) были очевидны, компания Oracle реализовала кэширование результатов функций в Oracle Database 11g. Эта возможность предоставляет механизм кэширования, лишенный недостатков пакетного кэширования и почти не уступающий ему по скорости.

Включение кэширования результатов функций для конкретной функции дает следующие преимущества:

Кэширование результатов чаще всего используется для функций, запрашивающих данные из таблиц. Хорошие кандидаты для кэширования результатов:

Но если таблица изменяется каждую секунду, кэширование результатов нежелательно; оно может только замедлить приложение, так как Oracle потратит слишком много времени на заполнение и последующую очистку кэша. Тщательно выбирайте, когда и как использовать эту возможность; поговорите с администратором базы данных и убедитесь в том, что размер пула SGA для кэша результатов достаточно велик для хранения всех данных, кэшируемых в режиме типичной нагрузки.

Далее я сначала опишу синтаксис кэширования результатов, а затем приведу простые примеры его использования. Также мы обсудим, в каких обстоятельствах следует использовать кэш результатов, упомянем аспекты управления кэшем, относящиеся к администрированию, и изучим основные ограничения и ловушки.

 

Включение кэширования результатов функций

Кэширование результатов функций включается очень просто — для этого в заголовок функции включается секция RESULT_CACHE. Обо всем остальном позаботится Oracle. Синтаксис секции result_cache:

RESULT_CACHE [ RELIES_ON (таблица_или_представление [, таблица_или_представление2 ... таблица_или_представлениеN] ]

Секция RELIES_0N сообщает Oracle, от каких таблиц или представлений зависит содержимое кэша, и может включаться только в заголовки функций уровня схемы и в реализации пакетных функций (то есть в тело пакета). В Oracle Database 11g Release 2 она считается устаревшей. Ниже приведен пример пакетной функции — обратите внимание на то, что секция RESULT_CACHE должна присутствовать и в спецификации, и в теле:

CREATE OR REPLACE PACKAGE get_data 
IS
   FUNCTION session_constant RETURN VARCHAR2 RESULT_CACHE;
END get_data;
/

CREATE OR REPLACE PACKAGE BODY get_data 
IS
   FUNCTION session_constant RETURN VARCHAR2 
      RESULT_CACHE 
   IS
   BEGIN
      ...
   END session_constant;
END get_data;
/

Так просто и элегантно; вы добавляете всего одну секцию в заголовок своей функции, и приложение начинает работать заметно быстрее!

 

Секция RELIES_ON (считается устаревшей в 11.2)

Первое, что следует знать о RELIES_0N, — то, что в Oracle Database 11g Release 2 эта конструкция уже не нужна. Начиная с этой версии Oracle автоматически определяет, от каких таблиц зависят возвращаемые данные, и правильно объявляет недействительным содержимое кэша при изменении содержимого этих таблиц; включение своей секции RELIES_0N ни к чему не приведет. Чтобы убедиться в этом, запустите сценарий  11gR2_frc_no_relies_on.sql. В ходе анализа идентифицируются как таблицы, к которым программа обращается из статического (встроенного) и динамического SQL, так и таблицы, к которым она обращается только косвенно (через представления).

Но если вы работаете в Oracle Database 11g Release 1 или более ранней версии, вам придется явно перечислить все таблицы и представления, из которых берутся возвращаемые данные. Определить, какие таблицы и представления нужно включить в список, обычно бывает несложно. Если функция содержит команду SELECT, проследите за тем, чтобы все таблицы и представления из всех секций FROM запроса были включены в список.

При выборке из представления необходимо включить в список только само представление, а не таблицы, к которым оно обращается. Сценарий 11g_frc_views.sql показывает, как база данных по определению представления находит все таблицы, при изменении которых содержимое кэша становится недействительным.

Несколько примеров использования RELIES_0N:

  1. Функция уровня схемы с секцией relies_on указывает, что кэш зависит от таблицы employees:
CREATE OR REPLACE FUNCTION name_for_id (id_in IN employees.employee_id%TYPE)
   RETURN employees.last_name%TYPE 
   RESULT_CACHE RELIES ON (employees)

     2. Пакетная функция с секцией RELIES_ON (может присутствовать только в теле):

CREATE OR REPLACE PACKAGE get_data 
IS
   FUNCTION name_for_id (id_in IN employees.employee_id%TYPE)
      RETURN employees.last_name%TYPE 
      RESULT_CACHE 
END get_data;
/

CREATE OR REPLACE PACKAGE BODY get_data 
IS
   FUNCTION name_for_id (id_in IN employees.employee_id%TYPE)
      RETURN employees.last_name%TYPE 
      RESULT_CACHE RELIES ON (employees)
   IS
   BEGIN
      ...
   END name_for_id;
END get_data;
/

   3. Секция RELIES_ON с перечислением нескольких объектов:

CREATE OR REPLACE PACKAGE BODY get_data 
IS
   FUNCTION name_for_id (id_in IN employees.employee_id%TYPE)
      RETURN employees.last_name%TYPE
      RESULT_CACHE RELIES ON (employees, departments, locations)
      ...

 

Пример кэширования результатов: детерминированная функция

В предыдущем разделе вы узнали о кэшировании, связанном с детерминированными функциями. В частности, я заметил, что кэширование действует только при вызове функции внутри запроса. Теперь применим механизм кэширования результатов функций Oracle Database 11g к функции betwnstr и посмотрим, как он работает при вызове из блока PL/SQL.

В следующей функции в заголовок добавляется секция RESULT_CACHE. Я также добавил вызов DBMS_0UTPUT.PUT_LINE, который показывает, какие входные данные были переданы функции:


FUNCTION betwnstr (
   string_in IN VARCHAR2, start_in IN INTEGER, end_in IN INTEGER)
   RETURN VARCHAR2 RESULT_CACHE 
IS
BEGIN
   DBMS_OUTPUT.put_line (
      'betwnstr for ' || string_in || '-' || start_in || '-' || end_in);
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END;

Затем я вызываю эту функцию для 10 строк таблицы employees. Для четных идентификаторов работника к фамилии работника применяется функция betwnstr. В противном случае передается постоянный набор из трех значений:

DECLARE
   l_string employees.last_name%TYPE;
BEGIN
   FOR rec IN (SELECT * FROM employees WHERE ROWNUM < 11)
   LOOP
      l_string :=
         CASE MOD (rec.employee_id, 2)
            WHEN 0 THEN betwnstr (rec.last_name, 1, 5)
            ELSE	betwnstr ('FEUERSTEIN', 1, 5)
         END;
   END LOOP;
END;

При выполнении функции я получаю следующий результат:

betwnstr for OConnell-1-5
betwnstr for FEUERSTEIN-1-5
betwnstr for Whalen-1-5
betwnstr for Fay-1-5
betwnstr for Baer-1-5
betwnstr for Gietz-1-5
betwnstr for King-1-5

Обратите внимание: строка FEUERSTEIN встречается только один раз, хотя при вызове она использовалась пять раз. Этот пример демонстрирует практическое применение кэширования результатов.

 

Пример кэширования результатов функций: выборка данных из таблицы

Кэширование результатов функций чаще всего применяется при запросах данных из таблиц, содержимое которых чаще читается, чем изменяется (между изменениями данные остаются статическими). Предположим, в системе управления недвижимостью имеется таблица, в которой хранятся проценты для разных типов ссуд. Содержимое этой таблицы обновляется запланированным заданием, которое запускается ежечасно в течение дня. Структура таблицы и данные, которые будут использованы в демонстрационном сценарии:


CREATE TABLE loan_info (
   NAME VARCHAR2(100) PRIMARY KEY,
   length_of_loan INTEGER,
   initial_interest_rate NUMBER,
   regular_interest_rate NUMBER,
   percentage_down_payment INTEGER)
/
BEGIN
   INSERT INTO loan_info VALUES ('Five year fixed', 5, 6, 6, 20);
   INSERT INTO loan_info VALUES ('Ten year fixed', 10, 5.7, 5.7, 20);
   INSERT INTO loan_info VALUES ('Fifteen year fixed', 15, 5.5, 5.5, 10);
   INSERT INTO loan_info VALUES ('Thirty year fixed', 30, 5, 5, 10);
   INSERT INTO loan_info VALUES ('Two year balloon', 2, 3, 8, 0);
   INSERT INTO loan_info VALUES ('Five year balloon', 5, 4, 10, 5);
   COMMIT;
END;
/

Функция для получения всей информации в одной строке данных:

FUNCTION loan_info_for_name (NAME_IN IN VARCHAR2)
   RETURN loan_info%ROWTYPE 
   RESULT_CACHE RELIES_ON (loan_info)
IS
   l_row loan_info%ROWTYPE;
BEGIN
   DBMS_OUTPUT.put_line ('> Looking up loan info for ' || NAME_IN);
   SELECT * INTO l_row FROM loan_info WHERE NAME = NAME_IN;
   RETURN l_row;
END loan_info_for_name;

В этом случае секция RESULT_CACHE включает внутреннюю секцию RELIES_0N, которой сообщает, что кэш для этой функции должен базироваться на («зависеть от») данных таблицы loan_info. Затем выполняется следующий сценарий, который вызывает функцию для двух разных имен, после чего изменяет содержимое таблицы, и наконец, снова вызывает функцию для одного из исходных имен:

DECLARE
   l_row loan_info%ROWTYPE;
BEGIN
   DBMS_OUTPUT.put_line ('First time for Five year fixed...'); 
   l_row := loan_info_for_name ('Five year fixed');
   DBMS_OUTPUT.put_line ('First time for Five year balloon...'); 
   l_row := loan_info_for_name ('Five year balloon');
   DBMS_OUTPUT.put_line ('Second time for Five year fixed...'); 
   l_row := loan_info_for_name ('Five year fixed');

   UPDATE loan_info SET percentage_down_payment = 25 
   WHERE NAME = 'Thirty year fixed';
   COMMIT;
   DBMS_OUTPUT.put_line ('After commit, third time for Five year fixed...'); 
   l_row := loan_info_for_name ('Five year fixed');
END;

Результат, полученный при выполнении этого сценария:

First time for Five year fixed...
> Looking up loan info for Five year fixed 
First time for Five year balloon...
> Looking up loan info for Five year balloon 
Second time for Five year fixed...
After commit, third time for Five year fixed...
> Looking up loan info for Five year fixed

Несколько слов по поводу того, что же здесь происходит:

 

Пример кэширования результатов функций: кэширование коллекции

До настоящего момента я приводил примеры кэширования отдельных значений и целых записей. Кэшировать также можно целую коллекцию данных и даже коллекцию записей. В следующем коде я изменил функцию таким образом, чтобы она возвращала все имена ссуд в виде коллекции строк (на базе предопределенного типа коллекции DBMS_SQL). Затем я многократно вызываю эту функцию, но коллекция заполняется только один раз (конструкция BULK COLLECT рассматривается позднее в этой статье):


FUNCTION loan_names RETURN DBMS_SQL.VARCHAR2S 
   RESULT_CACHE RELIES_ON (loan_info)
IS
   l_names DBMS_SQL.VARCHAR2S;
BEGIN
   DBMS_OUTPUT.put_line ('> Looking up loan names....');
   SELECT name BULK COLLECT INTO l_names FROM loan_info;
   RETURN l_names;
END loan_names;

Следующий сценарий показывает, что механизм кэширования успешно работает даже при заполнении таких сложных типов:

DECLARE
   l_names DBMS_SQL.VARCHAR2S;
BEGIN
   DBMS_OUTPUT.put_line ('First time retrieving all names...'); 
   l_names := loan_names ();
   DBMS_OUTPUT.put_line('Second time retrieving all names...'); 
   l_names := loan_names ();

   UPDATE loan_info SET percentage_down_payment = 25 
   WHERE NAME = 'Thirty year fixed';

   COMMIT;
   DBMS_OUTPUT.put_line ('After commit, third time retrieving all names...'); 
   l_names := loan_names ();
END;
/

Результат:

First time retrieving all names...
> Looking up loan names	
Second time retrieving all names...
After commit, third time retrieving all names...
> Looking up loan names

 

Когда применяется кэширование результатов функций

К кэшированию всегда следует подходить с величайшей осторожностью. Если оно будет организовано неправильно, приложение может передать пользователю некорректные данные. Кэширование результатов функций — самый гибкий и часто применяемый из разных типов кэширования, встречающихся в коде PL/SQL, но и с ним возможны неприятности.

Рассмотрите возможность включения RESULT_CACHE в заголовок функции в следующих обстоятельствах:

 

Когда кэширование результатов не применяется

Секция RESULT_CACHE не может применяться при выполнении любых из следующих условий:

Не используйте (или по крайней мере очень внимательно проанализируйте использование) секции RESULT_CACHE в случае истинности каких-либо из следующих условий:

 

Полезная информация о поведении кэширования результатов функций

Следующая информация пригодится каждому, кто захочет подробно разобраться в тонкостях кэширования результатов функций в приложениях:

  

Управление кэшем результатов функций

Кэш результатов функций представляет собой область памяти в SGA. Oracle представляет стандартный набор средств для управления кэшем:

Динамические представления

Процедура, которая может использоваться для вывода зависимостей:


CREATE OR REPLACE PROCEDURE show_frc_dependencies ( 
   name_like_in IN VARCHAR2)
IS
BEGIN
   DBMS_OUTPUT.put_line ('Dependencies for "' || name_like_in || '"');

   FOR rec
      IN (SELECT d.result_id
                  /* Clean up display of function name */
               , TRANSLATE (SUBSTR (res.name, 1, INSTR (res.name, ':') - 1)
                           , 'A"', 'A')
                    function_name 
               , dep.name depends_on 
            FROM v$result_cache_dependency d 
               , v$result_cache_objects res 
               , v$result_cache_objects dep 
          WHERE res.id = d.result_id 
                AND dep.id = d.depend_id 
                AND res.name LIKE name_like_in)
   LOOP
      /* Не включать зависимости от себя */
      IF rec.function_name <> rec.depends_on 
      THEN
         DBMS_OUTPUT.put_line (
            rec.function_name || ' depends on ' || rec.depends_on);
      END IF;
   END LOOP;
END;
/

 

Детализированные зависимости в версии 11.2 и выше

Существенным улучшением версии 11.2 в отношении кэширования результатов функций стало детализированное отслеживание зависимостей. Oracle теперь запоминает, от каких таблиц зависит каждый набор кэшированных данных (значения аргументов IN и результат). Иначе говоря, разные строки кэшированных данных могут иметь разные наборы зависимых таблиц. При фиксации изменений в таблицах Oracle удаляет из кэша только те результаты, которые зависят от этих таблиц, — не обязательно весь кэш (как произошло бы в версии 11.1).

Как правило, функции, к которым применяется кэширование результатов, зависят от одних и тех же таблиц (не считая фактических значений, передаваемых для формальных параметров). В следующем примере зависимости могут изменяться:


CREATE TABLE tablea (col VARCHAR2 (2));

CREATE TABLE tableb (col VARCHAR2 (2));

BEGIN
   INSERT INTO tablea VALUES ('a1');
   INSERT INTO tableb VALUES ('b1');
   COMMIT;
END;
/

CREATE OR REPLACE FUNCTION dynamic_query (table_suffix_in VARCHAR2)
   RETURN VARCHAR2 
   RESULT_CACHE 
IS
   l_return VARCHAR2 (2);
BEGIN
   DBMS_OUTPUT.put_line ('SELECT FROM table' || table_suffix_in);

   EXECUTE IMMEDIATE 'select col from table' || table_suffix_in INTO l_return;

   RETURN l_return;
END;
/

Как видите, если передать суффикс а, функция получает строку из таблицы TABLEA, а при передаче b функция выполняет выборку из TABLEB. Теперь предположим, что был выполнен следующий сценарий, который использует приведенную выше процедуру для вывода изменений в кэше результатов и его зависимостей:


DECLARE
   l_value VARCHAR2 (2);
BEGIN
   l_value := dynamic_query ('a');
   show_frc_dependencies ('%DYNAMIC_QUERY%', 'After a(1)'); 

   l_value := dynamic_query ('b');
   show_frc_dependencies ('%DYNAMIC_QUERY%', 'After b(1)');

   UPDATE tablea SET col = 'a2';
   COMMIT;

   show_frc_dependencies ('%DYNAMIC_QUERY%', 'After change to a2'); 

   l_value := dynamic_query ('a');
   show_frc_dependencies ('%DYNAMIC_QUERY%', 'After a(2)');

   l_value := dynamic_query ('b');
   show_frc_dependencies ('%DYNAMIC_QUERY%', 'After b(2)');

   UPDATE tableb SET col = 'b2';
   COMMIT;

   show_frc_dependencies ('%DYNAMIC_QUERY%', 'After change to b2'); 
END;
/

Результат выполнения предыдущего сценария:

SELECT FROM tablea
After a(1): Dependencies for "%DYNAMIC_QUERY%"
HR.DYNAMIC_QUERY depends on HR.TABLEA

SELECT FROM tableb
After b(1): Dependencies for "%DYNAMIC_QUERY%" 
HR.DYNAMIC_QUERY depends on HR.TABLEB 
HR.DYNAMIC_QUERY depends on HR.TABLEA

After change to a2: Dependencies for "%DYNAMIC_QUERY%" 
HR.DYNAMIC_QUERY depends on HR.TABLEB

SELECT FROM tablea
After a(2): Dependencies for "%DYNAMIC_QUERY%" 
HR.DYNAMIC_QUERY depends on HR.TABLEB 
HR.DYNAMIC_QUERY depends on HR.TABLEA

After b(2): Dependencies for "%DYNAMIC_QUERY%" 
HR.DYNAMIC_QUERY depends on HR.TABLEB 
HR.DYNAMIC_QUERY depends on HR.TABLEA

After change to b2: Dependencies for "%DYNAMIC_QUERY%"
HR.DYNAMIC_QUERY depends on HR.TABLEA

Как видите, даже после внесения (и фиксации) изменений в строке одной таблицы кэшированный результат функции dynamic_query сбрасывается не полностью. Удаляются только строки, зависящие от этой конкретной таблицы.

 

Виртуальные приватные базы данных и кэширование результатов функций

При использовании в приложениях виртуальных приватных баз данных, или VPD, определяются политики безопасности операций SQL с таблицами. База данных Oracle затем автоматически добавляет эти политики в форме предикатов WHERE для ограничения строк, которые пользователь может запрашивать или изменять в конкретной таблице. Обойти эти политики невозможно, так как они применяются на уровне SQL — и остаются невидимыми для пользователя. Таким образом, пользователи, подключенные к двум разным схемам, могут выполнить одинаковый (на первый взгляд) запрос (например, SELECT last_name FROM employees) и получить разные результаты.

А пока рассмотрим пример тривиального использования VPD и возможности поставки некорректных данных о пользователе (весь код из этого раздела содержится в файле 11g_frc_vpd.sql). Предположим, я определяю следующий пакет с двумя функциями в схеме приложения: одна функция возвращает фамилию работника с заданным идентификатором, а другая используется как политика безопасности VPD:


PACKAGE emplullg 
IS
   FUNCTION last_name (employee_id_in IN employees.employee_id%TYPE)
      RETURN employees.last_name%TYPE 
      result_cache;

   FUNCTION restrict_employees (schema_in VARCHAR2, NAME_IN VARCHAR2)
      RETURN VARCHAR2;
END emplu11g;

PACKAGE BODY emplu11g 
IS
   FUNCTION last_name (employee_id_in IN employees.employee_id%TYPE)
      RETURN employees.last_name%TYPE 
      RESULT_CACHE RELIES_ON (employees)
   IS
      onerow_rec employees%ROWTYPE;
   BEGIN
      DBMS_OUTPUT.PUT_LINE ( 'Looking up last name for employee ID '
                              || employee_id_in );
      SELECT * INTO onerow_rec 
        FROM employees
      WHERE employee_id = employee_id_in;

      RETURN onerow_rec.last_name;
   END last_name;

   FUNCTION restrict_employees (schema_in VARCHAR2, NAME_IN VARCHAR2)
      RETURN VARCHAR2 
   IS
   BEGIN
      RETURN (CASE USER
                 WHEN 'HR' THEN '1 = 1'
                 ELSE '1 = 2'
              END
             );
   END restrict_employees;
END emplu11g;

Функция restrict_employees работает по очень простому принципу: при подключении к схеме HR видны все строки таблицы employees; в остальных случаях не видно ничего. Затем эта функция назначается политикой безопасности для всех операций с таблицей employees:

BEGIN
   DBMS_RLS.add_policy
                    (object_schema   => 'HR'
                   , object_name     => 'employees'
                   , policy_name     => 'rls_and_rc'
                   , function_schema => 'HR'
                   , policy_function => 'emplu11g.restrict_employees'
                   , statement_types => 'SELECT,UPDATE,DELETE,INSERT'
                   , update_check    => TRUE
                    );
END;

Затем схеме SCOTT предоставляется возможность выполнения пакета и выборки из таблицы:

GRANT EXECUTE ON emplu11g TO scott
/
GRANT SELECT ON employees TO scott
/

Прежде чем выполнять функцию, убедимся в том, что политика безопасности работает и влияет на данные, видимые схемам HR и SCOTT.

Я подключаюсь к схеме HR и успешно запрашиваю данные из таблицы employees:

SELECT last_name 
   FROM employees 
   WHERE employee_id = 198/
LAST NAME
----------------------------
OConnell

Теперь тот же запрос выполняется с подключением к SCOTT; обратите внимание на различия!

CONNECT scott/tiger@oracle11 
SELECT last_name 
   FROM hr.employees 
  WHERE employee_id = 198/
no rows selected.

Механизм VPD работает: при подключении к SCOTT я не вижу строки данных, видимые из HR. Теперь посмотрим, что произойдет при выполнении того же запроса из функции с кэшированием результатов, принадлежащей HR. Сначала я подключаюсь к схеме HR и выполняю функцию, после чего вывожу возвращенное имя:

BEGIN
   DBMS_OUTPUT.put_line (emplu11g.last_name (198));END;/
Looking up last name for employee ID 198 
OConnell

 

Обратите внимание на две строки выходных данных:

  1. Сообщение «“Looking up last name for employee ID 198» выводится потому, что функция была выполнена.
  2. Строка «OConnell» выводится потому, что строка данных была найдена, а функция вернула фамилию.

Теперь я подключаюсь к схеме SCOTT и выполняю тот же блок кода. Так как функция выполняет команду SELECT INTO, которая не должна возвращать строки, я ожидаю увидеть необработанное исключение NO_DATA_FOUND. Вместо этого...

SQL> BEGIN
   2    DBMS_OUTPUT.put_line (hr.emplu11g.last_name (198));END;/
OConnell

Функция успешно возвращает «OConnell», но обратите внимание: текст «Looking up...» не выводится. Это объясняется тем, что ядро PL/SQL не выполняет эту функцию (и вызов DBMS_OUTPUT. PUT_LINE внутри функции); оно просто возвращает кэшированную фамилию. И именно это обстоятельство делает комбинацию VPD с кэшированием результатов функций такой опасной. Так как функция сначала вызывалась с входным значением 198 от HR, фамилия, связанная с идентификатором, была кэширована для всех остальных сеансов, подключенных к тому же экземпляру. Таким образом, пользователь, подключенный к схеме SCOTT, видит данные, которые ему видеть не положено.

Чтобы убедиться в том, что при отсутствии кэширования функция действительно будет возвращать NO_DATA_FOUND, подключимся к HR и объявим содержимое кэша недействительным, зафиксировав изменение в таблице employees (подойдет любое изменение):

BEGIN
   UPDATE employees SET salary = salary * 1.5;
   COMMIT;END;/

Теперь при подключении к схеме SCOTT и выполнении функции я получаю необработанное исключение NO_DATA_FOUND:

SQL> BEGIN
   2    DBMS_OUTPUT.put_line (hr.empluiig.last_name (198));END;/
ORA-01403: no data found 
ORA-06512: at "HR.EMPLU11G", line 10 
ORA-06512: at line 3

Итак, если вы работаете над одним из относительно редких приложений, использующих технологию VPD, будьте крайне осторожны с функциями, использующими кэширование результатов.

 

Сводка способов кэширования

Если значение не изменялось с момента его последнего запроса, постарайтесь найти способ сведения к минимуму времени его выборки. Как было доказано за многие годы на примере SGA в архитектуре баз данных Oracle, технология кэширования данных играет важнейшую роль в оптимизации производительности. Прозрачное кэширование курсоров, блоков данных и т. д. демонстрирует, как организовать собственное кэширование или использовать непрозрачные кэши SGA (что потребует внесения изменений в код). Ниже приведена краткая сводка рекомендаций в области кэширования данных. Основные варианты:

  

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 14850 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 4652 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 12356 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Использование записей (records...
Использование записей (records... 19769 просмотров Алексей Вятский Thu, 05 Jul 2018, 07:49:43
Печать
Войдите чтобы комментировать