Наш эксперт рассказывает о серверном кеше результатов выполнения запросов и кеше результатов вычисления функций PL/SQL.
В течение следующих нескольких месяцев я буду изучать новые функциональные возможности, появившиеся в самой последней версии сервера базы данных Oracle Database - Oracle Database 11g. Их так много, что трудно выбрать, с чего же начать. Поэтому я поговорил с Брином Лльюеллином (Bryn Llewellyn), менеджером по PL/SQL-продуктам, и он сообщил мне о паре прекрасных новых возможностях в языках SQL и PL/SQL.
Кешируйте
Сервер Oracle Database 11g поднимает планку кеширования данных. В прошлом сервер Oracle Database кешировал блоки базы данных. Он мог кешировать эти блоки в различных местах, таких как пул по умолчанию, удерживающий буферный пул или рециклирующий буферный пул. Но он всегда кешировал блоки данных - строительные блоки, используемые для построения результирующих наборов.
Начиная с Oracle Database 11g, сервер может кешировать результирующие наборы! Если у вас есть запрос, который многократно обращается к медленно изменяющимся или никогда не изменяющимся данным, вы обнаружите, что новый серверный кеш результатов выполнения запросов (server results cache) вызовет чрезвычайный интерес. Практически любое приложение может и будет получать выгоду от использования этого кеша.
Если кратко, идея такова: когда вы выполняете запрос, сервер Oracle Database 11g сначала выяснит, а не были ли результаты этого запроса уже вычислены и кешированы другим сеансом или пользователем, если это так, то он извлечет результат из серверного кеша результатов, вместо того, чтобы снова с нуля собирать все нужные блоки базы данных и вычислять результат. Для первого выполнения запроса потребуется столько же времени, сколько и обычно (поскольку вычисляется ответ), а последующие выполнения будут мгновенными, поскольку ответ не вычисляется, а сразу же возвращается.
Я считаю, что это средство аналогично динамическим "материализованным представлениям" (just-in-time materialized view). В сервере Oracle8i Database Release 1 корпорация Oracle ввела понятие материализованных представлений. С помощью такого представления АБД мог создать таблицу итогов, почти так же, как он создавал индексы, и оптимизатор распознавал, что эта таблица итогов существует и, если возможно, использовал ее для ответа на запросы, вместо того, чтобы запрашивать и обрабатывать детальную информацию, которая хранится в базовых таблицах. Этот способ работы хорош, но он скорее статический, очень похожий на схему индексирования. Однако в сервере Oracle Database 11g с помощью серверного кеша результатов материализованные представления создаются и сопровождаются в сущности "на проходе". Этот кеш (динамическое материализованное представление) заполняется при необходимости, без какого-либо вмешательства АБД. В качестве примера я скопирую представление ALL_OBJECTS в таблицу:
SQL> create table t 2 as 3 select * 4 from all_objects; Table created.
Теперь я создам небольшую функцию, которая формирует отчет - выполняет запрос к этой таблице. Для четкой демонстрации отличий в производительности этого запроса я не буду ничего делать внутри цикла (обычно в нем я печатаю строки и выполняю некоторые вычисления):
SQL> create or replace procedure 2 my_function 3 as 4 begin 5 for x in 6 (select owner, 7 object_type, 8 count(*) cnt 9 from t 10 group by owner, object_type 11 order by owner, object_type ) 12 loop 13 -- do_something 14 null; 15 end loop; 16 end; 17 / Procedure created.
Теперь я три раза выполню эту процедуру, засекая время каждого выполнения:
SQL> set timing on SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:01.54 SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.10 SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.11 SQL> set timing off
Обратите внимание, для первого выполнения потребовалось существенное время, около 1.5 секунд, из-за физического ввода-вывода, необходимого для чтения данных с диска в обычный буферный кеш. Для второго и третьего выполнения потребовалось намного меньше времени, около 0.1 секунды, поскольку блоки, необходимые для получения ответа запроса, были найдены в кеше, а не на диске. Сравним это с тем, что произойдет, если я к запросу добавлю подсказку "cache the results" (кешировать результаты):
SQL> create or replace procedure 2 my_function 3 as 4 begin 5 for x in 6 (select /*+ result_cache */ 7 owner, 8 object_type, 9 count(*) cnt 10 from t 11 group by owner, object_type 12 order by owner, object_type ) 13 loop 14 -- do_something 15 null; 16 end loop; 17 end; 18 / Procedure created. SQL> set timing on SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.10 SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> set timing off
Обратите внимание, для первого выполнения потребовалось около 0.1 секунды, поскольку для этого первого выполнения ответ нужно было компоновать, а последующие выполнения были чертовски быстрыми - иногда такими быстрыми, что казались мгновенными.
Еще хорошая вещь об этом - этот кеш автоматически делается недействительным и обновляется сервером базы данных, этот процесс совершенно прозрачен для приложения. Приложение не должно заботиться об устаревших или недействительных результатах. Например, если я обновлю одну строку, изменив, таким образом, результаты:
SQL> update t 2 set owner = lower(owner) 3 where rownum = 1; 1 row updated. SQL> commit; Commit complete. - то я вижу следующее: SQL> set timing on SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.10 SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> set timing off
Обратите внимание, что после выполнения оператора UPDATE для первого выполнения запроса снова потребовалось около 0.1 секунды, поскольку нужно было строить новый ответ. От этой работы получают выгоды последующие выполнения, которые кажутся мгновенными.
Я уверен, что, если вы удобно усядетесь и поразмышляете о ваших собственных приложениях, вы сможете найти более одного места, где пригодится серверный кеш результатов. Этот кеш предоставляет собой многие из преимуществ некоторых материализованных представлений, но не требует настройки и административных накладных расходов.
Постойте, это ещё не все...
Как говорят в ночных телепрограммах: "Следующий сюжет удивит вас ещё больше!" В сервере Oracle Database 11g есть также новый кеш результатов вычисления функций PL/SQL (PL/SQL function results cache). Если в вышерассмотренном серверном кеше результатов кешируются результирующие наборы SQL-операторов, то в этом новом кеше кешируются результаты вызовов функций и процедур PL/SQL.
Раньше, если вы вызывали PL/SQL-функцию 1 000 раз и каждый вызов потреблял 1 секунду, то для 1 000 вызовов требовалось 1 000 секунд. В зависимости от входных данных и изменения используемых данных, которые находятся в базе данных, кеш результатов вычисления функций позволяет выполнить 1 000 вызовов функции всего приблизительно за 1 секунду. Будет полезен небольшой пример: я создам две функции, они идентичны, за исключением имен и опций параметров компилятора. Обе они обращаются к ранее созданной таблице T:
SQL> create or replace 2 function not_cached 3 ( p_owner in varchar2 ) 4 return number 5 as 6 l_cnt number; 7 begin 8 select count(*) 9 into l_cnt 10 from t 11 where owner = p_owner; 12 dbms_lock.sleep(1); 13 return l_cnt; 14 end; 15 / Function created. SQL> create or replace 2 function cached 3 ( p_owner in varchar2 ) 4 return number 5 result_cache 6 relies_on(T) 7 as 8 l_cnt number; 9 begin 10 select count(*) 11 into l_cnt 12 from t 13 where owner = p_owner; 14 dbms_lock.sleep(1); 15 return l_cnt; 16 end; 17 / Function created.
Единственное различие в этих функциях (исключая их имена) - параметры компилятора: RESULT_CACHE (кеш результатов) и RELIES_ON (основан на). Директива RESULT_CACHE указывает серверу Oracle Database, что вы хотите сохранить ответы этой функции, так что, если потом кто-нибудь вызовет ее с такими же входными параметрами, то код этой функции не будет выполнятся, а сразу же будет выдаваться уже известный ответ. Предложение RELIES_ON указывает серверу базы данных, когда делать недействительным значение кеша результатов вычисления этой функции - в данном случае при модификации таблицы T (в этом случае изменяется ответ моей кешированной функции, поэтому его нужно вычислить снова). Обратите внимание, для большего эффекта я обе функции перевожу в состояние односекундного ожидания, оно позволяет сделать более заметными отличия реальных вызовов функции от повторного использования результатов.
Я начну с трехкратного вызова обычной (некешированной) функции с включенным таймированием:
SQL> exec dbms_output.put_line( not_cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:01.93 SQL> exec dbms_output.put_line( not_cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:01.29 SQL> exec dbms_output.put_line( not_cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:01.07
Как видите, для каждого вызова требуется по меньшей мере одна секунда - работа самой функции плюс выполнение ею SQL-оператора. Теперь я испытаю кешированную версию этой функции:
SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:01.09 SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
Как видите, для первого выполнения потребовалось немного больше секунды, примерно также как и для некешированной версии, а последующие вызовы выполнялись со скоростью света - просто потому, что на самом деле кешированная функция не выполнялась. Если я модифицирую используемую в функции таблицу или изменю использованные мной входные данные, я могу увидеть, что сервер базы данных делает то, что нужно:
SQL> update t set owner = initcap(owner) where rownum = 1; 1 row updated. SQL> commit; Commit complete. SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:01.25
Для первого выполнения кешированной функции потребовалось больше секунды, поскольку она должна обновить кеш результатов, а последующие выполнения получают выгоды, используя этот кешированный результат:
SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
Чтобы показать, что кеш результатов вычисления функций PL/SQL понимает, что изменение входных данных приводит к изменению выходных данных, я могу вызвать кешированную функцию с другим именем пользователя:
SQL> exec dbms_output.put_line( cached( 'SYS' ) ); 29339 PL/SQL procedure successfully completed. Elapsed: 00:00:01.21 SQL> exec dbms_output.put_line( cached( 'SYS' ) ); 29339 PL/SQL procedure successfully completed. Elapsed: 00:00:00.01
Обратите внимание, для первого выполнения потребовалось относительно много времени (для построения кеша результатов), а последующее выполнение было быстрым. Вызов этой функции с другим именем пользователя не делает недействительными другие кешированные результаты:
SQL> exec dbms_output.put_line( cached( 'SCOTT' ) ); 6 PL/SQL procedure successfully completed. Elapsed: 00:00:00.00
Вызовы функции с входным параметром SCOTT будут выполняться быстро до тех пор, пока кеш не должен будет сделан недействительным или же не закончиться выделенная для него память, которая понадобилась для других вызовов.
Кроме того, эту возможность можно реализовать без основательного изменения архитектуры приложений - фактически, совсем без изменения архитектуры. Эта возможность может быть активирована параметром компилятора, а выгоды будет получать любой клиент, который вызывает эту функцию. Например, неофициальные тесты в среде Oracle Application Express показали примерно 15-процентное уменьшение времени выполнения - конечно, у вас могут получиться другие результаты тестировани!
Если WHEN OTHERS, то ничего не возвращается
В выпуске этой колонки за июль-август 2007 (русский перевод: "Том Кайт: об игнорировании, блокировании и разборах") я писал о программной конструкции языка PL/SQL ¬- обработчике исключительных ситуаций WHEN OTHERS. Я писал: "В языке PL/SQL есть крайне нежелательная для меня программная конструкция WHEN OTHERS."
Я знаю, что корпорация Oracle никогда не будет устранять эту особенность языка, но сейчас она сделана более безопасной. Только чтобы напомнить, почему я хочу, чтобы эта программная конструкция не существовала, я снова процитирую себя:
Проблема заключается в том, что многие используют предложение WHEN OTHERS без последующего инициирования исключительных ситуаций (вызовы RAISE или RAISE_APPLICATION_ERROR). Это фактически скрывает ошибку. На самом деле ошибка происходит, но она не обрабатывается каким-то осмысленным образом, а просто игнорируется - молча. Вызывающий вашего кода не имеет никакого понятия, что случилось нечто чрезвычайное и ваш код сбился, обычно он думает, что на самом деле все работает успешно.
Много раз, очень много раз, нежели я могу вспомнить или сосчитать, причиной "странного" поведения программных модулей PL/SQL оказывалось ненадлежащее использование предложения WHEN OTHERS - за ним не следовал вызов RAISE. Ошибка скрывается, обработка ошибок фактически отключается, а в результате данные обычно логически разрушаются (приложение не делает свою работу) или получается неверный ответ.
Теперь в сервере Oracle Database 11g я легко могу найти кусок проблемного кода. Когда я полагаю, что была инициирована и скрыта исключительная ситуация, я быстро могу проверить мое подозрение. Рассмотрим следующую безобидную процедуру:
SQL> create table t( x varchar2(4000) );
Table created.
SQL> create or replace
2 procedure maintain_t
3 ( p_str in varchar2 )
4 as
5 begin
6 insert into t
7 ( x ) values
8 ( p_str );
9 exception
10 when others
11 then
12 -- call some log_error() routine
-- вызов некоторой подпрограммы протоколирования ошибок
13 null;
14 end;
15 /
Procedure created.
Она настолько проста, насколько это возможно. Ничего не должно работать неправильно, но для случаев, когда происходит ошибка, я протоколирую ее, используя написанную мною вспомогательную процедуру. Где-то она пишет хорошее сообщение об ошибке (будем надеяться!), а, случается, что кто бы не вызвал эту подпрограмму, он не будет иметь понятия, что произошла непредвиденная исключительная ситуация, поэтому она не может быть локализована. (Я постоянно встречаю подобный код.) Теперь, когда кто-то вызовет эту подпрограмму:
SQL> exec maintain_t( rpad( 'x', 4001, 'x' ) ); PL/SQL procedure successfully completed.
Ее выполнение кажется успешным, но это не так:
SQL> select * from t; no rows selected
Здесь и начинается неразбериха: пользователи "обрывают" телефон, пишут электронные письма, они говорят: "Сломался сервер Oracle Database, транзакция завершилась успешно, однако данные - некорректные". На самом деле, проблема заключается в скрытой ошибке. Теперь в среде сервера Oracle Database 11g для обнаружения таких ошибок я просто буду просить людей сначала выполнить над их кодом следующие действия:
SQL> alter procedure maintain_t compile
2 PLSQL_Warnings = 'enable:all'
3 reuse settings
4 /
SP2-0805: Procedure altered with
compilation warnings
SQL> show errors procedure maintain_t
Errors for PROCEDURE MAINTAIN_T:
LINE/COL ERROR
---------- ---------------------------------------
9/8 PLW-06009: procedure
"MAINTAIN_T" OTHERS handler
does not end in RAISE or
RAISE_APPLICATION_ERROR
И тотчас же вы извлечете список приложений (и строки исходного кода), чтобы незамедлительно добавить простые вызовы RAISE, которые позволят увидеть, откуда поступает эта скрытая ошибка. Работа сделана.
Пустячок, а приятно. . .
Недаром говорят - пустячок, а приятно. Есть у меня напоследок одна мелочь, которая немного упрощает вашу программистскую жизнь, а язык PL/SQL делает немного более совершенным:
SQL> create sequence this_is_nice; Sequence created. SQL> declare 2 n number; 3 begin 4 n := this_is_nice.nextval; 5 dbms_output.put_line( n ); 6 end; 7 / 1 PL/SQL procedure successfully completed.
Сравните это с тем, как такое нужно было делать в сервере Oracle Database 10g и предыдущих версиях. В сервере Oracle Database 11g не нужно больше выбирать последовательность из таблицы DUAL. Так что, язык PL/SQL стал намного более совершенным.