Январь/Февраль 2004
Профессионалу администратору
Том Кайт
Том Кайт: о процедурах, очистке кешей и записях
(On Procedures, Flushes, and Writes, by Tom Kyte)
Источник: журнал Oracle Magazine, July-August 2003,
(http://otn.oracle.com/oramag/oracle/03-jul/o43asktom.html).
Наш эксперт сравнивает триггеры и процедуры, очистку кеша и не очистку.
Вопрос: Предположим, часть кода может быть реализована либо как триггер, либо как процедура. Какой способ лучше в смысле скорости выполнения и почему?
Ответ: Ответ на первую часть вопроса: вызов пакетной процедуры либо непосредственный, либо из триггера. SQL-код пакетов и процедур будет кешироваться для всего вашего сеанса, что уменьшит количество его разборов. SQL-код внутри триггера кешируется только на время выполнения оператора, запустившего триггер, что приводит к дополнительным мягким разборам при выполнении триггера. Практический вывод из этого: размещать логику в пакетных PL/SQL-процедурах и вызывать их из триггера.
Я могу продемонстрировать это поведение с помощью простого моделирования. В реальном окружении я использую PL/SQL-пакет, но здесь, в демонстрационных целях, я буду использовать небольшую автономную процедуру. Я создам также таблицу и триггер для этой таблицы. В триггере будет выполняться SQL-запрос, который я могу легко распознать в отчете утилиты TKPROF, и в триггере также будет вызываться автономная процедура. В этой автономной процедуре будет выполняться такой же SQL-запрос, что и в триггере, и снова он может быть распознан а отчете TKPROF, как исходящий из процедуры. Пример, следовательно, начинается с создания таблицы и кода процедуры, которая будет вызываться:
SQL> create table t ( x int );
Table created.
SQL> create or replace procedure p
2 as
3 l_cnt int;
4 begin
5 select count(*) into l_cnt
6 from dual IN_PROCEDURE;
7 end;
8 /
Procedure created.
Заметим, я "пометил" SQL-запрос псевдонимом таблицы DUAL – IN_PROCEDURE. Это поможет мне распознать его в отчете TKPROF. Теперь создадим триггер для нашей таблицы:
SQL> create or replace trigger t_trig
2 before insert on t for each row
3 declare
4 l_cnt number;
5 begin
6 select count(*) into l_cnt
7 from dual IN_TRIGGER;
8 p;
9 end;
10 /
Trigger created.
Сейчас я готов к тестированию. Все что я хочу протестировать – это включить трассировку (SQL_TRACE), а затем выполнить несколько вставок в таблицу T. Здесь для краткости я показываю только две операции вставки, на самом деле я выполнил первую вставку одиночной строки шесть раз, а вторую вставку множественных строк один раз:
SQL> alter session set sql_trace=true;
Session altered.
SQL> insert into t values ( 1 );
1 row created. Repeated 6 times
SQL> insert into t
2 select rownum from all_users;
32 rows created.
Теперь, когда я просматриваю отчет TKPROF, я замечаю следующее:
select count(*)
from dual IN_TRIGGER;
call count ...
------ ----- ...
Parse 7 ...
Execute 38 ...
Fetch 38 ...
------ ----- ...
total 83 ...
select count(*)
from dual IN_PROCEDURE;
call count ...
------ ----- ...
Parse 1 ...
Execute 38 ...
Fetch 38 ...
------ ----- ...
total 77 ...
Обратите внимание, счетчик разборов для запроса, помеченного как IN_TRIGGER, равен 7, из которых 6 разборов приходится на вставки одиночных строк, а еще один разбор – на вставку множественных строк. Каждый вызов, каждый оператор, запускающие триггер, являются причиной мягкого разбора всех SQL-операторов в теле этого триггера. Однако SQL-оператор, помеченный как IN_PROCEDURE, не показывает такого же поведения. Он был разобран для моего сеанса один раз и машина PL/SQL будет сохранять его для меня кешированным столько, сколько это возможно, многократно используя этот кешированный курсор. Для интенсивного выполнения разборов (даже мягких разборов) не только потребляются ресурсы (такие, как время ЦП), но также требуются защелки разделяемого пула, что со временем приведет к возникновению проблем производительности и масштабируемости.
Очистка кеша
Вопрос: Оператор ALTER SYSTEM FLUSH SHARED_POOL выполняет очистку разделяемого пула, а не буферов блоков. Есть ли способ очистки и их тоже? Это важно для настройки, когда один за другим испытываются несколько методов, но нам хотелось бы уменьшить влияние уже загруженных в буфер блоков без перезагрузки экземпляра СУБД.
Ответ: Фактически, важно, что инструментальные средства настройки не делают этого. Важно выполнить прогон теста, игнорируя результаты, а затем выполнить два или три его прогона и вычислить средние величины их результатов. В реальном окружении кеш буферов никогда не будет влиять на результаты тестирования. Никогда. Во время настройки ваша цель – уменьшить количество операций логического ввода-вывода (LIO, logical I/O), потому что в таком случае физический ввод-вывод (PIO, physical I/O) позаботится о себе сам.
Учтите следующее: очистка разделяемого пула и кеша буферов – гораздо более искусственное явление, чем их не очистка. Большинство скептически относится к этому, я полагаю, из-за того, что это бросает вызов здравому смыслу. Я покажу, как делать это, но не для того, чтобы вы использовали это для тестирования. Скорее я буду использовать это, чтобы продемонстрировать, почему это – бесплодная и совершенно искусственная попытка (и поэтому приводит к неправильным предположениям). Я только что перезапустил мой ПК и выполнил следующий запрос к большой таблице. Я "очищаю" кеш буферов и выполняю запрос снова:
SQL> set autotrace traceonly statistics
SQL> set timing on
SQL> select count(data_object_id)
2 from big_table real_pio;
1 row selected.
Elapsed: 00:00:07.06
Statistics
--------------------------------
13720 consistent gets
13646 physical reads
Итак, очевидно, чтобы в данной системе выполнить 13,646 PIO, требуется около семи секунд. Не так ли? Я могу заставить Oracle избавиться от кешированных блоков табличного пространства, просто переведя его в автономный режим (offline), а затем вновь вернуть его в оперативный режим (online):
SQL> alter tablespace users offline;
Tablespace altered.
SQL> alter tablespace users online;
Tablespace altered.
SQL> select count(data_object_id)
2 from big_table fake_pio;
1 row selected.
Elapsed: 00:00:01.06
Statistics
--------------------------------
13656 consistent gets
13646 physical reads
Итак, как я согласую это? Неожиданно, в данной системе для выполнения 13,646 PIO потребовалась одна секунда. Даже если я между прогонами теста остановлю и вновь запущу экземпляр СУБД, я по-прежнему буду наблюдать это странное поведение (я знаю это, потому что проверял). Здесь случилось то, что сама операционная система имеет кеш файловой системы, и PIO, которые, как вы думаете, вы выполняете, оказались ненастоящими. Для того чтобы выполнить этот запрос снова за семь секунд, мне нужно либо что-то сделать с моей системой для полной очистки буферного кеша файловой системы ОС, либо перезагрузить компьютер – ничего этого в реальном окружении не происходит.
Интересно, как этот практический вопрос с кешем файловой системы ОС влияет на нас. Здесь он дает нам скошенное представление о производительности нашего запроса. Некоторые запросы, выполняющие 100 PIO, буду казаться более медленными по сравнению с некоторыми запросами, выполняющими 10,000 PIO, в зависимости от содержимого кеша файловой системы ОС. Аналогичная проблема возникает, когда решается вопрос о переносе всех данных из обычных файловых систем на диски без файловой системы – "чистые" устройства (raw devices).
Мотивация здесь следующая: каждый знает, “"чистые" устройства быстрые” и “"чистые" устройства быстрее обычных файловых систем”. Пользователи наблюдают за своими собственными системами и замечают, что они выполняют массу PIO. В таком случае здравый смысл подсказывает: “"чистые" устройства быстрее обычных файловых систем, поэтому нужно перейти на них и система будет более быстрой”.
Хорошо, на следующий день после перехода на "чистые" устройства производительность большинства таких систем будет ужасной – намного более низкой, чем до этого. Что случилось? В их системах была двойная буферизация. Был кеш буферов Oracle и был кеш файловой системы ОС. Может быть 90 процентов их PIO удовлетворялось буферным кешем ОС. Так, только 10 процентов их PIO реально приходилось на диски. Когда они перешли на "чистые" устройства они отказались от буферного кеша ОС, так что сейчас 100 процентов их PIO – реальные PIO; они действительно обращаются к дискам. В результате производительность падает.
Итак, хотя вы можете очищать кеш буферов Oracle при нагрузочном тестировании, этого делать не следует. Если только вы не хотите получить вводящие в заблуждение результаты.
Вставка и обновление записей с помощью типа записи
Вопрос: Я использую новую функциональную возможность Oracle9i Release 2, которая позволяет вставлять и обновлять записи с помощью типа записи в PL/SQL. Он не позволяет мне задавать имена столбцов в операторах INSERT/UPDATE. Нормально ли это?
Этот вопрос относится к новой возможности Oracle9i Release 2, которая позволяет вам выполнять операции, используя тип записи в PL/SQL, вместо явного указания списка столбцов. Например, предположим у вас есть простая таблица:
SQL> create table t
2 ( x varchar2(5),
3 y varchar2(5) );
Table created.
В Oracle9i Release 2 вы можете создавать и обновлять записи, используя следующую нотацию:
SQL> declare
2 l_rec t%rowtype;
3 begin
4 l_rec.x := 'x val';
5 l_rec.y := 'y val';
6
7 insert into t
8 values l_rec;
9
10 l_rec.x := 'X VAL';
11 update t
12 set row = l_rec;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL> select * from t;
X Y
----- -----
X VAL y val
В предыдущих версиях вы должны были программировать: INSERT INTO T (X,Y) VALUES ( L_REC.X, L_REC.Y ), что может быть утомительным делом для таблиц с большим количеством столбцов. Так что этот сокращенный код работает прекрасно. Если вы хотите явно задавать список в операторах INSERT (или UPDATE), вам нужно использовать вложенное (inline) представление. Я продемонстрирую эту технику, но я буду также использовать ее, чтобы показать, почему вы можете не захотеть делать это, и укажу важный факт, относящийся к этой новой функциональной возможности. Факт заключается в том, что вставки и обновления выполняются позиционно, а не по именам. Если вы явно зададите список столбцов в операторе INSERT/UPDATE, позднее вы можете быть наказаны за это. Сначала я покажу синтаксис, как это делать:
SQL> declare
2 l_rec t%rowtype;
3 begin
4 l_rec.x := 'x val';
5 l_rec.y := 'y val';
6 by design
7 insert into
8 (select x, y from t)
9 values l_rec;
10
11 l_rec.x := 'X VAL';
12 update
13 (select x, y from t)
14 set row = l_rec;
15 end;
16 /
PL/SQL procedure successfully completed.
SQL> select * from t;
X Y
------ -------
X VAL y val
Обратите внимание, как я модифицирую вложенное представление. Здесь я могу явно перечислить столбцы по порядку. Последние мои слова – ключевые: по порядку. Рассмотрим, что случится, если я в показанном выше представлении поменяю местами X и Y:
SQL> declare
2 l_rec t%rowtype;
3 begin
4 l_rec.x := 'x val';
5 l_rec.y := 'y val';
6
7 insert into
8 (select y, x from t)
9 values l_rec;
10
11 l_rec.x := 'X VAL';
12 update
13 (select y, x from t)
14 set row = l_rec;
15 end;
16 /
PL/SQL procedure successfully completed.
SQL> select * from t;
X Y
----- -----
y val X VAL
Данные для столбца X оказались в столбце Y, а данные для столбца Y – в X. Это умышленно – эти записи применяются к таблице позиционно, по порядку атрибутов записей. По этой причине я не думаю, что вы захотите использовать эту технику, даже если вы зададите имена столбцов в "правильном" порядке – кто-то когда-нибудь удалит таблицу и повторно создаст ее оператором CREATE TABLE T (Y INT, X INT), изменив таким образом порядок столбцов, и ваш явный код будет работать не правильно. Если, с другой стороны, вы используете только оператор INSERT INTO T VALUES L_REC, то после уничтожения и повторного создания таблицы сервер базы данных перекомпилирует ваш код и все "исправит" за вас. Значение X будет попадать в столбец X, а значение Y – в столбец Y. Итак, это тот случай, когда явное не является хорошим решением.
Это также указывает, если вы для объявления записей используете конструкцию ИМЯ_ТАБЛИЦЫ%ROWTYPE, вы должны использовать ее с учетом особенностей этой новой функциональной возможности. Если же вы определите свой собственный тип записи и кто-то добавит, удалит или поменяет местами столбцы в таблице, ваш существующий код сломается, возможно, тихо.
Говоря "тихо", я имею в виду, что во время выполнения код не выдаст ошибку; он "молча" будет помещать данные в неправильные столбцы. Такой вид ошибок очень сложно обнаружить и локализовать.
Как запрограммировать данный запрос
Вопрос: Следующий оператор представляет собой пример того, что я хочу делать:
select ename from bonus
where sal = nvl(:v, sal);
Не обращая внимания, зачем я хочу это делать, что наиболее предпочтительно: a) иметь два оператора, один используется, когда переменная связывания :v имеет значение NULL, другой – в противном случае; b) динамически создавать тип REF CURSOR; c) оставить все как есть; d) сделать что-то другое?
Ответ: Этот вопрос показывает, что можно изучать что-то новое каждый день. Моей первоначальной реакцией было указать, что правильными ответами являются A и B, однако тестирование показало, что в настоящее время правильный ответ – C! A и B были правильными ответами, но сейчас правильный ответ – C. Вот почему я всегда пробую предоставить набор тестов, демонстрирующий мои утверждения, так как со временем могут происходить и действительно происходят изменения.
Когда я думал, что A и B – правильные ответы, я использовал мое знание, что при выполнении запросов в основном используется оптимизатор по синтаксису (RBO, rule-based optimizer). Когда я подготовил и выполнил тест, чтобы показать, насколько ответы A и B лучше по сравнению с C, я обнаружил, что A и B сейчас хуже чем C, когда используется оптимизатор по стоимости (CBO, cost-based optimizer)! Чтобы показать это, я создаю очень простую, но большую таблицу:
SQL> create table t
2 ( acc_nbr number,
3 data char(255),
4 constraint t_pk
5 primary key(acc_nbr) );
Table created.
SQL> insert into t
2 select rownum, 'x'
3 from all_objects;
29882 rows created.
SQL> analyze table t compute statistics
2 for table
3 for all indexes
4 for all indexed columns;
Table analyzed.
А сейчас я буду тестировать эффективность различных подходов. Я буду выполнять два запроса, отличающиеся только используемым оптимизатором. Запрос выглядит так: SELECT COUNT(*) FROM T WHERE ACC_NBR = nvl(:n,ACC_NBR). Я буду выполнять его с подсказкой /*+ RULE */ и без нее, и буду выполнять каждый вариант запроса с переменной связывания, имеющей значение NULL, а также с переменной связывания, имеющей числовое значение. Результаты выполнения я буду сравнивать с помощью SQL_TRACE и TKPROF. Начиная с запроса, использующего CBO и переменную связывания, имеющую значение NULL, я обнаруживаю следующее:
select count(*)
from t n_is_null_cbo
where acc_nbr = nvl(:n,acc_nbr);
call count cpu elapsed query
----- ----- ---- -------- ------
total 4 0.23 0.27 62
Rows Row Source Operation
------ ---------------------------
1 SORT AGGREGATE
29882 CONCATENATION
29882 FILTER
29882 INDEX FAST FULL SCAN T_PK
0 FILTER
0 INDEX UNIQUE SCAN T_PK
Обратите внимание, план выполнения почти похож на два плана в одном: в нем имеется операция INDEX FAST FULL SCAN (быстрый полный просмотр индекса), используемая, когда переменная связывания имеет значение NULL, и операция INDEX UNIQUE SCAN (выборка ROWID из индекса по уникальному ключу), которая, в данном случае, не выполняется совсем. План был сконструирован таким образом, что во время выполнения, когда N имела значение NULL, последняя часть плана не должна была выполняться. Я получил ответ в данном случае с помощью эффективной операции INDEX FAST FULL SCAN, выполняющей просмотр небольшого индекса по первичному ключу. Сравним это с запросом, использующим RBO:
select /*+ RULE */ count(*)
from t n_is_null_rbo
where acc_nbr = nvl(:n,acc_nbr);
call count cpu elapsed query
----- ----- ---- -------- ------
total 4 0.10 0.13 1131
Rows Row Source Operation
------ ----------------------
1 SORT AGGREGATE
29882 TABLE ACCESS FULL T
Здесь оптимизатор RBO действует довольно упрощенно. Он не беспокоится о том факте, что можно использовать индекс, выполнив над ним операцию INDEX FAST FULL SCAN (этот путь доступа используется только в CBO). Кроме того, он считает, что "один план подходит для всего". Этот запрос выполняет полный просмотр таблицы, безотносительно к значению :N, установленному во время выполнения. Простое сравнение эффективности выполнения операции INDEX FAST FULL SCAN, используемой CBO (62 операции логического ввода-вывода (столбец query)), и операции TABLE ACCESS FULL, используемой RBO (1,131 операций логического ввода-вывода), уже показывает большое преимущество CBO. Но он работает еще лучше.
Теперь я устанавливаю в переменной связывания :N число 55 и повторно выполняю оба запроса.
Здесь я наблюдаю следующее:
select count(*)
from t n_is_55_cbo
where acc_nbr = nvl(:n,acc_nbr);
call count cpu elapsed query
----- ----- ---- -------- ------
total 4 0.00 0.00 2
Rows Row Source Operation
---- -----------------------------
1 SORT AGGREGATE
1 CONCATENATION
0 FILTER
0 INDEX FAST FULL SCAN T_PK
1 FILTER
1 INDEX UNIQUE SCAN T_PK
Обратите внимание, в данном случае часть INDEX FAST FULL SCAN плана выполнения запроса не выполняется совсем. Это ясно не только из-за того, что счетчик строк в плане запроса равен нулю, но и из-за снижения количества операций логического ввода-вывода – в данном случае для выполнения операции INDEX UNIQUE SCAN потребовалось только 2 операции логического ввода-вывода. Теперь сравнивая то же самое с запросом, использующим RBO и :N = 55, я наблюдаю следующее:
select /*+ RULE */ count(*)
from t n_is_55_rbo
where acc_nbr = nvl(:n,acc_nbr)
call count cpu elapsed query
----- ----- ---- -------- ------
total 4 0.10 0.13 1131
Rows Row Source Operation
------ ----------------------
1 SORT AGGREGATE
1 TABLE ACCESS FULL OBJ#
Это предсказуемо. Независимо от входных данных этот запрос работает одинаково: каждый раз полный просмотр всех 1,131 блоков. Итак, из данного вопроса можно извлечь два факта. Один из этих фактов: все изменяется и вы каждый день узнаете что-то новое. Другой факт: CBO дает большие преимущества, как только вы начнете использовать его.
В CBO доступны эффективные пути доступа, которые просто отсутствуют в RBO. Разумные, усовершенствованные планы запросов – подобные показанному выше – выдает только CBO. Даже более убедительным, возможно, является факт, что версии после Oracle9i Release 2 не будут включать оптимизатор по синтаксису.
Распределенные запросы только для чтения
Вопрос: Мы используем нашу резервную базу данных в режиме только для чтения. Почти все пользователи могут выполнять запросы только для чтения в лучшем виде. Однако один пользователь, имеющий связь баз данных с другой базой данных, не может выполнять запросы только для чтения. Он получает ошибку ORA-16000 "database open for read-only access" (база данных открыта только для чтения). Целевая база данных, с которой он соединяется, находится в режиме "чтение-запись". Временные файлы данных исходной базы данных находятся в режиме "чтение-запись. Не могли бы вы просветить нас, что делается неправильно?
Ответ: Распределенные средства СУБД начинают вашу транзакцию "на всякий случай" как распределенную. Если вы перед выполнением запросов установите режим выполнения транзакции только для чтения, вы можете изменить это поведение. Выполните оператор SET TRANSACTION READ ONLY перед выполнением каких-либо других операторов, и все ваши распределенные запросы будут выполняться успешно.
Однако, если вы попытаетесь сделать это, соединившись как пользователь SYS, вы по-прежнему будете получать ошибку ORA-16000. Дело в том, что пользователь SYS никогда не может находиться в режиме выполнения транзакций только для чтения. Но предложенное решение работает для всех "нормальных" пользователей, и все мы знаем, что мы не должны использовать SYS для чего-то ни было!
Ведущий данной колонки Том Кайт (Tom Kyte
thomas.kyte@oracle.com) с 1993 года работает в Oracle. Кайт – вице-президент Oracle Government, Education, and Health group, он автор книги “Expert One on One: Oracle”( Прим. пер. Имеется русский перевод: Oracle для профессионалов. Книга 1. Архитектура и основные особенности. Книга 2. Расширение возможностей и защита. – ДиаСофт, 2003 г.) и соавтор книги “Beginning Oracle Programming” – “Введение в программирование баз данных Oracle” (обе книги были опубликованы издательством Wrox Press, www.wrox.com).
| 
