Главная цель данной главы - научить АБД разбираться со стоящими перед ним проблемами настройки приложений. Для этого будет полезно документи­ровать некоторые плохие приемы программирования SQL, с которыми нам приходилось встречаться и которые из раза в раз снижают производительность приложения. Это далеко не полный список. Некоторые из ловушек (там, где возможно) содержат образцы кодов, показывающих, что было и что стало. Ни­. же дано несколько не самых лучших "примеров" написания операторов SQL, ко­торым не нужно подражать. Постарайтесь следовать рекомендациям.

•  Не применяйте индексы в тех случаях, когда фраза where предписывает оператору SQL посетить при использовании индексов больше блоков данных, чем при проведении сканирования полной таблицы. Этого можно добиться, добавив к индексированному столбцу безвредное выражение (например, + 0 для цифрового столбца или конкатенацию с пустой строкой (") для алфавитно-цифровых столбцов), либо путем включения в оператор SQL подсказки FULL (в случае стоимостного

оптимизатора).

Итак, рассмотрим примеры:

1.        В таблице LINE_ITEMS насчитывается 1 млн строк.

2.        Столбец ShippedDate индексирован и используется последующим
запросом.

3.        Распределение данных в столбце Shipped_Date таково, что строки
разбросаны по значительному числу блоков таблицы.

4.        Избирательность индекса хорошая.

Что было: select *

from LINEITEMS where Shipped_Date between SYSDATE and (SYSDATE - 30);

В предыдущем запросе используется индекс по столбцу Shipped_Date, не­смотря на то, что он не оптимален для этой цели. Следовательно, приве­денный выше запрос нужно переписать, используя подсказку FULL (чтобы принудительно вызвать сканирование полной таблицы), а, может быть, учитывая размер таблицы LINE_ITEMS, даже подсказку PARALLEL (в этом случае необходимо предварительно установить параметры файла init.ora). Переписанный запрос может выглядеть так:

Что стало:

select /* + FULL(LINE_ITEMS) PARALLEL(LINE_ITEMS, 2)  */ * from LINE_ITEMS

where Shipped_Date between SYSDATE and (SYSOATE - 30);

Запретите сканирование полной таблицы, когда фраза 

where

предписывает оператору SQL обработать или вернуть лишь малую часть

таблицы, если только таблица не слишком сильно фрагментирована

-

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

поскольку индексы оказывают влияние на время 

вставки, удаления 

и 

обновления 

строк таблицы. Рассмотрим следующие моменты:

В таблице ORDERS 1 млн строк.

2.        Столбец Ord_Id может содержать алфавитно-цифровые значения

(комбинацию чисел и алфавитных данных), которые записываются в

верхнем регистре. Но приложение допускает ввод значений Ord_Id как в верхнем, так и в нижнем регистрах.

3.        Таблица имеет составной индекс по столбцам Ord_Id, Ord_Status и
Ord_Date.

4.        Избирательность индекса хорошая.

Что было: select *

from ORDERS where upper(Ord_Id) = ' :Ы

and OrcLStatus = 'Not Filled'

and Ord_0ate = SYSDATE;

В предыдущем запросе индекс не используется, потому что к его ведуще­му столбцу применена функция 

upper. 

Переменная связи ':Ы 'находится в правой части выражения. Если удалить функцию м^егизведущего столб­ца и перенести ее в правую часть выражения, то появится возможность использовать индекс. Можно переписать запрос так:

Что стало:

select * from ORDERS

where Ordjd = upper (':Ы') and Ord_Status = 'Not Filled' and Ordinate = SYSDATE;

Если значения столбца Ord_Id хранятся не в каком-либо предопределен­ном формате, например в верхнем регистре, а версия базы данных -Oracle8i, можно создать индексы на базе функции, чтобы избежать ска­нирования полной таблицы. В более ранних, чем OracleSi, версиях стоит

рассмотреть хранение значений столбца в каком-либо стандартном реги-
стре: 

верхнем, нижнем 

или 

initcap 

(режим, при котором начальные буквы
слов записываются прописными буквами, а остальные - строчными). Да,
при этом происходит        данных, но отцы-основатели реля-

ционных баз данных и правил нормализации поймут наше затруднитель­ное положение.

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

number, 

не нужно
использовать одиночные кавычки вокруг числового значения во фразе

where. 

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

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

where 

значений должны присутствовать, потому что их отсутствие автоматически приводит к сканированию

полной таблицы.

* Не следует использовать оператор 

null 

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

where. 

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

Что было:

select First_Name, Last_Name, Hire_Date from EMP where Empno = 1234; select First_Name, Last_Name, Hire_Date from EMP where Empno = 9876;

Что стало:

select First_Name, Last_Name,  Hire_Date from EMP where Empno =  :Ы;

Допустим, что приложение нельзя перепрограммировать с переменны-
ми связи, а используемая версия базы данных - OraclcSi (8.1.6). В таком
случае может существенно помочь применение параметра файла init.ora

CURSOR_SHARING=for, 

При этом не только вводится новое понятие
коллективного SQL, но и уменьшается конкуренция за защелку библио-
течного кэша. Параметр 

CURSOR_SHARIN(ka^e 

может быть установ-
лен на уровне сеанса. Однако следует знать, что установка

force 

иногда вызывает существенное замедление син-
таксического анализа, так что предпочтительнее        переписать
приложение.

Не кодируйте внутри цикла PL/SQL итеративные одиночные
операторы 

insert, update или delete 

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

insert и 

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

виртуального устранения генерации записей журнала обновления. К сожалению, подобная опция недоступна для операторов 

delete 

и 

update, 

В любом случае операция, которая может быть выполнена как групповая, не должна проводиться итеративным способом, поскольку операции сами по себе не масштабируются, когда число итеративно

обрабатываемых строк существенно возрастает. Приведем пример: Что было:

declare

Ord_Struct 0RDERS%R0WTYPE; Cursor c_ord is select *

from ORDERS where Ord_Status = 'Not Filled' and Ord_Date = SYSDATE;

begin

open .c_ord; loop

fetch c_ord into Ord_Struct; exit when c_ord%N0TF0UND; insert into TEMPJJRD values (Ord_Struct.Ord_Id, Ord„Struct.Ord_Date,

(Ord„Struct.Ord„Price *  1.1),  Ord_Struct.Ord_Status);

commit;

end loop;

close c_ord;

end;

/

Предшествующий блок кода PL/SQL представляет упрощенный пример использования классической итеративной методики ввода и вычисления

значений для последующей обработки. Здесь определяется новая цена

для        путем увеличения ее на 10%. Приведенный ниже эквива-

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

Заметьте, что атрибут таблицы        должен быть уста-

новлен до выполнения описываемой попытки. Для этого нужно выпол-
нить команду alter table. Да, конечно, в результате Oracle не выполнит
полноценной журнализации действий по вставке данных. Это может
привести к тому, что в случае аварийного завершения задания из-за отка-
за любого вида данные в таблице        станут невосстанавливае-
мыми. Но ввиду временной природы этих данных все, что нужно
сделать, - это еще раз стартовать задание после того, как будут устранены
последствия сбоя. Кроме того, отметьте, что операция commit в настроен-
ной версии выполняется всего один раз, а не на каждой итерации цикла.

Что стало:

declare begin

insert /-append./ into TEMP_0R0

select Ord_Id,  Ord_Date,  (Ord_Price * 1.1),  Ord_Status from ORDERS where Ord_Status = 'Not Filled' and Ord_Date = SYSDATE;

commit;

end;

/

* He кодируйте в своем приложении коррелированные подзапросы, так как они отрицательно влияют на производительность системы, поглощая значительные ресурсы ЦП, потенциально вызывая связанные с ЦП узкие места и не позволяя приложениям масштабироваться.

Значит, если число строк в коррелированных подзапросах растет, мы сами подписали своим ЦП смертный приговор. Вместо этого нужно использовать встроенные представления (подзапросы во фразе 

from 

оператора 

select, 

которые стали доступны с версии 7. ?>), работающие на порядок быстрее и намного лучше масштабирующиеся. В приведенном далее запросе отражены все сотрудники, получающие зарплату выше средней по департаменту, в котором они трудятся. Смотрите , и наслаждайтесь!

Что было: select OUTER. from EMP OUTER where OUTER.Salary > (select Avg(Salary) from EMP INNER where INNER.Dept_Id = OUTER.Dept_Id);

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

Переписанный с помощью встроенных представлений запрос является

функционально эквивалентным, но он в существенно большей степени допускает масштабирование и гарантированно превосходит по произво­дительности своего предшественника.

Что стало: select

from ЕМР Е1,   (select Е2. Dept^Id Dept_Id, Avg(E2. Salary) Avg_Sal from EMP E2 group by Dept_Id) DEPT_AVG_SAL where E1.Dept_Id = DEPT_AVG_SAL.Dept_Id and E1.Salary > DEPT_AVG_SAL.Avg_Sal;

Этот пример приводится только для того, чтобы нас не упрекнули в неполноте нашего списка. Не стройте предикаты фразы 

where 

оператора 

select, 

если у вас не полностью представлены условия соединения для всех таблиц, упомянутых во фразе 

from. 

Мы совсем не хотим, чтобы месье Декарт со своим картезианским произведением (у нас оно больше известно как прямое, или декартово, произведение таблиц. - 

Прим. тф.) 

помогал нам осуществлять план выполнения запроса. Вы будете

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

Не кодируйте транзакционную логику ваших приложений буквально
так, как это описывается в документе по проектированию. Звучит это
весьма радикально, но давайте попробуем объяснить. Поймите, что как
SQL, так и PL/SQL позволяют объединять несколько операций в одну.
Предлагаем вам пример. В документе по проектированию логика
транзакции определяется следующим образом: во временную таблицу
необходимо вставить 1 млн строк, а затем последовательно провести по
12 изменений для каждой из этих строк. Но эти действия не должны
выполнять именно так. Исследуйте использование функции 

decode 

(она
поддерживает логику        г/в операторе SQL) в операторе

insert, чтобы по возможности избежать применения операторов 

update.

На самом деле 

decode 

является очень мощной функцией, при правильной
трактовке ее можно использовать        операций 

greater than

и 

less than. 

Исследуйте применение оператора внешнего соединения

он бывает полезен в довольно большом числе приложений и облегчает

объединение нескольких операций в одну. Помните, что нет ничего плохого в том, чтобы выполнять какие-либо дополнительные вычисления для операции 

insert, 

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

update 

должна уравновесить увеличение стоимости операции 

insert. 

Попытки оптимизировать время реакции каждого оператора SQL без нацеливания на общее время реакции пакетного задания лишает управление вычислениями и ресурсами всякого смысла. Приведенный ниже псевдокод содержит оператор 

create table, 

в котором объединено в одну несколько операций и оптимизируется использование ресурсов: create table DM_SUMMARY_TBL ( Factjd

,D2_Key

,D1 Key

,Datekey

, D2_Col

,D1_Col2,

)

parallel  (degree 12) nologging

partition by range(datekey)

(PI values less than...P36 values less than NOMAXVALUE)

as

select /*+ FUlL(F) FU!..L(D1) FULL(02) */ F.Fact_Id, F.D2_Key, F.D1_Key, F.Datekey, D2.D2_Col, D1.D1_Col1, D1.D1_Col2

from FACT F,  DIMENSION D1,  DIMENSION D2 where D1.D1_key(+) = F. D1_Key and F.D2_key = D2.D2_Key(+);

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

select 

соединяет две или несколько таблиц и соединяемые пары во фразе 

where таковы, 

что одна из таблиц очень мала (скажем, 1 тысяча строк), а другая -слишком велика (1 млн строк). Если при этом предикаты фразы 

where 

таковы, что придется обработать подавляющую часть большой таблицы, то предпочтительной методологией соединения для такой пары будет именно хешированное соединение. Если вы с поистине религиозным пылом пытаетесь устранить сканирование полной таблицы, принудительно заставив каждый оператор SQL приложения использовать вложенные циклы, вы закончите тем, что база данных Oracle получит фантастически высокий коэффициент попадания в буферный кэш, возможно, даже на уровне 99,999%, но ее производительность все же будет оставлять желать лучшего.

Избегайте использования операторов типа select х from DUAL везде, где только возможно. Как бы безобидно он ни выглядел, он может буквально поглотить производительность системы.

К примеру, если требуется заполнить цифровой столбец с помощью

генератора последовательностей, не кодируйте независимый оператор 

select 

для выборки следующего значения ( 

nextval, 

в переменную PL/SQL с последующим использованием этой переменной во фразе 

values 

оператора 

insert. 

Вместо этого используйте во фразе 

values 

оператора 

insert 

операцию 

nextval 

для последовательности (sequcnce_name.nextval). Обратите внимание на существенную разницу между приведенными ниже кодами (что было и что стало). Во втором коде удалена необязательная ссылка на таблицу DUAL.

Что было: declare

Ord_Seq_Val ORDERS.Ord_Id%TYPE; begin

for i  in  1. .10000 loop

select 0rd_Seq. NEXTVAL into Ord_Seq_Val from DUAL;

insert into TEMP_ORD(Ord_Id) values (Ord_Seq_Val); /* Do more misc.  processing */ end loop;

/

declare begin

for i  in   1. .10000

loop

insert into TEMP_ORD(Ord_Id)

values (Ord_Seq_Val.NEXTVAL); /* Do more misc. processing */ end loop; /

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

использовать свой родной язык, если, конечно, хотите, чтобы кто-нибудь из нас смог помочь, если у вас возникнет проблема. У нас уже был один производитель систем планирования ресурсов предприятия (ERP), который пытался заставить нас быть двуязычными. И хватит с нас!