200 важнейших советов по настройке PL/SQL

Стивен Фернстайн,
HA-LO Industries/RevealNet

Источник: доклад на конференции OOW-2000 (http://www.oracle.com/openworld/upload/W3540.doc)

Зачем настраивать PL/SQL код?

Вы можете провести весь день, настраивая SQL-предложения. Вы можете использовать любой из большого количества фантастических инструментальных средств для SQL-настройки приложений, предлагаемых как Oracle, так и третьими фирмами. Вы можете пригласить специалиста по настройке Oracle. Да, вы можете настраивать, настраивать и настраивать – и тем не менее ваша PL/SQL-программа может работать очень неэффективно. Трудно найти подробные, полные рекомендации по настройке PL/SQL-кодов приложений Oracle. После 12 лет, в течение которых я изучал, использовал и писал о языке Oracle PL/SQL, мне удалось собрать 1,247 советов по настройке PL/SQL для разработчиков. Я отобрал из этих советов 200 самых важных для этой статьи – да, это так!

Ну ладно, честно говоря, у меня нет 1,247 советов по настройке для PL/SQL-разработчиков. Возможно, у меня наберется 200 советов по настройке. Но смогу ли я предложить их вам на 6-10 страницах, дозволенных форматом ECO/SEOUC? Смогу ли я вообще представить все эти советы в промежуток времени, отведенный на выступления на конференции Oracle? НЕТ! Но, чтобы вы не разочаровались, используя эту статью, я приложу все усилия, сосредоточившись на освященном временем принципе настройки:

Избегать выполнения ненужного кода!

Это может показаться весьма банальным советом. Конечно, если код не является необходимым, я не буду включать его в свою программу. И, конечно же, после того как программа написана, лишние строки можно легко обнаружить при просмотре кода и удалить их.

Ну, разумеется! Несмотря на давление сроков, я никогда, ни в коем случае не тороплюсь выбрать кратчайший путь, не делаю вещи, которые, как мне известно, делать не стоит, хотя у меня и не хватает времени, чтобы обратить необходимое внимание на написание оптимального кода. И мой руководитель понимает значение просмотра и критического анализа кода, поэтому он всегда выделяет время для этого очень важного шага, необходимого для улучшения качества кода. [От редактора: на месте автора я бы здесь поставил такое примечание: J J J . Думаю, что С.Фернстайн, судя по следующему абзацу не слишком будет в претензии на такую мою вольность. А.Бачин ]

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

В этой статье предлагаются советы по обнаружению лишнего выполняемого кода и о том, как этого избежать, в частности в контексте языка Oracle PL/SQL.

Поиск ненужного кода

Прежде всего, я хотел бы объяснить смысл этих упражнений по настройке: я не предлагаю вам прочитать эту статью и затем тщательно просматривать каждую строку вашего приложения в поисках потенциального выигрыша в выполнении. Можно найти множество мест, где нужно сделать изменения, и, когда вы их сделаете, вы снова обнаружите, что время отклика приложения улучшилось столь незначительно, что пользователи этого даже не заметили.

Не руководствуйтесь теоретическим подходом в ваших усилиях по настройке (теоретически каждая строка кода вашего приложения должна быть совершенна во всех отношениях: по производительности, удобочитаемости и т.д.). Ваше время будет использовано гораздо лучше, если вы обратите внимание на программы, которые работают медленно, и затем плотно сконцентрируетесь на них. Когда вы обнаружите в приложении фрагмент, который требует настройки, чтобы быстрее выявить проблемы, последуйте этим советам:

Проверьте циклы: код внутри циклов (FOR, WHILE и простого) выполняется, как правило, более одного раза. Следовательно, любой неэффективный код внутри цикла оказывает потенциально умножающееся воздействие.

Проверьте SQL операторы: прежде всего, конечно, необходимо быть уверенным, что SQL операторы оптимальны. Эта тема выходит за рамки данной статьи; существует много хороших инструментов и книг, которые помогут вам настроить SQL. Однако, бывают ситуации, когда следствием использования чистого SQL являются слишком большие накладные расходы – и тогда разумное использование PL/SQL может улучшить выполнение такого предложения.

Просмотрите разделы программы, которые исправлялись много раз: В любой сложной программе, которая эксплуатируется более 6 месяцев, легко можно найти разделы, которые изменялись снова, снова и снова. При таких нарастающих изменениях очень легко может вкрасться лишний код.

Не используйте раздел объявлений для авансов: Конечно, это место, где вы объявляете все переменные, присваиваете им начальные значения и так далее. Весьма вероятно, однако, что некоторые действия, выполняемые в этом разделе (собственно объявление переменной или присвоение значения по умолчанию) не всегда необходимы и не всегда должны быть выполнены в начале блока.

Эти и другие темы рассматриваются в остальной части статьи.

Проверьте циклы

Код внутри циклов (FOR, WHILE и простого) выполняется, как правило, более одного раза. Следовательно, любой неэффективный код внутри цикла оказывает потенциально умножающееся воздействие. Выполняя настройку приложения одного клиента, я обнаружил функцию в тридцать строк, которая выполнялась менее половины секунды, но вызывалась столь часто, что общее время ее работы составляло пять часов. Концентрация усилий по настройке на одной этой процедуре сократило общее время ее выполнения до менее чем двадцати минут. Всегда обращайте внимание прежде всего на циклы, и вы, наверняка, не раз столкнетесь с такой проблемой.

Ниже приводится очевидный пример; процедура принимает в качестве входного параметра имя и затем обрабатывается каждая запись, выбранная из пакетного курсора.

PROCEDURE process_data (nm_in IN VARCHAR2) IS
BEGIN
 FOR rec IN pkgd.cur
 LOOP
 process_rec (
 UPPER (nm_in), 
 rec.total_production);
 END LOOP;
END;

Проблема заключается в том, что функция UPPER применяется к аргументу nm_in в каждой итерации цикла. Это излишне, поскольку значение nm_in не меняется. Эту процедуру легко настроить, объявив локальную переменную для хранения имени в верхнем регистре:

PROCEDURE process_data (nm_in IN VARCHAR2) 
IS
 v_nm some_table.some_column%TYPE := 
 UPPER (nm_in);
BEGIN
 FOR rec IN pkgd.cur
 LOOP
 process_rec (
 v_nm, rec.total_production);
 END LOOP;
END;

Конечно, не всегда так легко обнаружить выполнение ненужного кода. В данном примере можно предположить, что я перевожу имя в верхний регистр, потому что либо: (а) я знаю наверняка, что process_rec не работает корректно со строками в нижнем или смешанном регистре, либо: (b) я не знаю точно, как работает process_rec и поэтому застраховываюсь от любых возможных проблем.

Если я нахожу, что процедура process_data является узким местом, то очень важно понимать, как работает код, от которого она зависит. Неверное предположение может пересечься самым скверным образом с алгоритмами нижележащих программ. Например, возможен вариант, что process_rec всегда выполняет преобразование своего первого параметра к верхнему регистру. Это может сделать мой UPPER ненужным, и их UPPER излишним.

В этой ситуации, возможно, имеет смысл попросить автора process_rec удалить функцию UPPER, или сделать ее необязательной, передавая третий параметр, может быть что-то вроде этого:

PROCEDURE process_rec (
 name_in IN VARCHAR2,
 prod_in IN NUMBER,
 uc_name_in IN BOOLEAN := TRUE)

и тогда я смогу передать FALSE в качестве значения третьего параметра, чтобы избежать UPPER.

Ниже приведен блок кода с циклом. Посмотрите, сможете ли вы найти фрагменты, которые нужно улучшить:

DECLARE
 CURSOR emp_cur
 IS
 SELECT last_name, 
 TO_CHAR (SYSDATE, 'MM/DD/YYYY') today
 FROM employee;
BEGIN
 FOR rec IN emp_cur
 LOOP
 IF LENGTH (rec.last_name) > 20
 THEN
 rec.last_name := 
 SUBSTR (rec.last_name, 20);
 END IF;
 process_employee_history (
 rec.last_name, rec.today, USER);
 END LOOP;
END;

Здесь представлено по крайней мере три различных примера выполнения лишнего кода:

Функция SYSDATE вызывается многократно внутри оператора SELECT, хотя выбирается всего лишь дата, а не время. Допуская, что этот фрагмент кода всегда начинается и заканчивается в один и тот же день (это можно определить без особых усилий), это вычисление можно вынести за пределы запроса и поместить в локальную переменную. Это позволит избежать не только многократного вызова форматирования, но и выполнять SELECT FROM dual только один раз, а ведь именно таким образом реализуется SYSDATE в PL/SQL.

Внутри цикла, я должен быть уверен, что длина передаваемой фамилии не более 20 символов. В качестве альтернативы, можно выполнять SUBSTR внутри запроса и не проверять длину вовсе. Если большинство строк являются короткими (< 20 символов), это изменение может, как ни странно, улучшить производительность.

Я вызываю функцию USER для каждой итерации цикла. Изменяется ли за это время значение, которое возвращает USER? Ничуть. Она всегда возвращает одно и то же значение на протяжении одного соединения с Oracle. Поэтому, можно вызвать ее только один раз, сохранить это значение в памяти (в локальной переменной или, возможно, лучше даже в пакетной константе, чтобы она была доступна на протяжении всей сессии), и избежать многих вызовов SELECT FROM dual.

Вот исправленная версия этого блока:

DECLARE
 v_today VARCHAR2(20) := TO_CHAR (SYSDATE, 'MM/DD/YYYY');
 v_user VARCHAR2(30) := USER;
 CURSOR emp2_cur
 IS
 SELECT SUBSTR (last_name, 1, 20) last_name FROM employee;
BEGIN
 FOR rec IN emp2_cur
 LOOP
 process_employee_history (
 rec.last_name, v_today, v_user);
 END LOOP;
END;
/

Вы можете сравнить производительность этих двух скриптов, запустив скрипт someplsql.tst.

Проверьте SQL

Допустим, что я основательно настроил SQL, используя утилиту explain plan или инструмент для анализа и настройки третьей фирмы – и все же нашлась еще PL/SQL-программа, в которой SQL выполняется медленно. Проблема может заключаться в том, что сам SQL-оператор может содержать избыточность – и, в большинстве случаев, разумное использование PL/SQL может улучшить производительность.

Рассмотрим следующий запрос:

SELECT 'Top employee in ' || department_id || 
 ': ' ||
 E.last_name || ', ' || E.first_name str
 FROM employee_big E
 WHERE E.salary = 
 (SELECT MAX (salary)
 FROM employee E2
 WHERE E2.department_id = E.department_id);

Я использую этот запрос, чтобы отобразить сотрудников, получающих самую высокую зарплату в своих отделах. Написать коррелированный подзапрос для реализации этого требования очень легко. Проблема такого подхода заключается в том, что максимальная зарплата для каждого отдела будет вычисляться много, много раз (предполагается, что сотрудников много).

Можно переписать этот запрос в виде вложенных циклов PL/SQL, что позволит выполнить эту работу намного эффективнее.

DECLARE
 CURSOR dept_cur IS
 SELECT department_id, MAX (salary) max_salary
 FROM employee_big E
 GROUP BY department_id;
 CURSOR emp_cur (
 dept IN department.department_id%TYPE,
 maxsal IN NUMBER)
 IS
 SELECT 'Top employee in ' || department_id || 
  ': ' ||
 last_name || ', ' || first_name str
 FROM employee_big
 WHERE department_id = dept
 AND salary = maxsal;
 
BEGIN
 FOR dept_rec IN dept_cur
 LOOP
 FOR rec IN emp_cur (
 dept_rec.department_id, dept_rec.max_salary)
 LOOP
 str := rec.str;
 END LOOP;
 END LOOP;
END;
/

Запустите скрипт, который находится в файле useplsql.tst, для сравнения производительности этих двух подходов.

[Примечание: скрипт useplsql.tst также содержит другую реализацию, выполненную только на SQL и включающую встроенное представление, производительность которой сравнивается с производительностью – в данном случае – вложенного цикла PL/SQL. Как правило, существует большое количество различных способов написания SQL и PL/SQL для получения правильного ответа. Для определения оптимальной реализации необходимо учитывать не только производительность, но и понятность кода (для обслуживания, улучшения и т.д.). ]

Задержка выполнения до тех пор, пока это действительно не будет необходимо

То, что секция объявлений располагается “перед” исполняемой секцией вовсе не означает, что вы должны все свои программные переменные объявлять здесь. Весьма вероятно, что некоторые действия, выполняемые в этом разделе (само объявление или присвоение значения по умолчанию) не всегда является необходимым и не всегда должно выполняться в начале блока.

Рассмотрим следующий фрагмент кода:

PROCEDURE always_do_everything (
 criteria_in IN BOOLEAN) 
IS
 big_string VARCHAR2(32767) :=
 ten_minute_lookup (...);
 big_list 
 list_types.big_strings_tt :=
 two_minute_number_cruncher (...); 
BEGIN
 IF NOT criteria_in
 THEN
 use_big_string (big_string);
 process_big_list (big_list);
 ELSE
  /* Ничего “большого” здесь не делается */
 ...
 END IF; 
END;

Я объявил “большую” строку (big_string), и вызвал функцию, которая занимает в общей сложности десять минут и тратит процессорное время, чтобы присвоить этой строке значение по умолчанию. Я также объявил и заполнил набор (через таблицу TYPE, объявленную в пакете), вновь обратившись к функции, интенсивно использующей CPU для заполнения этого списка. Я выполнил оба этих шага потому, что я знаю, что в программе необходимо использовать структуры данных big_string и big_list.

Затем я написал исполняемый раздел, выполнил некоторые первоначальные проверки и все, казалось бы, прекрасно, кроме того, что это работает слишком медленно. Я решил просмотреть свою программу, чтобы лучше понять ход ее выполнения. Я обнаружил нечто весьма интересное: в программе всегда объявляются и заполняются структуры big_string и big_list, но используются они только в том случае, если criteria_in является FALSE, что бывает далеко не всегда!

Теперь, когда я значительно лучше понял логический поток моей программы, можно использовать преимущества вложенных блоков (анонимного блока, объявленного внутри другого блока), чтобы задержать инициализацию структур данных до того момента, когда это будет действительно необходимо. Вот переработанная версия этой программы:

PROCEDURE only_as_needed (
 criteria_in IN BOOLEAN) IS
BEGIN
 IF NOT criteria_in
 THEN
 DECLARE
 big_string VARCHAR2(32767) :=
 ten_minute_lookup (...);
 big_list 
 list_types.big_strings_tt :=
 two_minute_number_cruncher (...);
 BEGIN
 use_big_string (big_string);
 Process_big_list (big_list);
 END;
 ELSE
 /* Nothing big 
 going on here */
 ...
 END IF; 
END;

Еще одно преимущество такого подхода заключается в том, что когда вложенный блок завершается, память, связанная с этими структурами данных, освобождается. Такое поведение в примере, приведенном выше, может быть очень удобным, если необходимо выполнять много операций в программе, после того как я поработал с "большими" переменными. Предыдущий подход означал, что память не будет освобождена до тех пор, пока не выполнится вся программа.

Будьте хорошим слушателем

Являетесь ли вы хорошим слушателем? Когда люди говорят, прикладываете ли вы усилия, чтобы придумать, что нужно ответить, вместо того, чтобы попытаться действительно понять, что они имеют в виду? Я верю, что умение быть хорошим слушателем является знаком уважения по отношению к другим, и является искусством, которое мы все должны совершенствовать (Я знаю, что я должен приложить больше усилий).

Умение быть хорошим слушателем является также важным навыком, когда программист выясняет требования пользователей и переводит их в код. И слишком часто мы слушаем, что говорят наши пользователи, но на самом деле не слышим их. В результате мы пишем код, который либо вовсе не удовлетворяет требованиям, либо делает это неэффективным образом.

Рассмотрим следующий пример:

CREATE OR REPLACE PROCEDURE remove_dept (
 deptno_in IN NUMBER, 
 new_deptno_in IN NUMBER)
IS
 emp_count NUMBER;
BEGIN
 SELECT COUNT(*) INTO emp_count 
 FROM emp WHERE deptno = deptno_in;
 IF emp_count > 0
 THEN
 UPDATE emp 
 SET deptno = new_deptno_in
 WHERE deptno = deptno_in;
 END IF;
 DELETE FROM dept WHERE deptno = deptno_in;
END drop_dept;

Эта процедура удаляет отдел из таблицы department, но перед тем, как сделать это, она переводит всех сотрудников этого отдела в другой. Логика программы следующая: если в этом отделе есть сотрудники, то выполняя update, мы осуществляем перевод. Затем удаляем строки из таблицы department.

Заметили ли вы, что неверно в этой программе? В действительности существует два различных уровня, на которых эта программа вызывает возражения. Прежде всего, значительная часть кода является лишней. Если оператор UPDATE не найдет ни одной строки, которую нужно изменить, он не выдаст ошибку, он просто ничего не сделает. Следовательно, процедура remove_dept может быть доведена до:

CREATE OR REPLACE PROCEDURE remove_dept (
 deptno_in IN NUMBER, 
 new_deptno_in IN NUMBER)
IS
 emp_count NUMBER;
BEGIN
 UPDATE emp 
 SET deptno = new_deptno_in
 WHERE deptno = deptno_in;
 DELETE FROM dept WHERE deptno = deptno_in;
END drop_dept;

Предположим, однако, что было действительно необходимо выполнять проверку наличия сотрудников. Рассмотрим получше, что же в действительности здесь делается. Вопрос, на который нужно ответить, звучит так: "Существует ли хотя бы один сотрудник?", однако если посмотреть на код внимательнее, становится понятно, что на самом деле я отвечаю на вопрос "Сколько существует сотрудников?" Я могу преобразовать ответ на этот вопрос в ответ на мой первый вопрос с помощью булевского выражения (emp_count > 0), но так можно зайти очень далеко.

На самом деле существует несколько способов ответить на вопрос "Имеется ли по крайней мере один X?"; способ, который вы выберете, может оказать существенное влияние на выполнение. Вот некоторые способы:

1. Использовать COUNT(*) как показано выше:

BEGIN
 SELECT COUNT(*) INTO emp_count 
 FROM employee 
 WHERE deptno = deptno_in;
 atleastone := emp_count > 0;

2. Использовать явный курсор для проверки существования, путем выбора одной записи и проверки атрибута ISOPEN курсора:

 CURSOR count_cur IS
 SELECT COUNT(*)
 FROM employee 
 WHERE deptno = deptno_in;
 rec count_cur%ROWTYPE;
BEGIN
 OPEN count_cur;
 FETCH count_cur INTO rec;
 atleastone := count_cur%FOUND;

3. Использовать скрытый курсор и надеяться на обработку исключений для определения результата:

BEGIN
 SELECT 'x' INTO dummy
 FROM employee_big 
 WHERE department_id = &2;
 atleastone := TRUE;
EXCEPTION
 WHEN NO_DATA_FOUND 
 THEN atleastone := FALSE;
 WHEN TOO_MANY_ROWS 
  THEN atleastone := TRUE;
END;

4. Использовать возможности SQL. Существует множество вещей, которые можно сделать, чтобы уменьшить потенциальную избыточность COUNT или, иначе, сократить работу, выполняемую SQL машиной для получения этого ответа. Вот несколько примеров:

BEGIN
 SELECT COUNT(1) INTO dummy 
 FROM employee_big 
 WHERE department_id = &2
 AND ROWNUM < 2;
 atleastone := dummy > 0;
 
BEGIN
 SELECT 1 INTO dummy
 FROM dual 
 WHERE EXISTS (
 SELECT 'x' FROM employee_big 
 WHERE department_id = &2);
 atleastone := dummy IS NOT NULL;

Красота! Я полагаю, что писать все это очень весело. Вы можете использовать ROWNUM, чтобы сократить работу COUNT'а. Вы можете даже использовать оператор EXISTS и подзапрос для выполнения этой задачи. Игры разработчиков SQL!

И все-таки, какой из этих приемов даст наиболее эффективную программу? Для сравнения различных подходов можно запустить скрипт atleastone.sql (обратите внимание: в начале скрипта создается достаточно большая копия таблицы сотрудников; этот код закомментирован, чтобы избежать накладных расходов на этот шаг в случае, если эта таблица уже существует. Вы можете раскомментировать этот раздел, когда вы запускаете этот скрипт в первый раз).

Вот результат, обычный для этого скрипта (каждый вариант выполнялся 1000 раз, проверяя существование по крайней мере одного сотрудника в 20 отделе):

SQL> @atleastone 1000 20
Implicit Elapsed: .84 seconds. Factored: .00084 seconds.
Explicit Elapsed: .34 seconds. Factored: .00034 seconds.
COUNT Elapsed: 4.22 seconds. Factored: .00422 seconds.
COUNT ROWNUM < 2 Elapsed: .27 seconds. Factored: .00027 seconds.
EXISTS Elapsed: .36 seconds. Factored: .00036 seconds.

Явный курсор вместе с двумя “умными” реализациями получил наилучшую оценку по результатам контрольной задачи. Можно также заметить, что COUNT(*) выполняется значительно медленнее любого другого подхода. Получив эти результаты, для ответа на вопрос “Существует ли хотя бы один?” я выберу прямой и эффективный явный курсор. Хотя COUNT(1) с ROWNUM < 2 работает немного быстрее, он значительно сложнее (как и его EXISTS партнер). Незначительное улучшение производительности не возмещает возрастающую сложность поддержания и улучшения такого кода.

Хотя этот сценарий и сопровождающий его код сосредоточены на отдельном требовании, я сумел создать лучший вариант, только удостоверившись в том, что код, который я написал (“ответ”) отвечает на “вопрос” - требования пользователя.

Поймите как работают ваши инструменты

Корпорация Oracle последние несколько лет работает над улучшением языка PL/SQL. Мы видим, что наше программное обеспечение работает быстрее, благодаря настройкам низкого уровня. С помощью PL/SQL мы можем сделать так много, как никогда раньше, благодаря улучшению языка. В качестве частных улучшений можно отметить добавление в наш репертуар большого количества встроенных пакетов.

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

Примером такого рода может служить использование (или неправильное использование) пакета DBMS_SQL. DBMS_SQL это единственный способ (до Oracle8i) выполнения динамического SQL: SQL-предложений и PL/SQL-блоков, которые конструируются и выполняются во время работы программы. Пакет DBMS_SQL является наиболее сложным в использовании из всех встроенных пакетов. Неправильное использование пакета может привести к серьезным проблемам производительности.

Рассмотрим следующий фрагмент кода:

CREATE OR REPLACE PROCEDURE no_justice
IS
 cur INTEGER;
 rows_updated INTEGER;
BEGIN
 FOR rec IN (
 SELECT name, bonus
 FROM ceo_compensation
 WHERE layoffs > 1000)
 LOOP
 cur := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE (
 cur,
 'UPDATE ill_gotten_gains
  SET compensation = compensation + ' 
 || rec.bonus ||
 'WHERE slave_to_profits = ''' 
 || rec.name || '''', 
 DBMS_SQL.native);
 rows_updated := DBMS_SQL.EXECUTE(cur);
 DBMS_SQL.CLOSE_CURSOR(cur);
 END LOOP;
END;

Эта программа достаточно проста: добавить премию, полученную каждым генеральным директором (CEO), снявшим с работы более 1,000 сотрудников, к его общему пакету вознаграждений. Вот уж, действительно, нечестная прибыль (и также пример, который не требует использования DBMS_SQL, но, пожалуйста, отнеситесь ко мне благосклонно, ведь целью является иллюстрация этой проблемы).

На первый взгляд, конструкция этой программы кажется вполне логичной: открываем динамический SQL-курсор, разбираем оператор UPDATE с конкретными значениями для генерального директора, выполняем UPDATE и закрываем курсор. Простой план проведения испытаний подтверждает, что код работает просто замечательно. Тем не менее, эта программа имеет два серьезных недостатка, в смысле эффективного выполнения:

При работе с DBMS_SQL нет необходимости открывать и закрывать курсор для каждого отдельного SQL-оператора, который вы выполняете. Можно открыть один курсор и использовать его для любого количества совершенно разных операторов, таких как запрос, удаление, PL/SQL-блок.

В процедуре no_justice используется конкатенация, чтобы подставить в строку конкретное значение для генерального директора. Хотя это работает, это вовсе не так эффективно как использование связанных переменных. При вставке символьных значений в SQL-строку, каждая SQL-строка является физически различной, и должна разбираться SQL-механизмом индивидуально. С другой стороны, при использовании связанных переменных (и заполнителей в самой SQL-строке), я могу разобрать только одну SQL-строку и больше ничего не делать, только связывать с каждой строкой, выбираемой из курсора.

Вот переписанная процедура no_justice, включающая эти изменения:

CREATE OR REPLACE PROCEDURE no_justice
IS
 cur INTEGER := DBMS_SQL.OPEN_CURSOR;
 rows_updated INTEGER;
BEGIN
 DBMS_SQL.PARSE (
 cur,
 'UPDATE ill_gotten_gains
 SET compensation = 
 compensation + :bloodMoney
 WHERE slave_to_profits = :theCEO', 
 DBMS_SQL.native);
 FOR rec IN (
 SELECT name, bonus
 FROM ceo_compensation
 WHERE layoffs > 1000)
 LOOP
 DBMS_SQL.BIND_VARIABLE (
 cur, 'bloodMoney', rec.bonus);
 DBMS_SQL.BIND_VARIABLE (
 cur, 'theCEO', rec.name);
 rows_updated := DBMS_SQL.EXECUTE(cur);
 END LOOP;
 DBMS_SQL.CLOSE_CURSOR(cur);
END;

Теперь выполняется только код, который действительно необходимо выполнять в данный момент. В результате, в зависимости от сложности SQL-оператора и от накладных расходов на разбор, можно существенно снизить время выполнения. Вы можете запустить скрипт effdsql.tst для сравнения производительности нескольких различных реализаций одного и того же запроса.

Пишите только необходимый код, который легко поддерживать

Вот чем наши программы должны быть, и что они должны делать: нужно писать и выполнять только код, необходимый для реализации задачи – и писать таким образом, чтобы облегчить его улучшение по прошествии некоторого времени.

В этой статье предложено несколько примеров, которые, я надеюсь, облегчат вам просмотр собственных программ и помогут найти в них фрагменты, которые необходимо улучшить. И если вы обнаружите свои случаи, требующие улучшения, и, возможно, некоторые другие прекрасные иллюстрации “лишнего кода”, я призываю вас присылать свои примеры (до и после) на PL/SQL-конференцию по теме "отладка, настройка и трассировка".