Ссылки по теме:
- Описание методов обработки данных, используемых оптимизатором, в документации Oracle 19c. SQL Tuning Guide. Optimizer Access Paths
- Отличный учебник от команды разработчиков The Oracle Optimizer Explain the Explain Plan
- Jože Senegačnik. Under The Hood Of Query Transformations
- White Papers and Blog Entries for Oracle Optimizer (Doc ID 1337116.1)
Описание некоторых методов доступа к данным (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! Объединение(выбор) планов по условию в общем плане выполнения обозначается как
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)
- Oracle 11g: стоимость операции FILTER и OR-Expansion
- в описании операции CONCATENATION
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))
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
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-х шагов:
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
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
Space Management and Oracle Direct Path Load — описание и предствление с бд:
12.2@ SQL> select distinct operation || ' ' || options from dba_hist_sql_plan where operation like 'LOAD%' order by 1; OPERATION||''||OPTIONS --------------------------------------------- LOAD AS SELECT LOAD AS SELECT (CURSOR DURATION MEMORY) -- TEMP TABLE TRANSFORMATION in 12.2 LOAD AS SELECT (HIGH WATER MARK BROKERED) LOAD AS SELECT (HYBRID TSM/HWMB) -- TEMP SEGMENT MERGE/HIGH WATER MARK BROKERED LOAD AS SELECT (TEMP SEGMENT MERGE) LOAD TABLE CONVENTIONAL
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) не меняется
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 |
------------------------------------------------------------------------------
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
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
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′)*/
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
JPPD: JPPD bypassed: View has non-standard group by.
SQL> @fix 19046459 BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT -------- ----- -------------------- ---------------------------------------------------------------- ------------------------- ----- ---------- 19046459 1 QKSFM_JPPD_19046459 no OJPPD rejection on non-pushable predicate when valid exists 12.2.0.1 0 1
Join Factorization
Expression Evaluation
Parallel Correlated Filters and Expressions in 12c
OR Expansion
- CONCATENATION is replaced with UNION-ALL.
- Each UNION-ALL branch can be subject to further query transformations, if applicable. This is not possible with CONCATENATION.
- Parallel queries can execute UNION-ALL branches concurrently. Again, this is not possible with CONCATENATION.
Внутренние обзоры в планах выполнения как результат трансформации запросов (query transformation)
What is a «VW_NSO_1» View? (Doc ID 117182.1): «… при компиляции запросов, для которых внутренний nested subquery трансформирован в обзор (view), который, в свою очередь, не может быть объединён (merged) [с основным запросом], обзор [VW_NSO_1] появляется в виде отдельной структуры плана выполнения…»
tbd
Oracle mechanics 
Хороший FAQ, почерпнул много нового.
комментарий от blacksaifer — 28.08.2012 @ 08:06 |
Спасибо за статью. Все по методоам доступа в одном месте. За вторую книжку в списке литературы — отдельный респект!
комментарий от nord_tramper — 28.08.2012 @ 17:11 |