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.

29
0
8
Общее количество ответов: 8

Ответ #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

35
0

Ответ #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 как двухэтапное решение в исходной публикации.

7
1

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

Ответ #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 во время компиляции.

6
0

Ответ #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 -поднято.

4
0

Ответ #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 самым быстрым.

3
0

Ответ #6

Ответ на вопрос: Oracle PL / SQL - вредны ли исключения NO_DATA_FOUND для производительности хранимых процедур?

Если это важно, вам действительно нужно 9X_select-statement протестировать оба варианта!

Сказав это, я 9X_sql всегда использовал метод исключения, мотивируя 9X_sql-select это тем, что лучше обратиться к базе данных 9X_custom-exceptions только один раз.

2
0

Ответ #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).

1
0

Ответ #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;

1
0