Конкатенация против INLIST ITERATOR

18.11.2013

Конкатенация против INLIST ITERATOR

Filed under: bugs,CBO,hints,Oracle — Игорь Усольцев @ 00:12
Tags: CBO, Oracle bug, plan management

Достаточно давно Саян Малакшинов описал и уже успешно разрешил через поддержку ( патч + фиксация в версии Oracle 12.2 ) проблему применения / управления операцией INLIST ITERATOR — Unresolved quiz: Avoiding in-list iterator

И, хотя, к сожалению, заметка попалась на глаза достаточно поздно, ~~2drink borjomi is 2late,~~ мне кажется интересным рассмотреть этот отличный тест-кейс и предложить ещё один вариант решения с форсированным подсказкой использованием конкатенации, как операции противожной INLIST

На всякий случай (для быстрой воспроизводимости) — DDL кейса из блога Саяна:

drop table xt1
/
create table xt1 as
select
   level a
 , mod(level,1000) b
 , lpad(1,300,1) padding
from dual
connect by level<=1e5
/
create index ix_xt1 on xt1(b)
/
drop table xt2
/
create table xt2 as
select
   level        a
 , mod(level,5) b
 , lpad(1,50,1) padding
from dual
connect by level<=1e6
/
alter table xt2
  add constraint uq_xt2
  unique (a)
  using index(create index ix_xt2 on xt2(a,b))
/
exec dbms_stats.gather_table_stats('','XT1',cascade=>true)
exec dbms_stats.gather_table_stats('','XT2',cascade=>true)

Типичное выполнение тестового запроса:

SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b in (1,2)
  7  /

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','+outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  0cyby2w6mjnp9, child number 0
-------------------------------------

Plan hash value: 2715236140

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |   501 (100)|          |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   501   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|   5 |    INLIST ITERATOR            |        |       |       |            |          |
|*  6 |     INDEX RANGE SCAN          | IX_XT2 |     2 |       |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "XT1"@"SEL$1" ("XT1"."B"))
      INDEX(@"SEL$1" "XT2"@"SEL$1" ("XT2"."A" "XT2"."B"))
      NUM_INDEX_KEYS(@"SEL$1" "XT2"@"SEL$1" "IX_XT2" 2)
      LEADING(@"SEL$1" "XT1"@"SEL$1" "XT2"@"SEL$1")
      USE_NL(@"SEL$1" "XT2"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "XT2"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   4 - access("XT1"."B"=10)
   6 - access("XT1"."A"="XT2"."A" AND (("XT2"."B"=1 OR "XT2"."B"=2)))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        505  consistent gets

— показывает условно «плохой» и по стоимости, и по статистике план выполнения — собственно, в этом и заключается одно из проявлений бага: по умолчанию Oracle выбирает более дорогой план, что противоречит основополагающим принципам CBO

Как описал Саян, одним из вариантов решения является установка Event 10157 (CBO disable index access path for in-list), срабатывающий, однако, только в случае отсутствия в кэше курсора с «плохим» планом

Если же в Shared Pool уже есть «плохой» план, установка этого события никак не способствует генерирации нового, условно «хорошего»:

SQL> select plan_hash_value from v$sql where sql_id = '0cyby2w6mjnp9';

PLAN_HASH_VALUE
---------------
     2715236140 -- присутствует "плохой" курсор

SQL> alter session set events '10157 trace name context forever , level 1'
  2  /

Session altered.

SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b in (1,2)
  7  /

no rows selected

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID  0cyby2w6mjnp9, child number 0 -- используется имеющийся курсор
-------------------------------------

Plan hash value: 2715236140           -- и план

После удаления курсора установка события 10157, как и дОлжно, формирует «хороший» план:

SQL> @purge_cu 0cyby2w6mjnp9

PL/SQL procedure successfully completed.

SQL> alter session set events '10157 trace name context forever , level 1'
  2  /

Session altered.

SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b in (1,2)
  7  /

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','+outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  0cyby2w6mjnp9, child number 0
-------------------------------------

Plan hash value: 2884586137

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |   401 (100)|          | -- формально более дешёвый план
|   1 |  NESTED LOOPS                 |        |       |       |            |          | -- реально более выгоден
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   401   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IX_XT2 |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "XT1"@"SEL$1" ("XT1"."B"))
      INDEX(@"SEL$1" "XT2"@"SEL$1" ("XT2"."A" "XT2"."B"))
      LEADING(@"SEL$1" "XT1"@"SEL$1" "XT2"@"SEL$1")
      USE_NL(@"SEL$1" "XT2"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "XT2"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   4 - access("XT1"."B"=10)
   5 - access("XT1"."A"="XT2"."A")
       filter(("XT2"."B"=1 OR "XT2"."B"=2))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        305  consistent gets

— очевидно, по этой же причине поддержка рекомендует устанавливать event 10157 для всех запросов на уровне системы в spfile/pfile, что не всегда может быть применимо

Интересно, что единственное формальное отличие планов — присутствие в «плохом» Outline подсказки
NUM_INDEX_KEYS(@»SEL$1″ «XT2″@»SEL$1» «IX_XT2» 2), изменение параметра которой в виде NUM_INDEX_KEYS(@»SEL$1″ «XT2″@»SEL$1» «IX_XT2» 1), по определению способное решить проблему, до применения полученного Саяном патча также пока не работает — и это вторая сторона бага

Фиксация «хорошего» плана как бы помогает:

SQL> @create_bsline 0cyby2w6mjnp9 2884586137 "Disable inline"
Baseline SQL_0fa369f0d5001046 SQL_PLAN_0z8v9y3ah0426c2507a8e was [re]created
for SQL_ID=0cyby2w6mjnp9, SQL_PLAN_HASH=2884586137

SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b in (1,2)
  7  /

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','+outline'))
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID  0cyby2w6mjnp9, child number 0
-------------------------------------
select * from xt1,xt2 where      xt1.b=10  and xt1.a=xt2.a  and xt2.b
in (1,2)

Plan hash value: 2884586137 -- более дешёвый "хороший" план

...

Note
-----
   - SQL plan baseline SQL_PLAN_0z8v9y3ah0426c2507a8e used for this statement

но только до того момента, пока курсор с «хорошим» планом находится в кэше

После удаления правильного курсора из Shared Pool, незамедлительно генерируется новый курсор и запрос продолжает выполняться по «плохому» плану:

SQL> @purge_cu 0cyby2w6mjnp9

PL/SQL procedure successfully completed.

SQL> alter session set events '10157 trace name context off';

Session altered.

SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b in (1,2)
  7  /

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','+outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  0cyby2w6mjnp9, child number 0
-------------------------------------

Plan hash value: 2715236140 -- Bad Santa

, созданный Baseline при этом меняет статус на REPRODUCED=NO:

SQL> select * from dba_sql_plan_baselines where description = 'DISABLE INLINE'
  2  /

PLAN_NAME                      ORIGIN      DESCRIPTION    CREATED           LAST_MODIFIED     LAST_EXECUTED     ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE OPTIMIZER_COST
------------------------------ ----------- -------------- ----------------- ----------------- ----------------- ------- -------- ----- ---------- --------- --------------
SQL_PLAN_0z8v9y3ah0426c2507a8e MANUAL-LOAD DISABLE INLINE 17.11.13 00:24:19 17.11.13 00:27:41 17.11.13 00:24:44 YES     YES      YES   NO         NO                   401

— но ровно до того момента, пока в Shared Pool опять не окажется «хороший» план, как было показано выше. В точности также работает фиксация этого плана с помощью Stored Outline

Т.о. план в описываемом случае определяется не только набором подсказок Baseline / Stored Outline (а также параметрами инстанса и сессии, статистикой объектов и т.д.), которые формально соответствуют секции Outline «хорошего» плана:

SQL> @bl_hints SQL_PLAN_0z8v9y3ah0426c2507a8e

OUTLINE_HINTS
--------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "XT1"@"SEL$1" ("XT1"."B"))
INDEX(@"SEL$1" "XT2"@"SEL$1" ("XT2"."A" "XT2"."B"))
LEADING(@"SEL$1" "XT1"@"SEL$1" "XT2"@"SEL$1")
USE_NL(@"SEL$1" "XT2"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "XT2"@"SEL$1")

, но и присутствием в кэше SGA курсора с «плохим» или «хорошим» планом выполнения (сгенерированным с помощью определённого флага оптимизатора — event 10157 как в этом случае, например). Т.е. план не является детерминированным — что, как я понимаю, и является одним из важных признаков бага (ранее уже встречаемом для другого бага оптимизатора)

Безполезный Baseline можно удалить:

SQL> @drop_bsline SQL_PLAN_0z8v9y3ah0426c2507a8e
Baseline SQL_0fa369f0d5001046 SQL_PLAN_0z8v9y3ah0426c2507a8e was dropped

и, обратив внимание на попадающуюся рекомендацию поддержки использовать подсказку USE_CONCAT для отключения операции INLIST, попробовать хинт в виде:

SQL> select--+ USE_CONCAT(OR_PREDICATES(32767))
  2   * from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b in (1,2)
  7  /

no rows selected

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  8wv8b8tnhnd63, child number 0
-------------------------------------

Plan hash value: 2884586137          -- "хороший" план

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |   401 (100)|          |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   401   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IX_XT2 |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("XT1"."B"=10)
   5 - access("XT1"."A"="XT2"."A")
       filter(("XT2"."B"=1 OR "XT2"."B"=2)) -- в этом преобразованном выражении OR_PREDICATES подсказка диктует не использовать INLIST ITERATOR

— что позволяет получить «правильный» план выполнения. Значение параметра 32767 значения здесь не имеет, имеет значение только сам факт присутствия этого параметра в диапазоне 0-32767

Далее можно сгенерировать, например, соответствующий SQL Patch:

SQL> @sqlpatch+ 0cyby2w6mjnp9 "USE_CONCAT(OR_PREDICATES(0))" "DISABLE INLINE"

PL/SQL procedure successfully completed.

SQL_ID        NAME                           CATEGORY                       CREATED             STATUS
------------- ------------------------------ ------------------------------ ------------------- --------
0cyby2w6mjnp9 DISABLE INLINE                 DEFAULT                        17.11.2013 01:46:08 ENABLED

SQL> @SQLPATCH_HINTS "DISABLE INLINE"

SQL_PATCH_HINTS
----------------------------
USE_CONCAT(OR_PREDICATES(0))

— с подсказкой директивного типа USE_CONCAT, которая явно не попадая в Outline плана выполнения, и не генерируя соответствующих операций типа CONCATENATION напрямую:

SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b in (1,2)
  7  /

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','+predicate'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  0cyby2w6mjnp9, child number 0
-------------------------------------

Plan hash value: 2884586137

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |   401 (100)|          |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   401   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IX_XT2 |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("XT1"."B"=10)
   5 - access("XT1"."A"="XT2"."A")
       filter(("XT2"."B"=1 OR "XT2"."B"=2))

Note
-----
   - SQL patch "DISABLE INLINE" used for this statement

— успешно генерирует правильный план, форсируя вместо использования «плохой» INLIST итерации:

Plan hash value: 2715236140

-----------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
...
|   5 |    INLIST ITERATOR            |        |       |       |            |
|*  6 |     INDEX RANGE SCAN          | IX_XT2 |     2 |       |     3   (0)|
|   7 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     4   (0)|
-----------------------------------------------------------------------------

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

   ...
   6 - access("XT1"."A"="XT2"."A" AND (("XT2"."B"=1 OR "XT2"."B"=2)))

более простую и дешёвую по расчётам CBO операцию конкатенации из «хорошего» плана:

-----------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
...
|*  5 |    INDEX RANGE SCAN           | IX_XT2 |     1 |       |     2   (0)|
|   6 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     3   (0)|
-----------------------------------------------------------------------------

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

   ...
   5 - access("XT1"."A"="XT2"."A")
       filter(("XT2"."B"=1 OR "XT2"."B"=2))

Комментарии отключены

	Игорь Усольцев к записи Рекурсивный sql_id gcfw4cgp0hh…
	MM к записи Рекурсивный sql_id gcfw4cgp0hh…
	Игорь Усольцев к записи Oracle 19c bug: JPPD не работа…
	Sayan Malakshinov к записи Oracle 19c bug: JPPD не работа…
	roliynichenko к записи ORA-00600: internal error code…

Oracle mechanics

18.11.2013