Oracle mechanics

Подсказки (Oracle Hints)

SQL Hints в документации Oracle

Замечания

QREF: SQL Statement HINTS [ID 29236.1]:

  • «О синтаксисе: /*+ 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с указанием несуществующего индекса будет проигнорирована без сообщений об ошибках…»

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

Подсказки

— общие цели оптимизатора

/*+ RULE */
/*+ ALL_ROWS */
/*+ FIRST_ROWS */
/*+ FIRST_ROWS(n) */

— порядок доступа

/*+ LEADING */
/*+ ORDERED */

— методы соединения

/*+ USE_HASH */
/*+ USE_NL */
/*+ USE_MERGE */
/*+ USE_HASH_AGGREGATION */
/*+ NATIVE_FULL_OUTER_JOIN */
/*+ INDEX_JOIN */
/*+ INDEX_COMBINE */
/*+ NUM_INDEX_KEYS */

— способы выполнения [под]запроса

/*+ DRIVING_SITE */
/*+ MATERIALIZE */
/*+ INLINE */
/*+ PRECOMPUTE_SUBQUERY */

— статистика объектов

/*+ DYNAMIC_SAMPLING */
/*+ DYNAMIC_SAMPLING_EST_CDN */
/*+ CARDINALITY */
/*+ OPT_ESTIMATE */

— трансформации [под]запросов

/*+ 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 */
/*+ OR_EXPAND */

— использование курсоров

/*+ CURSOR_SHARING_EXACT */
/*+ BIND_AWARE */ /*+ NO_BIND_AWARE */

— параллельное выполнение

/*+ PARALLEL */
/*+ SHARED */
/*+ STATEMENT_QUEUING */ /*+ NO_STATEMENT_QUEUING */

— прочие

/*+ QB_NAME */
/*+ 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, если:

  • кроме RULE в запросе используются другие подсказки
  • запрос использует партицированные или таблицы, организованные в виде индекса (IOT), или материализованные представления (mview)
  • в запросе используются кляузы SAMPLE, SPREADSHEET, конструкции GROUPING SETS
  • в запросе используются ANSI left|full outer join
  • запрос выполняется параллельно
  • используется Flashback cursor (as of [scn|timestamp])

В 11.2 при использовании RBO может формироваться трейс оптимизатора и использоваться некоторые преобразования запросов за исключением Cost-Based Query Transformation (что логично:) — см. замечания в Индекс в статусе unusable, подсказка INDEX и обновлённый RBO в Oracle 11.2

/*+ 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

Не совсем понятная фраза в документации: «Оптимизатор игнорирует эту подсказку в SQL предложениях DELETE и UPDATE и в запросах SELECT, включающих блокирующие операции, такие как сортировки и группировки. Такие SQL предложения не могут быть оптимизированы с целью наименьшего времени отклика (best response time), поскольку Oracle должен обработать все строки запроса до того, как вернуть первую строку результата. При указании этой подсказки запросы указанного типа оптимизируются с целью лучшего времени получения всех строк запроса с минимальным расходом ресурсов (best throughput, как при использовании подсказки ALL_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_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

SQL> create table T1 as select * from dba_objects
2  /

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 BY
SQL> select /*+ ALL_ROWS*/
2   *
3    from t1
4   where object_name like '%#%'
5     and object_id > 1
6   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 NULL
AND "OBJECT_ID">1)

SQL> select /*+ FIRST_ROWS*/
2   *
3    from t1
4   where object_name like '%#%'
5     and object_id > 1
6   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 t1
4   where object_name like '%#%'
5     and object_id > 1
6   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 BY
SQL> select /*+ ALL_ROWS*/
2   object_id, count(*)
3    from t1
4   where object_name like '%#%'
5     and object_id > 1
6   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*/
2   object_id, count(*)
3    from t1
4   where object_name like '%#%'
5     and object_id > 1
6   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 t1
4   where object_name like '%#%'
5     and object_id > 1
6   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 on
SQL> declare
2    low_value_raw  RAW(2000);
3    high_value_raw RAW(2000);
4    low_value_num  number;
5    high_value_num number;
6  begin
7    select low_value, high_value
8      into low_value_raw, high_value_raw
9      from user_tab_col_statistics
10     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 = 2
High 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_statistics
4   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]) */

Операции CBO: Nested Loop Join

/*+ USE_MERGE( [@query_block] [tablespec] … ) */
/*+ NO_USE_MERGE( [@query_block] [tablespec] … ) */

Операции CBO: Sort Merge Join

/*+ 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

/*+ DRIVING_SITE ( [@query_block] [tablespec] ) */

«… подсказка указывает оптимизатору выполнять запрос на сайте [сайте таблицы, указанной в хинте], отличном от выбранного бд [Oracle]. Хинт полезен для оптимизации выполнения распределённых запросов»

В зависимости от подсказки запрос выполняется полностью на удалённом сайте — при указании удалённой таблицы в хинте DRIVING_SITE(e):

SQL> select--+ DRIVING_SITE(e)
2   count(*)
3    from emp@loopback_dblink e,
4           dual
5  /

Execution Plan
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|        |     1 |     3   (0)| 00:00:01 |        |      | -- ***
|   1 |  SORT AGGREGATE        |        |     1 |            |          |        |      |
|   2 |   MERGE JOIN CARTESIAN |        |    14 |     3   (0)| 00:00:01 |        |      |
|   3 |    REMOTE              | DUAL   |     1 |     2   (0)| 00:00:01 |      ! | R->S |
|   4 |    BUFFER SORT         |        |    14 |     1   (0)| 00:00:01 |        |      |
|   5 |     INDEX FULL SCAN    | PK_EMP |    14 |     1   (0)| 00:00:01 | ORCL1~ |      |
-----------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT 0 FROM "SYS"."DUAL" "A1" (accessing '!' )

Note
-----
   - fully remote statement -- ***

, либо локально, с копированием данных из удалённой таблицы на локальный инстанс — при использовании подсказки DRIVING_SITE(dual), указывающей на локальную таблицу dual. В плане указывается операция REMOTE_TO_SERIAL (R->S):

SQL> select--+ DRIVING_SITE(dual)
2   count(*)
3    from emp@loopback_dblink e,
4           dual
5  /

Execution Plan
---------------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     3   (0)| 00:00:01 |        |      |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |        |      |
|   2 |   NESTED LOOPS   |      |    14 |     3   (0)| 00:00:01 |        |      |
|   3 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |        |      |
|   4 |    REMOTE        | EMP  |    14 |     1   (0)| 00:00:01 | LOOPB~ | R->S | -- ***
---------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT 0 FROM "EMP" "E" (accessing 'LOOPBACK_DBLINK.LOCAL_DOMAIN.COM' )

Limitations of DRIVING_SITE Hint (Doc ID 825677.1):

Версии: 9.2.0.1 — 11.2.0.2 … распределённый DML должен выполняться в бд, где расположена целевая таблица DML. Подсказка DRIVING_SITE не меняет этого поведения

DRIVING_SITE предполагает управление выполнением (mapping) всего курсора (а не отдельных подзапросов)… [однако можно перенести выполнение части курсора / подзапроса в удалённый обзор]

Подсказка DRIVING_SITE предназначена для оптимизации запросов (SELECT) и не предназначена для DML или DDL

/*+ MATERIALIZE */

www.club-oracle.com: «Подсказка Materialize представляет собой технику оптимизации запросов и может быть особенно полезна для больших наборов данных. Материализация подзапроса означает создание определённого типа динамической временной таблицы (dynamic temporary table) для использования во время выполнения запроса»

Tom Kyte: «… подсказка указывает оптимизатору Oracle «материализовать» блок запроса (query block), в котором присутствует подсказка, в виде временной таблицы [только] на время выполнения запроса .., подсказка поддерживается даже в текущей версии 11g»

SQL> with emp_sq as
2   (select  * from emp where comm > 0)
3  select e.ename, d.loc from emp_sq e, dept d where d.deptno = e.deptno
4  /

-----------------------------------------------------------------------------
| 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 processed

SQL> with emp_sq as
2   (select /*+ MATERIALIZE */ * from emp where comm > 0)
3  select e.ename, d.loc from emp_sq e, dept d where d.deptno = e.deptno
4  /

------------------------------------------------------------------------------------------------
| 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_subquery

NAME            VALUE      IS_DEF   DSC
--------------- ---------- -------- ----------------------------
_with_subquery  OPTIMIZER  TRUE     WITH subquery transformation

11.2.0.1.SQL> @pvalid_ _with_subquery

PARAMETER                                          VALUE                          DEFAULT
-------------------------------------------------- ------------------------------ -------
_with_subquery                                     INLINE
                                                   OPTIMIZER                      DEFAULT
                                                   MATERIALIZE

_with_subquery parameter – controlling subquery factoring

Материализация XMLTYPE запросов

/*+ 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.0
INLINE                         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 as
2   (select deptno, count(*) count_by_dept from emp group by deptno)
3  select e.count_by_dept, d.loc
4    from emp_sq e, dept d
5   where d.deptno = e.deptno
6     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 processed

SQL> with emp_sq as
2   (select /*+ INLINE */ deptno, count(*) count_by_dept from emp group by deptno)
3  select e.count_by_dept, d.loc
4    from emp_sq e, dept d
5   where d.deptno = e.deptno
6     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] при превышении лимита количества дочерних курсоров

По крайней мере с 11.2 подсказка допускает указание блока запроса в форме /*+ PRECOMPUTE_SUBQUERY(@QB_NAME)*/

Подсказки, динамически влияющие на статистику оптимизатора

/*+ 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] ) */

AskTom:

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 on
SQL> 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.04

Execution 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 ) */

Превосходное неофициальное описание от Christo Kutrovsky

Предназначен для влияния на план выполнения через корректировку оценки кол-ва строк (cardinality), получаемых в результате операций доступа к данным и, как следствие, стоимости плана выполнения

Часто используется при создании SQL Profile, является развитием / замещением подсказки CARDINALITY

Параметры:

  • query block
  • operation_type: QUERY_BLOCK / TABLE / INDEX_FILTER / INDEX_SCAN | INDEX_SKIP_SCAN / JOIN
  • identifier: TABLES / INDEX / JOINS
  • adjustment: ROWS / SCALE_ROWS / MIN / MAX

Управления преобразованиями, выполняемыми в процессе оптимизации запросов

/*+ 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) выполняются на последнем шаге плана выполнения. Раннее выполнение подзапроса может значительно ускорить производительность, если подзапрос относительно лёгок и может значительно уменьшить количество обрабатываемых строк»

По мнению Дж.Льюиса, начиная с Oracle 10g подсказка может быть успешно использована в следующих видах:

  • на примере запроса, который без подсказок сначала соединяет таблицы головного запроса (HASH JOIN), и только затем обращается к таблице DEPT из подзапроса EXISTS (HASH JOIN SEMI):
SQL> select
2   ename
3    from scott.emp, scott.dept
4   where emp.sal between 1001 and 2200
5     and dept.deptno = emp.deptno
6     and dept.dname >= 'C'
7     and exists (select
8           null
9            from scott.dept
10           where deptno = emp.deptno
11             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   ename
3    from scott.emp, scott.dept
4   where emp.sal between 1001 and 2200
5     and dept.deptno = emp.deptno
6     and dept.dname >= 'C'
7     and exists (select /*+ qb_name(subq1) no_unnest*/
8           null
9            from scott.dept
10           where deptno = emp.deptno
11             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> select
2   ename
3    from scott.emp, scott.dept
4   where emp.sal between 1001 and 2200
5     and dept.deptno = emp.deptno
6     and dept.dname >= 'C'
7     and exists (select /*+ no_unnest push_subq*/
8           null
9            from scott.dept
10           where deptno = emp.deptno
11             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   ename
3    from scott.emp, scott.dept
4   where emp.sal between 1001 and 2200
5     and dept.deptno = emp.deptno
6     and dept.dname >= 'C'
7     and exists (select /*+ no_unnest */
8           null
9            from scott.dept
10           where deptno = emp.deptno
11             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 outer
3   where outer.sal >
4               (select
5                 avg(inner.sal)
6                  from scott.emp inner
7                 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 outer
3   where outer.sal >
4               (select/*+ NO_UNNEST*/
5                 avg(inner.sal)
6                  from scott.emp inner
7                 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 outer
4   where outer.sal >
5               (select/*+ QB_NAME(subq1)*/
6                 avg(inner.sal)
7                  from scott.emp inner
8                 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.1
NO_ELIMINATE_OBY  QKSFM_OBYE   ELIMINATE_OBY     10.2.0.1

Отличный пример товарища wurdu с форума sql.ru

SQL> select * from(
2        select * from (
3              select 3, 4 from dual
4              union all
5              select 2, 5 from dual
6              union all
7              select 1, 4 from dual) a
8                order by 1
9                )
10    order by 2
11  /

         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 dual
5              union all
6              select 2, 5 from dual
7              union all
8              select 1, 4 from dual) a
9                order by 1
10                )
11    order by 2
12  /

         3          4
---------- ----------
         1          4                -- другой рез-т за счёт неисключённого ORDER BY
         3          4
         2          5

SQL> select/*+ no_eliminate_oby(@subq1) */
2   * from(
3        select/*+ qb_name(subq1)*/
4         * from (
5              select 3, 4 from dual
6              union all
7              select 2, 5 from dual
8              union all
9              select 1, 4 from dual) a
10                order by 1)
11    order by 2
12  /

-------------------------------------------------------------------------
| 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> SELECT
2   e1.ename, v.emp_by_dept
3    FROM scott.emp e1,
4         (SELECT deptno, count(*) as emp_by_dept
5            FROM scott.emp e2
6           GROUP BY deptno) v
7   WHERE e1.deptno = v.deptno
8  /

SQL> SELECT /*+ NO_MERGE(v) */
2   e1.ename, v.emp_by_dept
3    FROM scott.emp e1,
4         (SELECT deptno, count(*) as emp_by_dept
5            FROM scott.emp e2
6           GROUP BY deptno) v
7   WHERE e1.deptno = v.deptno
8  /

-----------------------------------------------------------------------------
| 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_dept
3    FROM scott.emp e1,
4         (SELECT deptno, count(*) as emp_by_dept
5            FROM scott.emp e2
6           GROUP BY deptno) v
7   WHERE e1.deptno = v.deptno
8  /

----------------------------------------------------------------------------
| 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                    10

SQL> 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_2

Outline 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 |
----------------------------------------------------------------------------------------

Oracle 11g: стоимость операции FILTER и OR-Expansion

USE_CONCAT hint on different versions [ID 259741.1]

Кроме стимулирования использования операции CONCATENATION при явном использовании OR в условиях WHERE запроса, подсказка может быть успешно использована для директивного запрещения/отключения операции INLIST ITERATOR в случае индексного доступа — Конкатенация против INLIST ITERATOR

/*+ 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 processed

SQL> 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")

Уникальные индексы для Join Predicate Push-Down

/*+ FACTORIZE_JOIN( @query_set [tablespec@query_block1 tablespec@query_block2 […]])*/
/*+ NO_FACTORIZE_JOIN( @query_set )*/

Начиная с 11.2, для управления операцией Join Factorization

/*+ OR_EXPAND([@query_block] tablespec Column1 [Column2 … ]) */

Может быть использован для инициирования OR EXPAND query transformation Query Transformation Hints

Подсказки, связанные с генерацией курсоров

/*+ 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                          NONE
ENAME                          NONE
JOB                            NONE
MGR                            NONE
HIREDATE                       NONE
SAL                            NONE
COMM                           NONE
DEPTNO                         NONE

8 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)
----------
     29025

1 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 6arhfvzs91gv8

SQL_ID         CHILD USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON
------------- ------ ------------------ ---------------- ---------------- --------------------
6arhfvzs91gv8      0 N                  N                N                Bind mismatch(33)  |
6arhfvzs91gv8      1 N                  N                Y

Bug 9532657

Элементы технологий Bind Aware Cursor Sharing / Cardinality Feedback

Oracle Optimizer Blog: How do I force a query to use bind-aware cursor sharing?

/*+ NO_BIND_AWARE*/

Отключает применение технологии Extended Cursor Sharing (и, как следствие, Adaptive Cursor Sharing за отсутствием необходимости) на уровне запроса

Прочее

/*+ 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

Methods for Obtaining a Formatted Explain Plan [ID 235530.1]

How to Display All Loaded Execution Plan for a specific sql_id [ID 465398.1]

Пример применения:

SQL> set linesize 150
SQL> set pagesize 2000
SQL> set serveroutput OFF
SQL> select /*+ gather_plan_statistics */ count(*) from emp;
SQL> select * from TABLE(dbms_xplan.display_cursor('','','ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  9jjm288hx7buz, child number 2
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from emp
Plan hash value: 2937609675
-------------------------------------------------------------------------------------------------------------
| Id | Operation        | Name   | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   |Buffers |
-------------------------------------------------------------------------------------------------------------
|   1|  SORT AGGREGATE  |        |      1 |      1 |            |          |      1 |00:00:00.01 |  1 |
|   2|   INDEX FULL SCAN| PK_EMP |      1 |     14 |     1   (0)| 00:00:01 |     14 |00:00:00.01 |  1 |
-------------------------------------------------------------------------------------------------------------
...

Команда set serveroutput OFF выполняется на случай, если serveroutput был включен в сессии, т.к. в этом случае получим:

SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
SQL> select /*+ gather_plan_statistics */ count(*) from emp;
SQL> select * from TABLE(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
 Please verify value of SQL_ID and CHILD_NUMBER;
 It could also be that the plan is no longer in cursor cache (check v$sql_plan)

т.е. последним в сессии SQL*PLUS будет PL/SQL блок DBMS_OUTPUT, который и будет пытаться обработать функция dbms_xplan.display_cursor(»,»,’…’) при запуске с NULL-левыми параметрами sql_id и child_number.

В случае, если запрос с подсказкой GATHER_PLAN_STATISTICS выполнялся не последним, статистику плана выполнения можно получить запросом по обзору v$sql (предварительно снабдив запрос оригинальным комментарием MY_QUERY_TAG для облегчения дальнейшего поиска):

select t.* from v$sql s,
table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL ALLSTATS LAST')) t
where s.sql_text like '%select /*+ gather_plan_statistics MY_QUERY_TAG*/ count(*)%';

Описание третьего параметра (формат) и вывода функции dbms_xplan.display_cursor

/*+ OPT_PARAM(parameter_name [,] parameter_value) */

добавлен начиная с 10g R2, согласно документации 11g R2 «… позволяет установить инициализационные параметры [оптимизатора] на время выполнения запроса..:

OPTIMIZER_DYNAMIC_SAMPLING

OPTIMIZER_INDEX_CACHING

OPTIMIZER_INDEX_COST_ADJ

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 используется по умолчанию в режиме параллельной вставки [работает при выполнении след.условий:
  1. Oracle Enterprise Edition

  2. ALTER SESSION { ENABLE | FORCE } PARALLEL DML;

  3. установленный признак параллельности (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:

В течении 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]

Операции LOAD TABLE CONVENTIONAL / LOAD AS SELECT: direct-path и другие способы вставки в 11.2

/*+ APPEND_VALUES */

Подсказка APPEND_VALUES форсирует использование оптимизатором режима прямой вставки direct-path INSERT [только] в запросах вида INSERT INTO… VALUES, доступна с версии 11.2

/*+ PARALLEL */

Подсказка parallel

Подсказка PARALLEL в тексте обзора

/*+ SHARED */

Синоним подсказки PARALLEL, обычно используемый Oracle для управления параллельным выполнением подзапроса на дальней стороне db link

Работает также локально:

SQL> SELECT /*+ SHARED (AUTO) OPT_ESTIMATE (TABLE "D" ROWS=2000.000000 ) */ * FROM dual d;

D
-
X

1 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(*) count
FROM   hr.employees
GROUP BY department_id, manager_id ) view1
WHERE  department_id = 30;

для WITH view:

WITH view2 AS
( SELECT /*+ RESULT_CACHE */ department_id, manager_id, count(*) count
FROM hr.employees
GROUP BY department_id, manager_id )
SELECT *
FROM   view2
WHERE  count BETWEEN 1 and 5;

Использовании кэша результатов подзапросов отключает операции оптимизации (трансформации, merging) между внешними и внутренними блоками запроса

Кэширование результатов запросов логично отменяет действие подсказки DRIVING_SITE

Начиная с версии 12.1.0.2 поддерживется параметр /*+ RESULT_CACHE(snapshot=10) */, определяющий определяет время жизни кэша, по истечении которого статус меняется Published -> Expired и который добавлен, вероятно, для смягчения влияния Automatic Dynamic Statistics судя по активному применению именно в рекурсивно генерируемых запросах ADS:

12.1.0.2.@ SQL> select count(*)
  2    from gv$sqlarea
  3   where sql_text like
  4         'SELECT /* DS_SVC */ /*+ dynamic_sampling(%result_cache(snapshot=3600)%'
  5  /
 
  COUNT(*)
----------
      2230

Пример применения Result Cache на стороне сервера

/*+ 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 true
parallel_threads_per_cpu                 YES 2
parallel_query_mode                      YES enabled
parallel_degree_policy                   YES manual   -- параметры оптимизатора в ручном режиме
parallel_degree                          YES 0
parallel_min_time_threshold              YES 10
parallel_query_default_dop               YES 0
parallel_degree_limit                    YES 65535
parallel_max_degree                      YES 48
parallel_autodop                         YES 0

SQL> @param parallel%servers

NAME                     VALUE  DSC
------------------------ ------ ------------------------------------------------------
parallel_max_servers     50     maximum parallel query servers per instance
parallel_servers_target  50     instance target in terms of number of parallel servers -- минимальное кол-во доступных PX процессов для немедленного запуска параллельного выполнения

SQL> alter session force parallel query parallel 30; -- стимулируем DOP

Session 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  forced

SQL> 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               0

SQL> -- с подсказкой 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              18

SQL> --  запрос, не начиная выполнения, будет ждать освобождения требуемого кол-ва PX процессов на событии resmgr:pq queued

SQL> -- Соответствующий подсказке скрытый параметр при PARALLEL_DEGREE_POLICY = MANUAL по умолчанию запрещает очерёдность запросов:

SQL> @param_ _parallel_statement_queuing

NAME                            VALUE  IS_DEF   IS_MOD  IS_ADJ  DSC
------------------------------- ------ -------- ------- ------- ----------------------------------
_parallel_statement_queuing     FALSE  TRUE     FALSE   FALSE   parallel statement queuing enabled

SQL> -- При повторении теста с изменённым параметром:

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                                                      -- ожидающий освобождения PX

SQL> 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

TBD

11 комментариев »

  1. Щрифт для SQL-кода очень маленький. Совершенно нечитабельный. Чтобы там что-то разобрать, надо копировать его в текстовый редактор. Это так специально сделано, чтобы жизнь медом не казалась?

    комментарий от Вася — 28.04.2011 @ 14:12 | Ответить

  2. если шрифт очень маленький то удерживай ctrl скролл покрутите и будет вам щастье

    комментарий от Павел — 04.05.2011 @ 16:33 | Ответить

  3. Спасибо. Очень помогли подробные описания хинтов.

    комментарий от Аноним — 12.09.2012 @ 16:55 | Ответить

  4. Спасиба !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    комментарий от Петр — 21.08.2013 @ 17:39 | Ответить

  5. СПАСИБО БОЛЬШОЕ

    комментарий от Аноним — 07.05.2014 @ 15:48 | Ответить

  6. […] Подсказки (Oracle Hints) https://iusoltsev.wordpress.com/profile/individual-sql-and-cbo/cbo-hints/ […]

    Уведомление от Oracle mechanics | programmer's blog — 13.05.2014 @ 17:01 | Ответить

  7. Спасибо за проделанную работу Без лишней води и по существу

    комментарий от Андрей — 29.05.2014 @ 10:24 | Ответить

  8. Статья отличная, очень помогла в работе. Спасибо!

    комментарий от Anastasi Zhukova — 12.08.2014 @ 08:24 | Ответить

  9. Спасибо огромное!

    комментарий от Аноним — 19.08.2015 @ 22:05 | Ответить

  10. Игорь, спасибо большой за материал, вами проделана огромная работа!

    Есть вопрос. Пожалуйста, дополните статью, если вам известно как действовать в таких ситуациях.
    Вы пишите:
    «DRIVING_SITE предполагает управление выполнением (mapping) всего курсора (а не отдельных подзапросов)… [однако можно перенести выполнение части курсора / подзапроса в удалённый обзор]»
    Это факт. Никак не получается отправить подзапрос целиком на удалённый сервер. NO_MERGE не помогает, DISTINCT и GROUP BY в подзапросе отсутствуют. В итоге каждый шаг подзапроса целиком вытягивается с удалённого на локальный и только на локальном сервере происходит отсечение по access predicates.

    комментарий от Dmitry — 17.02.2016 @ 12:32 | Ответить


RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: