Oracle PL / SQL - вредны ли исключения NO_DATA_FOUND для производительности хранимых процедур?
Я пишу хранимую процедуру, для которой нужно 9X_exception много времени. Зная, что исключения могут 9X_custom-exceptions отрицательно сказаться на производительности, я 9X_exceptions всегда избегал их использования в PL / SQL. Мои 9X_oracle условия в этой хранимой процедуре в основном 9X_exception связаны с тем, существует ли запись, и я 9X_sqlselect мог бы сделать это одним из двух способов:
SELECT COUNT(*) INTO var WHERE condition; IF var > 0 THEN SELECT NEEDED_FIELD INTO otherVar WHERE condition; ....
-или-
SELECT NEEDED_FIELD INTO var WHERE condition; EXCEPTION WHEN NO_DATA_FOUND ....
Второй 9X_exceptions случай кажется мне немного более элегантным, потому 9X_plsql что тогда я могу использовать NEEDED_FIELD, который 9X_exceptions мне пришлось бы выбрать в первом операторе 9X_sql после условия в первом случае. Меньше кода. Но 9X_exception-handling если с помощью COUNT (*) хранимая процедура 9X_oracle будет работать быстрее, тогда я не против 9X_exception набрать еще немного, чтобы увеличить скорость 9X_sqlselect обработки.
Есть подсказки? Я упускаю другую 9X_plsql возможность?
ИЗМЕНИТЬ Я должен был упомянуть, что 9X_exception все это уже вложено в цикл FOR. Не уверен, имеет 9X_exception ли это значение для использования курсора, поскольку 9X_sql-select я не думаю, что могу ОБЪЯВИТЬ курсор как 9X_sql-select выбор в FOR LOOP.
Ответ #1
Ответ на вопрос: Oracle PL / SQL - вредны ли исключения NO_DATA_FOUND для производительности хранимых процедур?
Я бы не стал использовать для этого явный 9X_exception курсор. Стив Ф. больше не советует использовать 9X_plsql явные курсоры, когда можно использовать 9X_select-statement неявный курсор.
Метод с count(*)
небезопасен. Если 9X_sql-query другой сеанс удалит строку, удовлетворяющую 9X_plsql условию, после строки с count(*)
и перед строкой 9X_exceptions с select ... into
, код выдаст исключение, которое не будет 9X_sql обработано.
Вторая версия исходного сообщения 9X_sql-query не имеет этой проблемы, и она обычно предпочтительнее.
Тем 9X_exceptions не менее, использование исключения сопряжено 9X_oracle с небольшими накладными расходами, и если 9X_exception вы на 100% уверены, что данные не изменятся, вы 9X_oracle можете использовать count(*)
, но я не рекомендую 9X_plsql этого делать.
Я провел эти тесты на Oracle 10.2.0.1 в 32-битной Windows. Я 9X_sql-statement смотрю только на прошедшее время. Существуют 9X_select-statement и другие тестовые программы, которые могут 9X_exception-handling предоставить более подробную информацию 9X_sqlselect (например, количество защелок и использованную 9X_oracle память).
SQL>create table t (NEEDED_FIELD number, COND number);
Таблица создана.
SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);
Создана 1 строка.
declare otherVar number; cnt number; begin for i in 1 .. 50000 loop select count(*) into cnt from t where cond = 1; if (cnt = 1) then select NEEDED_FIELD INTO otherVar from t where cond = 1; else otherVar := 0; end if; end loop; end; /
Процедура 9X_plsql PL / SQL успешно завершена.
Прошло: 00: 00: 02.70
declare otherVar number; begin for i in 1 .. 50000 loop begin select NEEDED_FIELD INTO otherVar from t where cond = 1; exception when no_data_found then otherVar := 0; end; end loop; end; /
Процедура 9X_sql-select PL / SQL успешно завершена.
Прошло: 00: 00: 03.06
Ответ #2
Ответ на вопрос: Oracle PL / SQL - вредны ли исключения NO_DATA_FOUND для производительности хранимых процедур?
Поскольку SELECT INTO предполагает, что 9X_sql-query будет возвращена одна строка, вы можете 9X_exception-handling использовать инструкцию в форме:
SELECT MAX(column) INTO var FROM table WHERE conditions; IF var IS NOT NULL THEN ...
SELECT выдаст 9X_select-statement вам значение, если оно доступно, и значение 9X_sql-syntax NULL вместо исключения NO_DATA_FOUND. Накладные 9X_sqlselect расходы, вносимые MAX(), будут минимальными 9X_exception-handling до нуля, поскольку набор результатов содержит 9X_sqlselect единственную строку. Он также имеет то преимущество, что 9X_sql-select он компактен по сравнению с решением на 9X_select-statement основе курсора и неуязвим для проблем параллелизма, таких 9X_select-statement как двухэтапное решение в исходной публикации.
- Недостатком этого решения является то, что оно скроет другие случаи исключения, которые вы, возможно, не захотите скрывать, потому что это не должно произойти, например ...
Ответ #3
Ответ на вопрос: Oracle PL / SQL - вредны ли исключения NO_DATA_FOUND для производительности хранимых процедур?
Альтернатива коду @ Steve.
DECLARE CURSOR foo_cur IS SELECT NEEDED_FIELD WHERE condition ; BEGIN FOR foo_rec IN foo_cur LOOP ... END LOOP; EXCEPTION WHEN OTHERS THEN RAISE; END ;
Цикл не выполняется, если 9X_exception нет данных. Циклы курсора FOR - это то, что 9X_exception-handling вам нужно - они помогают избежать лишних 9X_sql хлопот. Еще более компактное решение:
DECLARE BEGIN FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP ... END LOOP; EXCEPTION WHEN OTHERS THEN RAISE; END ;
Что 9X_select-statement работает, если вы знаете полную инструкцию 9X_sql-syntax select во время компиляции.
Ответ #4
Ответ на вопрос: Oracle PL / SQL - вредны ли исключения NO_DATA_FOUND для производительности хранимых процедур?
@DCookie
Я просто хочу отметить, что вы можете 9X_exception-handling опустить строки, в которых говорится
EXCEPTION WHEN OTHERS THEN RAISE;
Вы получите 9X_plsql тот же эффект, если вы полностью откажетесь 9X_select-statement от блока исключения, а номер строки, указанный 9X_oracle для исключения, будет той строкой, в которой 9X_exception-handling фактически возникло исключение, а не строкой 9X_sql в блоке исключения, где оно было повторно 9X_sqlselect -поднято.
Ответ #5
Ответ на вопрос: Oracle PL / SQL - вредны ли исключения NO_DATA_FOUND для производительности хранимых процедур?
Стивен Дарлингтон делает очень хорошее замечание, и 9X_sql-statement вы можете видеть, что если вы измените мой 9X_select-statement тест, чтобы использовать таблицу более реалистичного 9X_oracle размера, если я заполню таблицу до 10000 9X_custom-exceptions строк, используя следующее:
begin for i in 2 .. 10000 loop insert into t (NEEDED_FIELD, cond) values (i, 10); end loop; end;
Затем повторно 9X_select-statement запустите тесты. (Мне пришлось уменьшить 9X_sqlselect количество циклов до 5000, чтобы получить 9X_sql-syntax разумное время).
declare otherVar number; cnt number; begin for i in 1 .. 5000 loop select count(*) into cnt from t where cond = 0; if (cnt = 1) then select NEEDED_FIELD INTO otherVar from t where cond = 0; else otherVar := 0; end if; end loop; end; / PL/SQL procedure successfully completed. Elapsed: 00:00:04.34 declare otherVar number; begin for i in 1 .. 5000 loop begin select NEEDED_FIELD INTO otherVar from t where cond = 0; exception when no_data_found then otherVar := 0; end; end loop; end; / PL/SQL procedure successfully completed. Elapsed: 00:00:02.10
Метод за исключением теперь 9X_exception более чем в два раза быстрее. Итак, почти 9X_select-statement для всех случаев метод:
SELECT NEEDED_FIELD INTO var WHERE condition; EXCEPTION WHEN NO_DATA_FOUND....
- это то, что нужно. Это 9X_sql-select дает правильные результаты и, как правило, является 9X_sql-query самым быстрым.
Ответ #6
Ответ на вопрос: Oracle PL / SQL - вредны ли исключения NO_DATA_FOUND для производительности хранимых процедур?
Если это важно, вам действительно нужно 9X_select-statement протестировать оба варианта!
Сказав это, я 9X_sql всегда использовал метод исключения, мотивируя 9X_sql-select это тем, что лучше обратиться к базе данных 9X_custom-exceptions только один раз.
Ответ #7
Ответ на вопрос: Oracle PL / SQL - вредны ли исключения NO_DATA_FOUND для производительности хранимых процедур?
Да, вам не хватает курсоров
DECLARE CURSOR foo_cur IS SELECT NEEDED_FIELD WHERE condition ; BEGIN OPEN foo_cur; FETCH foo_cur INTO foo_rec; IF foo_cur%FOUND THEN ... END IF; CLOSE foo_cur; EXCEPTION WHEN OTHERS THEN CLOSE foo_cur; RAISE; END ;
по общему признанию, это 9X_sql больше кода, но он не использует ИСКЛЮЧЕНИЯ 9X_sql в качестве управления потоком, что, узнав 9X_exception большую часть моего PL / SQL из книги Стива 9X_sql-statement Фейерштейна по программированию PL / SQL, я 9X_plsql считаю, что это хорошо.
Быстрее ли это или 9X_sql-select нет, я не знаю (в настоящее время я очень 9X_exceptions мало использую PL / SQL).
Ответ #8
Ответ на вопрос: Oracle PL / SQL - вредны ли исключения NO_DATA_FOUND для производительности хранимых процедур?
Вместо вложенных циклов курсора более эффективным 9X_sql-select подходом было бы использование одного цикла 9X_sql-statement курсора с внешним соединением между таблицами.
BEGIN FOR rec IN (SELECT a.needed_field,b.other_field FROM table1 a LEFT OUTER JOIN table2 b ON a.needed_field = b.condition_field WHERE a.column = ???) LOOP IF rec.other_field IS NOT NULL THEN -- whatever processing needs to be done to other_field END IF; END LOOP; END;
-
7
-
5
-
1
-
2
-
5
-
8
-
14
-
5
-
1
-
9
-
6
-
5
-
5
-
4
-
3
-
3
-
3
-
7
-
4
-
5
-
2
-
5
-
3
-
2
-
4
-
8
-
2
-
7
-
3
-
5
-
2
-
2
-
3
-
9
-
3
-
5
-
11
-
8
-
2
-
20
-
3
-
2
-
6
-
1
-
3
-
3
-
3
-
5
-
2
-
2