XT&R blackboard: Список и описание новых хинтов в Oracle 11.1 - 11.2.0.3(включая недокументированные)

Увидев новые пару строк на 11.2.0.3 в списке хинтов(V$SQL_HINT), решил свести их описания в одну удобную заметку, попутно попытавшись разобраться в них.
Описание до конца еще не закончено, постараюсь сделать это на днях.

Cписок хинтов добавленных в 11g

NAME	INVERSE	VERSION
FULL_OUTER_JOIN_TO_OUTER	NO_FULL_OUTER_JOIN_TO_OUTER	11.2.0.3
OUTER_JOIN_TO_ANTI	NO_OUTER_JOIN_TO_ANTI	11.2.0.3
TABLE_LOOKUP_BY_NL	NO_TABLE_LOOKUP_BY_NL	11.2.0.2
USE_HASH_GBY_FOR_PUSHDOWN	NO_USE_HASH_GBY_FOR_PUSHDOWN	11.2.0.2
XDB_FASTPATH_INSERT	NO_XDB_FASTPATH_INSERT	11.2.0.2
APPEND_VALUES	NOAPPEND	11.2.0.1
COALESCE_SQ	NO_COALESCE_SQ	11.2.0.1
CONNECT_BY_ELIM_DUPS	NO_CONNECT_BY_ELIM_DUPS	11.2.0.1
DST_UPGRADE_INSERT_CONV	NO_DST_UPGRADE_INSERT_CONV	11.2.0.1
EXPAND_TABLE	NO_EXPAND_TABLE	11.2.0.1
FACTORIZE_JOIN	NO_FACTORIZE_JOIN	11.2.0.1
NO_SUBSTRB_PAD		11.2.0.1
PLACE_DISTINCT	NO_PLACE_DISTINCT	11.2.0.1
STATEMENT_QUEUING	NO_STATEMENT_QUEUING	11.2.0.1
TRANSFORM_DISTINCT_AGG	NO_TRANSFORM_DISTINCT_AGG	11.2.0.1
XMLINDEX_SEL_IDX_TBL		11.2.0.1
BIND_AWARE	NO_BIND_AWARE	11.1.0.7
CHANGE_DUPKEY_ERROR_INDEX		11.1.0.7
IGNORE_ROW_ON_DUPKEY_INDEX		11.1.0.7
RETRY_ON_ROW_CHANGE		11.1.0.7
CHECK_ACL_REWRITE	NO_CHECK_ACL_REWRITE	11.1.0.6
COST_XML_QUERY_REWRITE	NO_COST_XML_QUERY_REWRITE	11.1.0.6
DB_VERSION		11.1.0.6
DOMAIN_INDEX_FILTER	NO_DOMAIN_INDEX_FILTER	11.1.0.6
INDEX_RS_ASC		11.1.0.6
INDEX_RS_DESC		11.1.0.6
MONITOR	NO_MONITOR	11.1.0.6
NLJ_BATCHING	NO_NLJ_BATCHING	11.1.0.6
NLJ_PREFETCH	NO_NLJ_PREFETCH	11.1.0.6
NO_LOAD		11.1.0.6
OUTER_JOIN_TO_INNER	NO_OUTER_JOIN_TO_INNER	11.1.0.6
PLACE_GROUP_BY	NO_PLACE_GROUP_BY	11.1.0.6
RESULT_CACHE	NO_RESULT_CACHE	11.1.0.6
SUBQUERY_PRUNING	NO_SUBQUERY_PRUNING	11.1.0.6
USE_INVISIBLE_INDEXES	NO_USE_INVISIBLE_INDEXES	11.1.0.6
USE_MERGE_CARTESIAN		11.1.0.6
XML_DML_RWT_STMT		11.1.0.6
XMLINDEX_REWRITE	NO_XMLINDEX_REWRITE	11.1.0.6
XMLINDEX_REWRITE_IN_SELECT	NO_XMLINDEX_REWRITE_IN_SELECT	11.1.0.6
CONNECT_BY_CB_WHR_ONLY	NO_CONNECT_BY_CB_WHR_ONLY	10.2.0.5

FULL_OUTER_JOIN_TO_OUTER / NO_FULL_OUTER_JOIN_TO_OUTER

SQL feature:	QKSFM_CBO
Class:	FULL_OUTER_JOIN_TO_OUTER
Version:	11.2.0.3
Документирован	нет

Включает/выключает механизм трансформации запроса из full outer join в left outer join, появившийся и работающий по умолчанию с 11.2.0.2.
Помимо самого хинта, появившегося в 11.2.0.3, управлять этим преобразованием можно начиная с 11.2.0.2 через fix control (bugno=9287401) и с помощью:

OPT_PARAM('_optimizer_full_outer_join_to_outer' 'false')

или alter session set "_optimizer_full_outer_join_to_outer"=true;

Пример:

create table mss_foj1(id primary key, a, constraint uq_foj1_a unique(a) using index) as select level id, level*2-1 a from dual connect by level<=1e5;
create table mss_foj2(id primary key, a, constraint uq_foj2_a unique(a) using index) as select level id, level*2 a from dual connect by level<=1e5;
begin
   dbms_stats.gather_table_stats(user,'mss_foj1',cascade=>true);
   dbms_stats.gather_table_stats(user,'mss_foj2',cascade=>true);
end;
/

>> explain plan for
  2  select
  3   *
  4  from mss_foj1 f1
  5       full outer join mss_foj2 f2
  6                  on f1.id=f2.id
  7  where f1.a=3;

Explained.

Elapsed: 00:00:00.03
 >> @xplan

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

Plan hash value: 2311935082

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |              |     1 |    20 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MSS_FOJ1     |     1 |    10 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | UQ_FOJ1_A    |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| MSS_FOJ2     |   100K|   976K|     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | SYS_C0094323 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - access("F1"."A"=3)
   5 - access("F1"."ID"="F2"."ID"(+))

18 rows selected.

Elapsed: 00:00:00.18

>> alter session set "_optimizer_full_outer_join_to_outer"=false;

Session altered.

Elapsed: 00:00:00.01
 >> explain plan for
  2  select
  3   *
  4  from mss_foj1 f1
  5       full outer join mss_foj2 f2
  6                  on f1.id=f2.id
  7  where f1.a=3;

Explained.

Elapsed: 00:00:00.04
 >> @xplan

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

Plan hash value: 2034556344

------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |   100K|  5078K|       |  1684  (14)| 00:00:01 |
|*  1 |  VIEW                 | VW_FOJ_0 |   100K|  5078K|       |  1684  (14)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|          |   100K|  1953K|  2152K|  1684  (14)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | MSS_FOJ1 |   100K|   976K|       |   269  (22)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | MSS_FOJ2 |   100K|   976K|       |   268  (22)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - filter("F1"."A"=3)
   2 - access("F1"."ID"="F2"."ID")

17 rows selected.

Elapsed: 00:00:01.03

OUTER_JOIN_TO_ANTI / NO_OUTER_JOIN_TO_ANTI

SQL feature:	QKSFM_CBO
Class:	OUTER_JOIN_TO_ANTI
Version:	11.2.0.3
Документирован	нет

Судя по названию и скрытому параметру _optimizer_outer_to_anti_enabled (Enable transformation of outer-join to anti-join if possible), введенному еще в 10.2, этот хинт отвечает за трансформацию outer join'ов к anti-join, но мне, к сожалению, не удалось воспроизвести это, и буду рад, если кто-нибудь поделится примером. Скажу сразу, что я пробовал только простые запросы полные аналоги классического anti-join'a.

TABLE_LOOKUP_BY_NL / NO_TABLE_LOOKUP_BY_NL

SQL feature:	QKSFM_TABLE_LOOKUP_BY_NL
Class:	TABLE_LOOKUP_BY_NL
Version:	11.2.0.2
Документирован	нет

С этим хинтом у меня тоже, к сожалению, разобраться не получилось. Из описания параметра "_optimizer_enable_table_lookup_by_nl" - consider table lookup by nl transformation. Кстати говоря, этот параметр в 11.2.0.1 по умолчанию был false, а с 11.2.0.2 - true.
В первую очередь я подумал, что он связан с новым вариантом nested loops появившемся в 11g, но у него есть свой хинт - NLJ_BATCHING. Второй мыслью было о том, что хинт отвечает за включение/выключение этапа анализа для возможности NLJ_BATCHING, но добавление хинта в запросы с nested loops планов не меняло(попозже попробую проанализировать трассировку 10053 с ним на различных nl - будет ли видно, что он добавляет/убирает). Третью мыслью было, что это относится к механизму table lookup prefetch, но тоже не получилось их как-то связать, т.к. у того тоже есть свое название - NLJ_PREFETCH.

В общем тоже прощу сообщить, если узнаете за что этот хинт отвечает.

USE_HASH_GBY_FOR_PUSHDOWN / NO_USE_HASH_GBY_FOR_PUSHDOWN

SQL feature:	USE_HASH_GBY_FOR_PUSHDOWN
Class:	USE_HASH_GBY_FOR_PUSHDOWN
Version:	11.2.0.2
Документирован	нет

И еще один хинт с которым не получилось разобраться: тоже судя по названию только догадываюсь о том, что этот хинт отвечает за использование hash group by для view pushed predicate, но сделать так, чтобы он хоть что-нибудь менял в 10053 мне не удалось. При этом, если моя догадка верна, непонятно где именно должен быть hash group by - по идее для hash group by задумывался use_hash_aggregation.
Правда он черезчур загадочный: например, даже при появлении его в плане в секции outline, в самом плане может быть sort group by:

ORCL/XTENDER> explain plan for
  2  with v_push as (
  3       select
  4          t_8k.c100
  5          ,count(*) s
  6       from  t_4k
  7            ,t_8k
  8       where t_4k.col_unique=t_8k.col_unique
  9       group by t_8k.c100
 10  )
 11  select
 12    *
 13  from
 14       t_1k
 15      ,v_push
 16  where t_1k.col_unique=v_push.c100(+)
 17        and t_1k.c3=5;

Explained.

Elapsed: 00:00:00.23
ORCL/XTENDER> @xplan

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

Plan hash value: 438187520

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     3 |    96 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER             |              |     3 |    96 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | T_1K         |     3 |    51 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN             | IX_T_1K_C3   |     3 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW PUSHED PREDICATE         |              |     1 |    15 |     2   (0)| 00:00:01 |
|   5 |    SORT GROUP BY                |              |     1 |    11 |     2   (0)| 00:00:01 |
|   6 |     NESTED LOOPS                |              |   100 |  1100 |     2   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| T_8K         |   100 |   700 |     2   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | IX_T_8K_C100 |   100 |       |     1   (0)| 00:00:01 |
|*  9 |      INDEX UNIQUE SCAN          | PK_T_4K      |     1 |     4 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   1 - SEL$2
   2 - SEL$2        / T_1K@SEL$2
   3 - SEL$2        / T_1K@SEL$2
   4 - SEL$9113C594 / V_PUSH@SEL$2
   5 - SEL$9113C594
   7 - SEL$9113C594 / T_8K@SEL$1
   8 - SEL$9113C594 / T_8K@SEL$1
   9 - SEL$9113C594 / T_4K@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$9113C594")
      USE_NL(@"SEL$9113C594" "T_4K"@"SEL$1")
      LEADING(@"SEL$9113C594" "T_8K"@"SEL$1" "T_4K"@"SEL$1")

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

      INDEX(@"SEL$9113C594" "T_4K"@"SEL$1" ("T_4K"."COL_UNIQUE"))
      INDEX_RS_ASC(@"SEL$9113C594" "T_8K"@"SEL$1" ("T_8K"."C100"))
      USE_NL(@"SEL$2" "V_PUSH"@"SEL$2")
      LEADING(@"SEL$2" "T_1K"@"SEL$2" "V_PUSH"@"SEL$2")
      NO_ACCESS(@"SEL$2" "V_PUSH"@"SEL$2")
      INDEX_RS_ASC(@"SEL$2" "T_1K"@"SEL$2" ("T_1K"."C3"))
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      PUSH_PRED(@"SEL$2" "V_PUSH"@"SEL$2" 1)
      OUTLINE_LEAF(@"SEL$9113C594")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   3 - access("T_1K"."C3"=5)
   8 - access("T_8K"."C100"="T_1K"."COL_UNIQUE")
   9 - access("T_4K"."COL_UNIQUE"="T_8K"."COL_UNIQUE")

XDB_FASTPATH_INSERT / NO_XDB_FASTPATH_INSERT

SQL feature:	QKSFM_ALL
Class:	XDB_FASTPATH_INSERT
Version:	11.2.0.2
Документирован	нет

Не документирован, но имеет свою ошибку:

ORA-19051: Cannot use fast path insert for this XMLType table

   Cause
   An attempt was made to insert using event 19049 into an XMLType table that does not support fast path insert.

   Action
   Unset event 19049 and try again.

APPEND_VALUES / NOAPPEND

SQL feature:	QKSFM_CBO
Class:	APPEND_VALUES
Version:	11.2.0.1
Документирован	да

Включает механизм direct path inserts аналогичный insert/*+ append*/ … select, но для insert into … values.

Пример:

forall i in c.first..c.last
insert/*+ APPEND_VALUES*/ values c(i);

Дополнительно: 1

COALESCE_SQ / NO_COALESCE_SQ

SQL feature:	QKSFM_COALESCE_SQ
Class:	COALESCE_SQ
Version:	11.2.0.1
Документирован	нет

Включает механизм удаления лишних join'ов c какой-либо таблицей(join elimination) в случаях подзапросов.

ORCL/XTENDER> explain plan for
  2  select * from mss_foj1 f1
  3  where
  4      exists(select * from mss_foj2 f2 where f2.id=f1.a)
  5  and exists(select * from mss_foj2 f2 where f2.id=f1.a);

Explained.

ORCL/XTENDER> @xplan

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

Plan hash value: 4138783270

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              | 50000 |   732K|    71  (15)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |              | 50000 |   732K|    71  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| MSS_FOJ1     |   100K|   976K|    62   (2)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C0014388 | 50000 |   244K|     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - SEL$82F4A621
   2 - SEL$82F4A621 / F1@SEL$1
   3 - SEL$82F4A621 / F2@SEL$2

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

  /*+
      BEGIN_OUTLINE_DATA
      ...
      COALESCE_SQ(@"SEL$2")
      COALESCE_SQ(@"SEL$3")
      ...
      END_OUTLINE_DATA
  */

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

   3 - access("F2"."ID"="F1"."A")

ORCL/XTENDER> explain plan for
  2  select/*+ NO_COALESCE_SQ(@SEL$2) NO_UNNEST(@SEL$2) */ * from mss_foj1 f1
  3  where
  4      exists(select * from mss_foj2 f2 where f2.id=f1.a)
  5  and exists(select * from mss_foj2 f2 where f2.id=f1.a)
  6  /

Explained.

Elapsed: 00:00:00.08
ORCL/XTENDER> @xplan

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

Plan hash value: 2240918256

------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |     1 |    15 | 39164   (1)| 00:07:50 |
|*  1 |  FILTER             |              |       |       |            |          |
|   2 |   NESTED LOOPS SEMI |              | 50000 |   732K|    71  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| MSS_FOJ1     |   100K|   976K|    62   (2)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0014388 | 50000 |   244K|     0   (0)| 00:00:01 |
|*  5 |   INDEX UNIQUE SCAN | SYS_C0014388 |     1 |     5 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST NO_COALESCE_SQ */ 0 FROM
              "MSS_FOJ2" "F2" WHERE "F2"."ID"=:B1))
   4 - access("F2"."ID"="F1"."A")
   5 - access("F2"."ID"=:B1)

Дополнительно:
1 2 3

CONNECT_BY_ELIM_DUPS / NO_CONNECT_BY_ELIM_DUPS

SQL feature:	QKSFM_ALL
Class:	CONNECT_BY_ELIM_DUPS
Version:	11.2.0.1
Документирован	нет

Включает механизм отсева дубликатов в иерархическом запросе(в строке плана с connect by появляется (UNIQUE), например "CONNECT BY NO FILTERING WITH SW (UNIQUE))", при выключенном просто "CONNECT BY NO FILTERING WITH START-WITH]".

select/*+ NO_CONNECT_BY_ELIM_DUPS */ distinct *
from t_connect_by
start with a=1
connect by prior a+1 = a

DST_UPGRADE_INSERT_CONV / NO_DST_UPGRADE_INSERT_CONV

SQL feature:	QKSFM_ALL
Class:	DST_UPGRADE_INSERT_CONV
Version:	11.2.0.1
Документирован	отчасти

Отвечает за работу внутренних операторов с конвертацией столбцов TIMESTAMP WITH TIMEZONE (TSTZ), которые ещё не были обновлены во время перехода на летнее время.

EXPAND_TABLE / NO_EXPAND_TABLE

SQL feature:	QKSFM_TABLE_EXPANSION
Class:	EXPAND_TABLE
Version:	11.2.0.1
Документирован	нет

В 11gR2 введен новый механизм, позволяющий использовать разные пути доступа к секциям в случаях, когда индексы в некоторых секциях изменены на unusable - это позволяет иметь отключать индексы на нагруженных dml секциях. Механизм хорошо описан тут. За включение механизма отвечает скрытый параметр _optimizer_table_expansion - consider table expansion transformation.

Пример:

create table t_expanded(part_key integer, field1 integer, field2 integer, val number)
partition by list(part_key)
(
    partition p1 values(1)
   ,partition p2 values(2)
   ,partition p3 values(3)
   ,partition p4 values(4)
   ,partition p5 values(5)
)
/
create index ix_t_expanded1 on t_expanded(field1, field2) local;
create index ix_t_expanded2 on t_expanded(field2) local;
alter index ix_t_expanded1 modify partition p1 unusable;
alter index ix_t_expanded2 modify partition p2 unusable;
/
insert/*+ APPEND */ into t_expanded
with t as (select level p from dual connect by level<=5)
    ,gen as (select level val from dual connect by level<=1e4)
select t.p
      ,decode(t.p,1,mod(val,3),mod(val,20))
      ,trunc(dbms_random.value(1,1000))
      ,dbms_random.normal
from t
    ,gen
/
select * from t_expanded te where te.field1=:a and te.field2=:b
/
begin 
   dbms_stats.gather_table_stats(null,'t_expanded',cascade=>true);
end;
/

>> explain plan for
  2  select/*+ NO_EXPAND_TABLE(TE) */
  3  *
  4  from t_expanded te
  5  where te.field1=:a
  6    and te.field2=:b;

Explained.

Elapsed: 00:00:00.03
 >> @advanced

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 2740702834

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     3 |    96 |   398  (16)| 00:00:01 |       |       |
|   1 |  PARTITION LIST ALL|            |     3 |    96 |   398  (16)| 00:00:01 |     1 |     5 |
|*  2 |   TABLE ACCESS FULL| T_EXPANDED |     3 |    96 |   398  (16)| 00:00:01 |     1 |     5 |
-------------------------------------------------------------------------------------------------

>> explain plan for
  2  select *
  3  from t_expanded te
  4  where te.field1=:a
  5    and te.field2=:b;

Explained.

Elapsed: 00:00:00.04
>> @advanced

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 527277835

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |     3 |    96 |   242  (15)| 00:00:01 |       |       |
|   1 |  VIEW                                | VW_TE_2        |     6 |   312 |   242  (15)| 00:00:01 |       |       |
|   2 |   UNION-ALL                          |                |       |       |            |          |       |       |
|   3 |    PARTITION LIST INLIST             |                |     2 |    64 |   240  (15)| 00:00:01 |KEY(I) |KEY(I) |
|*  4 |     TABLE ACCESS FULL                | T_EXPANDED     |     2 |    64 |   240  (15)| 00:00:01 |KEY(I) |KEY(I) |
|   5 |    PARTITION LIST SINGLE             |                |     3 |    96 |     1   (0)| 00:00:01 |     1 |     1 |
|*  6 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_EXPANDED     |     3 |    96 |     1   (0)| 00:00:01 |     1 |     1 |
|*  7 |      INDEX RANGE SCAN                | IX_T_EXPANDED2 |    10 |       |     1   (0)| 00:00:01 |     1 |     1 |
|   8 |    PARTITION LIST SINGLE             |                |     1 |    32 |     1   (0)| 00:00:01 |     2 |     2 |
|   9 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_EXPANDED     |     1 |    32 |     1   (0)| 00:00:01 |     2 |     2 |
|* 10 |      INDEX RANGE SCAN                | IX_T_EXPANDED1 |     1 |       |     1   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------------------------------
Outline Data 
-------------
             
  /*+        
      BEGIN_OUTLINE_DATA
      ...
      EXPAND_TABLE(@"SEL$1" "TE"@"SEL$1")                                                                                                                                 
      ...
      END_OUTLINE_DATA                                                                                                                                                    
  */                                                                                                                                                                      
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   4 - filter("TE"."FIELD2"=TO_NUMBER(:B) AND "TE"."FIELD1"=TO_NUMBER(:A))                                                                                                
   6 - filter("TE"."FIELD1"=TO_NUMBER(:A))                                                                                                                                
   7 - access("TE"."FIELD2"=TO_NUMBER(:B))                                                                                                                                
  10 - access("TE"."FIELD1"=TO_NUMBER(:A) AND "TE"."FIELD2"=TO_NUMBER(:B))

FACTORIZE_JOIN / NO_FACTORIZE_JOIN

SQL feature:	QKSFM_JOINFAC
Class:	FACTORIZE_JOIN
Version:	11.2.0.1
Документирован	нет

В 11gR2 введен новый механизм, позволяющий объединять обращения к одной присоединяемой таблице по одним и тем же условиям в разных частях "union [all]" в один join на более позднем этапе после слияния других таблиц. Т.е. запрос вида:

select * from t1 join t2 on t1.a=t2.a where предикаты_по_таблице_t1_1
union all
select * from t1 join t2 on t1.a=t2.a where предикаты_по_таблице_t1_2

Будет трансформирован в

select * 
from t2 
   join (
      select * from t1 where предикаты_по_таблице_t1_1
      union all
      select * from t1 where предикаты_по_таблице_t1_2
   ) on t1.a=t2.a

DB11G/XTENDER> explain plan for
  2  select *
  3  from t_1k
  4      ,t_8k
  5  where t_1k.c10=t_8k.c100
  6    and t_1k.col_unique=:a
  7  union all
  8  select *
  9  from t_1k
 10      ,t_8k
 11  where t_1k.c10=t_8k.c100
 12    and t_1k.c1000=:b
 13  /

Explained.

Elapsed: 00:00:00.03
DB11G/XTENDER> @xplan

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

Plan hash value: 3121763019

-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |   100K|  9384K|    16   (7)| 00:00:01 |
|*  1 |  HASH JOIN                     |                    |   100K|  9384K|    16   (7)| 00:00:01 |
|   2 |   VIEW                         | VW_JF_SET$154ACCB2 |  1001 | 78078 |     5   (0)| 00:00:01 |
|   3 |    UNION-ALL                   |                    |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_1K               |     1 |    17 |     2   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | PK_T_1K            |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL          | T_1K               |  1000 | 17000 |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL            | T_8K               |  8000 |   140K|    10   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      ...
      FACTORIZE_JOIN(@"SET$1"("T_8K"@"SEL$1" "T_8K"@"SEL$2"))
      ...
      END_OUTLINE_DATA
  */

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

   1 - access("ITEM_1"="T_8K"."C100")
   5 - access("T_1K"."COL_UNIQUE"=TO_NUMBER(:A))
   6 - filter("T_1K"."C1000"=TO_NUMBER(:B))

DB11G/XTENDER> explain plan for
  2  select--+ NO_FACTORIZE_JOIN(@SET$1)
  3    *
  4  from t_1k
  5      ,t_8k
  6  where t_1k.c10=t_8k.c100
  7    and t_1k.col_unique=:a
  8  union all
  9  select *
 10  from t_1k
 11      ,t_8k
 12  where t_1k.c10=t_8k.c100
 13    and t_1k.c1000=:b;

Explained.

Elapsed: 00:00:00.00
DB11G/XTENDER> @xplan

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

Plan hash value: 3677413949

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              | 80100 |  2737K|    18  (78)| 00:00:01 |
|   1 |  UNION-ALL                    |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |   100 |  3500 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_1K         |     1 |    17 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | PK_T_1K      |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T_8K         |   100 |  1800 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IX_T_8K_C100 |   100 |       |     1   (0)| 00:00:01 |
|*  7 |   HASH JOIN                   |              | 80000 |  2734K|    14   (8)| 00:00:01 |
|*  8 |    TABLE ACCESS FULL          | T_1K         |  1000 | 17000 |     3   (0)| 00:00:01 |
|   9 |    TABLE ACCESS FULL          | T_8K         |  8000 |   140K|    10   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   1 - SET$1
   2 - SEL$1
   3 - SEL$1 / T_1K@SEL$1
   4 - SEL$1 / T_1K@SEL$1
   5 - SEL$1 / T_8K@SEL$1
   6 - SEL$1 / T_8K@SEL$1
   7 - SEL$2
   8 - SEL$2 / T_1K@SEL$2
   9 - SEL$2 / T_8K@SEL$2

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

  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$1" "T_8K"@"SEL$1")
      LEADING(@"SEL$1" "T_1K"@"SEL$1" "T_8K"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T_8K"@"SEL$1" ("T_8K"."C100"))
      INDEX_RS_ASC(@"SEL$1" "T_1K"@"SEL$1" ("T_1K"."COL_UNIQUE"))
      USE_HASH(@"SEL$2" "T_8K"@"SEL$2")
      LEADING(@"SEL$2" "T_1K"@"SEL$2" "T_8K"@"SEL$2")
      FULL(@"SEL$2" "T_8K"@"SEL$2")
      FULL(@"SEL$2" "T_1K"@"SEL$2")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   4 - access("T_1K"."COL_UNIQUE"=TO_NUMBER(:A))
   6 - access("T_1K"."C10"="T_8K"."C100")
   7 - access("T_1K"."C10"="T_8K"."C100")
   8 - filter("T_1K"."C1000"=TO_NUMBER(:B))

Описание и пример от Тимура Ахмадеева.

NO_SUBSTRB_PAD

SQL feature:	QKSFM_EXECUTION
Class:	NO_SUBSTRB_PAD
Version:	11.2.0.1
Документирован	нет

Этот хинт используется dbms_stats в запросах сбора гистограмм. Он меняет поведение substrb - с этим хинтом результат substrb после получения подстроки по байтам будет еще обрезан по символам, т.е. остатки от мультибайтовых символов будут убраны:

SQL> select
  2     column_value
  3    ,dump(substrb(column_value,2,8)) "dump 2-8"
  4  from table(sys.odcivarchar2list('яяяяzz', 'zzzzяя','йййййй'));

COLUMN_VAL dump 2-8
---------- --------------------------------------------------
яяяяzz     Typ=1 Len=8: 32,209,143,209,143,209,143,122
zzzzяя     Typ=1 Len=7: 122,122,122,209,143,209,143
йййййй     Typ=1 Len=8: 32,208,185,208,185,208,185,32

3 rows selected.

Elapsed: 00:00:00.00
SQL> select--+ NO_SUBSTRB_PAD
  2     column_value
  3    ,dump(substrb(column_value,2,8)) "dump 2-8"
  4  from table(sys.odcivarchar2list('яяяяzz', 'zzzzяя','йййййй'));

COLUMN_VAL dump 2-8
---------- --------------------------------------------------
яяяяzz     Typ=1 Len=7: 209,143,209,143,209,143,122
zzzzяя     Typ=1 Len=7: 122,122,122,209,143,209,143
йййййй     Typ=1 Len=6: 208,185,208,185,208,185

PLACE_DISTINCT / NO_PLACE_DISTINCT

SQL feature:	QKSFM_DIST_PLCMT
Class:	PLACE_DISTINCT
Version:	11.2.0.1
Документирован	нет

В 11.2.0.1 введен новый механизм cbqt - перемещение кляузы distinct в запросах с джойнами с верхнего уровня в inner view, для уменьшения набора данных на наиболее ранних этапах.
При этом алиасы у этих внутренних представлений в плане будут начинаться с "VW_DTP_", в примере ниже это VW_DTP_7FEE568E.

Пример:

select
 distinct
  t1.c10,t2.c100
from (
       select c10,c100,c1000
       from t_1k
       where t_1k.col_unique <100
     ) t1
     ,(
       select c10,c100,c1000
       from t_8k
       where t_8k.col_unique in (select t_4k.c10 from t_4k where t_4k.c100=1)
     ) t2
where t1.c10=t2.c10

****************************************
 Cost-Based Group-By/Distinct Placement
****************************************
GBP/DP: Checking validity of GBP/DP for query block SEL$658B16C2 (#1)
GBP: Checking validity of group-by placement for query block SEL$658B16C2 (#1)
GBP: Bypassed: Query has invalid constructs.
DP: Checking validity of distinct placement for query block SEL$658B16C2 (#1)

DP: Using search type: linear
DP: Considering distinct placement on query block SEL$658B16C2 (#1)
****************************************
DP: Starting iteration 1, state space = (2,3) : (0,0)
DP: Original query
******* UNPARSED QUERY IS *******
SELECT DISTINCT "T_1K"."C10" "C10","T_8K"."C100" "C100" FROM "XTENDER"."T_4K" "T_4K","XTENDER"."T_1K" "T_1K","XTENDER"."T_8K" "T_8K" WHERE "T_1K"."C10"="T_8K"."C10" AND "T_1K"."COL_UNIQUE"<100 AND "T_8K"."COL_UNIQUE"="T_4K"."C10" AND "T_4K"."C100"=1
...
DP: Costing query block.
...
DP: Updated best state, Cost = 17.14
****************************************
DP: Starting iteration 2, state space = (2,3) : (1,0)
DP: Using DP transformation in this iteration.
...
DP: Transformed query
******* UNPARSED QUERY IS *******
SELECT DISTINCT "VW_DTP_7FEE568E"."ITEM_1" "C10","T_8K"."C100" "C100" FROM  (SELECT DISTINCT "T_1K"."C10" "ITEM_1" FROM "XTENDER"."T_1K" "T_1K" WHERE "T_1K"."COL_UNIQUE"<100) "VW_DTP_7FEE568E","XTENDER"."T_4K" "T_4K","XTENDER"."T_8K" "T_8K" WHERE "VW_DTP_7FEE568E"."ITEM_1"="T_8K"."C10" AND "T_8K"."COL_UNIQUE"="T_4K"."C10" AND "T_4K"."C100"=1
...
DP: Costing query block.
...
DP: Not update best state, Cost = 18.14
****************************************
DP: Starting iteration 3, state space = (2,3) : (0,1)
DP: Using DP transformation in this iteration.
...
DP: Transformed query
******* UNPARSED QUERY IS *******
SELECT DISTINCT "T_1K"."C10" "C10","T_8K"."C100" "C100" FROM  (SELECT DISTINCT "T_4K"."C10" "ITEM_1" FROM "XTENDER"."T_4K" "T_4K" WHERE "T_4K"."C100"=1) "VW_DTP_71E93533","XTENDER"."T_1K" "T_1K","XTENDER"."T_8K" "T_8K" WHERE "T_1K"."C10"="T_8K"."C10" AND "T_1K"."COL_UNIQUE"<100 AND "T_8K"."COL_UNIQUE"="VW_DTP_71E93533"."ITEM_1"
...
DP: Costing query block.
...
DP: Not update best state, Cost = 18.14
****************************************

****************************************
 Cost-Based Group-By/Distinct Placement
****************************************
GBP/DP: Checking validity of GBP/DP for query block SEL$5428C7F1 (#1)
DP: Checking validity of distinct placement for query block SEL$5428C7F1 (#1)
DP: Bypassed: Query tables are not connected.

Дополнительно: пример от Тимура Ахмадеева.

PLACE_GROUP_BY / NO_PLACE_GROUP_BY

SQL feature:	QKSFM_PLACE_GROUP_BY
Class:	PLACE_GROUP_BY
Version:	11.1.0.6
Документирован	нет

Этот механизм аналогичен PLACE_DISTINCT(точнее, наоборот, т.к. PLACE_DISTINCT появился позже) и уже давно и хорошо описан: У Льюиса в презентации Riyaj Shamsudeen В планах эти inner-view легко заметить по префиксу "VW_GBC_", например VW_GBC_10.

TRANSFORM_DISTINCT_AGG / NO_TRANSFORM_DISTINCT_AGG

SQL feature:	QKSFM_INDEX_RS_ASC
Class:	ACCESS
Version:	11.1.0.6
Документирован	нет

Тоже новый механизм трансформирования запроса c distinct для использования нового hash group aggregate, у этих вложенных представлений префиксы "VW_DAG_". Скрытый параметр - "_optimizer_distinct_agg_transform". Пример.

BIND_AWARE / NO_BIND_AWARE

SQL feature:	QKSFM_CURSOR_SHARING
Class:	BIND_AWARE
Version:	11.1.0.7
Документирован	нет

Это известнейший хинт включения/выключения bind aware cursor sharing(is_bind_aware в v$sql_shared_cursor). Подробнее Дополнительно А тут забавно прочесть, что в официальном блоге рекомендуют недокументированный хинт

INDEX_RS_ASC / INDEX_RS_DESC

SQL feature:	QKSFM_TRANSFORMATION
Class:	TRANSFORM_DISTINCT_AGG
Version:	11.2.0.1
Документирован	нет

Эти обычные хинты для index range scan [asc/desc] появились в общем-то давно, в аутлайнах профилей они в 10.2 уже были.

NLJ_BATCHING / NO_NLJ_BATCHING

SQL feature:	QKSFM_EXECUTION
Class:	ACCESS
Version:	11.1.0.6
Документирован	нет

Как я уже говорил в описании к хинту TABLE_LOOKUP_BY_NL в 11g появился новый вариант nested loops join и этот NLJ_BATCHING как раз и позволяет включать и выключать этот механизм. Дополнительно: Jeff's blog: Thoughts on NLJ batching Dion Cho: Batching NLJ optimization and ordering

NLJ_PREFETCH / NO_NLJ_PREFETCH

SQL feature:	QKSFM_EXECUTION
Class:	ACCESS
Version:	11.1.0.6
Документирован	нет

Отвечают за механизм nested loops table prefetch, появившийся в 9i, хорошо объяснено тут:

Logical I/O - Evolution: Part 2 - 9i, 10g Prefetching
Logical I/O Evolution - Part 3: 11g
и дополнительно у Тома

XT&R blackboard

Categories

Main Menu

Search

Список и описание новых хинтов в Oracle 11.1 - 11.2.0.3(включая недокументированные)

FULL_OUTER_JOIN_TO_OUTER / NO_FULL_OUTER_JOIN_TO_OUTER

OUTER_JOIN_TO_ANTI / NO_OUTER_JOIN_TO_ANTI

TABLE_LOOKUP_BY_NL / NO_TABLE_LOOKUP_BY_NL

USE_HASH_GBY_FOR_PUSHDOWN / NO_USE_HASH_GBY_FOR_PUSHDOWN

XDB_FASTPATH_INSERT / NO_XDB_FASTPATH_INSERT

APPEND_VALUES / NOAPPEND

COALESCE_SQ / NO_COALESCE_SQ

CONNECT_BY_ELIM_DUPS / NO_CONNECT_BY_ELIM_DUPS

DST_UPGRADE_INSERT_CONV / NO_DST_UPGRADE_INSERT_CONV

EXPAND_TABLE / NO_EXPAND_TABLE

FACTORIZE_JOIN / NO_FACTORIZE_JOIN

NO_SUBSTRB_PAD

PLACE_DISTINCT / NO_PLACE_DISTINCT

PLACE_GROUP_BY / NO_PLACE_GROUP_BY

TRANSFORM_DISTINCT_AGG / NO_TRANSFORM_DISTINCT_AGG

BIND_AWARE / NO_BIND_AWARE

INDEX_RS_ASC / INDEX_RS_DESC

NLJ_BATCHING / NO_NLJ_BATCHING

NLJ_PREFETCH / NO_NLJ_PREFETCH

Spread The Love, Share Our Article

Comments

Отправить комментарий

Популярные сообщения

Links

Labels

Обо мне

Revolver Maps

XT&R blackboard

My Latest Tweet

Twitter Updates

Join My Network

About Us

XT&R blackboard

Categories

Main Menu

Search

Список и описание новых хинтов в Oracle 11.1 - 11.2.0.3(включая недокументированные)

FULL_OUTER_JOIN_TO_OUTER / NO_FULL_OUTER_JOIN_TO_OUTER

OUTER_JOIN_TO_ANTI / NO_OUTER_JOIN_TO_ANTI

TABLE_LOOKUP_BY_NL / NO_TABLE_LOOKUP_BY_NL

USE_HASH_GBY_FOR_PUSHDOWN / NO_USE_HASH_GBY_FOR_PUSHDOWN

XDB_FASTPATH_INSERT / NO_XDB_FASTPATH_INSERT

APPEND_VALUES / NOAPPEND

COALESCE_SQ / NO_COALESCE_SQ

CONNECT_BY_ELIM_DUPS / NO_CONNECT_BY_ELIM_DUPS

DST_UPGRADE_INSERT_CONV / NO_DST_UPGRADE_INSERT_CONV

EXPAND_TABLE / NO_EXPAND_TABLE

FACTORIZE_JOIN / NO_FACTORIZE_JOIN

NO_SUBSTRB_PAD

PLACE_DISTINCT / NO_PLACE_DISTINCT

PLACE_GROUP_BY / NO_PLACE_GROUP_BY

TRANSFORM_DISTINCT_AGG / NO_TRANSFORM_DISTINCT_AGG

BIND_AWARE / NO_BIND_AWARE

INDEX_RS_ASC / INDEX_RS_DESC

NLJ_BATCHING / NO_NLJ_BATCHING

NLJ_PREFETCH / NO_NLJ_PREFETCH

Spread The Love, Share Our Article

Comments

Отправить комментарий

Популярные сообщения

Links

Labels

Подпишитесь

Обо мне

Revolver Maps

XT&R blackboard

My Latest Tweet

Twitter Updates

Join My Network

About Us