Oracle mechanics

01.11.2013

Join Factorization

Filed under: CBO,hints,Optimizer features,Oracle — Игорь Усольцев @ 23:36
Tags: ,

По долгу службы пришлось поразбираться с относительно свежим (11.2+) типом преобразования Cost-Based типа Join Factorization (JF), предназначенного для выделения общих составляющих (факторизации) в запросах с UNION ALL соединениями, и хорошо описанного в блоге разработчиков оптимизатора

Для управления этим типом трансформации на уровне системы / сессии / запроса существует соответствующий параметр:

11.2.0.3.@ SQL> @param_ _optimizer_join_factorization

NAME                           VALUE IS_DEF  DSC
------------------------------ ----- ------- -------------------------------------
_optimizer_join_factorization  TRUE  TRUE    use join factorization transformation

Кроме того, поскольку JF имеет версионное ограничение применения, запретить преобразование можно понижая версию оптимизатора, например, на уровне запроса подсказкой OPTIMIZER_FEATURES_ENABLE:

SQL> select/*+ OPTIMIZER_FEATURES_ENABLE('11.1.0.7')*/*
  2    from emp e
  3    join dept d
  4   using (deptno)
  5   where e.sal > 2000
  6  union all
  7  select *
  8    from emp e
  9    join dept d
 10   using (deptno)
 11   where e.hiredate > to_date('1981', 'YYYY')
 12  /

12 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2917334541

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |    11 |   638 |    12  (59)| 00:00:01 |
|   1 |  UNION-ALL                    |         |       |       |            |          |
|   2 |   MERGE JOIN                  |         |     6 |   348 |     6  (17)| 00:00:01 |
|   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                  |         |     6 |   228 |     4  (25)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL         | EMP     |     6 |   228 |     3   (0)| 00:00:01 |
|   7 |   MERGE JOIN                  |         |     5 |   290 |     6  (17)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   9 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|* 10 |    SORT JOIN                  |         |     5 |   190 |     4  (25)| 00:00:01 |
|* 11 |     TABLE ACCESS FULL         | EMP     |     5 |   190 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")
   6 - filter("E"."SAL">2000)
  10 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")
  11 - filter("E"."HIREDATE">TO_DATE('1981','YYYY'))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         18  consistent gets
        ...
          2  sorts (memory)
          0  sorts (disk)
         12  rows processed

— запрос выполняется «обычным» образом, без JF, в то время как по умолчанию, при текущей версии оптимизатора 11.2.0.3
JF используется, успешно снижая формальную стоимость и улучшая статистику выполнения тестового запроса:

SQL> select *
  2    from emp e
  3    join dept d
  4   using (deptno)
  5   where e.sal > 2000
  6  union all
  7  select *
  8    from emp e
  9    join dept d
 10   using (deptno)
 11   where e.hiredate > to_date('1981', 'YYYY')
 12  /

12 rows selected.

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------

Plan hash value: 3380585282

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |       |       |     9 (100)|          | -- улучшенная стоимость
|   1 |  MERGE JOIN                  |                    |    11 |  1177 |     9  (12)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT               |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT            |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |                    |    11 |   957 |     7  (15)| 00:00:01 |
|   5 |    VIEW                      | VW_JF_SET$C9AABA81 |    11 |   957 |     6   (0)| 00:00:01 | -- характерный для JF псевдообзор
|   6 |     UNION-ALL                |                    |       |       |            |          |
|*  7 |      TABLE ACCESS FULL       | EMP                |     6 |   228 |     3   (0)| 00:00:01 |
|*  8 |      TABLE ACCESS FULL       | EMP                |     5 |   190 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      ...
      FACTORIZE_JOIN(@"SET$1"("D"@"SEL$58A6D7F6" "D"@"SEL$37633EB5")) -- соответствующий хинт
      ...
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ITEM_1"="D"."DEPTNO")
       filter("ITEM_1"="D"."DEPTNO")
   7 - filter("E"."SAL">2000)
   8 - filter("E"."HIREDATE">TO_DATE('1981','YYYY'))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         16  consistent gets -- улучшенная статистика
          0  physical reads
        ...
          1  sorts (memory)  -- --//--
          0  sorts (disk)
         12  rows processed

— в списке подсказок Outline плана выполнения присутствует сопровождающая JF подсказка, не описанная в документации, но присутствующая в системном обзоре:

SQL> select * from v$sql_hint where SQL_FEATURE = 'QKSFM_JOINFAC';

NAME               SQL_FEATURE    CLASS           INVERSE            VERSION   VERSION_OUTLINE
------------------ -------------- --------------- ------------------ -------- ----------------
FACTORIZE_JOIN     QKSFM_JOINFAC  FACTORIZE_JOIN  NO_FACTORIZE_JOIN  11.2.0.1  11.2.0.1
NO_FACTORIZE_JOIN  QKSFM_JOINFAC  FACTORIZE_JOIN  FACTORIZE_JOIN     11.2.0.1  11.2.0.1

и это можно попробовать применить для точного управления преобразованием на уровне отдельного Query Set-а:

SQL> select/*+ NO_FACTORIZE_JOIN(@"SET$1")*/ *
  2    from emp e
  3    join dept d
  4   using (deptno)
  5   where e.sal > 2000
  6  union all
  7  select *
  8    from emp e
  9    join dept d
 10   using (deptno)
 11   where e.hiredate > to_date('1981', 'YYYY')
 12  /

12 rows selected.

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

Plan hash value: 2917334541 -- хэш плана выполнения соответствует плану для версии 11.1, без JF

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |       |       |    12 (100)|          |
|   1 |  UNION-ALL                    |         |       |       |            |          |
|   2 |   MERGE JOIN                  |         |     6 |   348 |     6  (17)| 00:00:01 |
|   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                  |         |     6 |   228 |     4  (25)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL         | EMP     |     6 |   228 |     3   (0)| 00:00:01 |
|   7 |   MERGE JOIN                  |         |     5 |   290 |     6  (17)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   9 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|* 10 |    SORT JOIN                  |         |     5 |   190 |     4  (25)| 00:00:01 |
|* 11 |     TABLE ACCESS FULL         | EMP     |     5 |   190 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      ...                -- подсказки, связанные с JF отсутствуют
      END_OUTLINE_DATA
  */

— стоит заметить, что подсказка /*+ NO_FACTORIZE_JOIN(@»SET$1″)*/ в примере срабатывает только в указанном виде, с обязательным указанием Query Set-а и без упоминания таблиц в любом виде

Обратная подсказка срабатывает только в следующих синтаксических вариантах:

/*+ FACTORIZE_JOIN(@"SET$1")*/
/*+ FACTORIZE_JOIN(@"SET$1"("D"@"SEL$58A6D7F6" "D"@"SEL$37633EB5"))*/

Попытка использовать сокращённые указатели таблиц успехом не увенчалась:

SQL> select/*+ FACTORIZE_JOIN(@"SET$1"("D" "D"))*/ *
  2    from emp e
  3    join dept d
  4   using (deptno)
  5   where e.sal > 2000
  6  union all
  7  select *
  8    from emp e
  9    join dept d
 10   using (deptno)
 11   where e.hiredate > to_date('1981', 'YYYY')
 12  /
select/*+ FACTORIZE_JOIN(@"SET$1"("D" "D"))*/ *
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 10336
Session ID: 130 Serial number: 11323

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

ORA-07445: exception encountered: core dump [kkqjfCmpHintAlias()+6] [ACCESS_VIOLATION] [ADDR:0x8] [PC:0x14586647A] [UNABLE_TO_READ] []

— и, судя по трейсу оптимизатора, как раз на этапе проверки / подготовки JF преобразования:

***********************************
Cost-Based Join Factorization
***********************************
Join-Factorization on query block SET$1 (#1)
JF: Using search type: exhaustive
JF: Checking validity of join factorization for query block SET$1 (#1)

Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x8] [PC:0x14586647A, kkqjfCmpHintAlias()+6]

— однако хинты недокументированные, и претензии предъявить некому

Придётся пока с осторожностью заниматься «точным управлением» JF трансформациями

P.S. Результаты повторяемы на версиях 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 такие блоггеры, как: