Oracle mechanics

31.05.2014

«Подсказка» BEGIN_OUTLINE_DATA

Filed under: hints,Oracle — Игорь Усольцев @ 00:43
Tags:

— с виду безобидная конструкция, обрамляющая (вместе с END_OUTLINE_DATA) список подсказок плана выполнения при выводе в формате:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format => '+outline'));

Интересно, что при применении в виде отдельного хинта/комментария в запросе конструкция /*+ BEGIN_OUTLINE_DATA */ сама по себе оказывает осмысленное влияние на оптимизатор версий 11.2/12.1

Например, стандартный запрос для демонстрации «новой» (для Oracle 11g) операции HASH JOIN ANTI NA:

11.2.0.3.ORCL112@SCOTT SQL> select count(*)
  2    from emp
  3   where mgr not in (select mgr from emp e)
  4  /

-----------------------------------------------------------------------------
| Id  | Operation           | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |        |       |     7 (100)|          |
|   1 |  SORT AGGREGATE     |      |      1 |     8 |            |          |
|*  2 |   HASH JOIN ANTI NA |      |      2 |    16 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |     14 |    56 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |     14 |    56 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

— после добавления такой подсказки выполняется с планом версии 10g:

SQL> select /*+ BEGIN_OUTLINE_DATA */
  2   count(*)
  3    from emp
  4   where mgr not in (select mgr from emp e)
  5  /

-----------------------------------------------------------------------------
| Id  | Operation           | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |        |       |    21 (100)|          |
|   1 |  SORT AGGREGATE     |      |      1 |     4 |            |          |
|*  2 |   FILTER            |      |        |       |            |          | -- характерная операция
|   3 |    TABLE ACCESS FULL| EMP  |     14 |    56 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP  |      2 |     8 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

по причине, заметной из вывода команды diff трейсов оптимизатора:

< SU:   Passed validity checks.                          -- запрос без хинта
< SU:   Transform ALL subquery to a null-aware antijoin.
---
> SU:     SU bypassed: Negative hint found.              -- запрос с /*+ BEGIN_OUTLINE_DATA */

— т.е. BEGIN_OUTLINE_DATA является (либо содержит) директиву, блокирующую основанную-на-стоимости-трансформацию (CBQT) Subquery Unnesting

Добавление стимулирующего Subquery Unnesting хинта UNNEST в любом (подходящем) месте запроса исправляет ситуацию:

SQL> select
  2    /*+
  3        BEGIN_OUTLINE_DATA
  4        UNNEST(@"SEL$2")                                  -- здесь
  5    */
  6   count(*)
  7    from emp
  8   where mgr not in (select mgr from emp e)
  9  /

SQL> select
  2    /*+
  3        BEGIN_OUTLINE_DATA
  4    */
  5   count(*)
  6    from emp
  7   where mgr not in (select /*+ UNNEST */ mgr from emp e) -- или здесь
  8  /

SQL> select
  2   /*+
  3       UNNEST(@"SEL$2")                                  -- , и даже здесь - т.е. независимо от порядка
  4       BEGIN_OUTLINE_DATA
  5   */
  6  count(*)
  7   from emp
  8  where mgr not in (select mgr from emp e)
  9  /

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     7 (100)|          |
|   1 |  SORT AGGREGATE     |      |     1 |     8 |            |          |
|*  2 |   HASH JOIN ANTI NA |      |     2 |    16 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |    56 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    56 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

— и выполнение возвращается к исходному быстрому и недорогому плану версии 11g

Кроме отключения Subquery Unnesting в 10053 трейсе запроса с /*+ BEGIN_OUTLINE_DATA */ присутствуют записи типа:

Or-Expansion validity checks failed on query block SEL$1 (#1) because NO_EXPAND hint

И действительно, простой тест показывает, что в присутствии /*+ BEGIN_OUTLINE_DATA */ отключается ещё один тип стоимостной трансформации — Or-Expansion:

SQL> create index IDX_MGR on EMP (MGR, 0) tablespace USERS
  2  /

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)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     3 (100)|          |
|   1 |  CONCATENATION               |         |       |       |            |          | -- Or-Expansion работает
|   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 |
----------------------------------------------------------------------------------------

SQL> select--+ BEGIN_OUTLINE_DATA
  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  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    76 |     3   (0)| 00:00:01 | -- не работает
--------------------------------------------------------------------------

— в 11.2.0.3 и 12.1.0.1 версиях

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

Комментариев нет.

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 такие блоггеры, как: