Ссылки по теме:
- Описание методов обработки данных, используемых оптимизатором, в документации Oracle Database Performance Tuning Guide: 11.2 Overview of Optimizer Access Paths
- Отличный учебник от команды разработчиков The Oracle Optimizer Explain the Explain Plan
Описание некоторых методов доступа к данным (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
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! Объединение(выбор) планов по условию в общем плане выполнения обозначается как
CONCATENATION
SQL> SELECT AA.ID,
...
10 FROM AA
11 WHERE (decode(:2091582, 1, AA.AFFILIATE_ID, :2091582) = AA.AFFILIATE_ID)
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 | | | | 3035 (100)| |
| 1 | SORT ORDER BY | | 113K| 21M| 3035 (1)| 00:00:37 |
| 2 | CONCATENATION | | | | | |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| AA | 113K| 21M| 2880 (1)| 00:00:35 |
|* 5 | INDEX SKIP SCAN | IX_AA_AFLID_DATE_INS | 1692 | | 1279 (0)| 00:00:16 |
|* 6 | FILTER | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| AA | 157 | 31400 | 154 (0)| 00:00:02 |
|* 8 | INDEX RANGE SCAN | IX_AA_AFLID_DATE_INS | 157 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter((TO_DATE(:SYS_B_6,:SYS_B_7)<=TO_DATE(:SYS_B_8,:SYS_B_9) AND
SYS_OP_MAP_NONNULL(:SYS_B_3)=SYS_OP_MAP_NONNULL(:SYS_B_4)))
5 - 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"."AFFILIATE_ID" IS NOT
NULL AND "AA"."DATE_INSERT"<=TO_DATE(:SYS_B_8,:SYS_B_9)))
6 - filter((TO_DATE(:SYS_B_6,:SYS_B_7)<=TO_DATE(:SYS_B_8,:SYS_B_9) AND
SYS_OP_MAP_NONNULL(:SYS_B_3)<>SYS_OP_MAP_NONNULL(:SYS_B_4)))
8 - access("AA"."AFFILIATE_ID"=:SYS_B_5 AND "AA"."DATE_INSERT">=TO_DATE(:SYS_B_6,:SYS_B_7)
AND "AA"."DATE_INSERT"<=TO_DATE(:SYS_B_8,:SYS_B_9))
- при этом общая стоимость плана просто складывается из стоимостей вариантов доступа:
3035~ 2880 +154
, что для условия с детерминированным результатом decode(:2091582, 1, AA.AFFILIATE_ID, :2091582) = AA.AFFILIATE_ID очевидно не совсем точно, т.к. запрос будет выполняться только по одному из предложенных оптимизатором вариантов, однако, это универсальная heuristic
В секции 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
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
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 | -----------------------------------------------------------------------------------
Подсказки:
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"
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
- 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
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 |
--------------------------------------------------------------------------------
Sort Merge Join
“Sort merge joins успешно используется, когда в условиях соединения двух таблиц присутствуют операторы сравнения: <, <=, >, или >= , но не для операторов равества/неравества. Производительность sort merge joins лучше, чем у nested loop joins для больших наборов данных (data sets). [Также следует обратить внимание на то, что ] вы не можете использовать [более производительные операции] hash joins для соединения таблиц, если условия соединения отличаются от равенства (equality condition). При выполнении операции merge join отсутствует концепция ведущей (driving table) / ведомой таблицы. Операция состоит из 2-х шагов:
Sort join: Оба источника входных данных / таблицы сортируются по ключу соединения (join key)
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
LOAD AS SELECT
Direct load using a SELECT statement as the source, используется при выполнении CREATE TABLE AS SELECT
Julian Dyke отмечает, что операция наблюдается только при выполнении EXPLAIN PLAN, и не наблюдается при реальном выполнении (AUTOTRACE or V$SQL_PLAN), однако по крайней мере начиная с Oracle 10.2 операцию можно наблюдать в выполняемых планах оптимизатора:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select version from v$instance; VERSION ----------------- 10.2.0.4.0 SQL> select OPERATION from v$sql_plan where sql_id = 'cscbhgn3v1a73' and id = 17; OPERATION ------------------------------------------------------------------------------------------ LOAD AS SELECT
sort unique nosort
операция последовательной сортировки результатов одноблочного чтения для получения набора неповторяющихся значений, применяемая при выполнении SELECT DISTINCT
sort unique nosort и кэширование индексных блоков
PX COORDINATOR FORCED SERIAL
План, рассчитанный для параллельного выполнения, переключён в последовательный режим (serial execution) по причине использования в запросе функций, не допускающих параллельного выполнения или других видов зависимостей от “непараллельных” функций (например, пользовательских типов в таблицах) – см. тестскейс http://oracle-randolf.blogspot.com/2011/03/px-coordinator-forced-serial-operation.html
Соответственно, рекомендованные способы включения параллельного выполнения функций: How To Enable Parallel Query For A Function? [ID 1093773.1]:
1) предпочтительный метод – использовать PARALLEL_ENABLE в определении функции
CREATE OR REPLACE PACKAGE P1 AS PROCEDURE Proc1 (x IN NUMBER) PARALLEL_ENABLE :
2) допустимый метод
CREATE OR REPLACE PACKAGE P1 AS PROCEDURE Proc1 (x IN NUMBER) PRAGMA RESTRICT_REFERENCES (Proc1, WNDS, RNPS, WNPS); :
Если в теле запроса явно не используются непараллельные фунции, можно использовать sql_dependency_tree.sql для определения неявных зависимостей запроса от функций / типов
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)
Методы преобразования запросов (query transformation)
Join Elimination
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) не меняется
Star Transformation
преобразование запроса с целью исключения обработки большого количества строк таблиц с фактическими данными (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
Методы преобразования запросов на основе стоимости (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
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
tbd
