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

String getWordb(int ID, int IDLang, Connection conn) throws SQLException { CallableStatement stmt = null;

stmt = conn.prepareCall( { call get.wordb (?,?,?)} );

stmt.setInt(l,ID);

stmt.setlnt(2,IDLang) ;

stmt.registerOutParameter (3, Java.sql.Types.VARCHAR) ; stmt.execute() ;

String word = stmt.getstring (3) ; stmt.close(); return word;

Я бы написал так: CallableStatement stmt = null;

String getWordbfint ID, int IDLang, Connection conn) throws SQLException { if (stmt - null) {

stmt = conn.prepareCall( {call get.wordb (?,?,?)) );

stmt.registerOutParameter (3, Java.sql.Types.VARCHAR);

stmt.setlnt(1,ID); stmt.setlnt(2,IDLang); s tmt.execute(); return stmt.getstring (3);

Здесь я гарантирую использование связываемых переменных, подставляя символы-заместители в оператор. Кроме того, я разбираю оператор не более одного раза при каждом выполнении программы. Это дает огромный выигрыш в производительности. В ходе одного тестирования я вызвал плохую и хорошую реализацию по 1000 раз. Плохая реализация, в которой мягкий разбор выполнялся при каждом обращении, работала две с половиной секунды. Хорошая реализация работала одну секунду. Это в однопользовательском режиме. Как вы уже знаете, при добавлении дополнительных пользователей, каждый из которых выполняет тысячи мягких разборов, работа существенно замедлится за счет ожидания снятия защелок в ходе мягкого разбора. Этих дополнительных расходов можно и нужно избегать в создаваемых приложениях.

Теперь ненадолго вернемся к параметру CURSOR SHARING. Я уже говорил, что имеются побочные эффекты использования параметра CURSOR SHARING, о которых надо знать. Их можно разбить на следующие категории:

Проблеме! оптимизатора. При установке CURSOR SHARING из запроса удаляются все символьные строки и числовые константы; у оптимизатора остается меньше информации для работы. Это может привести к совершенно другим планам выполнения запросов.

536 Глава 10

Проблеме! с результатами в1полнения запросов. Длина извлекаемых столбцов может неожиданно измениться. Запросы, обычно возвращавшие данные типа VARCHAR2(5) и NUMBER(2), могут теперь возвращать VARCHAR2(30) и

NUMBER(5). Фактический размер возвращаемых данных не изменится, но приложение будет получать информацию о том, что столбец может содержать строки длиной до 30 байт, а это может повлиять на отчеты и создающие их приложения.

Сложности в оценке планов в1полнения запросов. Они возникнут из-за того, что EXPLAIN PLAN будет видеть не такой запрос, как поступает в базу данных. Это усложняет настройку производительности запросов. Средства типа AUTOTRACE в SQL*Plus становятся ненадежными источниками информации при установке параметра CURSOR SHARING.

Мы подробно рассмотрим каждую из этих проблем, но сначала давайте поговорим о проблемах оптимизатора. Они непосредственно влияют на производительность приложения. Рассмотрим следующий простой пример, в котором выполняется запрос, содержащий как связываемые переменные, так и строковые константы. Производительность этого запроса до установки параметра CURSOR SHARING - отличная. Производительность после включения параметра CURSOR SHARING - ужасная. Причина падения производительности в том, что оптимизатор теперь имеет намного меньше информации, необходимой ему для работы. По строковым константам оптимизатор мог определить, что один индекс будет более избирателен по сравнению с другим. При удалении всех констант оптимизатор не может прийти к такому выводу. Подобная проблема - не редкость для многих приложений. В большинстве приложений в операторах SQL используются и связываемые переменные, и константы. Не использовать связываемые переменные - это ужасно, но использовать связываемые переменные всегда - немногим лучше. Вот какую таблицу надо создать для данного примера:

tkyte@TKYTE816> create table t as 2 select * from all objects;

Table created.

tkyte@TKYTE816> create index t idx1 on t(OBJE NAME) ; Index created.

tkyte@TKYTE816> create index t idx2 on t(OBJECT TYPE); Index created.

tkyte@TK816> analyze table t compute statistics

2 for all indexed columns

3 for table;

Table analyzed.

По таблице имеется два индекса. Индекс по столбцу OBJECT TYPE используется для выполнения представленного ниже запроса. Индекс по столбцу OBJECT NAME интенсивно используется другим приложением. Оба эти индекса необходимы. Мы будем выполнять к таблице следующий запрос:

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

select * from t

where obiect name like :search str

and object type in (FUNCTION, PROCEDURE, TRIGGER);

Этот запрос создавался для заполнения выпадающего списка в приложении. Пользователь выбирает из списка процедуру, функцию или триггер для редактирования. Запрос выполняется тысячи раз в день.

Если посмотреть на реальные данные в таблице:

tkyte@TKYTE816> compute sum of cnt on report

tkyte@TKYTE816> break on report

tkyte@TKYTE816> select object type, count(*) cnt from t group by 2 object type;

то окажется, что конструкция IN вернет 58 строк, а количество строк, возвращаемых конструкцией LIKE, определить заранее нельзя (их может быть сколько угодно - от О до 21782). Если выполнить в SQL*Plus запрос следующего вида:

tkyte@TKYTE816> variable search str varchar2(25)

tkyte@TKYTE816> exec :search str := %; PL/SQL procedure successfully completed.