Хинты - это инструкции, а не команды и оптимизатор им не всегда следует
(это для него только советы). Избегайте лишних подсказок.
На уровне init.ora можно задать директивы использования:
CHOOSE
RULE
FIRST_ROWS
ALL_ROWS
В OLTP изменение на уровне init.ora с CHOOSE на FIRST_ROWS настраивает оптимизатор
на возвращение первых нескольких строк быстрее. (наилучшее время отклика)
Он делает это разными способами, но основной из них - выполнение соединений в режиме nested loops.
В хранилищах изменение на уровне init.ora с CHOOSE на ALL_ROWS настраивает оптимизатор
на возвращение всех строк быстрее. (лучшая производительность).
Нужно всегда стремиться к директивам использования на уровне всей системы,
а не к директивам на уровне подсказок и советов для отдельных запросов.
При переходе к новой версии СУБД это позволяет избежать хлопот,
поскольку хинты могут работать по другому.
Если вы обнаружите, что при оптимизации системы используете чаще всего одни и те же хинты,
то это возможно симптом более существенной проблемы.
- отсутствие индекса
- нехватка памяти
то решайте эти проблемы на уровне системы.
Хинты должны использоваться лишь изредка.
Например, при отсутствии правильных индексов, оптимизатор может объединять
таблицы в неправильном порядке.
Если вы часто используете хинты:
ORDERED
LEADING
для объединений, посмотрите на индексы, для объединяемых таблиц.
Многие проблемы можно решить без использования инструкций, например перезаписав запрос так,
чтобы он правильно использовал индексы.
Синтаксис хинтов:
/*+ HINT1 HINT2 HINT3 */
отступы в один пробел имеют важное значение
/*+ ALL_ROWS */
подсказки всегда форсируют использование стоимостной оптимизации, кроме подсказки RULE.
Если в запросах используются псевдонимы (table alias), в подсказках также должны
использоваться псевдонимы вместо названий таблиц.
select /*+ FULL (my_alias) */ empno
from emp my_alias
where empno > 10;
В подсказках не должно быть указания названия схемы:
select /*+ index (scott.emp emp1) */ ...
Некорректные подсказки игнорируются без предупреждений.
Некорректность подсказки может быть неочевидна.
Например указание подсказки FIRST_ROWS (для получения первых строк) для запроса с ORDER BY.
Поскольку данные должны быть отсортированы преде, чем будут возвращены первые строки запроса.
Использование FIRST_ROWS может не дать желаемого результата.
Указанные в подсказке операции с данными должны быть доступны.
Подсказка INDEX с указанием несуществующего индекса будет проигнорирована
без сообщений об ошибках.
Подсказки:
Общие цели оптимизатора:
RULE
ALL_ROWS
FIRST_ROWS
FIRST_ROWS(n)
CHOOSE
Метоы доступа
AND_EQUAL
HASH
INDEX_COMBINE
INDEX_JOIN
CLUSTER
INDEX
INDEX_DESC
NO_INDEX
FULL
INDEX_ASC
INDEX_FFS
ROWID
Преобразование запроса
FACT
NO_FACT
REWRITE
USE_CONCAT
MERGE
NO_MERGE
STAR
NO_EXPAND
NOREWRITE
STAR_TRANSFORMATION
Параллельное исполнение
NOPARALLEL
PARALLEL_INDEX
NOPARALLEL_INDEX
PARALLEL
PQ_DISTRIBUTE
Операции объединения:
DRIVING_SITE
LEADING
NL_AJ
PUSH_SUBQ
USE_NL
HASH_AJ
MERGE_AJ
NL_SJ
USE_HASH
HASH_SJ
MERGE_SJ
ORDERED
USE_MERGE
Другие инструкции
APPEND
NOAPPEND
NOCACHE
UNNEST
CACHE
NO_UNNEST
ORDERED_PREDICATES
CURSOR_SHARING_EXACT
NO_PUSH_PRED
PUSH_PRED
Примеры использования:
Общие цели оптимизатора:
ALTER SYSTEM SET optimizer_mode=RULE SCOPE=MEMORY;
ALTER SYSTEM SET optimizer_mode=ALL_ROWS SCOPE=MEMORY;
ALTER SYSTEM SET optimizer_mode=FIRST_ROWS SCOPE=MEMORY;
ALTER SYSTEM SET optimizer_mode=CHOOSE SCOPE=MEMORY;
SELECT /*+ RULE */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
SELECT /*+ ALL_ROWS */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
SELECT /*+ FIRST_ROWS */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$';
SELECT /*+ FIRST_ROWS(10) */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$';
SELECT /*+ CHOOSE */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
(это для него только советы). Избегайте лишних подсказок.
На уровне init.ora можно задать директивы использования:
CHOOSE
RULE
FIRST_ROWS
ALL_ROWS
В OLTP изменение на уровне init.ora с CHOOSE на FIRST_ROWS настраивает оптимизатор
на возвращение первых нескольких строк быстрее. (наилучшее время отклика)
Он делает это разными способами, но основной из них - выполнение соединений в режиме nested loops.
В хранилищах изменение на уровне init.ora с CHOOSE на ALL_ROWS настраивает оптимизатор
на возвращение всех строк быстрее. (лучшая производительность).
Нужно всегда стремиться к директивам использования на уровне всей системы,
а не к директивам на уровне подсказок и советов для отдельных запросов.
При переходе к новой версии СУБД это позволяет избежать хлопот,
поскольку хинты могут работать по другому.
Если вы обнаружите, что при оптимизации системы используете чаще всего одни и те же хинты,
то это возможно симптом более существенной проблемы.
- отсутствие индекса
- нехватка памяти
то решайте эти проблемы на уровне системы.
Хинты должны использоваться лишь изредка.
Например, при отсутствии правильных индексов, оптимизатор может объединять
таблицы в неправильном порядке.
Если вы часто используете хинты:
ORDERED
LEADING
для объединений, посмотрите на индексы, для объединяемых таблиц.
Многие проблемы можно решить без использования инструкций, например перезаписав запрос так,
чтобы он правильно использовал индексы.
Синтаксис хинтов:
/*+ HINT1 HINT2 HINT3 */
отступы в один пробел имеют важное значение
/*+ ALL_ROWS */
подсказки всегда форсируют использование стоимостной оптимизации, кроме подсказки RULE.
Если в запросах используются псевдонимы (table alias), в подсказках также должны
использоваться псевдонимы вместо названий таблиц.
select /*+ FULL (my_alias) */ empno
from emp my_alias
where empno > 10;
В подсказках не должно быть указания названия схемы:
select /*+ index (scott.emp emp1) */ ...
Некорректные подсказки игнорируются без предупреждений.
Некорректность подсказки может быть неочевидна.
Например указание подсказки FIRST_ROWS (для получения первых строк) для запроса с ORDER BY.
Поскольку данные должны быть отсортированы преде, чем будут возвращены первые строки запроса.
Использование FIRST_ROWS может не дать желаемого результата.
Указанные в подсказке операции с данными должны быть доступны.
Подсказка INDEX с указанием несуществующего индекса будет проигнорирована
без сообщений об ошибках.
Подсказки:
Общие цели оптимизатора:
RULE
ALL_ROWS
FIRST_ROWS
FIRST_ROWS(n)
CHOOSE
Метоы доступа
AND_EQUAL
HASH
INDEX_COMBINE
INDEX_JOIN
CLUSTER
INDEX
INDEX_DESC
NO_INDEX
FULL
INDEX_ASC
INDEX_FFS
ROWID
Преобразование запроса
FACT
NO_FACT
REWRITE
USE_CONCAT
MERGE
NO_MERGE
STAR
NO_EXPAND
NOREWRITE
STAR_TRANSFORMATION
Параллельное исполнение
NOPARALLEL
PARALLEL_INDEX
NOPARALLEL_INDEX
PARALLEL
PQ_DISTRIBUTE
Операции объединения:
DRIVING_SITE
LEADING
NL_AJ
PUSH_SUBQ
USE_NL
HASH_AJ
MERGE_AJ
NL_SJ
USE_HASH
HASH_SJ
MERGE_SJ
ORDERED
USE_MERGE
Другие инструкции
APPEND
NOAPPEND
NOCACHE
UNNEST
CACHE
NO_UNNEST
ORDERED_PREDICATES
CURSOR_SHARING_EXACT
NO_PUSH_PRED
PUSH_PRED
Примеры использования:
Общие цели оптимизатора:
ALTER SYSTEM SET optimizer_mode=RULE SCOPE=MEMORY;
ALTER SYSTEM SET optimizer_mode=ALL_ROWS SCOPE=MEMORY;
ALTER SYSTEM SET optimizer_mode=FIRST_ROWS SCOPE=MEMORY;
ALTER SYSTEM SET optimizer_mode=CHOOSE SCOPE=MEMORY;
SELECT /*+ RULE */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
SELECT /*+ ALL_ROWS */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
SELECT /*+ FIRST_ROWS */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$';
SELECT /*+ FIRST_ROWS(10) */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$';
SELECT /*+ CHOOSE */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
