SQL Hints в документации Oracle
Замечания
- «О синтаксисе: /*+ HINT HINT … */ в PL/SQL пробел между «+» и первой буквой подсказки имеет значение, в случае отсутствия пробела подсказка может быть игнорирована, т.е. /*+ ALL_ROWS */ правильное использование, а /*+ALL_ROWS */ — неправильное
- Подсказки всегда «форсируют» использование стоимостной оптимизации (cost based optimizer) — кроме подсказки RULE
- Если в запросе используются псевдонимы (table alias), в подсказках также должны использоваться псевдонимы вместо названий таблиц:
SQL> SELECT /*+ FULL ( emp ) */ empno FROM emp myalias WHERE empno > 10; -- НЕправильноSQL> SELECT /*+ FULL ( myalias ) */ empno FROM emp myalias WHERE empno > 10; -- правильно |
- В посказках не должно быть указания названия схемы:
SQL> SELECT /*+ index(scott.emp emp1) */... |
- Некорректные подсказки (invalid hints) игнорируются без предупреждений… некорректность подсказки может быть неочевидна, например:
- указание подсказки FIRST_ROWS (для получения первых строк) для запроса с ORDER BY (поскольку данные должны быть отсортированы прежде, чем будут возвращены первые строки запроса, использование first_rows может не дать желаемого результата)
- указанные в подсказке операции с данными (access path) должны быть доступны Например: подсказка INDEXс указанием несуществующего индекса будет проигнорирована без сообщений об ошибках…»
Подсказка, указанная после некорректной (например, синтаксически) подсказки или текста в том же комментарии также может быть проигнорирована
Подсказки
— общие цели оптимизатора
— порядок доступа
— методы соединения
/*+ USE_HASH */
/*+ USE_NL */
/*+ USE_MERGE */
/*+ USE_HASH_AGGREGATION */
/*+ NATIVE_FULL_OUTER_JOIN */
/*+ INDEX_JOIN */
/*+ INDEX_COMBINE */
/*+ NUM_INDEX_KEYS */
/*+ USE_NL */
/*+ USE_MERGE */
/*+ USE_HASH_AGGREGATION */
/*+ NATIVE_FULL_OUTER_JOIN */
/*+ INDEX_JOIN */
/*+ INDEX_COMBINE */
/*+ NUM_INDEX_KEYS */
— способы выполнения [под]запроса
— статистика объектов
— трансформации [под]запросов
/*+ NO_QUERY_TRANSFORMATION */
/*+ PUSH_SUBQ */
/*+ NO_UNNEST */
/*+ UNNEST */
/*+ NO_ELIMINATE_OBY */
/*+ MERGE */ /*+ NO_MERGE */
/*+ USE_CONCAT */
/*+ NO_EXPAND */
/*+ PUSH_PRED */ /*+ NO_PUSH_PRED */
/*+ FACTORIZE_JOIN */ /*+ NO_FACTORIZE_JOIN */
/*+ PUSH_SUBQ */
/*+ NO_UNNEST */
/*+ UNNEST */
/*+ NO_ELIMINATE_OBY */
/*+ MERGE */ /*+ NO_MERGE */
/*+ USE_CONCAT */
/*+ NO_EXPAND */
/*+ PUSH_PRED */ /*+ NO_PUSH_PRED */
/*+ FACTORIZE_JOIN */ /*+ NO_FACTORIZE_JOIN */
— использование курсоров
— параллельное выполнение
— прочие
/*+ QB_NAME */
/*+ GATHER_PLAN_STATISTICS */
/*+ OPT_PARAM */
/*+ OPTIMIZER_FEATURES_ENABLE */
/*+ APPEND */ /*+ NOAPPEND */
/*+ APPEND_VALUES */
/*+ RESULT_CACHE */ /*+ NO_RESULT_CACHE */
/*+ IGNORE_ROW_ON_DUPKEY_INDEX */
/*+ GATHER_PLAN_STATISTICS */
/*+ OPT_PARAM */
/*+ OPTIMIZER_FEATURES_ENABLE */
/*+ APPEND */ /*+ NOAPPEND */
/*+ APPEND_VALUES */
/*+ RESULT_CACHE */ /*+ NO_RESULT_CACHE */
/*+ IGNORE_ROW_ON_DUPKEY_INDEX */
—
Optimization Goals and Approaches hints
Подсказки,определяющие общие цели и подходы для оптимизации плана выполнения запроса, включая правила и методы доступа к данным. Соответсвуют (почти) описаниям значений параметра OPTIMIZER_MODE
/*+ RULE */
Официально не поддерживается, начиная с Oracle 10, используется оптимизация по точно оределённым правилам (Rule Based Optimization) без учёта статистики объектов бд. Описание применяемых правил для инилизационного параметра optimizer_mode = rule
В Oracle 11gR2 введены дополнительные ограничения использования подсказки /*+ rule*/
В частности, несмотря на использование подсказки RULE Oracle будет использовать Cost-Based Optimization, если:
В 11.2 при использовании RBO может формироваться трейс оптимизатора и использоваться некоторые преобразования запросов за исключением Cost-Based Query Transformation (что логично:) — см. замечания в Индекс в статусе unusable, подсказка INDEX и обновлённый RBO в Oracle 11.2
- кроме RULE в запросе используются другие подсказки
- запрос использует партицированные или таблицы, организованные в виде индекса (IOT), или материализованные представления (mview)
- в запросе используются кляузы SAMPLE, SPREADSHEET, конструкции GROUPING SETS
- в запросе используются ANSI left|full outer join
- запрос выполняется параллельно
- используется Flashback cursor (as of [scn|timestamp])
- …
/*+ 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), оптимизатор выбирает индексный доступ во избежание дополнительной сортировки»
Начиная с 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
Oracle always uses ALL_ROWS mode internally for DML statement — в блоге Dion Cho есть показательный пример для версии 10.2
В Oracle 11.2 ничего не изменилось — независимо от количества обновляемых строк (Rows), устанавливаемого функцией rownum ни планы выполнения (кроме дополнительной операции COUNT STOPKEY), ни стоимость, ни ожидаемое время (Time) не меняются:
-- План при обновлении ВСЕХ строк таблицы11.2.0.3@SQL> update T...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 T...5 and rownum 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)
Простой тест для показывает, как по-разному режимы ALL_ROWS, FIRST_ROWS и FIRST_ROWS(n) влияют на поведение оптимизатора: собственно, планы выполнения и методы доступа к данным + Cost + Rows :) на примерах сортировки или группировки при использовании бессмысленного условия object_id > 1 — которому удовлетворяют все строки таблицы T1 — по условию создания таблицы min(object_id)=2)*:
11.2.0.3.@SQL> show parameter optimizer_modeNAME TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_mode string ALL_ROWSSQL> create table T1 as select * from dba_objects2 /Table created.SQL> create index T1_IDX on T1(OBJECT_ID)2 /Index created.SQL> exec dbms_stats.gather_table_stats('','T1');PL/SQL procedure successfully completed.SQL> ----------------------------------------------------------- ORDER BYSQL> select /*+ ALL_ROWS*/2 *3 from t14 where object_name like '%#%'5 and object_id > 16 order by object_id;30 rows selected.---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2952 | 279K| 164 (2)| 00:00:01 || 1 | SORT ORDER BY | | 2952 | 279K| 164 (2)| 00:00:01 ||* 2 | TABLE ACCESS FULL| T1 | 2952 | 279K| 163 (1)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - filter("OBJECT_NAME" LIKE '%#%' AND "OBJECT_NAME" IS NOT NULLAND "OBJECT_ID">1)SQL> select /*+ FIRST_ROWS*/2 *3 from t14 where object_name like '%#%'5 and object_id > 16 order by object_id;30 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 546753835--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2952 | 279K| 553 (1)| 00:00:01 | -- при тех же ожидаемых Rows=2952 - изменились план и стоимость|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 2952 | 279K| 553 (1)| 00:00:01 | -- исключена сортировка|* 2 | INDEX RANGE SCAN | T1_IDX | 59030 | | 66 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("OBJECT_NAME" LIKE '%#%' AND "OBJECT_NAME" IS NOT NULL)2 - access("OBJECT_ID">1) -- бессмысленное условие: INDEX RANGE SCAN только экономит на сортировкеSQL> select /*+ FIRST_ROWS(10)*/2 *3 from t14 where object_name like '%#%'5 and object_id > 16 order by object_id;30 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 546753835--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 970 | 4 (0)| 00:00:01 | -- абс.тот же план (Plan hash value) рассчитан по-другому:|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 970 | 4 (0)| 00:00:01 | -- изменились Cardinality (Rows) и итоговая стоимость|* 2 | INDEX RANGE SCAN | T1_IDX | | | 2 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("OBJECT_NAME" LIKE '%#%' AND "OBJECT_NAME" IS NOT NULL)2 - access("OBJECT_ID">1)SQL> ----------------------------------------------------------- GROUP BYSQL> select /*+ ALL_ROWS*/2 object_id, count(*)3 from t14 where object_name like '%#%'5 and object_id > 16 group by object_id;30 rows selected.---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2952 | 88560 | 164 (2)| 00:00:01 || 1 | HASH GROUP BY | | 2952 | 88560 | 164 (2)| 00:00:01 ||* 2 | TABLE ACCESS FULL| T1 | 2952 | 88560 | 163 (1)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - filter("OBJECT_NAME" LIKE '%#%' AND "OBJECT_NAME" IS NOT NULLAND "OBJECT_ID">1)SQL> select /*+ FIRST_ROWS*/2 object_id, count(*)3 from t14 where object_name like '%#%'5 and object_id > 16 group by object_id;30 rows selected.---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2952 | 88560 | 164 (2)| 00:00:01 || 1 | HASH GROUP BY | | 2952 | 88560 | 164 (2)| 00:00:01 ||* 2 | TABLE ACCESS FULL| T1 | 2952 | 88560 | 163 (1)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("OBJECT_NAME" LIKE '%#%' AND "OBJECT_NAME" IS NOT NULL AND "OBJECT_ID">1)SQL> select /*+ FIRST_ROWS(10)*/2 object_id, count(*)3 from t14 where object_name like '%#%'5 and object_id > 16 group by object_id;30 rows selected.---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 300 | 4 (0)| 00:00:01 | -- абсолютно другой план - дешевле и с более точной оценкой Rows| 1 | SORT GROUP BY NOSORT | | 10 | 300 | 4 (0)| 00:00:01 | -- вместо HASH GROUP BY используется SORT GROUP BY NOSORT,|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 10 | 300 | 4 (0)| 00:00:01 | -- благодаря индексному доступу к отсортированным данным|* 3 | INDEX RANGE SCAN | T1_IDX | | | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("OBJECT_NAME" LIKE '%#%' AND "OBJECT_NAME" IS NOT NULL) 3 - access("OBJECT_ID">1) |
*) статистика для таблицы T1 актуальна и оптимизатор прекрасно «знает» о минимальном и максимальном значении T1.Object_id:
SQL> set serveroutput onSQL> declare2 low_value_raw RAW(2000);3 high_value_raw RAW(2000);4 low_value_num number;5 high_value_num number;6 begin7 select low_value, high_value8 into low_value_raw, high_value_raw9 from user_tab_col_statistics10 where table_name = 'T1'11 and column_name = 'OBJECT_ID';12 dbms_stats.convert_raw_value(low_value_raw, low_value_num);13 dbms_stats.convert_raw_value(high_value_raw, high_value_num);14 dbms_output.put_line('Low value T1.OBJECT_ID = ' || low_value_num);15 dbms_output.put_line('High value T1.OBJECT_ID = ' || high_value_num);16 end;17 /Low value T1.OBJECT_ID = 2High value T1.OBJECT_ID = 107516 |
или так, попроще:
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_statistics4 where table_name = 'T1'5 and column_name = 'OBJECT_ID'; LOW HIGH---------- ---------- 2 107516 |
Access Path Hints
Подсказки,определяющие конкретные способы доступа к данным, порядок и применяемые методы объединения промежуточных наборов данных (result sets)*
Том Кайт называет эти подсказки плохими (bad hints):
«Плохие подсказки указывают оптимизатору как следует действовать [при выполнении запроса], какой индекс использовать, в каком порядке обрабатывать таблицы, с помощью какой операции (join technique) производить соединение [источников данных]«
*) Перед применение необходимо учитывать вероятность изменения статистики системы и объектов (таблиц и индексов), используемых в запросе со временем и, как следствие, возможную неоптимальность указываемых операций для будущих наборов данных. План выполнения, оптимизированный с помощью подсказок этого типа в среде разработки, может оказаться далеко не оптимальным для боевой бд (production system) из-за отличающихся наборов данных и характеристик системы («железо»)
/*+ LEADING( [@query_block] [tablespec],[tablespec],.. ) */
«Подсказка LEADING указывает оптимизатору использовать перечисленный порядок доступа к таблицам при построении плана выполнения запроса… более гибкая, чем ORDERED… Полностью игнорируются при использовании двух или более конфликтующих подсказок LEADING. Для оптимизатора подсказка ORDERED имеет преимущество против LEADING»
В версии 11.2 может игнорироваться оптимизатором при выполнении преобразований, основанных на стоимости (Cost-Based Query Transformation), например, Table Expansion
/*+ ORDERED */
«Подсказка ORDERED указывает Oracle [при выполнении запроса] проводить соединение таблиц в том же порядке, в котором таблицы перечислены в конструкции FROM. Oracle рекомендует вместо ORDERED использовать подсказку LEADING, обладающую большей гибкостью…», т.е. дающей оптимизатору больше возможностей в выборе плана выполнения
/*+ USE_HASH( [@query_block] [tablespec] [tablespec]… ) */
/*+ NO_USE_HASH( [@query_block] [tablespec] [tablespec]… ) */
…указывает оптимизатору использовать / не использовать операцию hash join для соединения каждой указанной таблицы с прочими источниками данных Операции CBO: Hash Join
/*+ USE_NL ( [@query_block] [inner_table] ) */
/*+ NO_USE_NL ( [@query_block] [inner_table] ) */
/*+ USE_NL_WITH_INDEX ( [@query_block] inner_table [indexspec]) */
/*+ USE_MERGE( [@query_block] [tablespec] … ) */
/*+ NO_USE_MERGE( [@query_block] [tablespec] … ) */
/*+ USE_HASH_AGGREGATION([@query_block]) */
/*+ NO_USE_HASH_AGGREGATION([@query_block]) */
указывают оптимизатору использовать или не использовать вместо классической операции Sort group by относительно «новую» операцию группировки Hash Group By
/*+ NATIVE_FULL_OUTER_JOIN*/
/*+ NO_NATIVE_FULL_OUTER_JOIN*/
управление использованием механизма Native Full Outer Join
/*+ INDEX_JOIN ( [@query_block] tablespec [indexspec],… ) */
использовать для получения результатов запроса временный индекс, получающийся в рез-те объединения существующих индексов методом Index [Hash] Join
/*+ INDEX_COMBINE ( [@query_block] tablespec [indexspec],… ) */
использовать для получения результатов запроса Bitmap операции с ROWID, полученными при индексном доступе.
Сравнение 2-х последних методов: Index Join vs Index Bitmap и использование db file parallel read при доступе к блокам таблицы
/*+ NUM_INDEX_KEYS( table index numkeys ) */
добавлен в версии 10.2 для управления кол-вом индексных ключей, используемых в INLIST ITERATOR с индексом по нескольким полям Bug 5152325 Enh: Add NUM_INDEX_KEYS hint
Функционал подсказки исправлен для 11.2.0.3 и зафиксирован в 12.2 — Unresolved quiz: Avoiding in-list iterator
Функционал подсказки исправлен для 11.2.0.3 и зафиксирован в 12.2 — Unresolved quiz: Avoiding in-list iterator
/*+ DRIVING_SITE ( [@query_block] [tablespec] ) */
«… подсказка указывает оптимизатору выполнять запрос на сайте [сайте таблицы, указанной в хинте], отличном от выбранного бд [Oracle]. Хинт полезен для оптимизации выполнения распределённых запросов»
В зависимости от подсказки запрос выполняется полностью на удалённом сайте — при указании удалённой таблицы в хинте DRIVING_SITE(e):
SQL> select--+ DRIVING_SITE(e)2 count(*)3 from emp@loopback_dblink e,4 dual5 /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 dual5 /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' ) |
Версии: 9.2.0.1 — 11.2.0.2 … распределённый DML должен выполняться в бд, где расположена целевая таблица DML. Подсказка DRIVING_SITE не меняет этого поведения
DRIVING_SITE предполагает управление выполнением (mapping) всего курсора (а не отдельных подзапросов)… [однако можно перенести выполнение части курсора / подзапроса в удалённый обзор]
Подсказка DRIVING_SITE предназначена для оптимизации запросов (SELECT) и не предназначена для DML или DDL
/*+ MATERIALIZE */
www.club-oracle.com: «Подсказка Materialize представляет собой технику оптимизации запросов и может быть особенно полезна для больших наборов данных. Материализация подзапроса означает создание определённого типа динамической временной таблицы (dynamic temporary table) для использования во время выполнения запроса»
Tom Kyte: «… подсказка указывает оптимизатору Oracle «материализовать» блок запроса (query block), в котором присутствует подсказка, в виде временной таблицы [только] на время выполнения запроса .., подсказка поддерживается даже в текущей версии 11g»
SQL> with emp_sq as2 (select * from emp where comm > 0)3 select e.ename, d.loc from emp_sq e, dept d where d.deptno = e.deptno4 /-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 66 | 6 (17)|| 1 | MERGE JOIN | | 3 | 66 | 6 (17)|| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)|| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)||* 4 | SORT JOIN | | 3 | 33 | 4 (25)||* 5 | TABLE ACCESS FULL | EMP | 3 | 33 | 3 (0)|-----------------------------------------------------------------------------Statistics---------------------------------------------------------- 1 recursive calls 10 consistent gets 0 redo size 3 rows processedSQL> with emp_sq as2 (select /*+ MATERIALIZE */ * from emp where comm > 0)3 select e.ename, d.loc from emp_sq e, dept d where d.deptno = e.deptno4 /------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 93 | 8 (13)|| 1 | TEMP TABLE TRANSFORMATION | | | | || 2 | LOAD AS SELECT | SYS_TEMP_0FD9D665D_A5677E | | | ||* 3 | TABLE ACCESS FULL | EMP | 3 | 114 | 3 (0)|| 4 | MERGE JOIN | | 3 | 93 | 5 (20)|| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)|| 6 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)||* 7 | SORT JOIN | | 3 | 60 | 3 (34)|| 8 | VIEW | | 3 | 60 | 2 (0)|| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D665D_A5677E | 3 | 114 | 2 (0)|------------------------------------------------------------------------------------------------Statistics---------------------------------------------------------- 54 recursive calls 56 consistent gets 572 redo size 3 rows processed |
Соответствующий параметр, доступный с версии 11.1:
11.2.0.1.@SQL> @param_ _with_subqueryNAME VALUE IS_DEF DSC--------------- ---------- -------- ----------------------------_with_subquery OPTIMIZER TRUE WITH subquery transformation11.2.0.1.SQL> @pvalid_ _with_subqueryPARAMETER VALUE DEFAULT-------------------------------------------------- ------------------------------ -------_with_subquery INLINE OPTIMIZER DEFAULT MATERIALIZE |
/*+ INLINE */
Противоположна по смыслу предыдущей подсказке /*+ MATERIALIZE*/:
SQL> select NAME, SQL_FEATURE, CLASS, INVERSE, VERSION from v$sql_hint where name in ('INLINE','MATERIALIZE');NAME SQL_FEATURE CLASS INVERSE VERSION------------------------------ ------------------------------ ---------- --------------------------- -------MATERIALIZE QKSFM_TRANSFORMATION INLINE INLINE 9.0.0INLINE QKSFM_TRANSFORMATION INLINE MATERIALIZE 9.0.0 |
Позволяет в случаях, когда Oracle обязан материализовать подзапрос конструкции WITH без подсказок (согласно правилам выполнения subquery factoring, например, когда подзапрос используется в основном запросе > 1 раза), не делать этого, рассматривая подзапрос в качестве inline view.
Полезен в случаях, когда материализуемый подзапрос используется
редков соединениях с малым количеством строк и накладные расходы на создание temporary table, подразумевающее ALL ROWS mode, оказываются больше стоимости нескольких выполнений подзапроса в режиме FIRST ROWS, например, с использованием индексов
На примере тестовой схемы можно видеть как применение подсказки уменьшает полную стоимости всего запроса, используя при выполнении одного из подзапросов WITH созданный индекс:
11.2.0.3.ORCL112@SCOTT SQL> create index IDX_EMP_DEPTNO on EMP(DEPTNO);Index created.SQL> with emp_sq as2 (select deptno, count(*) count_by_dept from emp group by deptno)3 select e.count_by_dept, d.loc4 from emp_sq e, dept d5 where d.deptno = e.deptno6 and d.deptno > (select avg(deptno) from emp_sq)7 /------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 37 | 11 (19)| -- стоимость 11| 1 | TEMP TABLE TRANSFORMATION | | | | | -- TempTable создаётся без подсказок| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6665_A5677E | | | || 3 | HASH GROUP BY | | 3 | 9 | 4 (25)|| 4 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)||* 5 | HASH JOIN | | 1 | 37 | 5 (20)|| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2 (0)||* 7 | INDEX RANGE SCAN | PK_DEPT | 1 | | 1 (0)|| 8 | SORT AGGREGATE | | 1 | 13 | || 9 | VIEW | | 3 | 39 | 2 (0)|| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6665_A5677E | 3 | 9 | 2 (0)|| 11 | VIEW | | 3 | 78 | 2 (0)|| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6665_A5677E | 3 | 9 | 2 (0)|------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 5 - access("D"."DEPTNO"="E"."DEPTNO") 7 - access("D"."DEPTNO"> (SELECT AVG("DEPTNO") FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "DEPTNO","C1" "COUNT_BY_DEPT" FROM "SYS"."SYS_TEMP_0FD9D6665_A5677E" "T1") "EMP_SQ"))Statistics---------------------------------------------------------- 48 recursive calls -- + накладные расходы 45 consistent gets -- - неэффективный план 576 redo size -- + накладные расходы 1 rows processedSQL> with emp_sq as2 (select /*+ INLINE */ deptno, count(*) count_by_dept from emp group by deptno)3 select e.count_by_dept, d.loc4 from emp_sq e, dept d5 where d.deptno = e.deptno6 and d.deptno > (select avg(deptno) from emp_sq)7 /-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 23 | 7 (29)| -- меньшая стоимость 7 против 11| 1 | HASH GROUP BY | | 1 | 23 | 7 (29)|| 2 | NESTED LOOPS | | 1 | 23 | 2 (0)|| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)||* 4 | INDEX RANGE SCAN | PK_DEPT | 1 | | 1 (0)|| 5 | SORT AGGREGATE | | 1 | 13 | || 6 | VIEW | | 3 | 39 | 4 (25)|| 7 | SORT GROUP BY | | 3 | 9 | 4 (25)|| 8 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)||* 9 | INDEX RANGE SCAN | IDX_EMP_DEPTNO | 5 | 15 | 0 (0)| -- индексный доступ-------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("D"."DEPTNO"> (SELECT AVG("DEPTNO") FROM (SELECT "DEPTNO" "DEPTNO",COUNT(*) "COUNT_BY_DEPT" FROM "EMP" "EMP" GROUP BY "DEPTNO") "EMP_SQ")) 9 - access("D"."DEPTNO"="DEPTNO")Statistics---------------------------------------------------------- 1 recursive calls -- - накладные расходы 10 consistent gets -- + эффективный план 0 redo size -- - накладные расходы 1 rows processed |
/*+ PRECOMPUTE_SUBQUERY */
Позволяет выделить выполнение подзапроса в отдельный курсор
Работает по крайней мере с версии Oracle 10.1 — Tanel Poder. Multipart cursor subexecution and PRECOMPUTE_SUBQUERY hint :
11.2.@SQL> select * from scott.emp e where e.deptno in ( select e.deptno from scott.emp e where rownum <= 1);---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 5 (100)| ||* 1 | HASH JOIN SEMI | | 5 | 250 | 5 (20)| 00:00:01 || 2 | TABLE ACCESS FULL | EMP | 14 | 518 | 2 (0)| 00:00:01 || 3 | VIEW | VW_NSO_1 | 1 | 13 | 2 (0)| 00:00:01 ||* 4 | COUNT STOPKEY | | | | | || 5 | TABLE ACCESS FULL| EMP | 1 | 3 | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("E"."DEPTNO"="DEPTNO") 4 - filter(ROWNUM<=1)11.2.@SQL> select * from scott.emp e where e.deptno in ( select /*+ PRECOMPUTE_SUBQUERY */ e.deptno from scott.emp e where rownum <= 1);-------------------------------------SQL_ID c3ng7ryq9agaa, child number 0---------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 (100)| | -- план выполнения основного запроса|* 1 | TABLE ACCESS FULL| EMP | 5 | 185 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("E"."DEPTNO"=20) -- фильтр получен при выполнении рекурсивного подзапроса11.2.@SQL> select * from scott.emp e where e.deptno in ( select /*+ PRECOMPUTE_SUBQUERY */ e.deptno from scott.emp e where rownum <= 1);-------------------------------------SQL_ID c3ng7ryq9agaa, child number 1 -- новый курсор для каждого выполнения с признаком CURSOR_PARTS_MISMATCH---------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 (100)| ||* 1 | TABLE ACCESS FULL| EMP | 5 | 185 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("E"."DEPTNO"=20)11.2.@SQL> select * from scott.emp e where e.deptno = ( select /*+ PRECOMPUTE_SUBQUERY */ e.deptno from scott.emp e where rownum <= 1);----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 4 (100)| | -- подсказка работает только с IN/NOT IN|* 1 | TABLE ACCESS FULL | EMP | 5 | 185 | 2 (0)| 00:00:01 ||* 2 | COUNT STOPKEY | | | | | || 3 | TABLE ACCESS FULL| EMP | 1 | 3 | 2 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("E"."DEPTNO"=)2 - filter(ROWNUM<=1) |
Подсказка недокументирована и может быть использована в боевых условиях только по согласованию с техподдежкой во избежание ORA-00600: [kglUnKeepHandle] при превышении лимита количества дочерних курсоров
Подсказки, динамически влияющие на статистику оптимизатора
/*+ DYNAMIC_SAMPLING ( [@query_block] [tablespec] degree_of_sampling ) */
Описание механизма -> OPTIMIZER_DYNAMIC_SAMPLING
При использовании подсказки DYNAMIC_SAMPLING:
Если в бд для таблицы имеется [актуальная] статистика по количеству строк,…, оптимизатор использует эту статистику. Иначе запросы dynamic sampling будет выполнены для оценки этой статистики. Если в подсказке указана таблица и имеется [актуальная] статистика по количеству строк:
- Если в условиях 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 ...
- которое, должно вызывать применение механизма для всех таблиц запроса, может приводить к неожиданному результату
Например, в 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
Запросы с комментарием /* OPT_DYN_SAMP */ в файле SQL trace:
** Generated dynamic sampling query: query text :SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1), 0), NVL(SUM(C2), 0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T_FACT") FULL("T_FACT") NO_PARALLEL_INDEX("T_FACT") */ 1 AS C1, CASE WHEN "T_FACT"."DT" > TO_DATE(' 2011-03-17 04:48:02', 'syyyy-mm-dd hh24:mi:ss') AND "T_FACT"."STATUS" < 4 THEN 1 ELSE 0 END AS C2 FROM "BO"."T_FACT" SAMPLE BLOCK(0.016795, 1) SEED(1) "T_FACT") SAMPLESUB |
/*+ DYNAMIC_SAMPLING_EST_CDN( [@query_block] [tablespec] ) */
To force cardinality estimation even for an analyzed table, you can use a further hint, dynamic_sampling_est_cdn
Подсказка оптимизатору — заново оценить количество строк в таблице (cardinality), независимо от состояния статистики таблицы
Может успешно применяться для уточнения избирательности запросов с условиями по множеству столбцов (complex predicates) при наличии правильных индексов, в случаях когда обычный механизм Dynamic Sampling (стимулируемый параметром optimizer_dynamic_sampling) неэффективен — Randolf Geist.Dynamic Sampling (III) – Real-Life Data – Part II
/*+ 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 onSQL> select * from table(select collect(empno) from emp) x;14 rows selected.--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 | -- стандартная оценка Rows для Pipelined Function| 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, 144) */ * from table(select collect(empno) from scott.emp) x;14 rows selected.--------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 144 | 288 | 29 (0)| 00:00:01 || 1 | COLLECTION ITERATOR PICKLER FETCH| | 144 | 288 | 29 (0)| 00:00:01 | -- оценка Rows из подсказки CARDINALITY| 2 | SORT AGGREGATE | | 1 | 4 | | || 3 | INDEX FULL SCAN | SYS_C0093796 | 14 | 56 | 1 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------- |
Механизм DYNAMIC SAMPLING с табличной функцией не работает до версии 11.1.0.7:
10.2.0.4.@ SQL> select /*+ DYNAMIC_SAMPLING(x 10) */ * from table(select collect(empno) from emp) x;14 rows selected.--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 | -- стандартная оценка Rows, в 10.2 DYNAMIC SAMPLING не работает| 1 | COLLECTION ITERATOR PICKLER FETCH| | | | | || 2 | SORT AGGREGATE | | 1 | 4 | | || 3 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------------11.2.0.3.@ SQL> select/*+ DYNAMIC_SAMPLING(x 10) */ * from table(select collect(empno) from scott.emp) x;14 rows selected.Elapsed: 00:00:00.04Execution Plan----------------------------------------------------------Plan hash value: 3266006369--------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 28 | 11 (0)| 00:00:01 | -- правильная оценка, DYNAMIC SAMPLING работает| 1 | COLLECTION ITERATOR PICKLER FETCH| | 14 | 28 | 11 (0)| 00:00:01 || 2 | SORT AGGREGATE | | 1 | 4 | | || 3 | INDEX FULL SCAN | SYS_C0093796 | 14 | 56 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------Note------ dynamic sampling used for this statement (level=2) |
Рекомендуется в качестве workaround при некоторых багах оптимизатора, например Bug 11826450 : WRONG CARDINALITY WITH CONNECT BY PRIOR IN 10.2.0.4
/*+ OPT_ESTIMATE( [query block] operation_type identifier adjustment ) */
Предназначен для влияния на план выполнения через корректировку оценки кол-ва строк (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
Управления преобразованиями, выполняемыми в процессе оптимизации запросов
/*+ NO_QUERY_TRANSFORMATION*/
«… инструктирует оптимизатор пропустить весь этап преобразований запроса (query transformations), влючая, но не ограничиваясь OR-expansion, view merging, subquery unnesting, star transformation, materialized view rewrite …»
Кроме собственно улучшения плана выполнения за счёт исключения неоптимальных операций/трансформаций, может быть полезет для сокращения времени разбора (parse time) запросов с [очень] большим количеством таблиц
/*+ PUSH_SUBQ( [@query_block] ) */
«Указывает оптимизатору выполнять непреобразованный подзапрос (nonmerged subqueries) на самом раннем возможном шаге плана выполнения запроса. В то время, как обычно, непреобразованные подзапросы (nonmerged subqueries) выполняются на последнем шаге плана выполнения. Раннее выполнение подзапроса может значительно ускорить производительность, если подзапрос относительно лёгок и может значительно уменьшить количество обрабатываемых строк»
<на примере запроса, который без подсказок сначала соединяет таблицы головного запроса (HASH JOIN), и только затем обращается к таблице DEPT из подзапроса EXISTS (HASH JOIN SEMI):
SQL> select2 ename3 from scott.emp, scott.dept4 where emp.sal between 1001 and 22005 and dept.deptno = emp.deptno6 and dept.dname >= 'C'7 and exists (select8 null9 from scott.dept10 where deptno = emp.deptno11 and loc >= '2')12 /----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 6 | 222 | 7 (15)| 00:00:01 | -- более точная оценка|* 1 | HASH JOIN SEMI | | 6 | 222 | 7 (15)| 00:00:01 | -- EXISTS subquery|* 2 | HASH JOIN | | 6 | 156 | 5 (20)| 00:00:01 ||* 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL| EMP | 6 | 78 | 2 (0)| 00:00:01 ||* 5 | TABLE ACCESS FULL | DEPT | 4 | 44 | 2 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("DEPTNO"="EMP"."DEPTNO") 2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO") 3 - filter("DEPT"."DNAME">='C') 4 - filter("EMP"."SAL"<=2200 AND "EMP"."SAL">=1001) 5 - filter("LOC">='2')Statistics---------------------------------------------------------- ... 5 rows processed |
- на уровне всего запроса в виде /*+ PUSH_SUBQ( @query_block ) */ с указанием блока, определяемого подсказкой QB_NAME:
11.2.0.3@ SQL> select /*+ push_subq(@subq1) */2 ename3 from scott.emp, scott.dept4 where emp.sal between 1001 and 22005 and dept.deptno = emp.deptno6 and dept.dname >= 'C'7 and exists (select /*+ qb_name(subq1) no_unnest*/8 null9 from scott.dept10 where deptno = emp.deptno11 and loc >= '2')12 /Execution Plan----------------------------------------------------------Plan hash value: 3414739439-----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0)| 00:00:01 | -- менее точная оценка Rows, и, как следствие, Cost| 1 | NESTED LOOPS | | | | | || 2 | NESTED LOOPS | | 1 | 26 | 3 (0)| 00:00:01 ||* 3 | TABLE ACCESS FULL | EMP | 1 | 13 | 2 (0)| 00:00:01 ||* 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 ||* 5 | INDEX UNIQUE SCAN | SYS_C0093797 | 1 | | 0 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | SYS_C0093797 | 1 | | 0 (0)| 00:00:01 ||* 7 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("EMP"."SAL"<=2200 AND "EMP"."SAL">=1001 AND EXISTS (SELECT /*+ -- шикарный FILTER, указывающий PUSH_SUBQ NO_UNNEST QB_NAME ("SUBQ1") */ 0 FROM "SCOTT"."DEPT" "DEPT" WHERE -- следующий метод использования PUSH_SUBQ "DEPTNO"=:B1 AND "LOC">='2')) 4 - filter("LOC">='2') 5 - access("DEPTNO"=:B1) 6 - access("DEPT"."DEPTNO"="EMP"."DEPTNO") 7 - filter("DEPT"."DNAME">='C')Statistics---------------------------------------------------------- ... 5 rows processed |
- прямо в подзапросе без указания блока /*+ PUSH_SUBQ NO_UNNEST */
11.2.0.3.OEBS2@SYS SQL> select2 ename3 from scott.emp, scott.dept4 where emp.sal between 1001 and 22005 and dept.deptno = emp.deptno6 and dept.dname >= 'C'7 and exists (select /*+ no_unnest push_subq*/8 null9 from scott.dept10 where deptno = emp.deptno11 and loc >= '2')12 /Execution Plan----------------------------------------------------------Plan hash value: 3414739439 -- план совпадает с предыдущим |
При указании на уровне всего запроса в виде /*+ PUSH_SUBQ */ без указания блока подсказка не работает, начиная с версии Oracle 10g, хотя план и изменился по сравнению с оригинальным из-за действия подсказки /*+ NO_UNNEST*/:
11.2.0.3.OEBS2@SYS SQL> select /*+ push_subq */2 ename3 from scott.emp, scott.dept4 where emp.sal between 1001 and 22005 and dept.deptno = emp.deptno6 and dept.dname >= 'C'7 and exists (select /*+ no_unnest */8 null9 from scott.dept10 where deptno = emp.deptno11 and loc >= '2')12 /---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 52 | 7 (0)| 00:00:01 ||* 1 | FILTER | | | | | | -- исчез HASH JOIN SEMI|* 2 | HASH JOIN | | 6 | 156 | 5 (20)| 00:00:01 ||* 3 | TABLE ACCESS FULL | DEPT | 4 | 52 | 2 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL | EMP | 6 | 78 | 2 (0)| 00:00:01 ||* 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 | -- EXISTS подзапрос выполняется отдельно от основного NO-UNNESTING|* 6 | INDEX UNIQUE SCAN | SYS_C0093797 | 1 | | 0 (0)| 00:00:01 |--------------------------------------------------------------------------------------------- |
Таким образом можно точно указать оптимизатору какие подзапросы должны и будут выполняться на ранних этапах плана выполнения, а какие на последних — соответственно, с помощью следующей подсказки
/*+ NO_PUSH_SUBQ( [@query_block] ) */
«Указывает оптимизатору выполнять непреобразованный подзапрос (nonmerged subqueries) на последнем шаге плана выполнения запроса [т.е. так, как это обычно делает оптимизатор - видимо, имеет смысл при каком-то необычном поведении оптимизатора]. Может положительно влиять на производительность, если подзапрос относительно тяжёлый или незначительно уменьшает количество строк»
Синтаксис аналогичен синтаксису подсказки /*+ PUSH_SUBQ */
/*+ NO_UNNEST*/
Добавляется к подзапросу для предотвращения операции объединения подзапроса с головным запросом (subquery unnesting)
Пример с сайта Jonathan Lewis’а — без хинтов оптимизатор объединяет подзапрос с основным запросом, используя условия запроса (через access predicate и filter) только на последнем шаге выполнения:
SQL> select outer.*2 from scott.emp outer3 where outer.sal >4 (select5 avg(inner.sal)6 from scott.emp inner7 where inner.deptno = outer.deptno)8 /--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 63 | 6 (34)| 00:00:01 ||* 1 | HASH JOIN | | 1 | 63 | 6 (34)| 00:00:01 || 2 | VIEW | VW_SQ_1 | 3 | 78 | 3 (34)| 00:00:01 || 3 | HASH GROUP BY | | 3 | 21 | 3 (34)| 00:00:01 || 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 || 5 | TABLE ACCESS FULL | EMP | 14 | 518 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("ITEM_1"="OUTER"."DEPTNO") filter("OUTER"."SAL">"AVG(INNER.SAL)") |
Подсказка NO_UNNEST меняет план, выделяя выполнение подзапроса (включая вычисление avg(inner.sal) — аггрегацию SORT AGGREGATE) по условию «INNER».»DEPTNO»=:B1:
SQL> select outer.*2 from scott.emp outer3 where outer.sal >4 (select/*+ NO_UNNEST*/5 avg(inner.sal)6 from scott.emp inner7 where inner.deptno = outer.deptno)8 /Execution Plan----------------------------------------------------------Plan hash value: 2649664444----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 37 | 8 (0)| 00:00:01 ||* 1 | FILTER | | | | | || 2 | TABLE ACCESS FULL | EMP | 14 | 518 | 2 (0)| 00:00:01 || 3 | SORT AGGREGATE | | 1 | 7 | | ||* 4 | TABLE ACCESS FULL| EMP | 5 | 35 | 2 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("OUTER"."SAL"> (SELECT /*+ NO_UNNEST */ AVG("INNER"."SAL") FROM "SCOTT"."EMP" "INNER" WHERE "INNER"."DEPTNO"=:B1)) 4 - filter("INNER"."DEPTNO"=:B1)SQL> select/*+ NO_UNNEST(@subq1) */2 outer.*3 from scott.emp outer4 where outer.sal >5 (select/*+ QB_NAME(subq1)*/6 avg(inner.sal)7 from scott.emp inner8 where inner.deptno = outer.deptno)9 /Execution Plan----------------------------------------------------------Plan hash value: 2649664444 -- тот же план |
/*+ 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") |
/*+ NO_ELIMINATE_OBY(@query_block) */
/*+ ELIMINATE_OBY(@query_block) */
Подсказка (совмеместно с QB_NAME) запрещает оптимизатору исключать избыточные с его точки зрения сортировки (ORDER BY) из подзапроса (inline view)
SQL> select name, sql_feature, inverse, version from v$sql_hint where name like '%ELIMINATE_OBY%';NAME SQL_FEATURE INVERSE VERSION----------------- ------------ ----------------- --------ELIMINATE_OBY QKSFM_OBYE NO_ELIMINATE_OBY 10.2.0.1NO_ELIMINATE_OBY QKSFM_OBYE ELIMINATE_OBY 10.2.0.1 |
SQL> select * from(2 select * from (3 select 3, 4 from dual4 union all5 select 2, 5 from dual6 union all7 select 1, 4 from dual) a8 order by 19 )10 order by 211 / 3 4---------- ---------- 3 4 1 4 2 5-------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 18 | 7 (15)| 00:00:01 || 1 | SORT ORDER BY | | 3 | 18 | 7 (15)| 00:00:01 | -- единственный ORDER BY| 2 | VIEW | | 3 | 18 | 6 (0)| 00:00:01 || 3 | UNION-ALL | | | | | || 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 || 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 || 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |-------------------------------------------------------------------------SQL> select * from(2 select /*+ no_eliminate_oby */3 * from (4 select 3, 4 from dual5 union all6 select 2, 5 from dual7 union all8 select 1, 4 from dual) a9 order by 110 )11 order by 212 / 3 4---------- ---------- 1 4 -- другой рез-т за счёт неисключённого ORDER BY 3 4 2 5SQL> select/*+ no_eliminate_oby(@subq1) */2 * from(3 select/*+ qb_name(subq1)*/4 * from (5 select 3, 4 from dual6 union all7 select 2, 5 from dual8 union all9 select 1, 4 from dual) a10 order by 1)11 order by 212 /-------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 18 | 8 (25)| 00:00:01 || 1 | SORT ORDER BY | | 3 | 18 | 8 (25)| 00:00:01 | -- ORDER BY| 2 | VIEW | | 3 | 18 | 7 (15)| 00:00:01 || 3 | SORT ORDER BY | | 3 | 18 | 7 (15)| 00:00:01 | -- ORDER BY| 4 | VIEW | | 3 | 18 | 6 (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 || 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |------------------------------------------------------------------------- |
/*+ 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)»
SQL> SELECT2 e1.ename, v.emp_by_dept3 FROM scott.emp e1,4 (SELECT deptno, count(*) as emp_by_dept5 FROM scott.emp e26 GROUP BY deptno) v7 WHERE e1.deptno = v.deptno8 /SQL> SELECT /*+ NO_MERGE(v) */2 e1.ename, v.emp_by_dept3 FROM scott.emp e1,4 (SELECT deptno, count(*) as emp_by_dept5 FROM scott.emp e26 GROUP BY deptno) v7 WHERE e1.deptno = v.deptno8 /-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 490 | 6 (34)| 00:00:01 ||* 1 | HASH JOIN | | 14 | 490 | 6 (34)| 00:00:01 || 2 | VIEW | | 3 | 78 | 3 (34)| 00:00:01 | -- операция создания INLINE VIEW| 3 | HASH GROUP BY | | 3 | 9 | 3 (34)| 00:00:01 || 4 | TABLE ACCESS FULL| EMP | 14 | 42 | 2 (0)| 00:00:01 || 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 2 (0)| 00:00:01 |-----------------------------------------------------------------------------SQL> SELECT /*+ MERGE(v) */2 e1.ename, v.emp_by_dept3 FROM scott.emp e1,4 (SELECT deptno, count(*) as emp_by_dept5 FROM scott.emp e26 GROUP BY deptno) v7 WHERE e1.deptno = v.deptno8 /----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 30 | 720 | 6 (34)| 00:00:01 | --операция VIEW исключена| 1 | HASH GROUP BY | | 30 | 720 | 6 (34)| 00:00:01 ||* 2 | HASH JOIN | | 65 | 1560 | 5 (20)| 00:00:01 || 3 | TABLE ACCESS FULL| EMP | 14 | 294 | 2 (0)| 00:00:01 || 4 | TABLE ACCESS FULL| EMP | 14 | 42 | 2 (0)| 00:00:01 |---------------------------------------------------------------------------- |
/*+ USE_CONCAT ([@query_block] [ OR_PREDICATES(1) [ PREDICATE_REORDERS(() ())] ] ) */
«… инструктирует оптимизатор преобразовывать дизъюнктивные запросы (OR-conditions in the WHERE clause) в объединение запросов с использованием оператора UNION ALL (CONCATENATION) [т.е. выполнять преобразование OR-Expansion]. В отсутствии подсказки трансформация выполняется на основе стоимостного подхода…»
11.2.@ SQL> select 2 * 3 FROM scott.emp e 4 WHERE e.mgr < 7700 5 OR e.empno > 7700 6 /---------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|---------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 380 | 7 (0)| -- на основе стоимости UNION ALL не выбирается|* 1 | TABLE ACCESS FULL| EMP | 10 | 380 | 7 (0)|---------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("E"."MGR"7700) |
В виде, указанном в документации /*+ USE_CONCAT ([@query_block]) */, подсказка может не работать:
11.2.@ SQL> select --+ QB_NAME(qb) USE_CONCAT(@qb) 2 * 3 FROM scott.emp e 4 WHERE e.mgr < 7700 5 OR e.empno > 7700 6 /---------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|---------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 380 | 7 (0)||* 1 | TABLE ACCESS FULL| EMP | 10 | 380 | 7 (0)|--------------------------------------------------------------- |
Корректно отрабатывает с параметром OR_PREDICATES(1) на версиях >= 11.2
11.2.@ SQL> select --+ USE_CONCAT(OR_PREDICATES(1)) 2 * 3 FROM scott.emp e 4 WHERE e.mgr < 7700 5 OR e.empno > 7700 6 /----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 380 | 14 (0)|| 1 | CONCATENATION | | | | | -- в соответствии с хинтом выбран UNION ALL| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 228 | 7 (0)||* 3 | INDEX RANGE SCAN | SYS_C0093796 | 6 | | 1 (0)||* 4 | TABLE ACCESS FULL | EMP | 4 | 152 | 7 (0)|----------------------------------------------------------------------------------Outline Data------------- /*+... USE_CONCAT(@"S1" OR_PREDICATES(1))... */Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("E"."EMPNO">7700) 4 - filter("E"."MGR"7700)) |
В данных Outline можно найти другой параметр подсказки PREDICATE_REORDERS:
11.2.0.3.ORCL112@SCOTT SQL> create index IDX_MGR on scott.EMP (MGR, 0);Index created.SQL> select /*+ qb_name(S1)*/ 2 * 3 FROM scott.emp e 4 WHERE e.empno = 7369 5 OR e.mgr is NULL 6 / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17.12.80 800 20 7839 KING PRESIDENT 17.11.81 5000 10SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','','all allstats advanced -projection last'));-----------------------------------------------------------------------------------------| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 3 (100)| || 1 | CONCATENATION | | | | | || 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| ||* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 ||* 5 | INDEX RANGE SCAN | IDX_MGR | 1 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - S1 2 - S1_1 / E@S1 3 - S1_1 / E@S1 4 - S1_2 / E@S1_2 5 - S1_2 / E@S1_2Outline Data------------- /*+ BEGIN_OUTLINE_DATA... USE_CONCAT(@"S1" 8 OR_PREDICATES(1) PREDICATE_REORDERS((3 2) (2 3))) -- ***... END_OUTLINE_DATA */ |
, позволяющий менять порядок выполнения конкатенации:
SQL> select --+ qb_name(S1) USE_CONCAT(@"S1" 8 OR_PREDICATES(1) PREDICATE_REORDERS((1 2) (2 1))) -- с параметрами 2 * 3 FROM scott.emp e 4 WHERE e.empno = 7369 5 OR e.mgr is NULL 6 /----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 76 | 3 (0)| 00:00:01 || 1 | CONCATENATION | | | | | || 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | IDX_MGR | 1 | | 1 (0)| 00:00:01 ||* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 ||* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |----------------------------------------------------------------------------------------SQL> select --+ qb_name(S1) USE_CONCAT(@"S1" 8 OR_PREDICATES(1) PREDICATE_REORDERS()) -- без параметров 2 * 3 FROM scott.emp e 4 WHERE e.empno = 7369 5 OR e.mgr is NULL 6 /----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 76 | 3 (0)| 00:00:01 || 1 | CONCATENATION | | | | | || 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | IDX_MGR | 1 | | 1 (0)| 00:00:01 ||* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 ||* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |---------------------------------------------------------------------------------------- |
/*+ NO_EXPAND ( [@query_block] ) */
Подсказка противоположная хинту USE_CONCAT: сигнализирует оптимизатору не использовать преобразование OR-expansion для запросов, содержащих дизъюнктивные условия OR или IN-lists в секции WHERE, несмотря на стоимость:
SQL> create index SCOTT.IDX_MGR on scott.EMP (MGR, 0) tablespace USERS;Index created.SQL> select * 2 FROM scott.emp e 3 WHERE e.empno = 7369 4 OR e.mgr is NULL 5 /-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 76 | 3 (0)|| 1 | CONCATENATION | | | | | -- по умолчанию выполняется OR-Expansion| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)||* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)||* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)||* 5 | INDEX RANGE SCAN | IDX_MGR | 1 | | 1 (0)|-----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("E"."EMPNO"=7369) 4 - filter(LNNVL("E"."EMPNO"=7369)) 5 - access("E"."MGR" IS NULL)Statistics----------------------------------------------------------... 4 consistent gets... 2 rows processedSQL> select --+ NO_EXPAND 2 * 3 FROM scott.emp e 4 WHERE e.empno = 7369 5 OR e.mgr is NULL 6 /--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 76 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| EMP | 2 | 76 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("E"."MGR" IS NULL OR "E"."EMPNO"=7369)Statistics----------------------------------------------------------... 7 consistent gets |
/*+ PUSH_PRED ( [@query_block] [tablespec] ) */
/*+ NO_PUSH_PRED ( [@query_block] [tablespec] ) */
указание использовать / не использовать CBQT Join Predicate Push-Down с обзором / таблицей, указанными в параметрах подсказки, например:
11.2.0.3.@SCOTT SQL> -- без хинта илиSQL> select --+ NO_PUSH_PRED(d) 2 * from emp e, 3 (select * from dept 4 union 5 select * from dept) d 6 where d.deptno = e.deptno 7 and e.job = 'SALESMAN' 8 /------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 7 | 476 | 12 (25)| 00:00:01 ||* 1 | HASH JOIN | | 7 | 476 | 12 (25)| 00:00:01 | -- JPPD не используется|* 2 | TABLE ACCESS FULL | EMP | 3 | 114 | 3 (0)| 00:00:01 || 3 | VIEW | | 8 | 240 | 8 (25)| 00:00:01 || 4 | SORT UNIQUE | | 8 | 160 | 8 (63)| 00:00:01 || 5 | UNION-ALL | | | | | || 6 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 || 7 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("D"."DEPTNO"="E"."DEPTNO") 2 - filter("E"."JOB"='SALESMAN')SQL> select --+ PUSH_PRED(d) 2 * from emp e, 3 (select * from dept 4 union 5 select * from dept) d 6 where d.deptno = e.deptno 7 and e.job = 'SALESMAN' 8 /-------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 7 | 476 | 15 (40)| 00:00:01 || 1 | NESTED LOOPS | | 7 | 476 | 15 (40)| 00:00:01 ||* 2 | TABLE ACCESS FULL | EMP | 3 | 114 | 3 (0)| 00:00:01 || 3 | VIEW | | 1 | 30 | 4 (50)| 00:00:01 || 4 | SORT UNIQUE | | 2 | 40 | 4 (75)| 00:00:01 || 5 | UNION ALL PUSHED PREDICATE | | | | | | -- JPPD с бессмысленным UNION ALL| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 ||* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 || 8 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 ||* 9 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("E"."JOB"='SALESMAN') 7 - access("DEPT"."DEPTNO"="E"."DEPTNO") 9 - access("DEPT"."DEPTNO"="E"."DEPTNO") |
/*+ FACTORIZE_JOIN( @query_set [tablespec@query_block1 tablespec@query_block2 [...]])*/
/*+ NO_FACTORIZE_JOIN( @query_set )*/
Начиная с 11.2, для управления операцией Join Factorization
Подсказки, связанные с генерацией курсоров
/*+ CURSOR_SHARING_EXACT */
«При указании этой подсказки, Oracle выполняет запрос без попыток заменить текстовые значения (literals) связанными переменными (bind variables)» Несмотря на значения CURSOR_SHARING = SIMILAR | FORCE , как минимум, для каждого нового набора literals будет выполняться разбор SQL (hard parse) — может быть приемлемым решением при нечастом выполнении сложных отчётных запросов
/*+ BIND_AWARE*/
Начиная с 11.1.0.7 отключает Adaptive Cursor Sharing (фазу мониторинга для оценки необходимости последующего применения ECS), форсируя применение Extended Cursor Sharing для курсоров с отличающимися наборами связанных переменных в случаях, когда ECS не срабатывает автоматически, например, при отсутствии гистограмм:
11.2.0.3.@ SQL> select column_name, histogram from user_tab_col_statistics where table_name = 'EMP';COLUMN_NAME HISTOGRAM------------------------------ ---------------EMPNO NONEENAME NONEJOB NONEMGR NONEHIREDATE NONESAL NONECOMM NONEDEPTNO NONE8 rows selected.SQL> var N1 number;SQL> exec :N1 := 100;PL/SQL procedure successfully completed.SQL> select /*+ BIND_AWARE */ sum(sal) from emp where deptno < :N1; SUM(SAL)---------- 290251 row selected.SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT-------------------------------------SQL_ID 6arhfvzs91gv8, child number 0-------------------------------------...SQL> exec :N1 := 1;PL/SQL procedure successfully completed.SQL> select /*+ BIND_AWARE */ sum(sal) from emp where deptno < :N1;...1 row selected.SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor()); -- при повторном выполнении запроса с отличными значениями переменнойPLAN_TABLE_OUTPUT-------------------------------------SQL_ID 6arhfvzs91gv8, child number 1 -- сгенерирован новый курсор-------------------------------------...SQL> @shared_cu 6arhfvzs91gv8SQL_ID CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS BIND_EQ_FAILURE REASON------------- ------ ------------------ ---------------- ---------------- --------------------6arhfvzs91gv8 0 N N N Bind mismatch(33) |6arhfvzs91gv8 1 N N Y |
/*+ NO_BIND_AWARE*/
Отключает применение технологии Extended Cursor Sharing (и, как следствие, Adaptive Cursor Sharing за отсутствием необходимости) на уровне запроса
Прочее
/*+ 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
/*+ GATHER_PLAN_STATISTICS */
Используется для сбора [расширенной] статистики выполнения запроса в соответствии с планом выполнения (или просто статистики плана выполнения запроса). Действует аналогично установке значения параметра statistics_level = ALL (или _rowsource_execution_statistics = TRUE) на уровне сессии или системы. Данные по статистике плана выполнения при этом сохраняется в обзоре v$sql_plan_statistics_all
Пример применения:
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')*/...
/*+ OPTIMIZER_FEATURES_ENABLE(‘NN.N.N.N’) */
подсказка для уточнения/ограничения действия опций оптимизатора (optimizer features) в зависимости от версии Oracle на уровне запроса
полезна при обновлениях для получения ожидаемого плана выполнения запроса
значения версии (‘NN.N.N.N’) соответствуют значениям параметра OPTIMIZER_FEATURES_ENABLE
Пример:
11.2.0.1.ORCL112@SCOTT SQL> select --+ OPTIMIZER_FEATURES_ENABLE('9.2.0') 2 * 3 from emp e 4 natural join dept d 5 order by e.ename 6 /------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost |------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 826 | 15 || 1 | SORT ORDER BY | | 14 | 826 | 15 ||* 2 | HASH JOIN | | 14 | 826 | 5 | -- используется HASH JOIN| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 || 4 | TABLE ACCESS FULL| EMP | 14 | 546 | 2 |------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("E"."DEPTNO"="D"."DEPTNO")Note----- - cpu costing is off (consider enabling it) -- ресурс ЦПУ не учитывается при расчёте стоимостиSQL> select --+ OPTIMIZER_FEATURES_ENABLE('11.2.0.1') 2 * 3 from emp e 4 natural join dept d 5 order by e.ename 6 /-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 826 | 7 (29)| 00:00:01 || 1 | SORT ORDER BY | | 14 | 826 | 7 (29)| 00:00:01 || 2 | MERGE JOIN | | 14 | 826 | 6 (17)| 00:00:01 | -- для 11.2 с учётом стоимости ЦПУ выбирается MERGE JOIN| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 || 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 ||* 5 | SORT JOIN | | 14 | 546 | 4 (25)| 00:00:01 || 6 | TABLE ACCESS FULL | EMP | 14 | 546 | 3 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 5 - access("E"."DEPTNO"="D"."DEPTNO") filter("E"."DEPTNO"="D"."DEPTNO") |
/*+ 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 (режим прямой вставки) для загрузки больших объёмов данных, а не для вставки одиночных строк, т.к. в последнем случае не будет получено преимуществ
В версии 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, бд устанавливает исключительную блокировку [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
/*+ PARALLEL */
/*+ SHARED */
Синоним подсказки PARALLEL, обычно используемый Oracle для управления параллельным выполнением подзапроса на дальней стороне db link
Работает также локально:
SQL> SELECT /*+ SHARED (AUTO) OPT_ESTIMATE (TABLE "D" ROWS=2000.000000 ) */ * FROM dual d;D-X1 row selected.--------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |--------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 (100)| | | | || 1 | PX COORDINATOR | | | | | | | | || 2 | PX SEND QC (RANDOM)| :TQ10000 | 2000 | 4000 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) || 3 | PX BLOCK ITERATOR | | 2000 | 4000 | 2 (0)| 00:00:01 | Q1,00 | PCWC | ||* 4 | TABLE ACCESS FULL| DUAL | 2000 | 4000 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |-------------------------------------------------------------------------------------------------------------- |
/*+ RESULT_CACHE*/
/*+ NO_RESULT_CACHE*/
Ручное управление кэшированием результатов запросов (или частей) на стороне сервера.
Примеры использования для inline view:
SELECT *FROM ( SELECT /*+ RESULT_CACHE */ department_id, manager_id, count(*) countFROM hr.employeesGROUP BY department_id, manager_id ) view1WHERE department_id = 30; |
для WITH view:
WITH view2 AS( SELECT /*+ RESULT_CACHE */ department_id, manager_id, count(*) countFROM hr.employeesGROUP BY department_id, manager_id )SELECT *FROM view2WHERE count BETWEEN 1 and 5; |
Использовании кэша результатов подзапросов отключает операции оптимизации (трансформации, merging) между внешними и внутренними блоками запроса
Кэширование результатов запросов логично отменяет действие подсказки DRIVING_SITE
Начиная с версии 12.1.0.2 поддерживется параметр /*+ RESULT_CACHE(snapshot=10) */, где параметр определяет время жизни кэша, в течение которого статус меняется Published->Expired
/*+ STATEMENT_QUEUING*/
/*+ NO_STATEMENT_QUEUING*/
Подсказки для управления очерёдностью и способом параллельного выполнения запросов (parallel statement queuing)
Пример использования:
11.2.@ SQL> select name, isdefault, value 2 from v$ses_optimizer_env 3 where sid = sys_context('USERENV', 'SID') 4 and name like '%parallel%' 5 /NAME ISD VALUE---------------------------------------- --- -------parallel_execution_enabled YES trueparallel_threads_per_cpu YES 2parallel_query_mode YES enabledparallel_degree_policy YES manual -- параметры оптимизатора в ручном режимеparallel_degree YES 0parallel_min_time_threshold YES 10parallel_query_default_dop YES 0parallel_degree_limit YES 65535parallel_max_degree YES 48parallel_autodop YES 0SQL> @param parallel%serversNAME VALUE DSC------------------------ ------ ------------------------------------------------------parallel_max_servers 50 maximum parallel query servers per instanceparallel_servers_target 50 instance target in terms of number of parallel servers -- минимальное кол-во доступных PX процессов для немедленного запуска параллельного выполненияSQL> alter session force parallel query parallel 30; -- стимулируем DOPSession altered.SQL> select name, isdefault, value 2 from v$ses_optimizer_env 3 where sid = sys_context('USERENV', 'SID') 4 and name in ('parallel_query_forced_dop', 'parallel_query_mode') 5 /NAME ISD VALUE---------------------------------------- --- --------parallel_query_forced_dop NO 30 -- изменившиеся параметрыparallel_query_mode NO forcedSQL> SELECT ... -- выполняем ресурсоёмкий параллельный запросSQL> select status, 2 sql_id, 3 px_maxdop, 4 px_servers_requested, 5 px_servers_allocated 6 from v$sql_monitor m 7 where sql_id = '0f4bz4kpwntas' 8 and px_maxdop is not null 9 /STATUS SQL_ID PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED------------------- ------------- ---------- -------------------- --------------------EXECUTING 0f4bz4kpwntas 25 60 50 -- , запросил 60=30x2 [parallel sets], получил все 50 доступных (parallel_max_servers) процессовSQL> SELECT/*+ NO_STATEMENT_QUEUING */ ... -- одновременно запустим тот же ресурсоёмкий параллельный запросSQL> select ss.status, 2 ss.sql_id, 3 ss.sql_child_number, 4 sa.plan_hash_value, 5 px_maxdop, 6 px_servers_requested, 7 px_servers_allocated, 8 event, 9 seconds_in_wait 10 from v$session ss, v$sqlarea sa, v$sql_monitor sm 11 where ss.sql_id = sa.sql_id 12 and sa.sql_text like 'SELECT/*+ NO_STATEMENT_QUEUING */%' 13 and ss.sql_id = sm.sql_id 14 and ss.sql_exec_id = sm.sql_exec_id 15 /STATUS SQL_ID SQL_CHILD_NUMBER PLAN_HASH_VALUE PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED EVENT SECONDS_IN_WAIT-------- ------------- ---------------- --------------- ---------- -------------------- -------------------- --------------------- ---------------ACTIVE 3yyjf9pu39q5f 0 3510803162 8 0 db file parallel read 0SQL> -- с подсказкой NO_STATEMENT_QUEUING или без подсказки запрос будет выполняться непараллельно (PX_SERVERS_ALLOCATED=0), используя параллельный план выполнения (PX_SERVERS_REQUESTED=8)SQL> -- Если тот же запрос запустить одновременно с подсказкой STATEMENT_QUEUING:SQL> SELECT/*+ STATEMENT_QUEUING */ ...SQL> select ss.status, 2 ss.sql_id, 3 ss.sql_child_number, 4 sa.plan_hash_value, 5 px_maxdop, 6 px_servers_requested, 7 px_servers_allocated, 8 event, 9 seconds_in_wait 10 from v$session ss, v$sqlarea sa, v$sql_monitor sm 11 where ss.sql_id = sa.sql_id 12 and sa.sql_text like 'SELECT/*+ STATEMENT_QUEUING */%' 13 and ss.sql_id = sm.sql_id 14 and ss.sql_exec_id = sm.sql_exec_id 15 /STATUS SQL_ID SQL_CHILD_NUMBER PLAN_HASH_VALUE PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED EVENT SECONDS_IN_WAIT-------- ------------- ---------------- --------------- ---------- -------------------- -------------------- ---------------- ---------------ACTIVE 7r8fah3w0mt98 0 3510803162 resmgr:pq queued 18SQL> -- запрос, не начиная выполнения, будет ждать освобождения требуемого кол-ва PX процессов на событии resmgr:pq queuedSQL> -- Соответствующий подсказке скрытый параметр при PARALLEL_DEGREE_POLICY = MANUAL по умолчанию запрещает очерёдность запросов:SQL> @param_ _parallel_statement_queuingNAME VALUE IS_DEF IS_MOD IS_ADJ DSC------------------------------- ------ -------- ------- ------- ----------------------------------_parallel_statement_queuing FALSE TRUE FALSE FALSE parallel statement queuing enabledSQL> -- При повторении теста с изменённым параметром:SQL> alter session set "_parallel_statement_queuing" = TRUE;Session altered.SQL> SELECT ... -- тот же параллельный запрос, запущенный в момент когда все параллельные процессы занятыSQL> select sql_exec_id, 2 status, 3 sql_id, 4 sql_plan_hash_value, 5 px_maxdop, 6 px_servers_requested, 7 px_servers_allocated 8 from v$sql_monitor m 9 where sql_id = '0f4bz4kpwntas' 10 and process_name not like 'p%' 11 /SQL_EXEC_ID STATUS SQL_ID SQL_PLAN_HASH_VALUE PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED----------- ------------------- ------------- ------------------- ---------- -------------------- -------------------- 16777221 EXECUTING 0f4bz4kpwntas 2021910991 25 60 50 -- выполняющийся запрос 16777222 QUEUED 0f4bz4kpwntas 3510803162 -- ожидающий освобождения PXSQL> select status, 2 state, 3 event, 4 seconds_in_wait 5 from v$session 6 where sql_id = '0f4bz4kpwntas' 7 and sql_exec_id = 16777222 8 /STATUS STATE EVENT SECONDS_IN_WAIT-------- ------------------- ----------------- ---------------ACTIVE WAITING resmgr:pq queued 834-- второй одновременно запущенный параллельный запрос ожидает освобождения PX процессов точно так же, как при использовании подсказки /*+ STATEMENT_QUEUING */-- |
Использование подсказки /*+ NO_STATEMENT_QUEUING */ позволяет аналогично отключить автоматическую очерёдность выполнения запросов при PARALLEL_DEGREE_POLICY = AUTO
Источник: https://iusoltsev.wordpress.com/?s=hints
2 комментария :
Неплохая статья, не хватает ссылки на источник, не так ли, Aleksey Panin ?)
Пожалуй, вы правы. Добавил.
Отправить комментарий