Oracle mechanics

Операции CBO

Ссылки по теме:

Описание некоторых методов доступа к данным (access path), соединения наборов данных (join method) и преобразований запросов (query transformation) используемых оптимизатором / в планах выполнения запросов Oracle

Методы доступа и соединения наборов данных

Index Full Scan

«… full index scan исключает сортировку, поскольку по одному блоку считывает упорядоченные по индексному ключу данные…»

Используется для эффективной замены табличного доступа при выполнении сортировок (ORDER BY), группировок (GROUP BY) и операции sort merge join (вместо традиционных full table scan с последующей сортировкой) при выполнении определённых условий

Index Fast Full Scan

«… все блоки индекса читаются с использованием многоблочного чтения ( определяемым параметром db_file_multiblock_read_count и ограничениями ОС), в отличие от full index scan, и могут сканироваться параллельно«

INDEX SKIP SCAN

«Index Skip Scan использует разделение составного индекса на логические части (subindexes). Такое сканирование используется в случаях, когда первый столбец составного индекса не входит в условия запроса. Другими словами, первый столбец пропускается (skipped) [при выполнении этой операции] … [Oracle] определяет количество таких логических частей (subindexes) по числу неповторяющихся значений (distinct values) первого столбца составного индекса. Такое сканирование является предпочтительным в случаях, когда первый столбец составного индекса имеет малое количество неповторяющихся значений, а следующий стобец — большое … [CBO] может предпочесть операцию index skip scan если первый столбец составного индекса не входит в условия запроса (query predicate)…»

Практический пример использования оптимизатором index skip scan при наличии составного индекса IX_AA_AFLID_DATE_INS по столбцам AA(AFFILIATE_ID, DATE_INSERT)

SQL> SELECT AA.ID,
...
10    FROM AA
11   WHERE (AA.AFFILIATE_ID = :2091582 OR :2091582 = 1)
12     AND AA.DATE_INSERT >= TO_DATE('01.05.2010', 'DD.MM.YYYY')
13     AND AA.DATE_INSERT <=
14         TO_DATE('30.06.2010 23:59:59', 'DD.MM.YYYY HH24.MI.SS')
15   order by AA.DATE_INSERT desc
16  /

------------------------------------------------------------------------------------------------------
 | Id  | Operation                     | Name                 | Rows  |  Bytes | Cost (%CPU)| Time     |
 ------------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT              |                      |       |        |  2881 (100)|          |
 |*  1 |  FILTER                       |                      |       |        |            |          |
 |   2 |   SORT ORDER BY               |                      |  1289 |    251K|  2881   (1)| 00:00:35 |
 |*  3 |    TABLE ACCESS BY INDEX ROWID| AA                   |  1289 |    251K|  2880   (1)| 00:00:35 |
 |*  4 |     INDEX SKIP SCAN           | IX_AA_AFLID_DATE_INS |  1692 |        |  1279   (0)| 00:00:16 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(:SYS_B_6,:SYS_B_7)<=TO_DATE(:SYS_B_8,:SYS_B_9))
   3 - filter((:SYS_B_4=:SYS_B_5 OR "AA"."AFFILIATE_ID"=:SYS_B_3))
   4 - access("AA"."DATE_INSERT">=TO_DATE(:SYS_B_6,:SYS_B_7) AND
              "AA"."DATE_INSERT"<=TO_DATE(:SYS_B_8,:SYS_B_9))
       filter(("AA"."DATE_INSERT">=TO_DATE(:SYS_B_6,:SYS_B_7) AND
              "AA"."DATE_INSERT"<=TO_DATE(:SYS_B_8,:SYS_B_9)))

к сожалению, из-за неэффективной обработки оптимизатором условия ((:SYS_B_4=:SYS_B_5 OR «AA».»AFFILIATE_ID»=:SYS_B_3)) на шаге №3 выбранный план не является оптимальным (и запрос, соответственно, очень медленно выполняется). После замены условия на DECODE (что допустимо при ненулевых значениях AFFILIATE_ID и связанных переменных) один из вариантов плана (для случая :SYS_B_4 = :SYS_B_5) по прежнему содержит index skip scan, другой вариант плана (:SYS_B_4 != :SYS_B_5) использует гораздо более эффективный и дефёвый метод доступа index range scan! Объединение(выбор) планов по условию в общем плане выполнения обозначается как

FILTER

Фильтрация по условию набора строк (row set), полученного в рез-тате предыдущей операции доступа к данным. Отражается в секции predicate info:

SQL> select * from emp where mgr = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("MGR"=1)

, или отдельной операцией плана выполнения, например, в случаях:

  • использования подзапросов
11.2.0.3.@SCOTT SQL> select *
  2     from emp e
  3  where (select d.deptno from dept d where d.dname = e.ename) = 10
  4  /

no rows selected

11.2.0.3.@SCOTT SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    32 (100)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     4   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     1 |    13 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter(=10)                                 -- в таком виде условия в V$SQL_PLAN.FILTER_PREDICATES (и в 10053 трейсе, и в выводе dbms_xplan.display_cursor)
   3 - filter("D"."DNAME"=:B1)

11.2.0.3.@SCOTT SQL> explain plan for
  2  select *
  3     from emp e
  4  where (select d.deptno from dept d where d.dname = e.ename) = 10
  5  /

Explained.

11.2.0.3.@SCOTT SQL> SELECT * FROM TABLE(dbms_xplan.display());

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   532 |    32   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     4   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     1 |    13 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( (SELECT "D"."DEPTNO" FROM "DEPT" "D" WHERE -- так отображается в PLAN_TABLE
              "D"."DNAME"=:B1)=10)
   3 - filter("D"."DNAME"=:B1)
filter(NULL IS NOT NULL)

специальный случай операции FILTER, используемый для исключения из универсального плана выполнения избыточных операций с данными при наличии невыполнимых условий

Например, если для ненулевого по определению поля EMP.EMPNO указать в в запросе невыполнимое условие  empno is null:

11.2.0.3.ORCL112@SCOTT SQL> desc emp
 Name  Null?    Type
 ----- -------- ---------
 EMPNO NOT NULL NUMBER(4)

SQL> select * from emp where empno is null;

no rows selected

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    38 |     0   (0)|          | -- стоимость всего запроса = 0
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 | -- при стоимости сканирования = 3
---------------------------------------------------------------------------

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

   1 - filter(NULL IS NOT NULL)                     -- исключает избыточную операцию сканирования

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets                               -- блоки бд не читаются
          0  physical reads
          0  redo size
        799  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

При этом в трейсе оптимизатора финальное уточнение стоимости выглядит след.образом:

OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  EMP[EMP]#0
***********************
Best so far:  Table#: 0  cost: 3.0019  card: 14.0000  bytes: 532 -- стоимость до уточнения
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

*********************************
Number of join permutations tried: 1
*********************************
Enumerating distribution method (advanced)

Cdn, Cost adjusted (to ~ 0) as where clause evalutes to FALSE   -- уточнённая стоимость
...
id=0 fptconst predicate=NULL IS NOT NULL                        -- по условию со значением FALSE
Final cost for query block SEL$1 (#0) - All Rows Plan:
  Best join order: 1
  Cost: 0.0000  Degree: 1  Card: 1.0000  Bytes: 532             -- итоговая стоимость плана
  Resc: 0.0000  Resc_io: 0.0000  Resc_cpu: 0
  Resp: 0.0000  Resp_io: 0.0000  Resc_cpu: 0

Операция filter(NULL IS NOT NULL) и запросы со связанными переменными

CONCATENATION

Операция выбора путей доступа к данным / объединения результатов.

В первом случае применяется в случаях, когда план выполнения запроса выбирается динамически, например, в зависимости от значения связанной переменной:

11.2.0.3.@ SQL> select * from scott.emp e WHERE (decode(:v, 1, e.empno, :v) = e.empno);

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    15 |   570 |     8   (0)| 00:00:01 |
|   1 |  CONCATENATION                |              |       |       |            |          |
|*  2 |   FILTER                      |              |       |       |            |          |
|*  3 |    TABLE ACCESS FULL          | EMP          |    14 |   532 |     7   (0)| 00:00:01 |
|*  4 |   FILTER                      |              |       |       |            |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMP          |     1 |    38 |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | SYS_C0093796 |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - filter(SYS_OP_MAP_NONNULL(:V)=HEXTORAW('C10200') )
3 - filter("E"."EMPNO" IS NOT NULL)
4 - filter(SYS_OP_MAP_NONNULL(:V)<>HEXTORAW('C10200') )
6 - access("E"."EMPNO"=:V)

при этом, несмотря на то, что запрос будет выполняться либо с использованием полного сканирования всех блоков таблицы (TABLE ACCESS FULL), либо — по уникальному индексу (TABLE ACCESS BY INDEX ROWID), общая стоимость плана (и ожидаемое кол-во строк Rows/Bytes) просто складываются из альтернативных стоимостей вариантов доступа:

8 = 7+1

В секции Predicate Information можно видеть малодокументированную функцию SYS_OP_MAP_NONNULL, используемую внутри DECODE для успешного сравнения (с результатом TRUE) нулевых значений

sys_op_map_nonnull (NULL) = sys_op_map_nonnull (NULL)

функция эта встречается, например, также в описании старого бага Oracle < 10.2 Bug 3899748 Incorrect join cardinality for lossless NVL predicates:

Workaround: Use SYS_OP_MAP_NONNULL(o.org_id)=SYS_OP_MAP_NONNULL(l.org_id) instead of NVL for the equijoin

В другом случае в результате применения OR-expansion transformation операция CONCATENATION используется в плане для обозначения объединения результатов вместо UNION ALL:

Concatenation is semantically equivalent to the Union All operator

11.2.0.3.@ SQL> select--+ USE_CONCAT(OR_PREDICATES(1))
2   *
3    FROM scott.emp e
4   WHERE e.mgr < 7700 5      OR e.sal >= 3000
6  /

8 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2595565339

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     9 |   342 |    14   (0)| 00:00:01 |
|   1 |  CONCATENATION     |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     3 |   114 |     7   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     6 |   228 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("E"."SAL">=3000)
3 - filter("E"."MGR"=3000))

Подсказка USE_CONCAT

CARTESIAN JOIN

«… используется в случаях, когда одна или несколько таблиц не имеют никаких условий (join conditions) для соединения с другими таблицами запроса. Оптимизатор соединяет каждую строку первого источника данных с каждой строкой другого, создавая картезианское произведение (Cartesian product) двух наборов данных»

SQL> select job, dname from emp cross join dept;
SQL> -- или
SQL> select job, dname from emp, dept;

---------------------------------------------
| Id  | Operation            | Name | Rows  |
---------------------------------------------
|   0 | SELECT STATEMENT     |      |    56 |
|   1 |  MERGE JOIN CARTESIAN|      |    56 |
|   2 |   TABLE ACCESS FULL  | DEPT |     4 |
|   3 |   BUFFER SORT        |      |    14 |
|   4 |    TABLE ACCESS FULL | EMP  |    14 |
---------------------------------------------

SQL> select /*+ ORDERED*/ * from dept d, bonus b, emp e
 2  where d.deptno = e.deptno
 3  and b.ename = e.ename;

-----------------------------------------------
| Id  | Operation             | Name  | Rows  |
-----------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |
|*  1 |  HASH JOIN            |       |     1 |
|   2 |   MERGE JOIN CARTESIAN|       |     1 |
|   3 |    TABLE ACCESS FULL  | DEPT  |     4 |
|   4 |    BUFFER SORT        |       |     1 |
|   5 |     TABLE ACCESS FULL | BONUS |     1 |
|   6 |   TABLE ACCESS FULL   | EMP   |    14 |
-----------------------------------------------

В последнем примере благодаря подсказке ORDERED оптимизатор вынужден первым делом соедиить таблицы DEPT и BONUS, не имеющие по условиям запроса никаких условий для соединения (join keys) и, следовательно, единственной возможной операцией для такого безусловного соединения оказывается картезианское произведение (Join Cartesian) В случае, когда две небольшие таблицы (DEPT и BONUS) соединяются через условия d.deptno = e.deptno and b.ename = e.ename к «большой» таблице EMP и имеются дополнительные условия на столбцы небольших таблиц (фильтры dept.loc = ‘CHICAGO’ and bonus.comm > 30), оптимизатор по соображениям улучшения избирательности (selectivity) и уменьшения стоимисти без всяких подсказок может выбрать Merge Join Cartesian небольших таблиц с последующим соединением (Hash join) с большой таблицей:

SQL> -- при наличии статистики
SQL> exec dbms_stats.set_table_stats(ownname => 'SCOTT',tabname => 'EMP', numrows => 100000);

PL/SQL procedure successfully completed.

SQL> select * from dept d, bonus b, emp e
 2  where d.deptno = e.deptno and d.loc = 'CHICAGO'
 3  and b.ename = e.ename and b.comm > 30;

Execution Plan
-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |  2381 |   174K|    16  (44)| 00:00:01 |
|*  1 |  HASH JOIN            |       |  2381 |   174K|    16  (44)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|       |     1 |    38 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL  | DEPT  |     1 |    18 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |       |     1 |    20 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL | BONUS |     1 |    20 |     3   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | EMP   |   100K|  3613K|     8  (63)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 1 - access("D"."DEPTNO"="E"."DEPTNO" AND "B"."ENAME"="E"."ENAME")
 3 - filter("D"."LOC"='CHICAGO')
 5 - filter("B"."COMM">30)

Начиная с Oracle 10g использование картезианского произведения на уровне системы, сессии, запроса может быть запрещено параметрами с разной степенью эффективности:

SQL> @param_ _optimizer_mjc_enabled

NAME                    VALUE  IS_DEF   IS_MOD  IS_ADJ   DSC
----------------------- ------ -------- ------  -------- ---------------------------
_optimizer_mjc_enabled  TRUE   TRUE     FALSE   FALSE    enable merge join cartesian

11.2.0.1.@ SQL> @param_ _optimizer_cartesian_enabled

NAME                          VALUE  IS_DEF  DSC
----------------------------- ------ ------- --------------------------------
_optimizer_cartesian_enabled  TRUE   TRUE    optimizer cartesian join enabled

SQL> select * from emp, dept
  2  /

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    56 |  3304 |    10   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    56 |  3304 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |    14 |   546 |     7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  |    14 |   546 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

SQL> select /*+ opt_param('_optimizer_cartesian_enabled' 'false')*/ * from emp, dept
  2  /

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    56 |  3304 |    10   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    56 |  3304 |    10   (0)| 00:00:01 | -- MJC не отключён в этом случае параметром _optimizer_cartesian_enabled, в других может запрещаться
|   2 |   TABLE ACCESS FULL  | DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |    14 |   546 |     7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  |    14 |   546 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

SQL> select /*+ opt_param('_optimizer_mjc_enabled' 'false')*/ * from emp, dept -- успешно отключает MJC
  2  /

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    56 |  3304 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    56 |  3304 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   546 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Optimizer Selects the Merge Join Cartesian Despite the Hints [ID 457058.1]

HASH JOIN

«… используется для соединения больших наборов данных (data sets). Оптимизатор использует меньшую из двух таблиц — источников данных для посторения хэш-таблицы ключа соединения (join key) в памяти. Затем сканирует бОльшую таблицу, сравнивая по ключу с хэш-таблицой для получения результирующего набора строк»

SQL> exec dbms_stats.set_table_stats(ownname => 'SCOTT',tabname => 'EMP',numrows => 100000);
PL/SQL procedure successfully completed.

SQL> exec dbms_stats.set_table_stats(ownname => 'SCOTT',tabname => 'DEPT',numrows => 100000);
PL/SQL procedure successfully completed.

SQL> select job, dname from emp e, dept d where d.deptno = e.deptno;

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2500M|    55G|       | 11524  (98)| 00:02:19 |
|*  1 |  HASH JOIN         |      |  2500M|    55G|  2248K| 11524  (98)| 00:02:19 |
|   2 |   TABLE ACCESS FULL| EMP  |   100K|  1074K|       |     4  (25)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |   100K|  1269K|       |     4  (25)| 00:00:01 |
-----------------------------------------------------------------------------------

Подсказки: USE_HASH / NO_USE_HASH

SQL> select name, class, inverse from V$SQL_HINT where name = 'USE_HASH';

NAME      CLASS  INVERSE
--------- ------ -----------
USE_HASH  JOIN   NO_USE_HASH

Управление использованием hash join с помощью oracle events на уровне сессии/системы:

$ oerr ora 10093
10093, 00000, "CBO Enable force hash joins"

SQL> alter session set events '10093 trace name context forever , level 1';

$ oerr ora 10092
10092, 00000, "CBO Disable hash join"

SQL> alter session set events '10092 trace name context forever , level 1';

$ oerr ora 10103
10103, 00000, "CBO Disable hash join swapping"

или скрытым, начиная с 10g, параметром:

SQL> @param_ _hash_join_enabled

NAME                DSC
------------------- ------------------------
_hash_join_enabled  enable/disable hash join

Right Join – The secret of swapping join input — про hash join swapping и испольхование хинта SWAP_JOIN_INPUTS

HASH UNIQUE / SORT UNIQUE

From10Gr2, HASH UNIQUE Operation Returns Results in UNSORTED ORDER by Default [ID 341838.1]: «Начиная с 10gR2 при выполнении SELECT DISTINCT при настройках по умолчанию [оптимизатор] отдаёт предпочтение операция HASH UNIQUE вместо SORT UNIQUE, в результате которой [в отличие от операции SORT UNIQUE] данные возвращаются в неотсортированном виде»

SQL> set autotrace on explain
SQL> select distinct job from emp;

JOB
---------
CLERK    -- неотсортированные значения
SALESMAN
PRESIDENT
MANAGER
ANALYST

Execution Plan
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    40 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE       |      |     5 |    40 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   112 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> alter session set "_gby_hash_aggregation_enabled"=false;

Session altered.

SQL> select distinct job from emp;

JOB
---------
ANALYST -- отсортированные значения
CLERK
MANAGER
PRESIDENT
SALESMAN

Execution Plan
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    40 |     4  (25)| 00:00:01 |
|   1 |  SORT UNIQUE       |      |     5 |    40 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   112 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

или же для исключения использования операции HASH UNIQUE и возвращения к «старому поведению» (использованию SORT UNIQUE) можно установить параметр optimizer_features_enableв значение 9.2.0 и ниже:

SQL> alter session set optimizer_features_enable="9.2.0";
Session altered.

SQL> select distinct job from emp;

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    40 |     5 |
|   1 |  SORT UNIQUE       |      |     5 |    40 |     5 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   112 |     2 |
-----------------------------------------------------------

Параметры:

  • _gby_hash_aggregation_enabled = TRUE (значение по умолчанию нового параметра Oracle 10g). Значение FALSE отключает использование HASH UNIQUE
  • optimizer_features_enable <= 9.2.0 отключает HASH UNIQUE

HASH GROUP BY / SORT GROUP BY

Аналогично начиная с версии Oracle 10g вместо операции SORT GROUP BY при операциях сортировки может использоваться новый внутренний алгоритм сортировки, который обозначается HASH GROUP BY (либо Hash unique при запросе неповторяющихся значений — описан выше) и не гарантирует сортировки выводимых данных — ‘Group By’ Does Not Sort If You Don’T Use Order By In 10g [ID 345048.1]

SQL> select object_name, count(object_id) from xxx_objects group by object_name;

OBJECT_NAME      COUNT(OBJECT_ID)
-----------      ----------------
ICOL$            1
I_USER1          1
PROXY_ROLE_DATA$ 1
TS$              1
I_CON2           1
...

Execution Plan
------------------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             | 41304 |  1210K|       |   704   (1)| 00:00:09 |
|   1 |  HASH GROUP BY     |             | 41304 |  1210K|  4336K|   704   (1)| 00:00:09 |
|   2 |   TABLE ACCESS FULL| XXX_OBJECTS | 68873 |  2017K|       |   284   (1)| 00:00:04 |
------------------------------------------------------------------------------------------

SQL> alter session set "_gby_hash_aggregation_enabled"=false;

Session altered.

SQL> select object_name, count(object_id) from xxx_objects group by object_name;

OBJECT_NAME                      COUNT(OBJECT_ID)
------------------------------   ----------------
/1000323d_DelegateInvocationHa   2
/1000e8d1_LinkedHashMapValueIt   2
/1005bd30_LnkdConstant           2
...

Execution Plan
------------------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             | 41304 |  1210K|       |   704   (1)| 00:00:09 |
|   1 |  SORT GROUP BY     |             | 41304 |  1210K|  4336K|   704   (1)| 00:00:09 |
|   2 |   TABLE ACCESS FULL| XXX_OBJECTS | 68873 |  2017K|       |   284   (1)| 00:00:04 |
------------------------------------------------------------------------------------------

Для отключения использования операции HASH GROUP BY также, как для отключения Hash unique могут быть использованы параметры:

  • _gby_hash_aggregation_enabled = FALSE на уровне сессии/системы либо хинт /*+ opt_param(‘_gby_hash_aggregation_enabled’ ‘false’) */ на уровне запроса
  • optimizer_features_enable <= 9.2.0

, либо подсказка NO_USE_HASH_AGGREGATION

SQL> select name, class, inverse from V$SQL_HINT where name = 'NO_USE_HASH_AGGREGATION';

NAME                     CLASS                 INVERSE
------------------------ --------------------- ---------------------
NO_USE_HASH_AGGREGATION  USE_HASH_AGGREGATION  USE_HASH_AGGREGATION

SQL> select /*+ no_use_hash_aggregation*/ object_name, count(object_id) from xxx_objects group by ob
ject_name;

Execution Plan
------------------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             | 41304 |  1210K|       |   704   (1)| 00:00:09 |
|   1 |  SORT GROUP BY     |             | 41304 |  1210K|  4336K|   704   (1)| 00:00:09 |
|   2 |   TABLE ACCESS FULL| XXX_OBJECTS | 68873 |  2017K|       |   284   (1)| 00:00:04 |
------------------------------------------------------------------------------------------

Native Full Outer Join

Метод доступен с версии 10.2.0.3, начиная с 11.1 используется автоматически При использовании «старого метода» FULL OUTER JOIN выполняется через два HASH JOIN‘а:

11.2.0.3.ORCL112@SCOTT SQL> create table t as select rownum dummy from dual connect by rownum <= 1000000;

Table created.

11.2.0.3.ORCL112@SCOTT SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

11.2.0.3.ORCL112@SCOTT SQL> select /*+ NO_NATIVE_FULL_OUTER_JOIN*/ * from t full outer join t t1 on t.dummy = t1.dummy;

1000000 rows selected.

Elapsed: 00:00:01.98

-------------------------------------------------------------------------------------
 | Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT     |      |  1010K|    25M|       |  4977   (2)| 00:01:00 |
 |   1 |  VIEW                |      |  1010K|    25M|       |  4977   (2)| 00:01:00 |
 |   2 |   UNION-ALL          |      |       |       |       |            |          |
 |*  3 |    HASH JOIN OUTER   |      |  1000K|  9765K|    16M|  2488   (2)| 00:00:30 |
 |   4 |     TABLE ACCESS FULL| T    |  1000K|  4882K|       |   431   (2)| 00:00:06 |
 |   5 |     TABLE ACCESS FULL| T    |  1000K|  4882K|       |   431   (2)| 00:00:06 |
 |*  6 |    HASH JOIN ANTI    |      | 10000 |    97K|    16M|  2488   (2)| 00:00:30 |
 |   7 |     TABLE ACCESS FULL| T    |  1000K|  4882K|       |   431   (2)| 00:00:06 |
 |   8 |     TABLE ACCESS FULL| T    |  1000K|  4882K|       |   431   (2)| 00:00:06 |
 -------------------------------------------------------------------------------------

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

 3 - access("T"."DUMMY"="T1"."DUMMY"(+))
 6 - access("T"."DUMMY"="T1"."DUMMY")

Statistics
 ----------------------------------------------------------
       6308  consistent gets

с почти удвоенными количеством consistent gets и, соответственно. стоимостью запроса по сравнению с Native Full Outer Join:

11.2.0.3.ORCL112@SCOTT SQL> select /*+ NATIVE_FULL_OUTER_JOIN*/ * from t full outer join t t1 on t.dummy = t1.dummy;

1000000 rows selected.

Elapsed: 00:00:01.47

 ------------------------------------------------------------------------------------------
 | Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
 ------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT      |          |  1000K|    24M|       |  2488   (2)| 00:00:30 |
 |   1 |  VIEW                 | VW_FOJ_0 |  1000K|    24M|       |  2488   (2)| 00:00:30 |
 |*  2 |   HASH JOIN FULL OUTER|          |  1000K|  9765K|    16M|  2488   (2)| 00:00:30 |
 |   3 |    TABLE ACCESS FULL  | T        |  1000K|  4882K|       |   431   (2)| 00:00:06 |
 |   4 |    TABLE ACCESS FULL  | T        |  1000K|  4882K|       |   431   (2)| 00:00:06 |
 ------------------------------------------------------------------------------------------

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

 2 - access("T"."DUMMY"="T1"."DUMMY")

Statistics
 ----------------------------------------------------------
       3254  consistent gets

— который, в свою очередь, по кол-ву consistent gets, стоимости, точности оценки cardinality cоответствует обычной (не OUTER) операции HASH JOIN:

11.2.0.3.ORCL112@SCOTT SQL> select * from t join t t1 on t.dummy = t1.dummy;

1000000 rows selected.

Elapsed: 00:00:01.36

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000K|  9765K|       |  2488   (2)| 00:00:30 |
|*  1 |  HASH JOIN         |      |  1000K|  9765K|    16M|  2488   (2)| 00:00:30 |
|   2 |   TABLE ACCESS FULL| T    |  1000K|  4882K|       |   431   (2)| 00:00:06 |
|   3 |   TABLE ACCESS FULL| T    |  1000K|  4882K|       |   431   (2)| 00:00:06 |
-----------------------------------------------------------------------------------

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

 1 - access("T"."DUMMY"="T1"."DUMMY")

Statistics
----------------------------------------------------------
       3254  consistent gets

Управляющие подсказки и параметры:

SQL> select * from v$sql_hint where name = 'NATIVE_FULL_OUTER_JOIN';

NAME                    SQL_FEATURE  CLASS                   INVERSE                    TARGET_LEVEL   PROPERTY VERSION  VERSION_OUTLINE
----------------------- ------------ ----------------------- -------------------------- ------------ ---------- -------- ---------------
NATIVE_FULL_OUTER_JOIN  QKSFM_ALL    NATIVE_FULL_OUTER_JOIN  NO_NATIVE_FULL_OUTER_JOIN             2         16 10.2.0.3 10.2.0.3

11.2.0.3.ORCL112@SYS SQL> @param_ _optimizer_native_full_outer_join

NAME                              VALUE IS_DEF DSC
--------------------------------- ----- ------ --------------------------------------------------
_optimizer_native_full_outer_join FORCE TRUE   execute full outer join using native implementaion

11.2.0.3.ORCL112@SYS SQL> @pvalid_ _optimizer_native_full_outer_join

PARAMETER                         VALUE
--------------------------------- ------
_optimizer_native_full_outer_join OFF
                                  FORCE
                                  CHOOSE

Native full outer join

NESTED LOOP JOIN

После определения оптимизатором ведущей (outer table — таблица на внешней стороне соединения, иначе именуемая driving table) и ведомой (inner table) таблиц, «… для каждой строки ведущей таблицы Oracle выбирает все строки в ведомой таблице. Операция состоит из внешнего цикла (outer loop) по каждой строке ведущей таблицы и внутреннего цикла (inner loop) по каждой строке ведомой таблицы. В плане выполнения внешний цикл (outer loop) показывается перед внутренним (inner loop

SQL> select * from emp e, dept d where e.empno > d.deptno;
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    56 |  3192 |     7 |
|   1 |  NESTED LOOPS      |      |    56 |  3192 |     7 |--3) результат NESTED LOOPS = 4*14 = 56 строк
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     2 |--1) выбирается 4 строки из OUTER TABLE
|*  3 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     2 |--2) для каждой строки  из OUTER TABLE выбирается по 14 строк INNER TABLE
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("E"."EMPNO">"D"."DEPTNO")

Для индексного доступа при значении optimizer_features_enable=’10.2.*’:

SQL> alter session set optimizer_features_enable='10.2.0.4';

Session altered.

SQL> select --+ use_nl(d) index(d pk_dept) index(e pk_emp)
2   d.dname
3    from scott.dept d, scott.emp e
4   where d.deptno = e.deptno
5     and e.empno > 20
6  /

14 rows selected.

-----------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   672 |   816   (0)|
|   1 |  NESTED LOOPS                |         |    14 |   672 |   816   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |    14 |   364 |   802   (0)|
|*  3 |    INDEX RANGE SCAN          | PK_EMP  |    14 |       |     2   (0)|
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    22 |     1   (0)|
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|
-----------------------------------------------------------------------------

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

3 - access("E"."EMPNO">20)
5 - access("D"."DEPTNO"="E"."DEPTNO")

Начиная с Oracle 11g применяется новая реализация NLJ IO Batching — для уменьшения времени дискового доступа | оптимизации физического чтения блоков ведомой таблицы (inner table), при этом в плане выполнения появляется дополнительная операция NESTED LOOPS:

SQL> alter session set optimizer_features_enable='11.1.0.6';

Session altered.

SQL> select --+ use_nl(d) index(d pk_dept) index(e pk_emp)
...
14 rows selected.

------------------------------------------------------------------------------
 | Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)|
 ------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT              |         |    14 |   672 |   816   (0)|
 |   1 |  NESTED LOOPS                 |         |       |       |            |--++
 |   2 |   NESTED LOOPS                |         |    14 |   672 |   816   (0)|--+
 |   3 |    TABLE ACCESS BY INDEX ROWID| EMP     |    14 |   364 |   802   (0)|
 |*  4 |     INDEX RANGE SCAN          | PK_EMP  |    14 |       |     2   (0)|
 |*  5 |    INDEX UNIQUE SCAN          | PK_DEPT |     1 |       |     0   (0)|
 |   6 |   TABLE ACCESS BY INDEX ROWID | DEPT    |     1 |    22 |     1   (0)|
 ------------------------------------------------------------------------------

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

4 - access("E"."EMPNO">20)
5 - access("D"."DEPTNO"="E"."DEPTNO")

Применение NLJ Batching регулируется подсказкой /*+ NO_NLJ_BATCHING(inner_table) */, отключающей механизм буферизации IO и возвращающей план выполнения к стандартному для Oracle 11g виду:

SQL> select --+ use_nl(d) index(d pk_dept) index(e pk_emp) NO_NLJ_BATCHING(d)
...
14 rows selected.

 ------------------------------------------------------------------------------
 | Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)|
 ------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT              |         |    14 |   672 |   816   (0)|
 |   1 |  TABLE ACCESS BY INDEX ROWID  | DEPT    |     1 |    22 |     1   (0)|-- < Oracle 11g style NLJ
 |   2 |   NESTED LOOPS                |         |    14 |   672 |   816   (0)|
 |   3 |    TABLE ACCESS BY INDEX ROWID| EMP     |    14 |   364 |   802   (0)|
 |*  4 |     INDEX RANGE SCAN          | PK_EMP  |    14 |       |     2   (0)|
 |*  5 |    INDEX UNIQUE SCAN          | PK_DEPT |     1 |       |     0   (0)|
 ------------------------------------------------------------------------------

, того же рез-то можно добиться изменением параметра _nlj_batching_enabledна уровне сессии | системы:

SQL> @param_ _nlj_batching_enabled

NAME                   VALUE  IS_DEF IS_MOD IS_ADJ DSC
---------------------- ------ ------ ------ ------ ------------------------------------
_nlj_batching_enabled  1      TRUE   FALSE  FALSE  enable batching of the RHS IO in NLJ -- RHS => Right HandSide

SQL> alter session set "_nlj_batching_enabled" = 0;

Session altered.

Подсказки, стимулирующие использование Nested Loops Join:

/*+ USE_NL ( [@query_block] inner_table [outer_table] ) */
/*+ USE_NL_WITH_INDEX ( [@query_block] inner_table [index_name] ) */
Semi-join / Anti-join

SEMIjoin — операция соединения (join методом nested loops или hash join), возвращающая строку ведущей (outer) таблицы при нахождении первого совпадения со строкой ведомой (inner table) таблицы по условиям запроса (predicate) ANTIjoin — аналогичная операция, которая в случае нахождения первого совпадения исключает строку ведущей (outer) таблицы из результатов поиска Используются при выполнении запросов с конструкциями «exists» , «in» (semi join) и «not exists» , «not in» (anti join) Для примера нужно отключить ограничение foreign key, иначе «умный» Oracle будет выполнять этот тестовый запрос с использованием только таблицы EMP ;)

SQL> alter table EMP disable constraint FK_DEPTNO;
Table altered.

SQL> select * from emp e
2  where exists ( select 1 from dept d
3  where d.deptno = e.deptno);

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   560 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |         |    14 |   560 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   518 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     5 |    15 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"="E"."DEPTNO")

SQL> select * from emp e
2  where not exists ( select 1 from dept d
3  where d.deptno = e.deptno);

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    40 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |         |     1 |    40 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   518 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     5 |    15 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

Для получения HASH JOIN «сделаем» таблицу EMP большой, увеличив статистику numrows:

SQL> exec dbms_stats.set_table_stats(ownname => 'SCOTT',tabname => 'EMP',numrows
=> 100000);

SQL> select * from emp e
2  where exists ( select 1 from dept d
3  where d.deptno = e.deptno);

--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |   100K|  3906K|    11  (64)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|         |   100K|  3906K|    11  (64)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | PK_DEPT |     5 |    15 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | EMP     |   100K|  3613K|     8  (63)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")

SQL> select * from emp e
2  where not exists ( select 1 from dept d
3  where d.deptno = e.deptno);

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    40 |    11  (64)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT ANTI|         |     1 |    40 |    11  (64)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | PK_DEPT |     5 |    15 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | EMP     |   100K|  3613K|     8  (63)| 00:00:01 |
--------------------------------------------------------------------------------
HASH JOIN ANTI SNA / HASH JOIN ANTI NA

[Single] Null-Aware Anti-Join

Sort Merge Join

«Sort merge joins успешно используется, когда в условиях соединения двух таблиц присутствуют операторы сравнения: <, <=, >, или >= , но не для операторов равества/неравества. Производительность sort merge joins лучше, чем у nested loop joins для больших наборов данных (data sets). [Также следует обратить внимание на то, что ] вы не можете использовать [более производительные операции] hash joins для соединения таблиц, если условия соединения отличаются от равенства (equality condition). При выполнении операции merge join отсутствует концепция ведущей (driving table) / ведомой таблицы. Операция состоит из 2-х шагов:

  1. Sort join: Оба источника входных данных / таблицы сортируются по ключу соединения (join key)

  2. Merge join: Совместная обработка / объдинение (merging) отсортированных списков

В случае, если источник данных уже отсортирован по столбцу[ам] условия соединения (join column), операция sort join не производится для этого источника данных» Например, при использовании оператора сравнения «>» несвязанных столцов видим по одной операции SORT JOIN для каждой таблицы и объединённую операцию MERGE JOIN:

SQL> select * from emp e, dept d where e.empno > d.deptno;

Execution Plan
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |    56 |  3192 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                   |        |    56 |  3192 |     6  (17)| 00:00:01 |
|   2 |   SORT JOIN                   |        |    14 |   518 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   518 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|*  5 |   SORT JOIN                   |        |     4 |    80 |     4  (25)| 00:00:01 |
|   6 |    TABLE ACCESS FULL          | DEPT   |     4 |    80 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(INTERNAL_FUNCTION("E"."EMPNO")>INTERNAL_FUNCTION("D"."DEPTNO"))
filter(INTERNAL_FUNCTION("E"."EMPNO")>INTERNAL_FUNCTION("D"."DEPTNO"))

В случае с отсортироваными значениями одна из операций SORT JOIN исключается за ненадобностью:

SQL> select * from
2  (select empno from emp order by 1) e,
3  (select deptno from dept order by 1) d
4  where e.empno > d.deptno;

Execution Plan
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    56 |   896 |     3  (34)| 00:00:01 |
|   1 |  MERGE JOIN        |         |    56 |   896 |     3  (34)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|*  3 |   SORT JOIN        |         |    14 |   182 |     2  (50)| 00:00:01 |
|   4 |    VIEW            |         |    14 |   182 |     1   (0)| 00:00:01 |
|   5 |     INDEX FULL SCAN| PK_EMP  |    14 |    56 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."EMPNO">"DEPTNO")
filter("E"."EMPNO">"DEPTNO")

Подсказки:

SQL> select name, class, inverse, version from v$sql_hint where name = 'USE_MERGE';

NAME       CLASS  INVERSE       VERSION
---------- ------ ------------- -------
USE_MERGE  JOIN   NO_USE_MERGE  8.1.0

Параметры:

NAME                               VALUE  IS_DEF   IS_MOD     IS_ADJ   DSC
---------------------------------- ------ -------- ---------- -------- -------------------------------------
_optimizer_sortmerge_join_enabled  TRUE   TRUE     FALSE      FALSE    enable/disable sort-merge join method
SORT UNIQUE NOSORT

операция последовательной сортировки результатов одноблочного чтения для получения набора неповторяющихся значений, применяемая при выполнении SELECT DISTINCT

sort unique nosort и кэширование индексных блоков

LOAD AS SELECT

отражает операции параллельной или непараллельной прямой загрузки данных (direct-path DML|DDL) при операциях:  CREATE TABLE AS SELECT, INSERT …SELECT,…

Julian Dyke отмечает, что в ранних версиях операция наблюдалась только при выполнении EXPLAIN PLAN, и не наблюдается при реальном выполнении (AUTOTRACE или V$SQL_PLAN), начиная с Oracle 10.2 операцию можно наблюдать в выполняемых планах оптимизатора

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

INDEX MAINTENANCE

«… производится перестроение индекса по окончании операций direct-path INSERT [в т.ч. в составе parallel MERGE]… Перестроение индекса выполняется PX процессами при parallel direct-path INSERT, либо клиентским процессом при непараллельном / serial direct-path INSERT. Для уменьшения влияния этой операции на производительность DML можно сделать индекс(ы) unusable перед загрузкой данных в таблицу (INSERT) с последующим перестроением индекс(ов)»

PX COORDINATOR FORCED SERIAL

Появление этой операции означает, что план или его часть, первоначально рассчитанные для параллельного выполнения, переключёны в последовательный режим (serial execution) по причине использования в запросе функций, не допускающих параллельного выполнения или других видов зависимостей от «непараллельных» функций (например, пользовательских типов в таблицах) — см. тестскейс http://oracle-randolf.blogspot.com/2011/03/px-coordinator-forced-serial-operation.html

Трейс оптимизатора при этом рапортует:

        Parallelism disabled at runtime because forced serial at compilation time

В случаях, когда Oracle не в состоянии самостоятельно определить безопасность PL/SQL кода для параллельного выполнения, рекомендуются следующие способы пометить функции, как пригодные для параллельного выполнения: How To Enable Parallel Query For A Function? [ID 1093773.1]:

1) предпочтительный метод — использовать PARALLEL_ENABLE в определении функции:

CREATE OR REPLACE Function F1 (x IN NUMBER)
RETURN NUMBER PARALLEL_ENABLE
IS ...

2) допустимый / устаревающий метод, пригодный только для определения функции в составе пакета:

CREATE OR REPLACE PACKAGE P1
 AS
 Function F1 (x IN NUMBER) RETURN NUMBER
 PRAGMA RESTRICT_REFERENCES (Proc1, WNDS, RNPS, WNPS);
 :
TEMP TABLE TRANSFORMATION

Преобразование промежуточного набора данных во временную таблицу во время выполнения запроса, может использоваться:

  • при материализации подзапросов в запросах с применением subquery factoring
  • в запросах, при выполнении которых используется star transformation

В случаях subquery factoring (WITH clause) временная таблица формируется при использовании подсказки /*+ MATERIALIZE*/ или без подсказки при выполнении определённых условий (например, количество использований таблицы-подзапроса в запросе):

SQL> with q as (select * from t where owner = 'SCOTT')
2  select * from q q1, q q2
3  where q1.table_name = q2.table_name
4  /

--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |       |       |    35 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   2 |   LOAD AS SELECT           |                           |       |       |            |          |
|*  3 |    TABLE ACCESS FULL       | T                         |   122 | 29402 |    28   (0)| 00:00:01 |
|*  4 |   HASH JOIN                |                           |     5 |  5410 |     7  (15)| 00:00:01 |
|   5 |    VIEW                    |                           |   122 | 66002 |     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6617_62613E |   122 | 29402 |     3   (0)| 00:00:01 |
|   7 |    VIEW                    |                           |   122 | 66002 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6617_62613E |   122 | 29402 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

3 - filter("OWNER"='SCOTT')
4 - access("Q1"."TABLE_NAME"="Q2"."TABLE_NAME")

Временная таблица SYS_TEMP_% строится на следующих шагах плана:

  • 3 — операция TABLE ACCESS FULL таблицы T с фильтром («OWNER»=’SCOTT’)
  • 2 — LOAD AS SELECT (при выполнении CREATE TABLE AS SELECT)
  • 1 — TEMP TABLE TRANSFORMATION — создание временной таблицы (global temporary table)

И затем, на шагах 6 и 8 сканируется только подготовленная временная таблица SYS_TEMP_% (исключая таким образом избыточный доступ к исходной таблице T)

MAT_VIEW CUBE ACCESS

Операция доступа к агрегированным данным MOLAP куба (Oracle OLAP Option), созданного с возможностями материализованного представления — cube materialized view.

Используется при выполнении  query rewrite, доступа с версии 11.1

На примере тестовой схемы OLAPTRAIN без  query rewrite запрос выполняется дорого с полным доступом к таблицам:

11.2.0.3.@OLAPTRAIN SQL> SELECT ch.class_name, pr.type_name, SUM(s.quantity)
  2    FROM olaptrain.sales_fact s,
  3         olaptrain.channels   ch,
  4         olaptrain.customers  cu,
  5         olaptrain.products   pr,
  6         olaptrain.times      t
  7   WHERE s.customer = cu.customer_key
  8     AND s.channel = ch.channel_key
  9     AND s.day_key = t.day_key
 10     and s.product = pr.item_key
 11     AND t.calendar_year_name = 'CY2007'
 12     and cu.country_name = 'China'
 13   GROUP BY cube(ch.class_name, pr.type_name)
 14  /

96 rows selected.

Elapsed: 00:00:01.15

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |    46 |  3542 |  8083   (2)| 00:01:37 |       |       |
|   1 |  VIEW                               |            |    46 |  3542 |  8083   (2)| 00:01:37 |       |       |
|   2 |   SORT GROUP BY                     |            |    46 |  3956 |  8083   (2)| 00:01:37 |       |       |
|   3 |    GENERATE CUBE                    |            |    46 |  3956 |  8083   (2)| 00:01:37 |       |       |
|   4 |     SORT GROUP BY                   |            |    46 |  3956 |  8083   (2)| 00:01:37 |       |       |
|*  5 |      HASH JOIN                      |            | 35027 |  2941K|  8081   (1)| 00:01:37 |       |       |
|   6 |       TABLE ACCESS FULL             | PRODUCTS   |  2713 | 59686 |    38   (0)| 00:00:01 |       |       |
|*  7 |       HASH JOIN                     |            | 35027 |  2189K|  8042   (1)| 00:01:37 |       |       |
|   8 |        TABLE ACCESS FULL            | CHANNELS   |    13 |   143 |     3   (0)| 00:00:01 |       |       |
|*  9 |        HASH JOIN                    |            | 35027 |  1812K|  8038   (1)| 00:01:37 |       |       |
|  10 |         PART JOIN FILTER CREATE     | :BF0000    |   365 |  5475 |    66   (0)| 00:00:01 |       |       |
|  11 |          PARTITION RANGE ALL        |            |   365 |  5475 |    66   (0)| 00:00:01 |     1 |1048575|
|* 12 |           TABLE ACCESS FULL         | TIMES      |   365 |  5475 |    66   (0)| 00:00:01 |     1 |1048575|
|* 13 |         HASH JOIN                   |            |   105K|  3896K|  7971   (1)| 00:01:36 |       |       |
|* 14 |          TABLE ACCESS FULL          | CUSTOMERS  |  3943 | 59145 |  4033   (1)| 00:00:49 |       |       |
|  15 |          PARTITION RANGE JOIN-FILTER|            |  2812K|    61M|  3924   (2)| 00:00:48 |:BF0000|:BF0000|
|  16 |           TABLE ACCESS FULL         | SALES_FACT |  2812K|    61M|  3924   (2)| 00:00:48 |:BF0000|:BF0000|
------------------------------------------------------------------------------------------------------------------

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

   5 - access("S"."PRODUCT"="PR"."ITEM_KEY")
   7 - access("S"."CHANNEL"="CH"."CHANNEL_KEY")
   9 - access("S"."DAY_KEY"="T"."DAY_KEY")
  12 - filter("T"."CALENDAR_YEAR_NAME"='CY2007')
  13 - access("S"."CUSTOMER"="CU"."CUSTOMER_KEY")
  14 - filter("CU"."COUNTRY_NAME"='China')

Statistics
----------------------------------------------------------
      17571  consistent gets
      17478  physical reads  -- serial direct path read
          0  redo size
...
          2  sorts (memory)
          0  sorts (disk)
         96  rows processed

При использовании  query rewrite данные получаются напрямую из куба CB$SALES_CUBE, значительно уменьшая стоимость и воемя выполнения:

11.2.0.3.@OLAPTRAIN SQL> alter session set query_rewrite_integrity = TRUSTED
  2  /

Session altered.

11.2.0.3.@OLAPTRAIN SQL> SELECT ch.class_name, pr.type_name, SUM(s.quantity)
  2    FROM olaptrain.sales_fact s,
  3         olaptrain.channels   ch,
  4         olaptrain.customers  cu,
  5         olaptrain.products   pr,
  6         olaptrain.times      t
  7   WHERE s.customer = cu.customer_key
  8     AND s.channel = ch.channel_key
  9     AND s.day_key = t.day_key
 10     and s.product = pr.item_key
 11     AND t.calendar_year_name = 'CY2007'
 12     and cu.country_name = 'China'
 13   GROUP BY cube(ch.class_name, pr.type_name)
 14  /

96 rows selected.

Elapsed: 00:00:00.07

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |     4 |   308 |    31  (10)| 00:00:01 |
|   1 |  VIEW                            |               |     4 |   308 |    31  (10)| 00:00:01 |
|   2 |   UNION-ALL                      |               |       |       |            |          |
|   3 |    HASH GROUP BY                 |               |     1 |   125 |    12   (9)| 00:00:01 |
|*  4 |     MAT_VIEW REWRITE CUBE ACCESS | CB$SALES_CUBE |     1 |   125 |    11   (0)| 00:00:01 |
|   5 |    HASH GROUP BY                 |               |     1 |    73 |     4  (25)| 00:00:01 |
|*  6 |     MAT_VIEW REWRITE CUBE ACCESS | CB$SALES_CUBE |     1 |    73 |     3   (0)| 00:00:01 |
|   7 |    HASH GROUP BY                 |               |     1 |   113 |    12   (9)| 00:00:01 |
|*  8 |     MAT_VIEW REWRITE CUBE ACCESS | CB$SALES_CUBE |     1 |   113 |    11   (0)| 00:00:01 |
|   9 |    SORT GROUP BY NOSORT          |               |     1 |    61 |     3   (0)| 00:00:01 |
|* 10 |     MAT_VIEW REWRITE CUBE ACCESS | CB$SALES_CUBE |     1 |    61 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   4 - filter("CB$SALES_CUBE"."D2_CALENDAR_YEAR_LONG_DESCR"='CY2007' AND
              "CB$SALES_CUBE"."D3_COUNTRY_LONG_DESCRIPTION"='China' AND
              "CB$SALES_CUBE"."SYS_GID"=120793956415)
   6 - filter("CB$SALES_CUBE"."D2_CALENDAR_YEAR_LONG_DESCR"='CY2007' AND
              "CB$SALES_CUBE"."D3_COUNTRY_LONG_DESCRIPTION"='China' AND
              "CB$SALES_CUBE"."SYS_GID"=120793989119)
   8 - filter("CB$SALES_CUBE"."D2_CALENDAR_YEAR_LONG_DESCR"='CY2007' AND
              "CB$SALES_CUBE"."D3_COUNTRY_LONG_DESCRIPTION"='China' AND
              "CB$SALES_CUBE"."SYS_GID"=533110816831)
  10 - filter("CB$SALES_CUBE"."D2_CALENDAR_YEAR_LONG_DESCR"='CY2007' AND
              "CB$SALES_CUBE"."D3_COUNTRY_LONG_DESCRIPTION"='China' AND
              "CB$SALES_CUBE"."SYS_GID"=533110849535)

Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
         33  consistent gets
          0  physical reads
          0  redo size
          1  sorts (memory)
          0  sorts (disk)
         96  rows processed

Те же данные можно получить прямым запросом к кубу, например через mview с вышеуказанными в Predicate Information условиями:

11.2.0.3.@OLAPTRAIN SQL> select cb$sales_cube.d1_class_long_description,
  2         cb$sales_cube.d4_type_long_description,
  3         cb$sales_cube.quantity
  4    from olaptrain.cb$sales_cube cb$sales_cube
  5   where ("CB$SALES_CUBE"."D2_CALENDAR_YEAR_LONG_DESCR" = 'CY2007' AND
  6         "CB$SALES_CUBE"."D3_COUNTRY_LONG_DESCRIPTION" = 'China' AND
  7         "CB$SALES_CUBE"."SYS_GID" = 120793956415)
  8      or ("CB$SALES_CUBE"."D2_CALENDAR_YEAR_LONG_DESCR" = 'CY2007' AND
  9         "CB$SALES_CUBE"."D3_COUNTRY_LONG_DESCRIPTION" = 'China' AND
 10         "CB$SALES_CUBE"."SYS_GID" = 120793989119)
 11      or ("CB$SALES_CUBE"."D2_CALENDAR_YEAR_LONG_DESCR" = 'CY2007' AND
 12         "CB$SALES_CUBE"."D3_COUNTRY_LONG_DESCRIPTION" = 'China' AND
 13         "CB$SALES_CUBE"."SYS_GID" = 533110816831)
 14      or ("CB$SALES_CUBE"."D2_CALENDAR_YEAR_LONG_DESCR" = 'CY2007' AND
 15         "CB$SALES_CUBE"."D3_COUNTRY_LONG_DESCRIPTION" = 'China' AND
 16         "CB$SALES_CUBE"."SYS_GID" = 533110849535)
 17  /

96 rows selected.

Elapsed: 00:00:00.05

---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |   120 |    29   (0)| 00:00:01 |
|*  1 |  MAT_VIEW CUBE ACCESS | CB$SALES_CUBE |     1 |   120 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - filter("CB$SALES_CUBE"."D3_COUNTRY_LONG_DESCRIPTION"='China' AND
              "CB$SALES_CUBE"."D2_CALENDAR_YEAR_LONG_DESCR"='CY2007' AND
              ("CB$SALES_CUBE"."SYS_GID"=120793956415 OR
              "CB$SALES_CUBE"."SYS_GID"=120793989119 OR
              "CB$SALES_CUBE"."SYS_GID"=533110816831 OR
              "CB$SALES_CUBE"."SYS_GID"=533110849535))
PARTITION LIST EMPTY

Операция не предполагает выполнения последующих / дочерних операций ни с одной из партиций

Наблюдалась в 11.2.0.3 при ошибочном применении преобразования Table Expansion в виде:

-------------------------------------------------------------------------+-----------------+
| Operation                          | Rows  | Bytes | Cost  | Time      |  Pstart| Pstop  |
-------------------------------------------------------------------------+-----------------+
 ...
| PARTITION LIST EMPTY               |     1 |   140 |     4 |  00:00:01 | INVALID| INVALID|
|  TABLE ACCESS BY LOCAL INDEX ROWID |     1 |   140 |     4 |  00:00:01 | INVALID| INVALID|
|   INDEX RANGE SCAN                 |     1 |       |     3 |  00:00:01 | INVALID| INVALID|
 ...

доступ к партициям при этой операции определялся следующими противоречивыми предикатами по ключу партицирования:

29 - access("B"."APPLICATION_ID"=200)
29 - filter("B"."APPLICATION_ID"=140)

Методы преобразования запросов (query transformation)

Join Elimination (JE)

What is join elimination [ID 858120.1]

При наличии ограничений целостности на столбцах, по которым выполняется соединение таблиц в запросе [например, PK->FK], трансформация типа join elimination может исключать из запроса таблицу в случае, когда сам факт соединения таблиц не влияет на результат запроса [т.е. правильное выполнение запроса не требует обращения к исключаемой таблице]

Контролирующий параметр:

NAME                                 VALUE  IS_DEF  IS_MOD  IS_ADJ  DSC
------------------------------------ ------ ------- ------- ------- ----------------------------------
_optimizer_join_elimination_enabled  TRUE   TRUE    FALSE   FALSE   optimizer join elimination enabled

“Бага в Оракле”: JOIN ELIMINATION

First K Rows Optimization

Применяется в процессе построения плана запроса, использующего функцию ROWNUM в условиях (rownum predicate)

Версии Oracle 10.1+

Query Slow With Rownum Predicate [ID 833286.1] — на уровне сессии/системы управляется параметром:

SQL> @param_ _optimizer_rownum_pred_based_fkr

NAME                                       VALUE   IS_DEF   IS_MOD     IS_ADJ   DSC
------------------------------------------ ------- -------- ---------- -------- ------------------------------------------------------
_optimizer_rownum_pred_based_fkr           TRUE    TRUE     FALSE      FALSE    enable the use of first K rows due to rownum predicate

— на уровне запроса с использованием подсказки OPT_PARAM параметр (в 11.2.0.3) не меняется

First K Rows optimization

Star Transformation (ST)

преобразование запроса с целью исключения обработки большого количества строк таблиц с фактическими данными (fact table) за счёт комбинированного индексного доступа по подготовленному набору внешних ключей, полученных из сканирования/фильтрации небольших справочных таблиц (dimension table)

Для выполнения этой трансформации поля таблицы фактов, по которым выполняется соединение должны быть проиндексированы — по этим индексаи будет выполняться доступ к большой таблице (fact) с использованием ключей, полученных в результате bitmap операций (BITMAP AND, BITMAP MERGE, BITMAP KEY ITERATION, ) над битовыми картами ключей, полученными из bitmap-индексов или регулярных B-tree индексов справочных таблиц (dimension)

Optimizer Transformations: Star Transformation

Параметры:

11.2.0.3.@SQL> @param star_transformation_enabled

NAME                         VALUE  IS_DEF  IS_MOD  DSC
---------------------------- ------ ------- ------- -------------------------------------
star_transformation_enabled  FALSE  TRUE    FALSE   enable the use of star transformation

11.2.0.3.@SQL> @pvalid star_transformation_enabled

Valid values for parameters %star_transformation_enabled%

PARAMETER                    VALUE  DEFAULT
---------------------------- ------ -------
star_transformation_enabled  FALSE  DEFAULT -- CBO не пытается использоватьтрансформацию, по умолчанию
                             TRUE           -- CBO может выполнить трансформацию, автоопределяя таблицы фактов и справочники, на основе стоимости
                                            -- + при этом для улучшения производительности может использоваться temporary table transformation
                             TEMP_DISABLE   -- трансформация допустима (TRUE), но без использования temporary table transformation
Set to Join Conversion (SJC)
SQL> -- управляющий параметр с версии 10.1, по умолчанию FALSE
SQL> @param_ _convert_set_to_join

NAME                  VALUE  IS_DEF   DSC
--------------------- ------ -------- ------------------------------------------
_convert_set_to_join  FALSE  TRUE     enables conversion of set operator to join

SQL> select deptno
  2    from dept
  3  minus
  4  select deptno from emp
  5  /

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

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |       |       |     6 (100)|          |
|   1 |  MINUS              |         |       |       |            |          |
|   2 |   SORT UNIQUE NOSORT|         |     4 |    12 |     2  (50)| 00:00:01 |
|   3 |    INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |         |    14 |    42 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP     |    14 |    42 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1                                                  -- ***
   3 - SEL$1 / DEPT@SEL$1
   5 - SEL$2 / EMP@SEL$2

SQL> select--+ opt_param('_convert_set_to_join' 'true')
  2   deptno from dept
  3  minus
  4  select deptno from emp
  5  /

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

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |     5 (100)|          | -- формально более дешёвый план
|*  1 |  HASH JOIN ANTI    |         |     1 |     6 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP     |    14 |    42 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      ...
      SET_TO_JOIN(@"SET$1")
      ...
      END_OUTLINE_DATA
  */

SQL> -- применение подсказки из OUTLINE
SQL> select --+ set_to_join(@set$1)
  2   deptno from dept
  3  minus
  4  select deptno from emp
  5  /

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     6 |     5  (20)| 00:00:01 | -- даёт тот же план
|*  1 |  HASH JOIN ANTI    |         |     1 |     6 |     5  (20)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP     |    14 |    42 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Query transformation – Part 1

RE: Minus conversion into antijoin

Filter Push-Down (FPD)

преобразование, позволяющее не только «опускать» условия внешнего запроса в используемые Inline View (аналогично Complex View Merging), но и логически генерировать дополнительные предикаты, которые могут быть использованы, например, для Partition Pruning:

Filter Push-Down и избыточные предикаты

Методы преобразования запросов на основе стоимости (Cost-Based QueryTtransformation | CBQT)

Complex View Merging

Complex View Merging — Example [ID 277009.1]

Параметр

NAME                  VALUE  IS_DEF  DSC
--------------------- ------ ------- ---------------------------
_complex_view_merging TRUE   TRUE    enable complex view merging

Подсказка MERGE

Group by Placement

— обратное по отношению к Complex View Merging преобразование

Пример J.Lewis: Group By

Подсказки:

SQL> select name, inverse, version from v$sql_hint where class = 'PLACE_GROUP_BY';

NAME               INVERSE            VERSION
------------------ ------------------ --------
PLACE_GROUP_BY     NO_PLACE_GROUP_BY  11.1.0.6
NO_PLACE_GROUP_BY  PLACE_GROUP_BY     11.1.0.6

Параметр:

NAME                          VALUE IS_DEF DSC
----------------------------- ----- ------ ----------------------------------------
_optimizer_group_by_placement TRUE  TRUE   consider group-by placement optimization
Distinct Aggregate Transformation

DAGG_TRANSFORM

Table Expansion

Преобразование, позволяющее при выполнении запросов к партиционированным таблицам использовать отличные пути доступа в зависимости от состояния (USABLE/UNUSABLE) партиций локальных индексов

Отображается в плане в виде VIEW VW_TE_2, цифра отражает номер итерации:

TE: Starting iteration 1, state space = (13) : (0)
...
TE: Updated best state, Cost = 310.62
TE: Starting iteration 2, state space = (13) : (1)
...
TE: Updated best state, Cost = 309.65
TE:   Transferring best state space to preseved query.
TE: Will expand table #13 named XLA_AE_LINES (Preserved)
TE:   Transferring best state space to preseved query.
TE: Will expand table #13 named XLA_AE_LINES (Original)

Управление:

  • подсказками /*+ EXPAND_TABLE(t) */ /*+ NO_EXPAND_TABLE(t) */
  • параметром opt_param(‘_optimizer_table_expansion’ ‘false’) на уровне сессии / запроса
  • отключением Table Expansion в составе полного набора CBQT преобразований параметром opt_param( ‘_optimizer_cost_based_transformation’ ‘off’), либо вместе со всеми преобразованиями запросов подсказкой /*+ NO_QUERY_TRANSFORMATION*/
  • понижением версии оптимизатора < 11.2.0.1, напимер /*+ OPTIMIZER_FEATURES_ENABLE(’11.1.0.7′)*/

Особенности преобразования Table Expansion

Join Predicate Push-Down (JPPD)

Преобразование, позволяющее использовать nested-loop join  для соединения внешнего блока запроса с обзором без объединения (merge). При этом используется [потенциально более предпочтительной] индексный доступ к таблицам коррелированного обзора на основе условий соединения с внешним блоком запроса. В отличие от Old-style JPPD (OJPPD), при использовании JPPD применяется стоимостной подход (+ некоторые правила)

В версиях до 11g преобразование применялось со значительными ограничениями, например:

Predicate pushdown for outer-joined views with GROUP BY

Начиная с 11g, JPPD поддерживается на след.типах обзоров, используемых в запросах:

  • UNION ALL/UNION view
  • Outer-joined view
  • Anti-joined view
  • Semi-joined view
  • DISTINCT view
  • GROUP-BY view

Операции в плане выполнения:

SQL> select distinct operation from  gv$sql_plan where operation like '%PUSH%';

OPERATION
--------------------------
UNION ALL PUSHED PREDICATE
VIEW PUSHED PREDICATE

SQL> -- и управляющие подсказки
SQL> select name, inverse from v$sql_hint where class = 'PUSH_PRED';

NAME           INVERSE
-------------- ------------
PUSH_PRED      NO_PUSH_PRED
NO_PUSH_PRED   PUSH_PRED

Основная информация и примеры в блоге Oracle Optimizer

Join Predicate Push-Down, допускающий картезианское произведение

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

Join Factorization
Внутренние обзоры в планах выполнения как результат трансформации запросов (query transformation)

J.Lewis: Internal Views

What is a «VW_NSO_1» View? (Doc ID 117182.1): «… при компиляции запросов, для которых внутренний nested subquery трансформирован в обзор (view), который, в свою очередь, не может быть объединён (merged) [с основным запросом], обзор [VW_NSO_1] появляется в виде отдельной структуры плана выполнения…»

tbd

2 комментария »

  1. Хороший FAQ, почерпнул много нового.

    комментарий от blacksaifer — 28.08.2012 @ 08:06 | Ответить

  2. Спасибо за статью. Все по методоам доступа в одном месте. За вторую книжку в списке литературы — отдельный респект!

    комментарий от nord_tramper — 28.08.2012 @ 17:11 | Ответить


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