Глава 10

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

tkyte@TKYTE816> declare

2 3 4 5

9 10

11 12

13 14

l number number;

begin

for i in 1 . loop

l number

10000

dbms random.random;

execute immediate

insert into t values (:xl, :x2, :x3, :x4) using l number, l number, l number,

end loop; commit;

l number;

end; /

Ft/SQL procedure successfully completed.

Если собираетесь выполнять этот пример в Oracle 8.1.5, см. информацию обошибках на Web-сайте издательства Wrox, http: www.wrox.com. Там можно узнать, что нужно сначала вызвать процедуруdbms random.initialize. Кроме того, дляустановки пакета dbms random в Oracle 8i вплоть до версии 8.1.6, необходимо от имени пользователя sSYSвыполнить сценарий catoctk.sql, который находится в каталоге $ORACLE HOME/rdbms/admin.

В этом случае мы видим заметное улучшение:

Это существенное улучшение, но можно сделать еще лучше. При двух пользователях общее количество ожиданий освобождения защелок в обшей области SQL сократилось до 46 процентов исходного значения, как и время ожидания. При добавлении пользователей ситуация еще улучшается. В случае пяти пользователей количество ожиданий сократилось на 80 процентов, и суммарное время ожиданий составило лишь 17 процентов исходного времени.

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

Стратегии и средства настройки 533

рять, можно ли его использовать. Представленный выше подход на базе оператора EXECUTE IMMEDIATE можно описать так:

loop

разобрать

связать

в1полнить

закрть end;

А лучше б1ло бы так:

разобрать loop

связать

в1полнить end;

закр1ть;

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

tkyte@TKYTE816> declare

2 l number number;

3 begin

4 for i in 1 . . 10000

5 loop

6 l number := dbms random.random; 7

8 insert into t

9 values(l number, l number, l number, l number);

10 end loop;

11 commit;

12 end;

13 /

PL/SQL procedure successfully completed.

PL/SQL будет автоматически кэшировать курсор - это одно из основных преимуществ PL/SQL. Оператор вставки обычно будет разобран только один раз на сеанс, если он помещен в хранимую процедуру. Он будет разобран один раз при выполнении блока, если помещен в анонимный блок. Теперь можно снова выполнить тестирование:

Глава 10

Время ожидания освобождения защелок крайне мало - им просто можно пренебречь. Рассмотренный пример показывает, почему:

использование связываемых переменных определяет производительность;

важно избегать лишних мягких разборов запроса.

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

В Oracle 8.1.6 добавлена новая возможность: совместное использование курсора - CURSOR SHARING. Совместное использование курсора - это своего рода автоматическая подстановка связываемых переменных. Сервер вынужден переформулировать поступающий запрос так, чтобы в нем использовались связываемые переменные, прежде чем разбирать его. В результате, запрос вида:

scott@TKYTE816> select * from emp where ename = KING;

будет автоматически преобразован в вид:

select * from where ename =:SYS B 0

или, в версиях 8.1.6 и 8.1.7

select * from emp where ename =: SYS B 0

Это шаг в правильном направлении, но нельзя использовать этот параметр как окончательное и долговременное решение проблемы. Использование CURSOR SHARING имеет побочные эффекты, о которых надо помнить. Производительность плохо написанной программы может существенно увеличиться при установке CURSOR SHARING= FORCE, но работать она все равно будет медленнее, чем могла бы, да и масштабируемость будет ограничена. Как б1ло показано выше, количество и время ожиданий можно значительно сократить за счет использования связываемых переменных. Того же результата можно добиться с помощью установки CURSOR SHARING=FORCE. Однако пока не предотвращены избыточные мягкие разборы операторов, от всех ожиданий избавиться нельзя. Совместное использование курсоров не избавляет от лишних мягких разборов. Если установка параметра CURSOR SHARING дает существенный результат, значит, вы требуете от сервера Oracle слишком часто разбирать большое количество запросов. Если требуется частый разбор большого количества запросов, то установка CURSOR SHARING решит проблему связываемых переменных, но не устранит дополнительный расход ресурсов на повторный мягкий разбор. Хотя и не так очевидно, как отсутствие связываемых переменных, большое количество мягких разборов тоже ограничивает производительность и масштабируемость. Единственное решение - использовать связываемые переменные и по возможности использовать курсоры повторно. Например, если бы я создавал программу на Java, я никогда не написал такой фрагмент: