Наш эксперт рассматривает программную конструкцию WHEN OTHERS, блокирование, каскадные обновления и разборы.
В языке PL/SQL есть крайне нежелательная для меня программная конструкция WHEN OTHERS. Когда это предложение используется в блоке обработки исключительных ситуаций, то захватываются все необработанные исключительные ситуации. Иногда это предложение полезно, например, для протоколирования ошибок:
exception
when others
then
log_error(....);
raise;
end;
Проблема заключается в том, что многие используют предложение WHEN OTHERS без последующего инициирования исключительных ситуаций (вызовы RAISE или RAISE_APPLICATION_ERROR). Это фактически скрывает ошибку. На самом деле ошибка происходит, но она не обрабатывается каким-то осмысленным образом, а просто игнорируется - молча. Вызывающий вашего кода не имеет никакого понятия, что случилось нечто чрезвычайное и ваш код сбился, обычно он думает, что на самом деле все работает успешно.
На сайте Ask Tom (asktom.oracle.com) я беспрестанно получаю вопросы об этом. Например, недавно я получил вопрос:
Я создал пакет, который запускает 10 заданий для массовой загрузки данных в плоский файл. Мне нужно использовать пакет UTL_FILE, поскольку в середине процесса я вызываю три процедуры, которые извлекают некоторые данные. Общее время массовой загрузки 9 500 000 строк - шесть часов. Мне нужен ваш совет, как снизить это время.
Мой код:
PROCEDURE prcl_MakeFile(...)
IS
... variables ...
BEGIN
l_FileID := UTL_FILE.FOPEN (...);
OPEN cur;
LOOP
... здесь обработка записей ...
... много кода ...
END LOOP;
CLOSE cur;
UTL_FILE.FCLOSE(l_FileID);
EXCEPTION
WHEN OTHERS THEN
IF (UTL_FILE.IS_OPEN(l_FileID))
THEN UTL_FILE.FCLOSE(l_FileID);
END IF;
END prcl_MakeFile;
Мой ответ был простым: я могу беспредельно убыстрить этот код. Все, что должна делать эта процедура:
PROCEDURE prcl_MakeFile(...)
IS
... variables...
BEGIN
Return;
END prcl_MakeFile;
Эти две процедуры логически эквивалентны, но моя работает намного быстрее! Итак, почему же они логически эквивалентны? Из-за предложения WHEN OTHERS, за котором не следует вызов RAISE или RAISE_APPLICATION_ERROR. Предположим, при вызове UTL_FILE .FOPEN возникает ошибка - что тогда произойдет? Весь код будет пропущен, но никто не узнает об этом. Когда в блоке обработки исключительных ситуаций используется предложение WHEN OTHERS, а повторное инициирование исключительной ситуации отсутствует, весь код по-моему мнению можно безболезненно удалить. Он же вам не нужен, поскольку вы игнорируете тот факт, что этот код не выполняется, если при его выполнении возникает ошибка. Если вы допускаете, что код иногда может не выполняться, вы фактически можете разрешить этому коду никогда не выполняться. Вы даже не можете полагаться, что этот код на самом деле работает, поэтому вам никогда не нужно выполнять его.
Я также утверждаю, что безопаснее не выполнять этот код, чем выполнять. По крайней мере, если вы его не выполняете, вы знаете, какое будет состояние базы данных. Например, рассмотрим следующую процедуру:
procedure p
is
begin
insert into t1 values(1);
insert into t2 values(2);
insert into t3 values(3);
exception
when others then
dbms_output.put_line
('something bad happened!');
end;
Вызывающий эту процедуру никогда не узнает, что:
- не вставилось ни одной строки;
- вставились все три строки;
- вставилось в таблицу T1, но не в T2 и T3;
- вставилось в таблицы T1 и T2, но не в T3
У этой процедуры есть четыре возможных результата, но вызывающий никогда не узнает, какой же результат получился на самом деле. По крайней мере, если из кода удалить конструкцию WHEN OTHERS, вызывающий будет знать, что произошло во время выполнения процедуры - особенно, если ничего не произошло.
Дополнительную информацию по этой важной теме см. на сайтах:
- tkyte.blogspot.com/2006/08/ouch-that-hurts.html
- tkyte.blogspot.com/2006/09/classic-example-of-why-i-despise.html
- google.com/search?q=site%3Atkyte.blogspot.com+%22when+others%22
Блокирование в веб-среде
Вопрос. Я недавно натолкнулся на .NET-приложение, работающее с сервером Oracle Database 10g, разработчики этого приложения использовали оптимистическое блокирование (извлекали из таблицы идентификатор версии, обновляли требуемую строку, а затем обновляли идентификатор версии), поскольку это единственный способ гарантировать невозможность одновременного обновления одной и той же записи многими пользователями. Я полагаю, что вместо излишнего кода, в котором реализован искусственный механизм блокирования, можно делать тоже самое с помощью предложения FOR UPDATE. Прав ли я?
Ответ. У n-звенных приложений есть два способа доступа к базе данных:
1. С сохранением состояния: подключения к серверу хранятся на протяжении длительного времени, в течении которого генерируется много веб-страниц.
2. Без сохранения состояния: подключения хранятся на протяжении очень короткого периода времени, может быть даже меньше времени генерации одной HTML-станицы.
В большинстве современных приложений, по моему опыту, используется метод 2. Таким образом, конечные пользователи потребляют ресурсы только тогда, когда они "активны" в сервере базы данных. Они захватывают подключение, используют и освобождают его.
Если у вас подключение без сохранения состояния, вы не можете использовать пессимистическое блокирование (предложение FOR UPDATE). Вы будете терять такую блокировку после генерации каждой страницы. Для такого типа приложений подходит только оптимистическое блокирование.
Итак, разработчики, с которыми вы работаете, делают, вероятно, то, что надо. Эту тему я широко изложил в книге Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions (Apress, 2005). Приведем из нее небольшой отрывок. Для подгонки к объему и формату колонки оригинальный текст был модифицирован.
Оптимистическое блокирование или пессимистическое блокирование?
Какой метод наилучший? По моему опыту, пессимистическое блокирование очень хорошо работает в сервере Oracle Database (но, возможно, это не так в других СУБД), и оно имеет много преимуществ по сравнению с оптимистическим блокированием. Однако для него требуется подключение к серверу базы данных с сохранением состояния, такое как в среде "клиент-сервер", поскольку между подключениями блокировки не сохраняются. Один этот факт во многих случаях сегодня делает пессимистическое блокирование нереалистичным. В прошлом при работе с клиент-сервисными приложениями и с несколькими дюжинами или сотнями пользователей это был мой первый и единственный выбор. Сейчас же, однако, я рекомендую для большинства приложений оптимистическое управление конкурентным доступом. За удерживание подключения на протяжении всего сеанса приходится платить слишком высокую цену.
Существует много способов реализации оптимистического управления конкурентным доступом, включая:
- использовать специальный столбец, поддерживаемый триггером базы данных или кодом приложения, для идентификации версии записи;
- использовать контрольную сумму или хеш-значение, которые были вычислены для исходных данных;
- использовать новый в сервере Oracle Database 10g псевдостолбец ORA_ROWSCN
Итак, что же использую я? Я предпочитаю использовать подход со столбцом версии и со столбцом отметки времени. Это дает мне дополнительную информацию о времени обновления конкретной строки. Этот подход менее дорогостоящий по сравнению с вычислением контрольной суммы или хеш-значения, и он не подвергается риску встречи с данными типа LONG, LONG RAW, CLOB, BLOB и другими очень большими столбцами.
Если таблица по-прежнему используется в схеме пессимистического блокирования, например, в клиент-серверных приложениях, то при добавлении оптимистического управления конкурентным доступом для веб-приложений я предпочитаю подход с использованием псевдостолбца ORA_ROWSCN. Дело в том, что существующее унаследованное приложение может не "понимать" появления нового столбца, и даже если я приму дополнительные меры для сокрытия дополнительного столбца, я не могу не принимать во внимание накладные расходы на триггер, необходимый для поддержки этого. В отношении этого механизм ORA_ROWSCN будет ненавязчивым и облегченным (конечно же после пересоздания таблицы).
Подход с вычислением хеш-значений или контрольных сумм существенно не зависит от сервера базы данных, особенно, если эти вычисления выполняются за пределами сервера. Тем не менее, выполнение этих вычислений не в сервере, а в промежуточном звене, означает большее потребление ресурсов, таких как использование центрального процессора и передачи данных по сети.
Каскадные обновления
Вопрос. У меня есть таблица EMP с дочерними таблицами, каждая из которых имеет свои собственные дочерние таблицы. Я хочу в таблице EMP обновить табельный номер служащего и хочу, чтобы все дочерние таблицы (включая дочерние таблицы дочерних таблиц) обновились автоматически. Как это сделать?
>Ответ. Предполагается, что первичные ключи неизменяемые - постоянные. По моему мнению, у вас проблема с моделью данных, а не с SQL. Если вы полагаете, что нужно обновить первичный ключ и сделать это в каскаде, вам нужно, на самом деле, пересмотреть свой подход. Вы должны понимать, что табельный номер в вашем примере не может быть первичным ключом таблицы EMP - нет, если он изменяется. Вам нужно для первичного ключа выбрать что-то другое (может быть даже искусственный ключ).
Тем не менее, вы можете использовать каскадное обновление, если оно действительно требуется (например, для единовременного обновления данных, которые были слиты с данными таблицы). Для этого используйте откладываемые ограничения и хранимую процедуру. Например, предположим у вас есть следующая схема:
create table p
( x int primary key );
create table c1
( x constraint c1_fk_p r
references p deferrable,
y int,
primary key(x,y) );
create table c2
( x int,
y int,
z int,
constraint c2_fk_c1
foreign key(x,y)
references c1 deferrable,
primary key(x,y,z));
Теперь, ограничения в таблицах C1 и C2 могут быть отложены - в этом случае они проверяются либо при фиксации транзакции, либо при изменения состояния ограничений на немедленное (IMMEDIATE). Это позволяет написать хранимую процедуру, как показано на листинге 1.
|
ЛИСТИНГ 1: каскадные обновления с отложенными ограничениями. |
create or replace procedure cascade_p_c1_c2 ( p_old in int, p_new in int ) as begin execute immediate 'set constraint c1_fk_p deferred'; execute immediate 'set constraint c2_fk_c1 deferred'; update p set x = p_new where x = p_old; update c1 set x = p_new where x = p_old; update c2 set x = p_new where x = p_old; execute immediate 'set constraint c1_fk_p immediate'; execute immediate 'set constraint c2_fk_c1 immediate'; end; |
А теперь вы можете вызвать эту процедуру, и она успешно выполнит каскад обновлений родительской таблицы P, дочерней таблицы C1 и ее дочерней таблицы C2. Но опять-таки, вы должны делать это в редчайших случаях - это не то, что должно стать частью вашей постоянной стратегии проектирования и реализации.
Есть разбор и есть разбор
Вопрос. У меня проблемы с защелками библиотечного кеша. Я попытался идентифицировать причину этих проблем с помощью пакета STATSPACK:
Per Second Per Transaction----------- --------------- ... User calls: 1,107.76 53.24 Parses: 389.92 18.74 Hard parses: 0.28 0.01 ...
У вас есть какие-нибудь предложения?
Ответ. В вашей системе выполняется разбор массы SQL-операторов - примерно 390 раз в секунду. Хорошая новость - эти разборы в основном частичные (soft parse).
Только сами разработчики могут уменьшать количество разборов. Сервер Oracle Database разбирает SQL-оператор каждый раз, когда приложение попросит об этом, и здесь приложение просит очень часто.
За 12.98 минут по вашему отчету пакета STATSPACK выполняется примерно 303 669 разборов. Для каждого разбора нужна защелка библиотечного кеша.
Оптимальное решение: понимайте, что единственно хорошим разбором является несуществующий разбор, и если в коде, который обращается к базе данных, соблюдается этот подход, у вас не будет слишком много разборов. Оставляйте курсоры открытыми. Не закрывайте их до тех пор, пока они не будут больше нужны. Вы можете легко реализовать этот подход, разместив все SQL-операторы в хранимых процедурах (машина PL/SQL автоматически кеширует курсоры - не закрывает их - так что, если вы потребуете: "PL/SQL закрой этот курсор", сервер базы данных проигнорирует вас и поместит курсор в кеш).
Если у вас используется язык Java, используйте для кеширования операторов интерфейс Java DataBase Connectivity (JDBC), так что, этот интерфейс будет игнорировать попытки разработчиков закрыть курсоры.
Единственный способ уменьшать количество защелок состоит в том, чтобы уменьшить количество раз, когда вы делаете то, для чего требуются защелки. И разбор - огромный пользователь защелок.
Еще нужно рассмотреть следующее: используется ли у вас механизм кеширования курсоров в сеансах (параметр инициализации session_cached_cursors). Он может сделать ваши частичные разборы более "мягкими." Установка этого параметра поможет, если ваше приложение многократно выполняет следующее:
1. Разбор.
2. Связывание.
3. Выполнение.
4. Закрытие.
5. Переход на шаг 1 и многократное повторение всех шагов.
Рассмотрим пример, мониторинг которого я выполняю, используя небольшой набор средств тестирования runstats (asktom.oracle.com/tkyte/runstats.html). Я начну с небольшой процедуры, в которой многократно выполняются только разборы, для этого используется динамический SQL (эти разборы будут в основном мягкими разборами). Процедура показана на листинге 2.
|
ЛИСТИНГ 2: беспрестанные разборы. |
create or replace procedure p( p_n in number )
as
l_cursor sys_refcursor;
begin
for i in 1 .. p_n
loop
open l_cursor for
'select * from dual d' // mod(i,2);
close l_cursor;
end loop;
end; |
Теперь, если я сравню разницу в количестве защелок, когда я выполняю эту процедуру с кешированием курсоров в сеансе и без кеширования, я увижу большое различие (см. листинг 3).
|
ЛИСТИНГ 3: защелки с параметром инициализации session_cached_cursors=0. |
SQL> alter session set session_cached_cursors=0; Session altered. SQL > exec runStats_pkg.rs_start; PL/SQL procedure successfully completed. SQL > exec p(1); PL/SQL procedure successfully completed. SQL > exec runStats_pkg.rs_middle; PL/SQL procedure successfully completed. SQL > exec p(100000); PL/SQL procedure successfully completed. SQL > exec runStats_pkg.rs_stop(90000); Name Run1 Run2 Diff STAT...parse count (total) 6 100,005 99,999 LATCH.shared pool simulator 8 100,012 100,004 LATCH.shared pool 10 100,053 100,043 LATCH.library cache lock 36 400,044 400,008 LATCH.library cache 67 400,093 400,026 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 361 11,001,012 1,000,651 .04% PL/SQL procedure successfully completed. |
Итак листинг 3 показывает, что для выполнения 100 000 мягких разборов потребовалось примерно 11 000 000 защелок, большинство из которых - защелки библиотечного кеша. Теперь, я установлю параметр session_cached_cursors следующим образом:
SQL> alter session set session_cached_cursors=100; Session altered.
Теперь, я выполню этот же пример (см. листинг 4). Как видно на листинге, количество защелок значительно снизилось, а количество разборов осталось прежним - механизм кеширования курсоров в сеансах начал действовать и сделал частичные разборы более мягкими.
|
ЛИСТИНГ 4: защелки с параметром инициализации session_cached_cursors=100. |
Name Run1 Run2 Diff STAT...parse count (total) 6 100,005 99,999 STAT...execute count 6 100,005 99,999 STAT...session cursor cache hi 2 100,001 99,999 STAT...calls to get snapshots 2 100,001 99,999 STAT...opened cursors cumulati 6 100,005 99,999 STAT...recursive calls 5 300,002 299,997 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 304 845 541 35.98% |
Транспонирование столбца в строку
Вопрос. Я хочу представить значения столбца как строки. То есть, я хочу, чтобы результат запроса к таблице EMP выглядел так:
DEPTNO ENAME
------------ --------------------
10 clark king miller
20 adams ford ...
...
Можно ли это сделать, используя только язык SQL?
Ответ. После появления в сервере Oracle8i Release 2 аналитических функций и функции SYS_CONNECT_BY_PATH() в сервере Oracle9i Database Release 1 сделать это на языке SQL довольно просто. Придерживайтесь следующего подхода:
- Секционируйте данные по номерам отделов (столбец DEPTNO), данные каждого отдела сортируйте по фамилиям служащих (столбец ENAME), назначьте строкам порядковые номера, используя аналитическую функцию ROW_NUMBER().
- Используйте иерархический запрос (с условием CONNECT BY), начиная с первой строки (ROW_NUMBER() = 1), затем соедините эту запись со строкой номер 2, имеющей то же самое значение столбца DEPTNO, и т.д. Итак, в конечном счете вы для каждого отдела получите запись. в которой соединены строки 1, 2, 3, 4 и т.д.
- Для каждого отдела выбирайте только самый длинный путь к значению столбца от корня до узла (connect by path) - в нем будут собраны все фамилии сотрудников отдела.
Функция SYS_CONNECT_BY_PATH() будет возвращать список сцепленных значений столбца ENAME.
Этот запрос выглядит так:
select deptno,
max(sys_connect_by_path
(ename, ' ' )) scbp
from (select deptno, ename,
row_number() over
(partition by deptno
order by ename) rn
from emp
)
start with rn = 1
connect by prior rn = rn-1
and prior deptno = deptno
group by deptno
order by deptno
/
DEPTNO SCBP
--------- ----------------------------------
10 CLARK KING MILLER
20 ADAMS FORD JONES SCOTT ...
30 ALLEN BLAKE JAMES MARTIN ...
Снижение объема генерируемой журнальной информации
Вопрос. У меня есть PL/SQL-пакет, который копирует данные в наше хранилище данных из множественных баз данных, используя для этого связь базы данных. В пакете используется массовое связывание (предложение BULK_COLLECT) с ограничением количества строк от 1 000 до 2 500, в зависимости от числа строк в каждой таблице. Транзакцию я фиксирую за пределами цикла, так что у меня только одна операция фиксации.
Администраторы базы данных заявляют, что объем журнальной информации немыслим, поэтому они даже должны были увеличить дисковое пространство, доступное серверу Oracle Database. Они определенно не говорили, что проблема связана с моим кодом, но она появилась приблизительно во время реализации моего кода.
Как я могу контролировать или гарантировать во время написания кода, что журнализация оптимизирована?
Ответ. Насколько это возможно старайтесь использовать одиночные SQL-операторы. Наибольшее влияние, которое вы можете оказать на генерацию журнальной информации - ограничение объема работы, выполняемой во время одного вызова.
Кроме того, рассмотрите также возможность выполнения операций прямой вставки (direct-path) с отключенной журнализацией. (Только согласовывайте свою работу с вашими администраторами базы данных! Сразу после выполнения этих операций они должны создать резервную копию.)
Сравните разницу в объемах генерируемой журнальной информации при выполнении построчных операций - обработка "мало-помалу" (slow-by-slow) - и одного SQL-оператора, показанную на листинге 5.
|
ЛИСТИНГ 5: минимизация объема генерируемой журнальной информации. |
SQL> create table t ( x int primary key, y char(10), z date ); Table created. SQL > create index t_idx1 on t(y); Index created. SQL > create index t_idx2 on t(z); Index created. SQL > @mystat "redo size" NAME VALUE ------------- ------------- redo size 84538288 SQL > begin 2 for x in (select object_id a, 'x' b, created c from all_objects) 3 loop 4 insert into t values ( x.a, x.b, x.c ); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL > @mystat2 NAME VALUE DIFF ------------- ------------- ---------- redo size 144124840 59,586,552 SQL > truncate table t; Table truncated. SQL > @mystat "redo size" SQL > set echo off NAME VALUE ------------- ------------ redo size 144294508 SQL > begin 2 insert into t select object_id, 'x', created from all_objects; 3 end; 4 / PL/SQL procedure successfully completed. SQL > @mystat2 NAME VALUE DIFF ------------- ----------- ----------- redo size 168114280 23,819,772 |
То есть, при выполнении построчной вставки мы имеем 59MB журнальной информации, а при выполнении одного эффективного SQL-оператора - 23MB!