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


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

Cписок хинтов добавленных в 11g
NAMEINVERSEVERSION
FULL_OUTER_JOIN_TO_OUTERNO_FULL_OUTER_JOIN_TO_OUTER11.2.0.3
OUTER_JOIN_TO_ANTINO_OUTER_JOIN_TO_ANTI11.2.0.3
TABLE_LOOKUP_BY_NLNO_TABLE_LOOKUP_BY_NL11.2.0.2
USE_HASH_GBY_FOR_PUSHDOWNNO_USE_HASH_GBY_FOR_PUSHDOWN11.2.0.2
XDB_FASTPATH_INSERTNO_XDB_FASTPATH_INSERT11.2.0.2
APPEND_VALUESNOAPPEND11.2.0.1
COALESCE_SQNO_COALESCE_SQ11.2.0.1
CONNECT_BY_ELIM_DUPSNO_CONNECT_BY_ELIM_DUPS11.2.0.1
DST_UPGRADE_INSERT_CONVNO_DST_UPGRADE_INSERT_CONV11.2.0.1
EXPAND_TABLENO_EXPAND_TABLE11.2.0.1
FACTORIZE_JOINNO_FACTORIZE_JOIN11.2.0.1
NO_SUBSTRB_PAD 11.2.0.1
PLACE_DISTINCTNO_PLACE_DISTINCT11.2.0.1
STATEMENT_QUEUINGNO_STATEMENT_QUEUING11.2.0.1
TRANSFORM_DISTINCT_AGGNO_TRANSFORM_DISTINCT_AGG11.2.0.1
XMLINDEX_SEL_IDX_TBL 11.2.0.1
BIND_AWARENO_BIND_AWARE11.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_REWRITENO_CHECK_ACL_REWRITE11.1.0.6
COST_XML_QUERY_REWRITENO_COST_XML_QUERY_REWRITE11.1.0.6
DB_VERSION 11.1.0.6
DOMAIN_INDEX_FILTERNO_DOMAIN_INDEX_FILTER11.1.0.6
INDEX_RS_ASC 11.1.0.6
INDEX_RS_DESC 11.1.0.6
MONITORNO_MONITOR11.1.0.6
NLJ_BATCHINGNO_NLJ_BATCHING11.1.0.6
NLJ_PREFETCHNO_NLJ_PREFETCH11.1.0.6
NO_LOAD 11.1.0.6
OUTER_JOIN_TO_INNERNO_OUTER_JOIN_TO_INNER11.1.0.6
PLACE_GROUP_BYNO_PLACE_GROUP_BY11.1.0.6
RESULT_CACHENO_RESULT_CACHE11.1.0.6
SUBQUERY_PRUNINGNO_SUBQUERY_PRUNING11.1.0.6
USE_INVISIBLE_INDEXESNO_USE_INVISIBLE_INDEXES11.1.0.6
USE_MERGE_CARTESIAN 11.1.0.6
XML_DML_RWT_STMT 11.1.0.6
XMLINDEX_REWRITENO_XMLINDEX_REWRITE11.1.0.6
XMLINDEX_REWRITE_IN_SELECTNO_XMLINDEX_REWRITE_IN_SELECT11.1.0.6
CONNECT_BY_CB_WHR_ONLYNO_CONNECT_BY_CB_WHR_ONLY10.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, хорошо объяснено тут:

Comments

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