Из документации QREF: SQL Statement HINTS [ID 29236.1]
- “О синтаксисе: /*+ HINT HINT … */ В PLSQL пробел между ‘+’ и первой буквой подсказки имеет значение, в случае отсутствия пробела подсказка может быть игнорирована, т.е. /*+ ALL_ROWS */ правильное использование, а /*+ALL_ROWS */ – неправильное
- Подсказки всегда “форсируют” использование стоимостной оптимизации (cost based optimizer) – кроме подсказки RULE
- Если в запросе используются псевдонимы (table alias), в подсказках также должны использоваться псевдонимы вместо названий таблиц:
Неправильно: SELECT /*+ FULL ( emp ) */ empno FROM emp myalias WHERE empno > 10;
Правильно: SELECT /*+ FULL ( myalias ) */ empno FROM emp myalias WHERE empno > 10;
- В посказках не должно быть указания названия схемы
Неправильно: SELECT /*+ index(scott.emp emp1) */
Используйте псевдонимы.
- Некорректные подсказки (invalid hints) игнорируются без предупреждений
- Некорректность подсказки (invalid hints) может быть неочевидна.
Например: указание подсказки FIRST_ROWS (для получения первых строк) для запроса с ORDER BY (поскольку данные должны быть отсортированы прежде, чем будут возвращены первые строки запроса, использование first_rows может не дать желаемого результата)
- Указанные в подсказке операции с данными (the access path) должны быть доступны
Например: подсказка INDEX с указанием несуществующего индекса будет проигнорирована без сообщений об ошибках…”
Optimization Goals and Approaches hints
Подсказки, определяющие общие цели и подходы для оптимизации плана выполнения запроса, включая правила и методы доступа к данным. Cоответсвуют описаниям значений параметра OPTIMIZER_MODE
/*+ RULE */
Не поддерживается, начиная с Oracle 10, используется оптимизация по точно оределённым правилам (Rule Based Optimization) без учёта статистики объектов бд. Описание применяемых правил для инилизационного параметра optimizer_mode = rule
В Oracle 11gR2 введены дополнительные ограничения использования подсказки /*+ rule*/ в запросах, в частности, несмотря на использование подсказки RULE Oracle будет использовать Cost-Based Optimization, если:
- кроме RULE в запросе используются другие подсказки
- запрос использует партицированные или таблицы, организованные в виде индекса (IOT), или материализованные представления (mview)
- в запросе используются кляузы SAMPLE, SPREADSHEET, конструкции GROUPING SETS
- в запросе используются ANSI left|full outer join
- запрос выполняется параллельно
- используется Flashback cursor (as of [scn|timestamp])
- …
Кроме того в 11.2 при использовании RBO может формироваться трейс оптимизатора и использоваться некоторые преобразования запросов за исключением Cost-Based Query Transformation, что естественно
/*+ ALL_ROWS */
“Подсказка ALL_ROWS определяет целью скорейшее выполнение запроса с минимальным расходом ресурсов (best throughput).
При одновременном с ALL_ROWS или FIRST_ROWS указании подсказок, определяющих методы доступа к данным (NO_INDEX_SS, INDEX_COMBINE,..) или указывающие методы объединения объектов БД (LEADING, USE_NL_WITH_INDEX,..), оптимизатор отдаёт предпочтение подсказкам методов доступа и объединения”
/*+ FIRST_ROWS */
Из документации Oracle 8:
“ПодсказкаFIRST_ROWS определяет стоимостной подход (cost-based approach) для оптимизации блоков запроса (statement block) с целью лучшего времени отклика (response time, минимального расхода ресурсов для возвращения первых строк запроса)
В соответствии с этой подсказкой оптимизатор делает следующие предпочтения [в выборе операций доступа к данным]:
- При наличии оптимизатор использует сканирование по индексу (index scan) вместо полного сканирования таблицы (full table scan)
- Если доступно сканирование по индексу (index scan), оптимизатор выбирает nested loops join вместо sort-merge join в случае, когда сканируемая таблица может быть использована как ведомая таблица (inner table) для операции nested loops.
- Если использование индекса (index scan) доступно для сортировки результатов (определённой фразой ORDER BY), оптимизатор выбирает сканирование по индексу (index scan) во избежание дополнительной сортировки”
Начиная с Oracle 9i:
“Подсказка FIRST_ROWS указанная без аргументов, предназначенная для оптимизации плана выполнения с целью скорейшего возвращения первой строки запроса, сохраняется только для обратной совместимости (backward compatibility) и стабильности планов выполнения (plan stability)”
Однако значение инилизационного параметра OPTIMIZER_MODE=FIRST_ROWS (что равносильно применению подсказки FIRST_ROWS для всех запросов) аннонсируется в документации вплоть до версии Oracle 11.2
/*+ FIRST_ROWS(n) */
Оптимизация, основанная на стоимости (Cost Based Optimization) + использование правил (предпочтений в выборе плана) с целью получения лучшего времени отклика для получения первых n строк. План рассчитывается с учётом значения n, как целевого количества выбранных запросом строк (query cardinality). См. описание правил для параметра optimizer_mode = first_rows
Не совсем понятная фраза в документации: “Оптимизатор игнорирует эту подсказку в SQL предложениях DELETE и UPDATE и в запросах SELECT, включающих блокирующие операции, такие как сортировки и группировки. Такие SQL предложения не могут быть оптимизированы с целью наименьшего времени отклика (best response time), поскольку Oracle должен обработать все строки запроса до того, как вернуть первую строку результата. При указании этой подсказки запросы указанного типа оптимизируются с целью лучшего времени получения всех строк запроса с минимальным расходом ресурсов (best throughput, как при использовании подсказки ALL_ROWS)”
По выражению Dion Cho Oracle always uses ALL_ROWS mode internally for DML statement – там есть показательный пример для версии 10.2
В Oracle 11.2 ничего не изменилось – независимо от количества обновляемых строк (Rows), устанавливаемого функцией rownum ни планы выполнения (кроме дополнительной операции COUNT STOPKEY), ни стоимость, ни ожидаемое время (Time) не меняются:
-- План при обновлении ВСЕХ строк таблицы 11.2.0.3@SQL> update ... 5 / ------------------------------------------------------------------------------- | Id | Operation | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | 1129K| 43M| 5751K (20)| 17:34:32 | | 1 | UPDATE | | | | | |* 2 | HASH JOIN SEMI | 1129K| 43M| 105K (1)| 00:19:26 | | 3 | TABLE ACCESS BY INDEX ROWID| 1129K| 33M| 75115 (1)| 00:13:47 | |* 4 | INDEX RANGE SCAN | 1129K| | 5531 (1)| 00:01:01 | |* 5 | INDEX FAST FULL SCAN | 11M| 95M| 16079 (1)| 00:02:57 | | 6 | TABLE ACCESS BY INDEX ROWID | 1 | 9 | 4 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | 1 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------- -- План при обновлении ОДНОЙ строки таблицы 11.2.0.3@SQL> update ... 5 and rownum <= 1 6 / -------------------------------------------------------------------------------- | Id | Operation | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | 1 | 40 | 5751K (20)| 17:34:32 | | 1 | UPDATE | | | | | |* 2 | COUNT STOPKEY | | | | | |* 3 | HASH JOIN SEMI | 1129K| 43M| 105K (1)| 00:19:26 | | 4 | TABLE ACCESS BY INDEX ROWID| 1129K| 33M| 75115 (1)| 00:13:47 | |* 5 | INDEX RANGE SCAN | 1129K| | 5531 (1)| 00:01:01 | |* 6 | INDEX FAST FULL SCAN | 11M| 95M| 16079 (1)| 00:02:57 | | 7 | TABLE ACCESS BY INDEX ROWID | 1 | 9 | 4 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | 1 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------
- при попытке обновить только одну строку Oracle выбирает тот же план с недешёвой операцией HASH JOIN SEMI (- в запроса используется конструкция EXISTS) – более подходящей для получения всех строк обновляемой таблицы (ALL_ROWS mode)
Простой тест для показывает, что подсказки FIRST_ROWS и FIRST_ROWS(n) влияют на поведение оптимизатора и влияют по-разному в случае сортировки или группировки при использовании бессмысленного условия object_id > 1 (при min(object_id)=2)*:
SQL> show parameter optimizer NAME TYPE VALUE ------------------------------------ ----------- -------- optimizer_features_enable string 11.1.0.6 optimizer_mode string ALL_ROWS SQL> select min(object_id) from t1; MIN(OBJECT_ID) -------------- 2 SQL> set autotrace traceonly exp stat SQL> select/*+ ALL_ROWS*/ * from t1 2 where object_name like '%#%' 3 and object_id > 1 4 order by object_name; 30 rows selected. Execution Plan --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 486 | 43740 | 41 (3)| 00:00:01 | | 1 | SORT ORDER BY | | 486 | 43740 | 41 (3)| 00:00:01 | |* 2 | TABLE ACCESS FULL| T1 | 486 | 43740 | 40 (0)| 00:00:01 | --------------------------------------------------------------------------- Statistics ---------------------------------------------------------- ... 133 consistent gets SQL> select/*+ FIRST_ROWS(1)*/ * from t1 2 where object_name like '%#%' 3 and object_id > 1 4 order by object_name; 30 rows selected. Execution Plan --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 486 | 43740 | 41 (3)| 00:00:01 | | 1 | SORT ORDER BY | | 486 | 43740 | 41 (3)| 00:00:01 | |* 2 | TABLE ACCESS FULL| T1 | 486 | 43740 | 40 (0)| 00:00:01 | --------------------------------------------------------------------------- Statistics ---------------------------------------------------------- ... 133 consistent gets SQL> select/*+ FIRST_ROWS*/ * from t1 2 where object_name like '%#%' 3 and object_id > 1 4 order by object_name; 30 rows selected. Execution Plan --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 486 | 43740 | 152 (1)| 00:00:02 | | 1 | SORT ORDER BY | | 486 | 43740 | 152 (1)| 00:00:02 | |* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 486 | 43740 | 151 (0)| 00:00:02 | |* 3 | INDEX RANGE SCAN | T1_IDX | 9717 | | 22 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- ... 151 consistent gets SQL> select/*+ ALL_ROWS*/ object_id, count(*) from t1 2 where object_id > 1000 3 and object_name like '%#%' 4 group by object_id; Execution Plan --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 437 | 10051 | 41 (3)| 00:00:01 | | 1 | HASH GROUP BY | | 437 | 10051 | 41 (3)| 00:00:01 | |* 2 | TABLE ACCESS FULL| T1 | 437 | 10051 | 40 (0)| 00:00:01 | --------------------------------------------------------------------------- Statistics ---------------------------------------------------------- ... 133 consistent gets SQL> select/*+ FIRST_ROWS(1)*/ object_id, count(*) from t1 2 where object_id > 1 3 and object_name like '%#%' 4 group by object_id; 30 rows selected. Execution Plan --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 23 | 3 (0)| 00:00:01 | | 1 | SORT GROUP BY NOSORT | | 1 | 23 | 3 (0)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 23 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T1_IDX | | | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- ... 155 consistent gets SQL> select/*+ FIRST_ROWS*/ object_id, count(*) from t1 2 where object_id > 1 3 and object_name like '%#%' 4 group by object_id; 30 rows selected. Execution Plan --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 486 | 11178 | 41 (3)| 00:00:01 | | 1 | HASH GROUP BY | | 486 | 11178 | 41 (3)| 00:00:01 | |* 2 | TABLE ACCESS FULL| T1 | 486 | 11178 | 40 (0)| 00:00:01 | --------------------------------------------------------------------------- Statistics ---------------------------------------------------------- ... 133 consistent gets
*) статистика для таблицы T1 актуальна и оптимизатор прекрасно “знает” о минимальном и максимальном значении T1.Object_id:
SQL> declare 2 low_value_raw RAW(2000); 3 high_value_raw RAW(2000); 4 low_value_num number; 5 high_value_num number; 6 begin 7 select low_value, high_value into low_value_raw, high_value_raw 8 from user_tab_col_statistics 9 where table_name = 'T1' and column_name = 'OBJECT_ID'; 10 dbms_stats.convert_raw_value(low_value_raw, low_value_num); 11 dbms_stats.convert_raw_value(high_value_raw, high_value_num); 12 dbms_output.put_line('Low value T1.OBJECT_ID = ' || low_value_num); 13 dbms_output.put_line('High value T1.OBJECT_ID = ' || high_value_num); 14 end; 15 / Low value T1.OBJECT_ID = 2 High value T1.OBJECT_ID = 10000
SQL> select UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) as "LOW",
2 UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) as "HIGH"
3 from user_tab_col_statistics
4 where table_name = 'T1'
5 and column_name = 'OBJECT_ID';
LOW HIGH
---------- ----------
2 10000
Access Path Hints
Подсказки, определяющие пути и методы доступа к данным.*
Том Кайт называет эти подсказки плохими (bad hints): “Плохие подсказки указывают оптимизатору как следует действовать [при выполнении запроса], какой индекс использовать, в каком порядке обрабатывать таблицы, с помощью какой операции ( join technique) производить соединение [источников данных]“
*) Перед применение необходимо учитывать вероятность изменения статистики системы и объектов (таблиц и индексов), используемых в запросе со временем и, как следствие, возможную неоптимальность указываемых операций для будущих наборов данных. План выполнения, оптимизированный с помощью подсказок этого типа в среде разработки, может оказаться далеко не оптимальным для боевой бд (production system) из-за отличающихся наборов данных и характеристик системы (“железо”)
/*+ LEADING( [@query_block] [tablespec],[tablespec],.. ) */
“Подсказка LEADING указывает оптимизатору использовать перечисленный набор таблиц перым по порядку в плане выполнения… более гибкая, чем ORDERED…
Полностью игнорируются при использовании двух или более конфликтующих подсказок LEADING. Для оптимизатора подсказка ORDERED имеет преимущество против LEADING”
/*+ ORDERED */
“Подсказка ORDERED указывает Oracle [при выполнении запроса] проводить соединение таблиц в том же порядке, в котором таблицы перечислены в конструкции FROM. Oracle рекомендует вместо ORDERED использовать подсказку LEADING, обладающую большей гибкостью…”, т.е. дающей оптимизатору больше возможностей в выборе плана выполнения
/*+ USE_HASH( [@query_block] [tablespec] [tablespec]… ) */
/*+ NO_USE_HASH( [@query_block] [tablespec] [tablespec]… ) */
…указывает оптимизатору использовать (или не использовать в случае использования NO_USE_HASH) операцию hash join для соединения каждой указанной таблицы с прочими источниками данных
USE_NL
USE_NL_WITH_INDEX
USE_MERGE
/*+ DRIVING_SITE ( [@query_block] tablespec] ) */
“… подсказка указывает оптимизатору выполнять запрос на сайте [сайте таблицы, указанной в хинте], отличном от выбранного бд [Oracle]. Хинт полезен для оптимизации выполнения распределённых запросов”
В зависимости от подсказки запрос выполняется полностью на удалённом сайте – при указании удалённой таблицы в хинте DRIVING_SITE(e):
SQL> select--+ DRIVING_SITE(e) 2 count(*) 3 from emp@loopback_dblink e, 4 dual 5 / Execution Plan ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | Inst |IN-OUT| ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT REMOTE| | 1 | 3 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | | | | | | 2 | MERGE JOIN CARTESIAN | | 14 | 3 (0)| 00:00:01 | | | | 3 | REMOTE | DUAL | 1 | 2 (0)| 00:00:01 | ! | R->S | | 4 | BUFFER SORT | | 14 | 1 (0)| 00:00:01 | | | | 5 | INDEX FULL SCAN | PK_EMP | 14 | 1 (0)| 00:00:01 | ORCL1~ | | ----------------------------------------------------------------------------------------- Remote SQL Information (identified by operation id): ---------------------------------------------------- 3 - SELECT 0 FROM "SYS"."DUAL" "A1" (accessing '!' ) Note ----- - fully remote statement
, либо локально, с копированием данных из удалённой таблицы на локальный инстанс – при использовании подсказки DRIVING_SITE(dual), указывающей на локальную таблицу dual. В плане указывается операция REMOTE_TO_SERIAL (R->S):
SQL> select--+ DRIVING_SITE(dual) 2 count(*) 3 from emp@loopback_dblink e, 4 dual 5 / Execution Plan --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | Inst |IN-OUT| --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | | | | | | 2 | NESTED LOOPS | | 14 | 3 (0)| 00:00:01 | | | | 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | | | | 4 | REMOTE | EMP | 14 | 1 (0)| 00:00:01 | LOOPB~ | R->S | --------------------------------------------------------------------------------- Remote SQL Information (identified by operation id): ---------------------------------------------------- 4 - SELECT 0 FROM "EMP" "E" (accessing 'LOOPBACK_DBLINK.LOCAL_DOMAIN.COM' )
Limitations of DRIVING_SITE Hint (Doc ID 825677.1):
Версии: 9.2.0.1 – 11.2.0.2
… распределённый DML должен выполняться в бд, где расположена целевая таблица DML. Подсказка DRIVING_SITE не меняет этого поведения. DRIVING_SITE предполагает управление выполнением (mapping) всего курсора (а не отдельных подзапросов)…
Подсказка DRIVING_SITE предназначена для оптимизации запросов (SELECT) и не предназначена для DML или DDL
Прочие и не[достаточно] документированные подсказки
/*+ CURSOR_SHARING_EXACT */
“При указании этой подсказки, Oracle выполняет запрос без попыток заменить символьные значения (literals) связанными переменными (bind variables)” независимо от значения параметра CURSOR_SHARING
/*+ DYNAMIC_SAMPLING ( [@query_block] [tablespec] degree_of_sampling ) */
См. описание механизма в описании соответствующего инилизационного параметра OPTIMIZER_DYNAMIC_SAMPLING
При использовании подсказки DYNAMIC_SAMPLING:
“Если в бд для таблицы имеется [актуальная] статистика по количеству строк (cardinality), оптимизатор использует эту статистику. Иначе dynamic sampling будет использован для оценки этой статистики.
Если в подсказке указана таблица и имеется [актуальная] статистика по количеству строк (cardinality):
- Если в условиях WHERE нет условий только для этой таблицы (single-table predicate), оптимизатор доверяет существующей статистике и игнорирует подсказку
- Если в условиях WHERE есть условия для этой таблицы (…WHERE DEPT.LOCATION=’CHICAGO’…), оптимизатор использует существующую статистику по количеству строк (cardinality statistic) и [динамически] оценивает избирательность условий (selectivity of the predicate), используя существующую статистику”
Значения параметра подсказки degree_of_sampling:
- 0: Механизм dynamic sampling не используется
- 1: Для анализа оптимизатором используется количество блоков по умолчанию (32, значение параметра _optimizer_dyn_smp_blks)
- 2, 3, 4, 5, 6, 7, 8, и 9: Для анализа оптимизатором используется количество боков по умолчанию, умноженное на 2, 4, 8, 16, 32, 64, 128, или 256 соответственно
- 10: Читаются ВСЕ блоки таблицы”
Можно использовать для таблиц, для которых, по вашему мнению, оптимизатор ошибочно определяет избирательность по условиям запроса, например , в случае отсутствии гистограм для столбцов с неравномерно распределёнными значениями (skewed columns)
Подсказка подходит для оптимизации отчётных запросов с длительным временем выполнения, в случаях, когда время выполнения запроса (например, десятки секунд) много больше времени подготовки запроса (SQL hard parse, включающего в себя dynamic sampling и выбор плана выполнения запроса с учётом результатов), составляющее миллисекунды при значении уровня degree_of_sampling <= 4
По умолчанию значение параметра OPTIMIZER_DYNAMIC_SAMPLING=2, начиная с Oracle 10g (точнее, при значении параметра OPTIMIZER_FEATURES_ENABLE >= 10.0.0), т.е. dynamic sampling применяется, но только для таблиц без собранной статистики, этого должно быть достаточно для быстрых запросов в OLTP системах, когда увеличение времени подготовки запроса (hard parse) может заметно увеличить общее время выполнения.
Рекомендуется применять с точным указанием таблицы или синонима (такой синтаксис указывает оптимизатору использовать dynamic sampling для указанной таблицы в безусловном порядке)
SQL> select /*+ DYNAMIC_SAMPLING(e 3)*/ ... from emp e, dept d ...
поскольку применение в упрощённом виде (что эквивалентно установке соотв.значения параметра OPTIMIZER_DYNAMIC_SAMPLING для запроса)
SQL> select /*+ DYNAMIC_SAMPLING(3)*/ ... from emp e, dept d ...
которое, должно вызывать применение механизма dynamic sampling для всех таблиц запроса, может приводить к неожиданному результату в Oracle 10.2.0.4 – CBO вообще не применяет динамического анализа (либо может применять, но не использовать результатов). Применение механизма DYNAMIC SAMPLING можно проконтролировать по записям в трейс файлах 10053 event:
механизм запускается (TRUE) только при использовании /*+ DYNAMIC_SAMPLING(e 3)*/
** Performing dynamic sampling initial checks. ** Column (#13): MY_COLUMN(NUMBER) AvgLen: 0.00 NDV: 0 Nulls: 0 Density: 0 Min: 0 Max: 100000 ** Dynamic sampling initial checks returning TRUE (level = 3)
и не запускается (FALSE) в случае использования подсказки в виде /*+ DYNAMIC_SAMPLING(3)*/либо /*+ DYNAMIC_SAMPLING(@query_block 3)*/
** Performing dynamic sampling initial checks. ** Column (#13): MY_COLUMN(NUMBER) AvgLen: 0.00 NDV: 0 Nulls: 0 Density: 0 Min: 0 Max: 100000 ** Dynamic sampling initial checks returning FALSE
/*+ DYNAMIC_SAMPLING_EST_CDN( [@query_block] [tablespec] ) */
AskTom: “To force cardinality estimation even for an analyzed table, you can use a further hint, dynamic_sampling_est_cdn, as in the following example:”…even for an analyzed table.
Подсказка оптимизатору – заново оценить количество строк в таблице (cardinality), независимо от состояния статистики таблицы.
/*+ QB_NAME(query_block_name) */
Добавлен с Oracle 10g
Используется для точного внешнего определения названия блока запроса
SQL> SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name FROM employees e WHERE last_name = 'Smith';
Название блока запроса (queryblock identifier) может быть либо сгенерировано Oracle, либо определено пользователем с помощью подсказки QB_NAME. Сгенерированное Oracle название блока запроса можно найти в выводе команды EXPLAIN PLAN
SQL> explain plan for select count(*) from emp 2 where deptno in (select deptno from dept 3 where length(loc) > 2);
SQL> SELECT PLAN_TABLE_OUTPUT FROM 2 TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL')); ... Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 3 - SEL$5DA710D3 / DEPT@SEL$2 4 - SEL$5DA710D3 / DEPT@SEL$2 6 - SEL$5DA710D3 / EMP@SEL$1 ...
, или c помощью запроса актуального плана выполненияк из обзора v$sql_plan (QBLOCK_NAME)
SQL> SELECT
lpad(' ',2*level)||pt.operation||' '||pt.options||' '||pt.object_name "QPlan"
, pt.cost
, pt.cardinality
, pt.bytes
, pt.cpu_cost
, pt.io_cost
, pt.temp_space
, pt.access_predicates
, pt.filter_predicates
, pt.QBLOCK_NAME
FROM (select * from v$sql_plan
where sql_id = '1234567890'
and child_number = 0) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0
/*+ PUSH_SUBQ( [@query_block] ) */
“Указывает оптимизатору выполнять непреобразованный подзапрос (nonmerged subqueries) на самом раннем возможном шаге плана выполнения запроса. В то время, как обычно, непреобразованные подзапросы (nonmerged subqueries) выполняются на последнем шаге плана выполнения. Раннее выполнение подзапроса может значительно ускорить производительность, если подзапрос относительно лёгок и может значительно уменьшить количество обрабатываемых строк”
Начиная с Oracle 10g подсказка может быть успешно использована ТОЛЬКО в виде:
- на уровне всего запроса в виде /*+ PUSH_SUBQ( @query_block ) */ с указанием блока (определяемым подсказкой QB_NAME)
select /*+ push_subq(@subq1) */ ename from emp, dept where emp.sal between 1001 and 2200 and dept.deptno = emp.deptno and dept.dname >= 'C' and exists ( select /*+ no_unnest qb_name(subq1)*/ null from dept where deptno = emp.deptno and loc >= '2')
- прямо в подзапросе без указания блока /*+ PUSH_SUBQ */
select ename from emp, dept where emp.sal between 1001 and 2200 and dept.deptno = emp.deptno and dept.dname >= 'C' and exists ( select /*+ no_unnest push_subq*/ null from dept where deptno = emp.deptno and loc >= '2')
При указании на уровне всего запроса в виде /*+ PUSH_SUBQ */ без указания блока подсказка не работает, начиная с версии Oracle 10g
select /*+ push_subq */ ename from emp, dept where emp.sal between 1001 and 2200 and dept.deptno = emp.deptno and dept.dname >= 'C' and exists ( select /*+ no_unnest */ null from dept where deptno = emp.deptno and loc >= '2')
Таким образом можно точно указать оптимизатору какие подзапросы должны будут выполняться на ранних этапах плана выполнения, а какие на последних – соответственно, с помощью подсказки NO_PUSH_SUBQ
/*+ NO_PUSH_SUBQ( [@query_block] ) */
“Указывает оптимизатору выполнять непреобразованный подзапрос (nonmerged subqueries) на последнем шаге плана выполнения запроса [т.е. так, как это обычно делает оптимизатор - видимо, имеет смысл при каком-то необычном поведении оптимизатора]. Может положительно влиять на производительность, если подзапрос относительно тяжёлый или незначительно уменьшает количество строк”
Синтаксис аналогичен синтаксису подсказки PUSH_SUBQ
/*+ CARDINALITY( objectname [,] integer ) */
Подсказка применяется для явного (без проверки) указания оптимизатору количества строк (cardinality) объекта бд для расчётов плана выполнения.
Объектом может быть обычная (heap) таблица, глобальная временная таблица (global temporary table), табличные функции (pipelined function), подзапрос и т.д.
Вместе с другими методами (использование механизма DYNAMIC SAMPLING для сбора статистики “на лету”, “ручного” ввода статистических данных с помощью процедур пакета DBMS_STATS и др.) рекомендуется для оптимизации запросов с участием объектов, для которых отсутствует (или не может быть собрана, или для целей оптимизации требуется изменённая) статистика по количеству строк – Metalink Note 356540.1: How to workaround issues with objects that have no statistics.
Пример с сайта lazydba.com:
SQL> set autotrace on SQL> select * from table(select collect(empno) from emp) x; COLUMN_VALUE ------------ 7369 ... 14 rows selected. Execution Plan -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| | | | | | | 2 | SORT AGGREGATE | | 1 | 4 | | | | 3 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- SQL> select /*+ CARDINALITY(x, 14) */ * from table(select collect(empno) 2 from emp) x; ... 14 rows selected. Execution Plan -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 28 | 29 (0)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| | | | | | | 2 | SORT AGGREGATE | | 1 | 4 | | | | 3 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------
Механизм DYNAMIC SAMPLING с табличной функцией не работает, т.к. блоки до выполнения запроса читать просто неоткуда :)
SQL> select /*+ DYNAMIC_SAMPLING(x 10) */ * from 2 table(select collect(empno) from emp) x; ... 14 rows selected. Execution Plan -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| | | | | | | 2 | SORT AGGREGATE | | 1 | 4 | | | | 3 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------
В случае отсутствия физической структуры таблицы ни динамический (DYNAMIC SAMPLING), ни статический (использование DBMS_STATS) методы определения или установки статистики не подходят
/*+ OPT_ESTIMATE( [query block] operation_type identifier adjustment ) */
Превосходное неофициальное описание от Christo Kutrovsky
Предназначен для влияния на план выполнения через изменение оценки кол-ва строк (cardinality), получаемых в результате операций доступа к данным и, как следствие, стоимости плана выполнения.
Часто используется при создании SQL Profile, является развитием / замещением подсказки CARDINALITY
Параметры:
- query block
- operation_type: QUERY_BLOCK / TABLE / INDEX_FILTER / INDEX_SCAN | INDEX_SKIP_SCAN / JOIN
- identifier: TABLES / INDEX / JOINS
- adjustment: ROWS / SCALE_ROWS / MIN / MAX
/*+ GATHER_PLAN_STATISTICS */
Используется для сбора [расширенной] статистики выполнения запроса в соответствии с планом выполнения (или просто статистики плана выполнения запроса). Действует аналогично установке значения параметра statistics_level = ALL (или _rowsource_execution_statistics = TRUE) на уровне сессии или системы. Данные по статистике плана выполнения при этом сохраняется в обзоре v$sql_plan_statistics_all
Methods for Obtaining a Formatted Explain Plan [ID 235530.1]
How to Display All Loaded Execution Plan for a specific sql_id [ID 465398.1]
Пример применения:
SQL> set linesize 150 SQL> set pagesize 2000 SQL> set serveroutput OFF SQL> select /*+ gather_plan_statistics */ count(*) from emp; SQL> select * from TABLE(dbms_xplan.display_cursor('','','ALLSTATS LAST')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9jjm288hx7buz, child number 2 ------------------------------------- select /*+ gather_plan_statistics */ count(*) from emp Plan hash value: 2937609675 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time |Buffers | ------------------------------------------------------------------------------------------------------------- | 1| SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 1 | | 2| INDEX FULL SCAN| PK_EMP | 1 | 14 | 1 (0)| 00:00:01 | 14 |00:00:00.01 | 1 | ------------------------------------------------------------------------------------------------------------- ...
Команда set serveroutput OFF выполняется на случай, если serveroutput был включен в сессии, т.к. в этом случае получим:
SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
SQL> select /*+ gather_plan_statistics */ count(*) from emp;
...
SQL> select * from TABLE(dbms_xplan.display_cursor('','','ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9babjv8yq8ru3, child number 0
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
т.е. последним в сессии SQL*PLUS будет PL/SQL блок DBMS_OUTPUT, который и будет пытаться обработать функция dbms_xplan.display_cursor(”,”,’…’) при запуске с NULL-левыми параметрами sql_id и child_number.
В случае, если запрос с подсказкой GATHER_PLAN_STATISTICS выполнялся не последним, статистику плана выполнения можно получить, например, запросом по обзору v$sql (предварительно снабдив запрос оригинальным комментарием MY_QUERY_TAG для облегчения дальнейшего поиска):
select t.* from v$sql s, table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL ALLSTATS LAST')) t where s.sql_text like '%select /*+ gather_plan_statistics MY_QUERY_TAG*/ count(*)%';
Описание третьего параметра (формат) и вывода функции dbms_xplan.display_cursor
/*+ OPT_PARAM(parameter_name [,] parameter_value) */
добавлен начиная с 10g R2, согласно документации 11g R2 “… позволяет установить инициализационные параметры [оптимизатора] на время выполнения запроса..:
OPTIMIZER_SECURE_VIEW_MERGING
STAR_TRANSFORMATION_ENABLED
В документе поддержки OPT_PARAM Hint [ID 377333.1] в примере применения дополнительно указываются след.параметры оптимизатора, доступные для корректировки на время запроса
/*+ OPT_PARAM('_always_semi_join' 'off')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('query_rewrite_enabled' 'false')
OPT_PARAM('_new_initial_join_orders' 'false')
OPT_PARAM('optimizer_dynamic_sampling' 3)
OPT_PARAM('optimizer_index_cost_adj' 10)
OPT_PARAM('optimizer_index_caching' 50)*/
там же ссылаются на, к сожалению, недоступный документ Note:986618.1 Parameters useable by OPT_PARAM hint с полным перечнем параметров
Также доступно изменение след.параметров оптимизатора:
--+ opt_param( '_optimizer_max_permutations' 20 )
- для уменьшения времени разбора SQL (в случае длительного времени разбора запроса – long parse time, например, при большом кол-ве таблиц, используемых в запросе – от 10), доступно начиная с 10.2.0.4 по крайней мере. В трейсе 10053 при этом можно видеть:
************************************* PARAMETERS WITH DEFAULT VALUES ****************************** ... _optimizer_max_permutations = 2000 ... *************************************** PARAMETERS IN OPT_PARAM HINT **************************** _optimizer_max_permutations = 20 ***************************************
--+ opt_param( 'optimizer_features_enable' '11.1.0.7' )
- изменения параметра optimizer_features_enable на уровне запроса
При получении ошибок, причиной которых являются баги в реализации Cost-Based Join Predicate Push:
SQL> select ... * ERROR at line 15: ORA-00600: internal error code, arguments: [kkocxj : pjpCtx], [], [], [], [], [], [], []
рекомендованный (Bug 9671977 – ORA-600 [kkocxj : pjpCtx] optimizing query with outer joins if JPPD is attempted [ID 9671977.8]) для отключения проблемной фичи параметр можно установить на уровне запроса:
SQL> select /*+ opt_param('_optimizer_push_pred_cost_based' 'false') */ ...
Тот же параметр помогает при ORA-600 [qctopn1], Oracle 10.2.0.3 (Bug 5163554 – OERI[qctopn1] during join predicate pushdown [ID 5163554.8]):
SQL> SELECT
...
41 /
FROM SOME_USER_VIEW V,
*
ERROR at line 10:
ORA-00600: internal error code, arguments: [qctopn1], [], [], [], [], [], [], []
SQL> SELECT--+ opt_param('_optimizer_push_pred_cost_based' 'false')
...
42 /
... rows selected
Включение / отключение конкретного _fix_control.
Например, попробовать отключить механизм cardinality feedback через отключение _fix_control 6699059:
11.2@SQL> select bugno, description, optimizer_feature_enable, value, is_default from v$session_fix_control where bugno = 6699059; BUGNO DESCRIPTION OPTIMIZER_FEATURE_ENABLE VALUE IS_DEFAULT ---------- ---------------------------------------------------------------- ------------------------- ----- ---------- 6699059 enable cardinality feedback-based cursor sharing 11.2.0.1 1 1
на уровне запроса можно так:
SQL> select /*+ OPT_PARAM('_fix_control' '6699059:0')*/...
/*+ USE_HASH_AGGREGATION([@query_block]) */
/*+ NO_USE_HASH_AGGREGATION([@query_block]) */
Недостаточно документированные подсказки, указывают оптимизатору использовать или не использовать относительно “новую” операцию группировки Hash group by вместо классической операции Sort group by
/*+ MATERIALIZE */
www.club-oracle.com: “Подсказка Materialize представляет собой технику оптимизации запросов и может быть особенно полезна для больших наборов данных. Материализация подзапроса означает создание определённого типа динамической временной таблицы (dynamic temporary table) для использования во время выполнения запроса”
Tom Kyte: “… подсказка указывает оптимизатору Oracle “материализовать” блок запроса (query block), в котором присутствует подсказка, в виде временной таблицы [только] на время выполнения запроса .., подсказка поддерживается даже в текущей версии 11g”
SQL> with subquery 2 as 3 (select deptno, count(*) cnt from emp group by deptno ) 4 select dname, subquery.cnt from dept, subquery where dept.deptno = subquery.deptno 5 / Execution Plan ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 117 | 7 (29)| 00:00:01 | | 1 | MERGE JOIN | | 3 | 117 | 7 (29)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 3 | 78 | 5 (40)| 00:00:01 | | 5 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 | | 6 | HASH GROUP BY | | 3 | 9 | 4 (25)| 00:00:01 | | 7 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- SQL> with subquery 2 as 3 (select /*+ MATERIALIZE */ deptno, count(*) cnt from emp group by deptno ) 4 select dname, subquery.cnt from dept, subquery where dept.deptno = subquery.deptno 5 / Execution Plan ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 117 | 9 (23)| 00:00:01 | | 1 | TEMP TABLE TRANSFORMATION | | | | || | 2 | LOAD AS SELECT | | | | || | 3 | HASH GROUP BY | | 3 | 9 | 4 (25)| 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 | | 5 | MERGE JOIN | | 3 | 117 | 5 (20)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 | | 7 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 8 | SORT JOIN | | 3 | 78 | 3 (34)| 00:00:01 | | 9 | VIEW | | 3 | 78 | 2 (0)| 00:00:01 | | 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6607_1E017E | 3 | 9 | 2 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------
/*+ PRECOMPUTE_SUBQUERY */
Работает по крайней мере с версии Oracle 10.1
Tanel Poder. Multipart cursor subexecution and PRECOMPUTE_SUBQUERY hint :
SQL> select a 2 from t1 3 where a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2); ------------------------------------------- | Id | Operation | Name | E-Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | TABLE ACCESS FULL| T1 | 5 | ------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("A"=11 OR "A"=12 OR "A"=13 OR "A"=14 OR "A"=15 OR "A"=16 OR "A"=17 OR "A"=18 OR "A"=19))
/*+ MERGE([@query_block] [tablespec]) */
/*+ NO_MERGE([@query_block] [tablespec]) */
Используются для явного разрешения /*+ MERGE*/ или запрещения /*+ NO_MERGE*/ использования оптимизатором механизма объединения (complex view merging) основного запроса и встроенных обзоров в секции FROM (inline view)
“В случае, если всроенный обзор содержит запрос с конструкцией GROUP BY или оператором DISTINCT оптимизатор может объединить такой обзор с основным запросом только если complex view merging разрешено. Тот же механизм complex merging может быть использован [оптимизатором] для объединения подзапроса из секции IN (IN subquery) с основным запросом, если подзапрос не зависит от основного запроса (subquery is uncorrelated)”
SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary FROM employees e1, (SELECT department_id, avg(salary) avg_salary FROM employees e2 GROUP BY department_id) v WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;
/*+ no_eliminate_oby(@query_block) */
Подсказка (совмеместно с QB_NAME) запрещает оптимизатору исключать избыточные с его точки зрения сортировки (ORDER BY) из подзапроса (inline view)
Отличный пример товарища wurdu с форума sql.ru
select * from( 2 select /*+ no_eliminate_oby */ * from ( 3 select 3, 4 from dual 4 union all 5 select 1,5 from dual) a order by 1) order by 2;
3 4 ---------- ---------- 3 4 1 5
------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 12 | 6 (34)| 00:00:01 | | 1 | SORT ORDER BY | | 2 | 12 | 6 (34)| 00:00:01 | | 2 | VIEW | | 2 | 12 | 5 (20)| 00:00:01 | | 3 | SORT ORDER BY | | 2 | 12 | 5 (20)| 00:00:01 | | 4 | VIEW | | 2 | 12 | 4 (0)| 00:00:01 | | 5 | UNION-ALL | | | | | | | 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------
/*+ optimizer_features_enable(‘NN.N.N.N’) */
подсказка для уточнения/ограничения действия опций оптимизатора (optimizer features) в зависимости от версии Oracle на уровне запроса
полезна при обновлениях для получения ожидаемого плана выполнения запроса
значения версии (‘ve.r.si.on’) соответствуют значениям параметра OPTIMIZER_FEATURES_ENABLE
/*+ NO_UNNEST*/
Добавляется к подзапросу для предотвращения операции объединения подзапроса с головным запросом (subquery unnesting)
Пример с сайта Jonathan Lewis’а – без хинтов оптимизатор объединяет подзапрос с осн.запросом, используя условия запроса (через access predicate и filter) только на последнем шаге выполнения:
SQL> select outer.*
2 from emp
3 outer where outer.sal >
4 (select avg(inner.sal)
5 from emp
6 inner where inner.deptno = outer.deptno)
7 /
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
|* 1 | HASH JOIN | | 1 | 63 | 8 (25)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 30 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="OUTER"."DEPTNO")
filter("OUTER"."SAL">"VW_COL_1")
Подсказка NO_UNNEST меняет план, выделяя выполнение подзапроса по условию “INNER”.”DEPTNO”=:B1
SQL> select outer.* 2 from emp 3 outer where outer.sal > 4 (select/*+ NO_UNNEST*/ avg(inner.sal) 5 from emp 6 inner where inner.deptno = outer.deptno) 7 /
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 7 | | |
|* 4 | TABLE ACCESS FULL| EMP | 5 | 35 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OUTER"."SAL">)
4 - filter("INNER"."DEPTNO"=:B1)
/*+ UNNEST*/
Использование подсказки UNNEST, напротив, форсирует по возможности операцию объединения (unnests specified subquery block if possible) и в этом примере план выполнения возвращается к более дешёвому плану, используемому оптимизатором по умолчанию
SQL> select outer.*
2 from emp
3 outer where outer.sal >
4 (select/*+ UNNEST*/ avg(inner.sal)
5 from emp
6 inner where inner.deptno = outer.deptno)
7 /
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
|* 1 | HASH JOIN | | 1 | 63 | 8 (25)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 30 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="OUTER"."DEPTNO")
filter("OUTER"."SAL">"VW_COL_1")
/*+ APPEND*/
/*+ NOAPPEND*/
“Подсказка APPEND форсирует использование оптимизатором direct-path INSERT в запросах вида INSERT INTO… SELECT …
- Традиционный (conventional) метод вставки в таблицу используется по умолчанию (без хинтов) в режиме непараллельной вставки. В этом режиме direct path INSERT может быть использован только при использовании подсказки APPEND.
- Direct-path INSERT используется по умолчанию в режиме параллельной вставки [работает при выполнении след.условий:
Oracle Enterprise Edition
ALTER SESSION { ENABLE | FORCE } PARALLEL DML;
установленный признак параллельности (USER_TABLES.DEGREE) для целевой таблицы на этапе создания/модификации (CREATE|ALTER TABLE...PARALLEL n)
или подсказкой PARALLEL во время выполнения DML
или параметером PARALLEL_DEGREE_POLICY = AUTO (11.2+)]
В этом случае традиционный (conventional) метод вставки строк в таблицу будет применяться только при использовании подсказки NOAPPEND.
… При direct-path INSERT, данные добавляются в конец таблицы [добавляя новые блоки и повышая High Water Mark (HWM)], вместо того, чтобы использовать свободное место в уже выделенных блоках таблицы. В результате direct-path INSERT может быть значительно быстрее традиционной (conventional) операции вставки”
Особенности использования подсказки APPEND в запросах вида INSERT INTO … VALUES, см. APPEND Hint (Direct-Path) Insert with Values Causes Excessive Space Usage on 11G [ID 842374.1]:
1. В версиях 10g, 10g R2, подсказка APPEND игнорируется
2. В 11g R1, хинт APPEND запускает режим direct-path insert даже в случае использования INSERT INTO … VALUES. Поведение аналогично использованию подсказки APPEND_VALUES в версиях, начиная с 11g R2.
3. Начиная с версии 11g R2, появляется новая подсказка APPEND_VALUES, позволяя т.о использовать механизм direct path load только в случае использования APPEND_VALUES. Подсказка APPEND игнорируется для предложений типа INSERT … VALUES…
Рекомендуется использовать хинт APPEND (direct-path) для загрузки больших объёмов данных, а не для вставки одиночных строк, т.к. в последнем случае не будет получено преимуществ
В версии 11.1 при включении режима direct-path insert в запросах типа INSERT /*+ APPEND*/ INTO … VALUES в PL/SQL конструкциях FORALL … SAVE EXCEPTIONS встречается Bug 7688258: ORA-38910 USING APPEND HINT WITH FORALL IN 11.1.0.6, где для отключения режима direct-path insert предлагается использовать параметр:
alter session set "_direct_path_insert_features"=1;
[После установки параметра] подсказка APPEND во всех запросах типа insert /*+ append */…values… будет игнорироваться в рамках сессии … аналогично поведению в версиях Oracle до 11g, где этот хинт игнорируется и в запросах insert …values always всегда используется традиционный (conventional) режим вставки независимо от наличия хинта”
Документация о блокировках при операциях direct-path insert
В течении direct-path INSERT, бд устанавливает исключительную блокировку [TM lock mode 6 (eXclusive)] на таблицу (либо на все партиции таблицы). В результате пользователи не могут выполнять никаких конкурентных операций insert, update, или delete на таблице, также невозможны операции создания и построения индексов [при конкурентных DML сессии будут ожидать enq: TM - contention для установки блокировки TM lock в режиме (mode) 3 - row-X (SX) Row Exclusive - SubExclusive]
/*+ APPEND_VALUES */
Подсказка APPEND_VALUES форсирует использование оптимизатором direct-path INSERT [только] в запросах вида INSERT INTO… VALUES …, доступна с версии 11.2
/*+ NO_QUERY_TRANSFORMATION*/
“… инструктирует оптимизатор пропустить весь этап преобразований запроса (query transformations), влючая, но не ограничиваясь OR-expansion, view merging, subquery unnesting, star transformation, materialized view rewrite …”
Может быть полезет для сокращения времени разбора (parse time) запросов с [очень] большим количеством таблиц.
/*+ PARALLEL …*/
/*+ BIND_AWARE*/
Отключает использование Adaptive Cursor Sharing (для оценки необходимости последующего применения ECS), форсируя применение Extended Cursor Sharing для курсоров с отличающимися наборами связанных переменных, см. также:
Bug 9532657
Элементы технологий Bind Aware Cursor Sharing / Cardinality Feedback
Oracle Optimizer Blog: How do I force a query to use bind-aware cursor sharing?
/*+ NO_BIND_AWARE*/
Отключает применение технологии Extended Cursor Sharing (и, как следствие, Adaptive Cursor Sharing за отсутствием необходимости) на уровне запроса
/*+ RESULT_CACHE*/
/*+ NO_RESULT_CACHE*/
Ручное управление кэшированием результатов запросов (или частей) на стороне сервера.
Примеры использования для inline view:
SELECT * FROM ( SELECT /*+ RESULT_CACHE */ department_id, manager_id, count(*) count FROM hr.employees GROUP BY department_id, manager_id ) view1 WHERE department_id = 30;
для WITH view:
WITH view2 AS ( SELECT /*+ RESULT_CACHE */ department_id, manager_id, count(*) count FROM hr.employees GROUP BY department_id, manager_id ) SELECT * FROM view2 WHERE count BETWEEN 1 and 5;
Использовании кэша результатов подзапросов отключает операции оптимизации (трансформации, merging) между внешними и внутренними блоками запроса
Кэширование результатов запросов логично отменяет действие подсказки DRIVING_SITE
Пример применения Result Cache на стороне сервера
/*+ NATIVE_FULL_OUTER_JOIN*/
/*+ NO_NATIVE_FULL_OUTER_JOIN*/
управление использованием механизма Native Full Outer Join
TBD
Щрифт для SQL-кода очень маленький. Совершенно нечитабельный. Чтобы там что-то разобрать, надо копировать его в текстовый редактор. Это так специально сделано, чтобы жизнь медом не казалась?
комментарий от Вася — 28.04.2011 @ 14:12 |
если шрифт очень маленький то удерживай ctrl скролл покрутите и будет вам щастье
комментарий от Павел — 04.05.2011 @ 16:33 |