НАСТРОЙКА ПРЕДЛОЖЕНИЙ SQL
Настройка ваших предложений SQL - важная часть процесса
достижения наилучшей возможной производительности ORACLE. Вы
должны настроить ваши предложения SQL до того, как ваш
администратор базы данных настроит сам ORACLE:
* Даже если вы не знакомы с внутренними тонкостями ORACLE,
вы можете существенно улучшить производительность путем
настройки вашего приложения на основе знания того, как
ORACLE исполняет предложения SQL.
* Если ваши предложения SQL не настроены, они могут
выполняться неэффективно, даже если сам ORACLE хорошо
настроен.
Эта глава рассказывает вам:
* как писать предложения SQL для лучшей производительности
ваших новых приложений
* как оптимизировать производительность предложений SQL в
ваших существующих приложениях
Эта глава предполагает, что вы знакомы с понятием плана
исполнения, и знаете, как он генерируется оптимизатором ORACLE.
Эта информация приведена в главе 13 "Оптимизатор" документа
ORACLE7 Server Concepts Manual.
После того, как вы настроите ваши предложения SQL, администратор
вашей базы данных может приступить к настройке ORACLE, как
описывается в главе 20 "Понимание процесса настройки" документа
ORACLE7 Server Administrator's Guide.
Как писать новые предложения SQL
Если вы пишете предложения SQL в новом приложении, выполните
следующие шаги, чтобы оптимизировать эти предложения:
* Создайте индексы, которые смогут использоваться вашими
предложениями.
* Создайте кластеры, чтобы оптимизировать ваши операции
соединения.
* Создайте хэш-кластеры, которые смогут использоваться
вашими предложениями.
* Выберите подход к оптимизации для ваших предложений.
* Используйте советы в ваших предложениях, где это имеет
смысл.
* Проведите сравнение альтернативных синтаксисов для ваших
предложений.
Все эти шаги обсуждаются в последующих секциях.
Как использовать индексы
------------------------
Эта секция дает рекомендации по созданию индексов и обсуждает
следующие вопросы:
* как решить, когда создавать индексы
* как выбрать, какие столбцы индексировать
* как использовать составные индексы
* как писать предложения, использующие индексы
Приняв решение о создании индекса, вы можете выдать команду
CREATE INDEX, чтобы создать этот индекс. Для дополнительной
информации о создании индексов обратитесь к главе 2 этого
руководства, "Управление объектами схемы".
Когда создавать индексы
Индексы улучшают производительность тех запросов, которые
выбирают небольшой процент строк из таблицы. Как общее правило,
вы должны создавать индексы по таблицам, из которых часто
выбирается 2-4% строк. Это правило основывается на следующих
предположениях:
* Строки с одинаковыми значениями того столбца, на котором
построен запрос, равномерно распределены между блоками
данных, распределенными таблице.
* Строки в таблице упорядочены случайным образом по
отношению к столбцу, на котором построен запрос.
* Каждый блок данных, распределенный таблице, содержит по
меньшей мере 10 строк.
* Таблица содержит относительно малое количество столбцов.
* Большинство запросов по таблице имеют относительно
простые фразы WHERE.
Если эти предположения не характеризуют данные в вашей таблице и
запросы, выбирающие их, то процент выбираемых строк, при котором
индекс полезен, может возрасти вплоть до 25%.
Как выбирать столбцы для индекса
При выборе столбцов для индекса руководствуйтесь следующими
правилами:
* Индексируйте столбцы, которые часто используются в фразах
WHERE.
* Индексируйте столбцы, которые часто используются для
соединения таблиц в предложениях SQL. Для дополнительной
информации об оптимизации соединений обратитесь к секции
"Как использовать кластеры" на странице 5-8.
* Индексируйте лишь столбцы, обладающие хорошей
селективностью. СЕЛЕКТИВНОСТЬ столбца - это процент
строк, имеющих одинаковое значение для индексированного
столбца. Селективность столбца хороша, если мало строк
имеют одинаковые значения для этого столбца. Заметьте,
что ORACLE неявно создает индексы по столбцам,
определенным (через ограничения целостности) как
первичные или уникальные ключи. Такие индексы наиболее
селективны и наиболее эффективны для оптимизации
производительности.
Вы можете определить селективность столбца, поделив число
строк в таблице на число различных индексированных
значений. Вы можете получить эти значения с помощью
команды ANALYZE. Селективность, вычисленная таким
способом, должна трактоваться как процент.
* Не индексируйте столбцы, имеющие мало различающихся
значений. Такие столбцы обычно имеют низкую
селективность, и потому не оптимизируют
производительность, если только часто выбираемые значения
столбца не встречаются намного реже, чем остальные
значения этого столбца.
Например, рассмотрим столбец, который содержит равные
количества значений 'YES' и 'NO'. Индексирование такого
столбца обычно не приводит к улучшению
производительности. Однако, если значение 'YES'
встречается относительно редко, а ваше приложение часто
опрашивает на 'YES', то индексирование такого столбца
может улучшить производительность.
* Не индексируйте столбцы, которые часто модифицируются.
Предложения UPDATE, модифицирующие индексированные
столбцы, и предложения INSERT и DELETE, модифицирующие
индексированные таблицы, выполняются дольше, чем если бы
индекса не было. Такие предложения SQL должны
модифицировать данные в индексах, когда они модифицируют
данные в таблице.
* Не индексируйте столбцы, которые появляются в фразах
WHERE только с функциями или операторами. Фраза WHERE,
использующая с индексированным столбцом функцию (отличную
от MIN или MAX) или оператор, не делает возможным путь
доступа, использующий индекс по этому столбцу.
* Индексируйте внешние ключи, входящие в ограничения
ссылочной целостности, в тех случаях, когда большое
количество одновременных предложений INSERT, UPDATE и
DELETE обращаются к родительской и порожденной таблицам.
Такой индекс позволяет ORACLE модифицировать данные в
порожденной таблице, не блокируя родительскую таблицу.
Решая, индексировать ли данный столбец, учитывайте, компенсирует
ли выигрыш в производительности, достигаемый для запросов, тех
потерь производительности, которые будут иметь место для
предложений INSERT, UPDATE и DELETE, а также той памяти, которая
будет затрачена на индекс. Вы можете провести эксперименты и
сравнить время обработки ваших предложений SQL с индексами и без
них. Время обработки можно измерять с помощью средства
трассировки SQL. Для информации о средстве трассировки SQL
обратитесь к приложению B "Инструменты диагностики
производительности" в этом руководстве.
Как выбирать составные индексы
СОСТАВНОЙ ИНДЕКС - это индекс, состоящий из более чем одного
столбца. Составные индексы могут предоставлять дополнительные
преимущества по сравнению с одностолбцовыми индексами:
лучшая Иногда можно скомбинировать два или более
селективность столбцов, каждый из которых обладает низкой
селективностью, в составной индекс, имеющий
хорошую селективность.
дополнительный Если все столбцы, выбираемые запросом, входят в
источник составной индекс, то ORACLE может возвратить эти
данных значения прямо из индекса, не обращаясь к
таблице.
Предложение SQL может использовать путь доступа, включающий
составной индекс, если это предложение содержит конструкты,
которые используют ведущую порцию индекса. ВЕДУЩАЯ ПОРЦИЯ
индекса - это один или несколько столбцов, которые были
специфицированы первыми и подряд в списке столбцов предложения
CREATE INDEX, с помощью которого был создан индекс. Рассмотрим
следующее предложение CREATE INDEX:
CREATE INDEX comp_ind
ON tab1(x, y, z)
Следующие комбинации столбцов являются ведущими порциями этого
индекса: X, XY и XYZ. Другие комбинации столбцов, например, XZ,
YZ или Z, не являются ведущими порциями этого индекса.
При выборе столбцов для составных индексов руководствуйтесь
следующими правилами:
* Создавайте составной индекс по тем столбцам, которые
часто используются вместе в условиях фразы WHERE, будучи
соединены операторами AND, особенно если их
комбинированная селективность лучше, чем индивидуальная
селективность каждого столбца по отдельности. Если
некоторые из столбцов, составляющих индекс, используются
в фразах WHERE более часто, не забудьте обеспечить, чтобы
эти столбцы составляли ведущую порцию индекса, с тем,
чтобы предложения, использующие только эти столбцы, могли
использовать доступ через индекс.
* Если существует ряд запросов, базирующихся на одном и том
же наборе столбцов, рассмотрите возможность создания
составного индекса, в который вошли бы все эти столбцы,
образуя его ведущую порцию.
* Если составной индекс должен использоваться в запросах,
базирующихся на значениях нескольких столбцов, то
упорядочение этих столбцов от более селективного к менее
селективному в предложении CREATE INDEX лучше всего
повышает производительность запросов.
Разумеется, необходимо учитывать и вопросы, касающиеся общих
преимуществ и недостатков индексов, которые рассматривались в
предыдущих секциях.
Как писать предложения, использующие индексы
После того, как вы создали индекс, оптимизатор не сможет
использовать путь доступа через этот индекс просто потому, что
он существует. Оптимизатор может выбрать такой путь доступа для
предложения SQL лишь тогда, когда это предложение содержит
конструкт, делающий этот путь возможным. Для информации о путях
доступа и конструктах, делающих их возможными, обратитесь к
главе 13 "Оптимизатор" документа ORACLE7 Server Concepts Manual.
Чтобы гарантировать, что предложение SQL может использовать путь
доступа через индекс, обеспечьте, чтобы это предложение
содержало конструкт, делающий этот путь возможным. Если вы
используете стоимостной подход, вы должны также сгенерировать
статистики для индекса. После того, как вы сделаете путь
доступа через индекс возможным для предложения, оптимизатор
сможет выбрать или не выбрать его, в зависимости от других
возможных путей доступа.
Как писать предложения, избегающие использования индексов
В некоторых случаях вы можете захотеть предотвратить возможность
использования пути доступа через существующий индекс. Например,
вы можете знать, что индекс очень селективен для вашего случая,
и что полный просмотр таблицы был бы более эффективен. Если
предложение содержит конструкт, который делает возможным путь
доступа через индекс, вы можете заставить оптимизатор
использовать полный просмотр таблицы с помощью одного из
следующих методов:
* Вы можете сделать индексный путь доступа недоступным,
модифицировав предложение так, чтобы не изменить его
смысла. Этот метод проиллюстрирован на примере,
показанном ниже.
* Вы можете использовать совет FULL, чтобы заставить
оптимизатор выбрать полный просмотр таблицы вместо
просмотра индекса.
* Вы можете использовать совет INDEX или AND_EQUAL, чтобы
заставить оптимизатор использовать один индекс или набор
индексов вместо другого.
Так как поведение оптимизатора может измениться в будущих
версиях ORACLE, полагаться на первый метод для выбора пути
доступа в долгосрочном плане не следует. Вместо этого
используйте советы, чтобы предлагать оптимизатору конкретные
пути доступа. Для информации о советах обратитесь к секции "Как
использовать советы" на странице 5-14.
Пример
Рассмотрим следующие запросы, которые выбирают строки из
таблицы, базируясь на значении единственного столбца:
SELECT *
FROM tab1
WHERE col1 = 'A'
SELECT *
FROM tab1
WHERE col1 = 'B'
Предположим, что значениями столбца COL1 являются буквы от A до
Z. Предположим также, что таблица имеет 1000 строк, и что 75%
этих строк имеют значение столбца COL1, равное 'A'. Каждая из
остальных букв появляется в 1% строк.
Так как значение 'A' появляется в 75% строк таблицы, первый
запрос, вероятно, был бы выполнен быстрее путем полного
просмотра таблицы, чем через просмотр индекса по столбцу COL1.
Так как значение 'B' появляется в 1% строк, для второго запроса
эффективнее был бы просмотр индекса по столбцу COL1.
Следовательно, желательно создать индекс для второго запроса, но
нежелательно, чтобы этот индекс использовался в первом запросе.
Однако количество экземпляров каждого конкретного значения
столбца COL1 неизвестно оптимизатору. Оптимизатор выберет один
и тот же путь доступа для обоих запросов, несмотря на
существенную разницу в процентах строк, возвращаемых каждым
запросом.
Чтобы добиться лучшей производительности для обоих запросов,
создайте индекс по столбцу TAB1.COL1, чтобы он мог
использоваться вторым запросом:
CREATE INDEX col1_ind
ON tab1(col1)
Модифицируйте фразу WHERE первого запроса так, чтобы она сделала
невозможным путь доступа через индекс COL1_IND:
SELECT *
FROM tab1
WHERE col1 || '' = 'A'
Это изменение предотвращает использование для этого запроса пути
доступа через индекс COL1_IND. Индексные пути доступа
невозможны, если фраза WHERE выполняет операцию или функцию на
индексированном столбце. Поэтому оптимизатор будет вынужден
выбрать полный просмотр таблицы для этого запроса.
Заметьте, что это изменение фразы WHERE не изменяет результат
условия, так что множество строк, возвращаемых запросом,
остается тем же самым. Для столбца, содержащего числовые данные
или дату, вы можете достичь той же цели, изменив фразу WHERE
так, чтобы значение столбца прибавлялось к 0.
Как использовать кластеры
-------------------------
При выборе таблиц для кластеризации руководствуйтесь следующими
правилами:
* Кластеризуйте таблицы, которые часто опрашиваются вашими
приложениями в операциях соединения.
* Не кластеризуйте таблицы, если ваше приложение соединяет
их не часто, или часто модифицирует значения их общих
столбцов. Модификация ключа кластера в строке таблицы
требует больше времени, чем такая же модификация в
некластеризованной таблице, потому что ORACLE может
оказаться вынужденным перенести модифицированную строку в
другой блок, чтобы поддержать кластер.
* Не кластеризуйте таблиц, если ваше приложение часто
выполняет по ним полные просмотры. Полный просмотр
кластеризованной таблицы может потребовать больше
времени, чем полный просмотр некластеризованной таблицы.
Кластеризованная таблица занимает большее количество
блоков, так как она разделяет их с другими таблицами.
* Кластеризуйте главную и подчиненную таблицы, если вы
часто выбираете главную запись, а затем соответстввующие
ей детальные записи. Поскольку в кластере детальные
записи хранятся в одних и тех же блоках данных с главными
записями, они, скорее всего, уже находятся в памяти,
когда вы выбираете их, и ORACLE должен выполнять меньше
ввода-вывода.
* Кластеризуйте одну подчиненную таблицу, если вы часто
выбираете много детальных записей для одной и той же
главной записи. Эта мера улучшает производительность
запросов, выбирающих детальные записи для заданной
главной записи, и в то же время не снижает
производительности полных просмотров по главной таблице.
* Не кластеризуйте таблиц, если совокупность данных из всех
таблиц с одинаковым значением ключа кластера превышает
один или два блока ORACLE. При обращении к строке
кластеризованной таблицы ORACLE считывает все блоки,
содержащие строки с этим значением ключа. Если эти
строки занимают несколько блоков, то доступ к одиночной
строке потребует больше операций чтения, чем доступ к той
же самой строке в некластеризованной таблице.
Рассмотрите достоинства и недостатки кластеров по отношению к
потребностям вашего приложения. Например, вы можете решить, что
выигрыш в производительности для операций соединения
перевешивает потери в производительности для тех предложений,
которые модифицируют значения ключа кластера. Вы можете
поэкспериментировать и сравнить время обработки для ваших таблиц
как с кластером, так и без него. Для создания кластера
используйте команду CREATE CLUSTER. Для дополнительной
информации о создании кластеров обратитесь к главе 2 "Управление
объектами схемы" этого руководства.
Как использовать хэширование
----------------------------
При выборе таблиц для хэширования руководствуйтесь следующими
правилами:
* Используйте хэш-кластеры для таблиц, доступ к которым
часто осуществляется предложениями SQL, фразы WHERE в
которых используют условия равенства по одному и тому же
столбцу или комбинации столбцов. Назначьте этот столбец
или комбинацию столбцов ключом кластера.
* Храните таблицу в хэш-кластере, если вы можете
определить, сколько памяти требуется на все строки с
данным значением ключа кластера, учитывая все строки,
которые могут быть добавлены в таблицу в будущем.
* Не используйте хэш-кластеров, если память в вашей базе
данных дефицитна, и вы не можете позволить себе
распределить дополнительную память для тех строк, которые
будут вставлены в будущем.
* Не используйте хэш-кластер для размещения постоянно
растущей таблицы, если для вас непрактично периодически
создавать новый хэш-кластер большего размера под эту
таблицу.
* Не храните таблицу в хэш-кластере, если ваше приложение
часто использует полные просмотры этой таблицы, и вы
чувствуете, что вам придется распределить порядочное
количество лишней памяти для хэш-кластера в предвидении
существенного роста таблицы в будущем. Полные просмотры
таблицы должны считывать все блоки, распределенные
хэш-кластеру, даже если некоторые блоки содержат мало
строк. Размещение таблицы вне кластера сокращает число
блоков, считываемых при полном просмотре таблицы.
* Не храните таблицу в хэш-кластере, если ваше приложение
часто модифицирует значения ключа кластера. Модификация
ключа кластера в строке таблицы требует больше времени,
чем такая же модификация в некластеризованной таблице,
потому что ORACLE может оказаться вынужденным перенести
модифицированную строку в другой блок, чтобы поддержать
кластер.
* Размещение одиночной таблицы в хэш-кластере может быть
полезным, независимо от того, часто ли эта таблица
используется в операциях соединения с другими таблицами,
при условии, что предыдущие условия в этом перечне
показывают приемлемость хэширования для данной таблицы.
Рассмотрите достоинства и недостатки хэш-кластеров по отношению
к потребностям вашего приложения. Вы можете
поэкспериментировать и сравнить время обработки для ваших таблиц
как с хэш-кластером, так и без него (с индексом). Для создания
хэш-кластера используйте команду CREATE CLUSTER с параметрами
HASH и HASHKEYS. Для дополнительной информации о создании
хэш-кластеров обратитесь к главе 2 "Управление объектами схемы"
этого руководства.
Как определить количество хэш-значений
При создании хэш-кластера вы должны использовать параметр
HASHKEYS предложения CREATE CLUSTER, чтобы указать количество
хэш-значений для этого хэш-кластера. Для лучшей
производительности хэш-просмотров выбирайте значение HASHKEYS не
меньше, чем количество значений ключа кластера. Такое значение
уменьшит число КОЛЛИЗИЙ, т.е. случаев, когда разные значения
ключа кластера дают одно и то же хэш-значение. Коллизии
заставляют ORACLE после выполнения хэш-просмотра проверять
строки в каждом блоке, отыскивая нужное значение ключа кластера.
Поэтому коллизии снижают производительность хэш-просмотров.
ORACLE всегда округляет заданное вами значение HASHKEYS вверх до
ближайшего простого числа, чтобы вычислить действительное
количество хэш-значений. Это округление имеет целью сокращение
числа коллизий.
Как выбирать подход оптимизации
-------------------------------
Эта секция обсуждает следующие вопросы:
* когда использовать стоимостной подход
* как выбирать цель для стоимостного подхода
* когда и как генерировать статистики для стоимостного
подхода
* когда использовать регулярный подход
Когда использовать стоимостной подход
В общем, вы должны использовать стоимостной подход для всех
ваших новых приложений. Стоимостной подход обычно выбирает план
исполнения, который как минимум не хуже плана исполнения,
выбираемого регулярным подходом, особенно для больших запросов с
множественными соединениями или множественными индексами.
Стоимостной подход также улучшает продуктивность разработки,
устраняя необходимость вам самим настраивать ваши предложения
SQL.
Чтобы задействовать стоимостной подход для предложения, соберите
статистики для таблиц, адресуемых этим предложением, и
убедитесь, что параметр инициализации OPTIMIZER_MODE имеет свое
умалчиваемое значение COST.
Вы можете также задействовать стоимостной подход следующими
способами:
* Чтобы включить стоимостную оптимизацию для вашей сессии,
выдайте команду ALTER SESSION со значением параметра
OPTIMIZER_GOAL, равным ALL_ROWS или FIRST_ROWS.
* Чтобы включить стоимостную оптимизацию для
индивидуального предложения SQL, используйте совет
ALL_ROWS или FIRST_ROWS. Для информации о советах
обратитесь к секции "Как использовать советы" на странице
5-14.
Генерация статистик
Поскольку стоимостной подход опирается на статистики, вы должны
сгенерировать статистики для всех таблиц, кластеров и индексов,
адресуемых в ваших предложениях SQL, прежде чем сможете
применять стоимостной подход. Если размеры и распределение
данных в ваших таблицах часто изменяются, вы должны генерировать
эти статистики регулярно, чтобы гарантировать, что они точно
отражают данные в таблицах.
ORACLE может генерировать статистики двумя способами:
* путем оценки, основанной на произвольной выборке данных
* путем точного вычисления
Используйте, как правило, оценку вместо точного вычисления, если
у вас нет оснований полагать, что вам требуются точные значения:
* Вычисление всегда обеспечивает точные значения, но может
потребовать большего времени. Время, требуемое для
вычисления статистик по таблице, сравнимо с временем
выполнения полного просмотра этой таблицы.
* Оценивание зачастую намного быстрее, чем вычисление,
особенно для больших таблиц, потому что при оценивании
никогда не считывается больше 1064 строк таблицы.
Результаты, как правило, близки к точным.
Сбор статистик по таблице блокирует эту таблицу и препятствует
доступу к ней предложений SELECT, INSERT, UPDATE и DELETE. По
этой причине, оценивание особенно полезно при сборе статистик по
таблицам, к которым одновременно обращаются другие приложения,
потому что при этом минимизируется время недоступности этих
таблиц. По той же причине, не собирайте статистик по вашей
производственной базе данных во время периодов высокой
активности.
Когда вы генерируете статистики для таблицы, столбца или
индекса, ORACLE заменяет существующие статистики по этому
объекту, если они уже есть в словаре данных, новыми
статистиками. ORACLE помечает как недействительные все ранее
разобранные предложения SQL, которые обращаются к любому из
анализируемых объектов. При очередном исполнении такого
предложения оптимизатор автоматически выберет новый план
исполнения, базирующийся на новых статистиках. Распределенные
предложения, выданные на удаленных базах данных, которые
обращаются к анализируемым объектам, будут использовать новые
статистики при очередном разборе.
Некоторые статистики всегда вычисляются, независимо от того,
выбрали ли вы оценку или точное вычисление. Если вы выбрали
оценку, но время, которое экономится за счет выборки,
незначительно, то ORACLE выбирает вычисление.
Чтобы сгенерировать статистики, используйте команду ANALYZE.
Пример
Следующий пример генерирует статистики для таблицы EMP и ее
индексов:
ANALYZE TABLE emp
ESTIMATE STATISTICS
Выбор цели для стоимостного подхода
План исполнения, выбираемый оптимизатором, может изменяться в
зависимости от цели оптимизатора. Оптимизация для лучшей
пропускной способности, т.е. для минимизации времени на
возвращение всех строк, обрабатываемых предложением, с большей
вероятностью приведет к полному просмотру таблицы, чем к
просмотру индекса, и к соединению через сортировку-слияние,
нежели к соединению через вложенные циклы. Оптимизация для
лучшего времени ответа, т.е. минимизация времени на возвращение
первой строки, обрабатываемой предложением, с большей
вореятностью приведет к доступу через индекс и соединению через
вложенные циклы. Например, рассмотрим предложение соединения,
которое может быть выполнено либо операцией вложенных циклов,
либо операцией сортировки-слияния. Операция сортировки-слияния
быстрее возвратит весь результат запроса, тогда как операция
вложенных циклов может быстрее возвратить первую строку.
Поэтому, если целью является лучшая пропускная способность,
оптимизатор скорее выберет соединение через сортировку-слияние;
и наоборот, если целью является лучшее время ответа, оптимизатор
предпочтет соединение через вложенные циклы.
Выберите цель для оптимизатора, базируясь на потребностях вашего
приложения:
* Для приложений, выполняемых в пакетном режиме, таких как
отчеты SQL*ReportWriter, оптимизируйте для лучшей
пропускной способности. Пропускная способность обычно
более важна в пакетных заданиях, потому что пользователь,
инициировавший это задание, заинтересован лишь в том,
чтобы скорее закончить все приложение. Время ответа
здесь менее важно, так как пользователь не анализирует
результатов индивидуальных предложений, пока приложение
не закончило работу.
* Для интерактивных приложений, таких как приложения
SQL*Forms или запросы SQL*Plus, оптимизируйте для лучшего
времени ответа. Время ответа в интерактивных приложениях
обычно важно, потому что интерактивный пользователь ждет
появления первой строки, возвращаемой предложением.
По умолчанию, стоимостной подход оптимизирует для лучшей
пропускной способности. Вы можете изменить цель стоимостного
подхода следующими способами:
* Чтобы изменить цель стоимостного подхода для всех
предложений SQL в вашей сессии, выдайте команду ALTER
SESSION с параметром OPTIMIZER_GOAL.
* Чтобы специфицировать цель стоимостного подхода для
индивидуального предложения SQL, используйте совет
ALL_ROWS или FIRST_ROWS. Для информации о советах
обратитесь к секции "Как использовать советы" на странице
5-14.
Пример
Следующее предложение изменяет цель стоимостного подхода для
вашей сессии на лучшее время ответа:
ALTER SESSION
SET OPTIMIZER_GOAL = FIRST_ROWS
Когда использовать регулярную оптимизацию
Если вы разрабатывали приложения, используя предыдущую версию
ORACLE, и проводили тщательную настройку ваших предложений SQL,
основываясь на правилах оптимизатора, то вы можете захотеть
продолжать использование регулярной оптимизации и после перевода
этих приложений на ORACLE7. Если вы не собирали статистику и не
добавляли советов в ваши предложения SQL, то эти предложения
будут продолжать использовать регулярную оптимизацию. Однако в
конце концов вы должны перевести ваши существующие приложения на
стоимостной подход, потому что регулярный подход не будет
поддерживаться в будущих версиях ORACLE.
Вы можете попробовать стоимостной подход на ваших приложениях,
просто собрав статистики. После этого вы можете вернуться к
регулярной оптимизации, просто удалив эти статистики, либо
установив значение параметра инициализации OPTIMIZER_MODE (или
параметра OPTIMIZER_GOAL команды ALTER SESSION) в RULE. Вы
можете также использовать это значение, если хотите просто
собрать и исследовать статистики по вашим данным, не переходя на
применение стоимостного подхода.
Как использовать советы
-----------------------
Как разработчик приложения, вы можете иметь о ваших данных такую
информацию, которая неизвестна оптимизатору. Например, вы
можете знать, что некоторый индекс более селективен для тех или
иных запросов, чем это может определить оптимизатор. Базируясь
на этой информации, вы можете быть способны выбирать более
эффективные планы исполнения, чем это может сделать оптимизатор.
В таком случае, вы можете использовать советы, чтобы заставить
оптимизатор использовать тот план исполнения, который выбран
вами.
СОВЕТЫ - это подсказки, которые вы даете оптимизатору для
конкретного предложения SQL. Советы позволяют вам принимать те
решения, которые обычно принимаются оптимизатором. Вы можете
использовать советы, чтобы специфицировать:
* подход к оптимизации для предложения SQL
* цель стоимостного подхода для предложения SQL
* путь доступа для таблицы, адресуемой предложением
* порядок соединения для предложения соединения
* операцию соединения для предложения соединения
Совет применяется к оптимизации лишь того блока предложения, в
котором он появляется. БЛОК ПРЕДЛОЖЕНИЯ - это одно из следующих
предложений или частей предложений:
* простое предложение SELECT, UPDATE или DELETE
* родительское предложение или подзапрос сложного
предложения
* часть составного запроса
Например, составной запрос, состоящий из двух составляющих
запросов, объединенных оператором UNION, имеет два блока
предложения, по одному для каждого составляющего запроса.
Поэтому советы в первой компоненте такого запроса применяются
только к оптимизации первого составляющего запроса, и не
применяются к оптимизации второго составляющего запроса.
Вы можете посылать советы оптимизатору для предложения SQL,
оформляя их как комментарии внутри предложения. Для
дополнительной информации о комментариях обратитесь к главе 2
"Элементы SQL" документа ORACLE7 Server SQL Language Reference
Manual.
Блок предложения может иметь лишь один комментарий, содержащий
советы. Этот комментарий может следовать только за ключевым
словом SELECT, UPDATE или DELETE. Следующие синтаксические
диаграммы показывают синтаксис советов для обоих типов
комментариев, которые ORACLE поддерживает в блоке предложения:
------------------¬
----T-- DELETE --T-- /*+ ----T-- совет --T--+-- */ ----
+-- SELECT --+ L-- текст ---
L-- UPDATE ---
------------------¬
----T-- DELETE --T-- --+ ----T-- совет --T--+----------
+-- SELECT --+ L-- текст ---
L-- UPDATE ---
где:
DELETE, ключевое слово DELETE, SELECT или UPDATE,
SELECT, которое начинает блок предложения. Комментарии,
UPDATE содержащие советы оптимизатору, могут появляться
только за этими ключевыми словами.
+ знак "плюс", который заставляет ORACLE
интерпретировать данный комментарий как список
советов. Знак "плюс" должен следовать
непосредственно за разделителем комментария (без
разделяющего их пробела).
совет один из советов, обсуждаемых ниже в этой секции.
Если комментарий содержит несколько советов,
между каждой парой советов должен стоять хотя бы
один пробел.
текст любой комментирующий текст, которым могут
перемежаться советы.
Если вы специфицируете советы некорректно, ORACLE игнорирует их,
но не возвращает ошибку:
* ORACLE игнорирует советы, если содержащий их комментарий
не следует непосредственно за ключевым словом SELECT,
UPDATE или DELETE.
* ORACLE игнорирует советы, содержащие синтаксические
ошибки, но принимает остальные корректно заданные советы
внутри того же самого комментария.
* ORACLE игнорирует комбинации противоречащих друг другу
советов, но принимает остальные советы внутри того же
самого комментария.
ORACLE также игнорирует любые советы в предложениях SQL в
окружениях, использующих PL/SQL версии 1, например, в триггерах
SQL*Forms версии 3.
Оптимизатор распознает советы лишь тогда, когда используется
стоимостной подход. И обратно, если вы включите в блок
предложения любой совет (за исключением совета RULE), то
оптимизатор автоматически использует стоимостной подход.
Следующие секции показывают синтаксис каждого совета.
Советы для оптимизации подходов и целей
Советы, описанные в этой секции, позволяют вам выбирать между
стоимостным и регулярным подходами к оптимизации, а также, при
стоимостном подходе, между целями лучшей пропускной способности
и лучшего времени ответа. Если предложение SQL содержит совет,
специфицирующий подход и цель оптимизации, то оптимизатор будет
использовать предписанный этим советом подход, независимо от
наличия или отсутствия статистики, значения параметра
инициализации OPTIMIZER_MODE и значения параметра OPTIMIZER_GOAL
команды ALTER SESSION.
ALL_ROWS
Совет ALL_ROWS явно выбирает стоимостной подход для оптимизации
блока предложения с целью лучшей пропускной способности.
Например, для следующего предложения будет выбран стоимостной
подход с целью лучшей пропускной способности:
SELECT /*+ ALL_ROWS */ empno, ename, sal, job
FROM emp
WHERE empno = 7566
FIRST_ROWS
Совет FIRST_ROWS явно выбирает стоимостной подход для
оптимизации блока предложения с целью лучшего времени ответа.
Этот совет заставляет оптимизатор принять следующие решения:
* Если доступен просмотр индекса, оптимизатор всегда
предпочитает его полному просмотру таблицы.
* Если доступен просмотр индекса, оптимизатор всегда
предпочитает соединение через вложенные циклы соединению
через сортировку-слияние, если ассоциированная таблица
является потенциально внутренней таблицей для операции
вложенных циклов.
* Если просмотр индекса становится возможен благодаря фразе
ORDER BY, то оптимизатор всегда выбирает его, чтобы
избежать операции сортировки.
Например, для следующего предложения будет выбран стоимостной
подход с целью лучшего времени ответа:
SELECT /*+ FIRST_ROWS */ empno, ename, sal, job
FROM emp
WHERE empno = 7566
Оптимизатор игнорирует этот совет в блоках предложений DELETE и
UPDATE, а также в тех блоках предложения SELECT, которые
содержат любой из следующих синтаксических конструктов:
* операторы множеств (UNION, INTERSECT, MINUS, UNION ALL)
* фразу GROUP BY
* фразу FOR UPDATE
* групповые функции
* оператор DISTINCT
Такие предложения не могут быть оптимизированы для лучшего
времени ответа, потому что ORACLE должен извлечь все строки,
затрагиваемые предложением, прежде чем сможет возвратить первую
строку. Если вы специфицируете этот совет для одного из
перечисленных предложений, то оптимизатор будет использовать
стоимостной подход, но оптимизирует для лучшей пропускной
способности.
Если вы специфицируете совет ALL_ROWS или FIRST_ROWS в
предложении SQL, но словарь данных не содержит никаких статистик
для таблиц, адресуемых этим предложением, то оптимизатор
использует умалчиваемые статистические характеристики, такие как
объем памяти, распределенной этим таблицам, чтобы оценить
отсутствующие статистики и выбрать план исполнения. Поскольку
такие оценки не столь точны, как статистики, генерируемые
командой ANALYZE, вы должны выполнить команду ANALYZE, чтобы
собрать статистики для всех таблиц, затрагиваемых всеми вашими
предложениями, которые используют стоимостную оптимизацию.
Если вы одновременно с советом ALL_ROWS или FIRST_ROWS
специфицируете советы для путей доступа или операций соединения,
то оптимизатор игнорирует совет ALL_ROWS или FIRST_ROWS, и
использует стоимостной подход и те пути доступа и операции
соединения, которые специфицированы советами.
RULE
Совет RULE явно выбирает регулярный подход для оптимизации блока
предложения. Этот совет также заставляет оптимизатор
игнорировать любые другие советы, которые специфицированы для
этого блока предложения. Например, для следующего предложения
будет выбран регулярный подход:
SELECT --+ RULE
empno, ename, sal, job
FROM emp
WHERE empno = 7566
Совет RULE, так же, как и регулярный подход, не будет доступен в
будущих версиях ORACLE.
Советы для методов доступа
Каждый из советов, описанных в этой секции, предлагает метод
доступа для таблицы. Задание одного из этих советов заставляет
оптимизатор выбрать предписанный путь доступа, при условии, что
этот путь доступа возможен благодаря существованию индекса или
кластера и синтаксическим конструктам предложения SQL. Для
обсуждения методов доступа, а также синтаксических конструктов и
соответствующих путей доступа, обратитесь к главе 13
"Оптимизатор" документа ORACLE7 Server Concepts Manual. Если
совет специфицирует недоступный путь доступа, то оптимизатор
игнорирует этот совет.
Вы должны специфицировать в совете имя таблицы точно так же, как
оно появляется в предложении. Если предложение использует алиас
для таблицы, то вы должны указывать в совете алиас вместо имени
таблицы. Имя или алиас должно представлять таблицу или синоним
таблицы в вашей локальной базе данных.
FULL
Совет FULL явно выбирает полный просмотр таблицы для указанной
таблицы. Синтаксис этого совета имеет следующий вид:
FULL(таблица)
где "таблица" специфицирует имя или алиас таблицы, по которой
должен быть выполнен полный просмотр.
Например, для следующего предложения ORACLE выполняет полный
просмотр по таблице ACCOUNTS, даже если существует индекс по
столбцу ACCNO, путь по которому доступен благодаря условию в
фразе WHERE:
SELECT /*+ FULL(a) Don't use index on ACCNO */ accno, bal
FROM accounts a
WHERE accno = 7086854
Заметьте, что, поскольку таблица ACCOUNTS имеет алиас A, совет
должен ссылаться на эту таблицу по алиасу, а не по имени.
ROWID
Совет ROWID явно выбирает просмотр таблицы по ROWID для
указанной таблицы. Синтаксис этого совета имеет следующий вид:
ROWID(таблица)
где "таблица" специфицирует имя или алиас таблицы, по которой
должен быть выполнен просмотр по ROWID.
CLUSTER
Совет CLUSTER явно выбирает просмотр кластера для доступа к
указанной таблице. Синтаксис этого совета имеет следующий вид:
CLUSTER(таблица)
где "таблица" специфицирует имя или алиас таблицы, по которой
должен быть выполнен просмотр кластера.
HASH
Совет HASH явно выбирает хэш-просмотр для доступа к указанной
таблице. Синтаксис этого совета имеет следующий вид:
HASH(таблица)
где "таблица" специфицирует имя или алиас таблицы, по которой
должен быть выполнен хэш-просмотр.
INDEX
Совет INDEX явно выбирает просмотр индекса для доступа к
указанной таблице. Синтаксис этого совета имеет следующий вид:
------- INDEX(таблица --T------------T-- ) -------
L-- индекс ---
где:
таблица специфицирует имя или алиас таблицы, для которой
задается путь доступа через просмотр индекса.
индекс специфицирует ассоциированный с таблицей индекс,
по которому должен выполняться просмотр.
Этот совет может специфицировать ни одного, один или несколько
индексов:
* Если этот совет специфицирует единственный доступный
индекс, то оптимизатор выполняет просмотр по этому
индексу. Оптимизатор не рассматривает полный просмотр
таблицы или просмотр по другому индексу для этой таблицы.
* Если этот совет специфицирует список доступных индексов,
то оптимизатор рассматривает стоимость просмотра по
каждому из индексов в списке, после чего выбирает
просмотр с наименьшей стоимостью. Оптимизатор может
также выбрать просмотр нескольких индексов из этого
списка с последующим слиянием результатов, если этот путь
доступа окажется дешевле. Оптимизатор не рассматривает
полный просмотр таблицы или просмотр по другим индексам
для этой таблицы, не перечисленным в списке.
* Если этот совет не специфицирует ни одного индекса, то
оптимизатор рассматривает стоимость просмотра по каждому
из доступных индексов по таблице, после чего выбирает
просмотр с наименьшей стоимостью. Оптимизатор может
также выбрать просмотр нескольких индексов с последующим
слиянием результатов, если этот путь доступа окажется
дешевле. Оптимизатор не рассматривает полный просмотр
таблицы.
Например, рассмотрим следующий запрос, который выбирает имя,
рост и вес для каждого пациента мужского пола в больнице:
SELECT name, height, weight
FROM patients
WHERE sex = 'M'
Предположим, что столбец SEX содержит значения M и F, и что по
этому столбцу существует индекс. Если в больнице примерно
поровну пациентов мужского и женского пола, то этот запрос
возвратит относительно большой процент строк таблицы, и полный
просмотр таблицы, вероятно, был бы быстрее, чем просмотр
индекса. Однако, если доля пациентов мужского пола в больнице
очень мала, то этот запрос возвратит относительно маленький
процент строк таблицы, и просмотр индекса, скорее всего,
окажется быстрее, чем полный просмотр таблицы.
Количество вхождений каждого уникального значения столбца не
известно оптимизатору. Стоимостной подход предполагает, что
каждое значение имеет одинаковую вероятность появления в каждой
строке. Для столбца, имеющего лишь два уникальных значения,
оптимизатор полагает, что каждое из этих значений встречается в
50% строк, так что стоимостной подход в данном случае, вероятно,
предпочтет полный просмотр таблицы индексному просмотру.
Если вы знаете, что значение в фразе WHERE вашего запроса
появляется в очень маленьком проценте строк, вы можете
использовать совет INDEX, чтобы заставить оптимизатор выбрать
просмотр индекса. В следующем предложении совет INDEX явно
выбирает индексный просмотр по индексу SEX_INDEX, который был
создан по столбцу SEX:
SELECT /*+ INDEX(patients sex_index) Use SEX_INDEX, since there
are few male patients */
name, height, weight
FROM patients
WHERE sex = 'M'
INDEX_ASC
Совет INDEX_ASC явно выбирает просмотр индекса для доступа к
указанной таблице. Если предложение использует интервальный
просмотр индекса, то ORACLE просматривает записи индекса в
порядке возрастания индексных значений. Синтаксис этого совета
имеет следующий вид:
------- INDEX_ASC(таблица --T------------T-- ) -------
L-- индекс ---
Параметры здесь имеют тот же смысл, что и в совете INDEX.
Так как умалчиваемым поведением ORACLE при интервальном
просмотре является просмотр в порядке возрастания индексных
значений, этот совет в настоящее время ничем не отличается от
совета INDEX. Однако корпорация Oracle не гарантирует, что
умалчиваемое поведение интервальных просмотров индекса не
изменится в будущем; поэтому вы можете явно специфицировать
совет INDEX_ASC для интервального индексного просмотра по
возрастанию, чтобы не полагаться на умалчиваемое поведение.
INDEX_DESC
Совет INDEX_DESC явно выбирает просмотр индекса для доступа к
указанной таблице. Если предложение использует интервальный
просмотр индекса, то ORACLE просматривает записи индекса в
порядке убывания индексных значений. Синтаксис этого совета
имеет следующий вид:
------- INDEX_DESC(таблица --T------------T-- ) -------
L-- индекс ---
Параметры здесь имеют тот же смысл, что и в совете INDEX. Этот
совет не имеет эффекта на предложения SQL, обращающиеся к более
чем одной таблице. Такие предложения всегда выполняют
интервальные просмотры в порядке возрастания индексных значений.
Например, рассмотрим следующую таблицу, которая содержит
показания температуры резервуара с морской водой, содержащей
образцы жизни моря:
CREATE TABLE tank_readings
(time DATE CONSTRAINT un_time UNIQUE,
temperature NUMBER )
Каждая строка этой таблицы содержит значение времени и
температуру, которая была измерена в этот момент времени.
Ограничение UNIQUE по столбцу TIME гарантирует, что таблица
содержит не более одного показания для каждого значения времени.
ORACLE задействует это ограничение с помощью автоматически
создаваемого индекса по столбцу TIME.
Рассмотрим теперь следующий сложный запрос, который выбирает
самую высокую температуру, замеренную до конкретного момента
времени T. Подзапрос этого предложения возвращает либо T, либо
последний момент времени перед T, когда замерялась температура.
Родительский запрос затем отыскивает температуру, которая была
измерена в этот момент времени:
SELECT temperature
FROM tank_readings
WHERE time = (SELECT MAX(time)
FROM tank_readings
WHERE time <= TO_DATE(:t) )
План исполнения для этого предложения имеет следующий вид:
------¬ДОСТУП К ТАБЛИЦЕ
¦--1--¦(ПО ROWID)
L--T---tank_readings
¦
---+--¬ИНДЕКС
¦--2--¦(УНИКАЛЬНЫЙ ПРОСМОТР)
L--T---un_time
¦
---+--¬АГРЕГАТ
¦ 3 ¦(GROUP BY)
L--T---
¦
---+--¬ИНДЕКС
¦--4--¦(ПО ИНТЕРВАЛУ)
L------un_time
Чтобы исполнить это предложение, ORACLE выполняет следующие
операции:
* Шаги 4 и 3 исполняют подзапрос:
* Шаг 4 выполняет интервальный просмотр индекса
UN_TIME, чтобы возвратить все значения TIME, не
превосходящие T.
* Шаг 3 выбирает наибольшее значение TIME из шага 4 и
возвращает его.
* Шаги 2 и 1 исполняют родительский запрос:
* Шаг 2 выполняет уникальный просмотр по индексу
UN_TIME, базируясь на значении TIME, полученном от
шага 3, и возвращая ассоциированный ROWID.
* Шаг 1 обращается к таблице TANK_READINGS, используя
ROWID, полученный от шага 2, и возвращает значение
TEMPERATURE.
На шаге 4 ORACLE просматривает значения TIME в индексе в порядке
возрастания, начиная с наименьшего значения. ORACLE прекращает
просмотр, когда находит значение TIME, превосходящее T, после
чего возвращает все значения, не превосходящие T, шагу 3.
Заметьте, что шагу 3 требуется лишь наибольшее из этих значений.
Используя совет INDEX_DESC, вы можете написать запрос, который
будет считывать из индекса лишь одно значение TIME:
SELECT /*+ INDEX_DESC(tank_readings un_time) /* temperature
FROM tank_readings
WHERE time <= TO_DATE(:t)
AND ROWNUM = 1
План исполнения для этого предложения имеет следующий вид:
------¬СЧЕТЧИК
¦ 1 ¦(STOPKEY)
L--T---
¦
---+--¬ДОСТУП К ТАБЛИЦЕ
¦--2--¦(ПО ROWID)
L--T---tank_readings
¦
---+--¬ИНДЕКС
¦--3--¦(ПО ИНТЕРВАЛУ С УБЫВАНИЕМ)
L------un_time
Чтобы исполнить это предложение, ORACLE выполняет следующие
операции:
* Шаг 3 выполняет интервальный просмотр индекса UN_TIME,
отыскивая значения TIME, не превосходящие T, и возвращая
их ассоциированные ROWID'ы.
* Шаг 2 обращается к таблице TANK_READINGS, используя
ROWID'ы, полученные от шага 3.
* Шаг 1 реализует условие ROWNUM=1, получая всего одну
строку от шага 2.
Блогодаря совету INDEX_DESC, шаг 3 отыскивает значения TIME в
индексе в убывающем порядке, начиная с T. Первое найденное при
этом значение TIME либо совпадает с T (если в момент T был замер
температуры), либо будет наибольшим значением TIME, не
превосходящим T. Поскольку шаг 1 запрашивает ровно одну строку,
шаг 3 не будет просматривать никаких записей индекса после
первой.
Так как умалчиваемым поведением является просмотр индекса по
возрастанию, выдача этого запроса без совета INDEX_DESC
заставила бы ORACLE начать просмотр с самого раннего момента
времени в таблице, а не с последнего момента времени, не
превосходящего T. После этого шаг 1 возвратил бы температуру в
самый ранний момент времени. Поэтому вы должны использовать
совет INDEX_DESC, чтобы заставить этот запрос возвратить ту же
самую температуру, что и сложный запрос, описанный выше в этой
секции.
AND_EQUAL
Совет AND_EQUAL явно выбирает план исполнения, который
использует путь доступа, осуществляющий слияние результатов
просмотра по нескольким одностолбцовым индексам. Синтаксис
этого совета имеет следующий вид:
-- AND_EQUAL(таблица индекс индекс -T--------TT--------TT--------T- ) --
L индекс -L индекс -L индекс -
где:
таблица специфицирует имя или алиас таблицы, для которой
задается путь доступа через слияние индексов.
индекс специфицирует ассоциированный с таблицей индекс,
по которому должен выполняться просмотр. Вы
должны специфицировать по меньшей мере два
индекса. Вы не можете специфицировать более
пяти индексов.
Советы для порядка соединения
Порядок соединения предлагается советом ORDERED.
ORDERED
Совет ORDERED заставляет ORACLE соединить таблицы в том порядке,
в каком они появляются в фразе FROM. Например, следующее
предложение соединяет таблицу TAB1 с таблицей TAB2, а затем
соединяет результат с таблицей TAB3:
SELECT /*+ ORDERED */ tab1.col1, tab2.col2, tab3.col3
FROM tab1, tab2, tab3
WHERE tab1.col1 = tab2.col1
AND tab2.col1 = tab3.col1
Если вы опустите совет ORDERED из предложения SQL, выполняющего
соединение, то оптимизатор сам выбирает порядок, в котором будут
соединяться таблицы.
Вы можете специфицировать совет ORDERED, когда имеете информацию
о количестве строк, выбираемых из каждой соединяемой таблицы,
которой не имеет оптимизатор. Такая информация могла бы помочь
вам более оптимально выбрать внутренние и внешние таблицы в
соединении, чем это сделал бы оптимизатор.
Советы для операций соединения
Каждый совет, описанный в этой секции, предлагает операцию
соединения для таблицы. Вы должны специфицировать в совете имя
таблицы точно так же, как оно появляется в предложении. Если
предложение использует алиас для таблицы, то вы должны указывать
в совете алиас вместо имени таблицы. Имя или алиас должно
представлять таблицу или синоним таблицы в вашей локальной базе
данных.
USE_NL
Совет USE_NL заставляет ORACLE соединить каждую из указанных
таблиц с другим источником строк путем операции соединения через
вложенные циклы, используя указанную таблицу как внутреннюю
таблицу. Синтаксис этого совета имеет следующий вид:
--------------¬
------- USE_NL( ------ таблица --+---- ) -------
где "таблица" - имя или алиас таблицы, которая должна
использоваться как внутренняя таблица в соединении через
вложенные циклы.
Например, рассмотрим следующее предложение, которое соединяет
таблицы ACCOUNTS и CUSTOMERS, в предположении, что эти таблицы
не находятся в (общем) кластере:
SELECT accounts.balance, customer.last_name, customer.first_name
FROM accounts, customers
WHERE accounts.custno = customers.custno
Так как умалчиваемой целью стоимостного подхода является лучшая
пропускная способность, оптимизатор выберет для соединения этих
таблиц либо операцию вложенных циклов, либо операцию
сортировки-слияния, в зависимости от того, какой способ быстрее
возвратит все строки, выбираемые этим запросом.
Однако вы можете захотеть оптимизировать это предложение для
лучшего времени ответа вместо лучшей пропускной способности,
т.е. так, чтобы минимизировать время ожидания первой строки,
возвращаемой запросом. В этом случае вы можете заставить
оптимизатор выбрать соединение через вложенные циклы, используя
совет USE_NL. В следующем предложении совет USE_NL явно
выбирает соединение через вложенные циклы с таблицей CUSTOMERS в
качестве внутренней таблицы:
SELECT /*+ USE_NL(customers) Use Nested Loops to get first
row faster */
accounts.balance, customer.last_name, customer.first_name
FROM accounts, customers
WHERE accounts.custno = customers.custno
Во многих случаях соединение через вложенные циклы возвращает
первую строку быстрее, чем соединение через сортировку-слияние.
Соединение через вложенные циклы возвращает первую строку после
того, как выберет первую строку из одной таблицы и соединит с
ней первую совпадающую строку из другой таблицы, в то время как
соединение через сортировку-слияние возвращает первую строку
после того, как прочитает и отсортирует все выбираемые строки из
обеих таблиц, а потом скомбинирует первые строки из обоих
отсортированных источников строк.
USE_MERGE
Совет USE_MERGE заставляет ORACLE соединить каждую из указанных
таблиц с другим источником строк путем операции соединения через
сортировку-слияние. Синтаксис этого совета имеет следующий вид:
--------------¬
------- USE_MERGE( ------ таблица --+---- ) -------
где "таблица" - имя или алиас таблицы, которая должна быть
соединена с другим источником строк (являющимся результатом
соединения предыдущих таблиц в порядке соединения) через
операцию сортировки-слияния.
Рассмотрение альтернативных синтаксисов
---------------------------------------
Благодаря гибкости языка SQL, потребности вашего приложения
можно выразить различными предложениями SQL. Хотя два разных
предложения SQL могут возвращать один и тот же результат, ORACLE
может обрабатывать одно из них быстрее, чем другое. Вы можете
воспользоваться результатами команды EXPLAIN PLAN, чтобы
сравнить планы исполнения и стоимости этих двух предложений и
определить, какое из них эффективнее.
Следующий пример показывает планы исполнения для двух
предложений SQL, выполняющих одну и ту же задачу. Оба эти
предложения возвращают все отделы из таблицы DEPT, для которых
нет ни одного сотрудника в таблице EMP. Каждое предложение
осуществляет поиск по таблице EMP с помощью подзапроса.
Предположим, что по столбцу DEPTNO таблицы EMP существует индекс
с именем DEPTNO_INDEX.
Первое предложение и его план исполнения имеют следующий вид:
SELECT dname, deptno
FROM dept
WHERE deptno NOT IN
(SELECT deptno FROM emp)
------¬ФИЛЬТР
-----------+ 1 +----------¬
¦ L------ ¦
¦ ¦
---+--¬ДОСТУП К ТАБЛИЦЕ ---+--¬ДОСТУП К ТАБЛИЦЕ
¦--2--¦(ПОЛНЫЙ) ¦--3--¦(ПОЛНЫЙ)
L------dept L------emp
Шаг 3 вывода команды EXPLAIN PLAN для этого запроса показывает,
что ORACLE исполняет это предложение путем полного просмотра
таблицы EMP, несмотря на существование индекса по столбцу
DEPTNO. Этот полный просмотр может быть довольно
продолжительной операцией. ORACLE не использует индекс потому,
что подзапрос, осуществляющий поиск по таблице EMP, не содержит
фразы WHERE, которая сделала бы возможным индексный путь
доступа.
Однако следующее предложение SQL выбирает те же самые строки,
обращаясь к индексу:
SELECT dname, deptno
FROM dept
WHERE NOT EXISTS
(SELECT deptno
FROM emp
WHERE dept.deptno = emp.deptno)
------¬ФИЛЬТР
-----------+ 1 +----------¬
¦ L------ ¦
¦ ¦
---+--¬ДОСТУП К ТАБЛИЦЕ ---+--¬ИНДЕКС
¦--2--¦(ПОЛНЫЙ) ¦--3--¦(ИНТЕРВАЛ)
L------dept L------deptno_index
Фраза WHERE подзапроса ссылается на столбец DEPTNO таблицы EMP,
что позволяет использовать индекс DEPTNO_INDEX. Использование
индекса отражено в шаге 3 плана исполнения. Интервальный
просмотр по индексу DEPTNO_INDEX требует меньше времени, чем
полный просмотр таблицы EMP в первом предложении. Более того,
первый запрос выполняет один полный просмотр таблицы EMP для
каждого значения DEPTNO в таблице DEPT. По этим причинам,
второе предложение SQL быстрее, чем первое.
Если в ваших приложениях есть предложения, использующие оператор
NOT IN, как в первом запросе этого примера, вы должны
рассмотреть возможность исправления этих предложений так, чтобы
они использовали оператор NOT EXISTS. Это позволило бы таким
предложениям использовать индекс, когда он существует.
Как настраивать существующие предложения SQL
Настройка предложений SQL в существующем приложении несколько,
хотя и не в полной мере, отличается от написания новых
предложений. Хотя знания, требуемые в обоих случаях, те же
самые, процесс может быть иным. Вы должны изолировать
конкретные предложения в вашем приложении, которые нуждаются в
настройке. Для этого следует:
* Ознакомиться с приложением.
* Выделить конкретные проблемные предложения с помощью
средства трассировки SQL.
После этого вы можете настраивать эти предложения индивидуально,
с учетом рекомендаций для написания новых предложений SQL,
обсуждавшихся в предыдущей секции, включая применение индексов,
кластеров, хэширования и советов оптимизатору.
Знакомство с приложением
------------------------
Вы должны ознакомиться с приложением, его предложениями SQL и
его данными. Если вы не проектировали и не разрабатывали это
приложение, проконсультируйтесь с теми, кто это делал.
Выясните, что делает это приложение:
* какие предложения SQL использует приложение
* какие данные обрабатывает приложение
* каковы характеристики и распределение этих данных
* какие операции приложение выполняет на этих данных
Обсудите производительность приложения с его пользователями.
Попросите их указать те части приложения, где, по их мнению,
следовало бы улучшить производительность. Сужайте эти части до
тех пор, пока не дойдете до индивидуальных предложений SQL, если
возможно.
Использование средства трассировки SQL
--------------------------------------
ORACLE предоставляет несколько диагностических инструментов для
измерения производительности. Одним из этих инструментов,
наиболее полезным в настройке приложений, является средство
трассировки SQL. Средство трассировки SQL генерирует статистики
для каждого предложения SQL, обрабатываемого ORACLE. Эти
статистики отражают:
* количество раз, когда каждое предложение SQL разбиралось,
исполнялось и извлекалось
* время, необходимое для обработки каждого предложения SQL
* память и дисковые операции, ассоциированные с каждым
предложением SQL
* число строк, обработанных каждым предложением SQL
Средство трассировки SQL может также генерировать планы
исполнения, используя команду EXPLAIN PLAN.
Запустите ваше приложение с включенным средством трассировки
SQL. По результирующим статистикам определите, какие предложения
SQL требуют наибольшего времени на обработку. Сконцентрируйте
ваши усилия по настройке на этих предложениях.
Для дополнительной информации о том, как вызывать средство
трассировки SQL и другие инструменты диагностики
производительности, и как анализировать их вывод, обратитесь к
приложению B этого руководства, "Инструменты диагностики
производительности".
Настройка индивидуальных предложений SQL
----------------------------------------
Помните, что вы можете испытывать альтернативные синтаксисы для
предложений SQL, не подвергая модификации ваше приложение.
Просто выдайте команду EXPLAIN PLAN с альтернативным
предложением, которое вы рассматриваете, и сравните его план
исполнения и стоимость с характеристиками существующего
предложения. Вы можете найти стоимость предложения SQL в
столбце POSITION первой строки вывода, генерируемого командой
EXPLAIN PLAN. Однако вы должны запустить приложение, чтобы
определить, какое из предложений может быть действительно
исполнено быстрее.
Если вы создаете новые индексы, чтобы настроить предложения, вы
также можете воспользоваться командой EXPLAIN PLAN, чтобы
определить, будет ли оптимизатор выбирать эти индексы при работе
приложения. Если вы создаете новые индексы, чтобы настроить
предложение, которое в данный момент разбирается, то ORACLE
помечает это предложение как недействительное. При очередном
исполнении этого предложения оптимизатор автоматически выберет
новый план исполнения, потенциально использующий новый индекс.
Если вы создаете новые индексы на удаленной базе данных, чтобы
настроить распределенное предложение, то оптимизатор будет
рассматривать эти индексы при очередном разборе предложения.
Не забывайте также, что средства, которые вы применяете для
настройки одного предложения, могут повлиять на выбор
оптимизатором планов исполнения для других предложений.
Например, если вы создаете индекс для использования одним
предложением, оптимизатор может выбирать этот индекс и для
других предложений в вашем приложении. Поэтому после настройки
предложений, которые вы идентифицировали первоначально, вы
должны заново исследовать производительность вашего приложения с
помощью средства трассировки SQL.