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

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

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

пропускная способность системы. 

Насколько успешно проходит ее трудовой день, успевает ли она сделать вовремя и надлежащим образом все, что необходимо?

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

Как способствовать        оптимальных SQL

Ниже приводится несколько советов по оптимизации производительности SQL. Они расположены отнюдь не по степени убывания важности, а в произволь­ном порядке. Их применение не ограничивается только запросами:

•        С точки зрения производительности ввода/вывода не имеет смысла стремиться к сканированию полной таблицы, если используется индекс.

Имейте в виду, что сканирование полной таблицы может быть очень

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

больше посещений блоков, чем при сканировании полной таблицы.

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

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

Замените во 

фразе where 

оператора SQL предикат 

not exist 

на 

not in.

Вместо функцишиЫг используйте оператор 

like с 

ведущим символом. Этот оператор (например, like        использует индекс для записи значения, с которым производится сравнение. Функция 

substr 

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

Там, где это возможно, используйте функцию 

nvl, 

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

исследований, проведенных некоторыми аналитиками производительности еще в начале 90-х, оказалось, что иЫдает хотя и небольшое, но все-таки преимущество в скорости выполнения по сравнению со стандартной конкатенацией (если представилась возможность выбора между конкатенацией и функцией NVL).

Для очень сложных запросов с большим количеством условий OR стоит переписать запрос с использованием функции 

union all. 

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

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

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

главный-подчиненный (master- detail). 

ш 

Используйте составные индексы (с двумя и более столбцами). Они

должны быть упорядочены по убыванию степени избирательности.

Чем меньше индексов используется в конкретном запросе, тем меньше будет физических операций ввода/вывода, а это, в свою очередь, приведет к увеличению производительности.

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

unique. 

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

primary key 

для столбца Ord_Id в таблице ORDERS, не требуется строить независимый уникальный индекс, если уже существует составной индекс, в котором Ord_Id выступает в роли ведущего столбца.

Рассмотрите использование фразы 

enable novalidatevo 

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

таблицах.

Рассмотрите двоичные индексы, если предикаты фразы 

where 

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

or, and 

и 

not 

по этим столбцам или возвращают

множество строк из таблицы с большим количеством строк. Двоичные

индексы обычно стараются не применять для таблиц со значительным объемом параллельных операций DML из-за присущей им внутренней склонности к созданию блокировок для целого диапазона        (даже в тех случаях, когда обновляется всего одна

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

* Избегайте операторов SQL с включенными в них представлениями. Как бы странно это ни выглядело, но Oracle вовсе не обязательно выполняет представление само по себе точно так же, как в сложном операторе SQL, содержащем таблицы. Рассмотрите включение определения представления в главный запрос путем внесения его кода без реального имени представления. В некоторых случаях наблюдалось существенное увеличение производительности, хотя и были подтвержденные проблемы с тем, как оптимизатор справлялся с представлениями и таблицами, когда ему приходилось сталкиваться с их сложным соединением.

В любых случаях избегайте дистанционного доступа. Будьте особенно

осторожны при соединении локальной таблицы с удаленной таблицей или представлением. Oracle (в зависимости от используемой версии)

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

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

where 

для таблицы. В результате получаем меньший ведущий

набор.

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

коллекции везде, где только уместно и возможно. Это верно и для тех

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

большой части значений из столбца Productjd (для этого используется новая опция Oracle8i 

bulk collect), 

а затем на 20% уменьшаются значения столбца ReorderJLevel таблицы PRODUCTS. Уменьшение уровней повторных заказов было инициировано благодаря недавно

declare

TYPE Ord_Id_Tab IS ТАВРЕ OF PRODUCTS. Product_Id*TYPE;

begin

/* Retrieve all values of Product_Id that        relevant  */

select /+ FUPP   (PROBUCTS)   */ Product_Id BUPK COPPECT into Product_Id_Tab; from PRODUCTS where ProductJJame like    A%"; forall i in 1. .Product_Id_Tab.LAST update PRODUCTS

set REORDER_LEVEL =   (REORDER1LEVEL *  0.8), where Productjd = Product_Id_Tab(i); /* Do more processing */ end;

/        . •     '

•        Если версия базы данныхОгас1е81, а приложение содержит большой объем генерации динамических SQL (с использованием DBMS_SQL), рассмотрите использование новой опции PL/SQL 

execute immediate, 

которая работает существенно лучше, чем DBMS_SQL. Далее приводится простой блок PL/SQL, в котором 

execute immediate 

применяется для увеличения ширины столбца Ord_Id таблицы ORDERS с 8 символов (его текущего размера) до 10 символов. Кроме того, новая возможность поддерживает в PL/SQL команды DDL, не требуя при этом написания многих строк кода для        чтобы выполнить нечто достаточно простое:

declare begin

execute immediate 'alter table ORDERS modify (Ord_Id VARCHAR(10))' ;

end; /

•        Для очень больших таблиц рассмотрите возможность воспользоваться преимуществами секционирования таблиц и

/

способом, которым они влияют на потребление пространства в буферном кэше базы данных области SGA Oracle. При проектировании и планировании секционирования не следует упускать из виду требования приложения.

•        Если вы все еще пользуетесь оптимизатором, основанным на системе правил (давно уже пора отказаться от него), структурируйте 

фразу from 

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

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

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

При использовании стоимостного оптимизатора порядок перечислениятаблицвофразегготнеиграетроли.если только не используется подсказка /*+ORDERED */. В таком случае ведущей таблицей соединения должна быть именно первая таблица во фразе from. Кроме того, в стоимостном оптимизаторе не оказывает влияния порядок предикатов во фразе where. До тех пор, пока указывается ведущий столбец индекса, он будет рассматриваться для плана выполнения второго запросов? Приложение, среда, нагрузка на систему и даже время суток, когда выполняются запросы - все это должно помочь в ответе на поставленные вопросы.