Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 12.2 и OR Expansion  [new]
micis
Member

Откуда:
Сообщений: 75
Тестируем переход с 12.1.0.2 на 12.2.0.1 (Oct 2018 Release Update)

Выключена битмап-трансформация плана:
alter system set "_b_tree_bitmap_plans"=false;

При этом некоторые запросы, в которых есть условие OR, стали использовать ACCESS FULL вместо индексного доступа к OR полям.

Например:
drop table t1;
drop table t2;

create table t1 as select level id1, level*2 id2 from dual connect by level<=1000000;
create table t2 as select level id from dual connect by level<=1000000;

create index t1_1 on t1(id1);
create index t1_2 on t1(id2);
create index t2_1 on t2(id);


variable b1 number
/
variable b2 number
/

exec :b1:=12345;
exec :b2:=10;

select a1.id, (select count(*) from t1 where (id1 = a1.ID or id2 = a1.ID) and ROWNUM < 2) F1
from t2 a1 where (ID = :b1) AND (ROWNUM <= :b2);
...
Plan hash value: 510964771
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     5 |   496   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    10 |            |          |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T1   |     2 |    20 |   495   (4)| 00:00:01 |
|*  4 |  COUNT STOPKEY      |      |       |       |            |          |
|*  5 |   INDEX RANGE SCAN  | T2_1 |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Пока смог построить хороший план только через возврат оптимизатора на 12.1:
alter session set optimizer_features_enable='12.1.0.2';
alter session set "_b_tree_bitmap_plans"=false;
...
Plan hash value: 342876580
 
------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |      |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                         |      |     1 |    10 |            |          |
|*  2 |   COUNT STOPKEY                         |      |       |       |            |          |
|   3 |    CONCATENATION                        |      |       |       |            |          |
|*  4 |     FILTER                              |      |       |       |            |          |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1   |     1 |    10 |     1   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN                  | T1_1 |     1 |       |     1   (0)| 00:00:01 |
|*  7 |     FILTER                              |      |       |       |            |          |
|*  8 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1   |     1 |    10 |     1   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN                  | T1_2 |     1 |       |     1   (0)| 00:00:01 |
|* 10 |  COUNT STOPKEY                          |      |       |       |            |          |
|* 11 |   INDEX RANGE SCAN                      | T2_1 |     1 |     5 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


В трейсе 10053 есть такое:
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$2 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
...
LORE: Or-Expansion validity checks failed on query block SEL$2 (#2) because Cost based OR expansion enabled

Как можно заставить оптимизатор использовать индексы?
14 мар 19, 12:16    [21832336]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 и OR Expansion  [new]
xtender
Member

Откуда: Мск
Сообщений: 5084
micis,

Ну, например, отключить эту новую фичу:
Alter system set "_optimizer_cbqt_or_expansion"=off
Или лучше на конкретные проблемные запросы создать sql patch/profile c opt_param("_optimizer_cbqt_or_expansion", "off")
14 мар 19, 12:22    [21832350]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 и OR Expansion  [new]
xtender
Member

Откуда: Мск
Сообщений: 5084
micis,

А вообще больше интересует почему cbqt or-expansion не сработал. По-хорошему оптимизатор должен был трансформировать это через union all, в отличие от старого concatenation
14 мар 19, 12:26    [21832358]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 и OR Expansion  [new]
-2-
Member

Откуда:
Сообщений: 14576
DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115 планы повторяются.

На 18.4 и 19.2 с дефолтным optimizer_features_enable и _b_tree_bitmap_plans=false
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |     1 |     5 |    10   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                         |                 |     1 |       |            |          |
|*  2 |   COUNT STOPKEY                         |                 |       |       |            |          |
|   3 |    VIEW                                 | VW_ORE_496B6688 |     2 |       |     7   (0)| 00:00:01 |
|   4 |     UNION-ALL                           |                 |       |       |            |          |
|*  5 |      INDEX RANGE SCAN                   | T1_1            |     1 |     5 |     3   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 |    10 |     4   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN                  | T1_2            |     1 |       |     3   (0)| 00:00:01 |
|*  8 |  COUNT STOPKEY                          |                 |       |       |            |          |
|*  9 |   INDEX RANGE SCAN                      | T2_1            |     1 |     5 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

micis
Пока смог построить хороший план только через возврат оптимизатора на 12.1:
alter session set optimizer_features_enable='12.1.0.2';
alter session set "_b_tree_bitmap_plans"=false;
Чем не устраивает bitmap conversion?
14 мар 19, 13:55    [21832534]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 и OR Expansion  [new]
xtender
Member

Откуда: Мск
Сообщений: 5084
-2-,

Покажите кто-нибудь 10053 с 12.2, интересно почему стоимостной or-expansion не сработал
14 мар 19, 17:21    [21832868]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 и OR Expansion  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17251
-2-
Чем не устраивает bitmap conversion?

Не знаю чем она не устраивает ТС, но лично у меня опыт встреч с этой штукой сугубо негативный - дорого и долго.
14 мар 19, 17:40    [21832887]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 и OR Expansion  [new]
micis
Member

Откуда:
Сообщений: 75
andrey_anonymous
-2-
Чем не устраивает bitmap conversion?

Не знаю чем она не устраивает ТС, но лично у меня опыт встреч с этой штукой сугубо негативный - дорого и долго.
Не зря это один из отключаемых параметров в OEBS (Doc ID 396009.1)

-2-
DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115 планы повторяются.
Нет уже возможности тестировать свежую psu, нам оракл по этой проблеме уже почти год патч доделать не может (сократили с 18 часов до 8). А обновляться надо через месяц уже.

xtender
-2-,
Покажите кто-нибудь 10053 с 12.2, интересно почему стоимостной or-expansion не сработал
В файле планы.

К сообщению приложен файл (plans.7z - 20Kb) cкачать
15 мар 19, 03:55    [21833189]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 и OR Expansion  [new]
micis
Member

Откуда:
Сообщений: 75
xtender
micis,
Ну, например, отключить эту новую фичу:
Спасибо, помогло.
15 мар 19, 08:23    [21833247]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 и OR Expansion  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 428
Bug 26019148 - cbqt ore does not apply to correlated scalar subquery with oe (Doc ID 26019148.8)

micis
Тестируем переход с 12.1.0.2 на 12.2.0.1 (Oct 2018 Release Update)

ALTER SESSION SET "_fix_control" = '26019148:1';
15 мар 19, 13:48    [21833765]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 и OR Expansion  [new]
xtender
Member

Откуда: Мск
Сообщений: 5084
micis
Тестируем переход с 12.1.0.2 на 12.2.0.1 (Oct 2018 Release Update)
автор
The fix for 26019148 is first included in
18.1.0
12.2.0.1.180116 (Jan 2018) Database Release Update (DB RU)
12.2.0.1.190115 (Jan 2019) DB Oct 2018 Release Update Revision(DB RUR)
12.2.0.1.190115 (Jan 2019) DB Jul2018 Release Update Revision(DB RUR)
12.2.0.1.181016 (Oct 2018) DB Jul2018 Release Update Revision(DB RUR)
12.2.0.1.180717 (Jul 2018) DB Jan2018 Release Update Revision(DB RUR)
12.2.0.1.DBJAN2018RUR:180417(Apr 2018) Database Release Update Revision(DB RUR)
12.2.0.1.180116 (Jan 2018) Bundle Patch for Windows Platforms

internal fix


Alexander Anokhin
ALTER SESSION SET "_fix_control" = '26019148:1';

А почему изначально задизейблено в RUR'ах на 12.2?
15 мар 19, 17:04    [21834102]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 и OR Expansion  [new]
xtender
Member

Откуда: Мск
Сообщений: 5084
SQL> @fix_control 26019148

    BUGNO VALUE SQL_FEATURE                         DESCRIPTION                          OPTIMIZER_      EVENT IS_DEFAULT     CON_ID
--------- ----- ----------------------------------- ------------------------------------ ---------- ---------- ---------- ----------
 26019148     1 QKSFM_OR_EXPAND_26019148            Allow ORE in select list subq        18.1.0              0          1          0
15 мар 19, 17:09    [21834107]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 и OR Expansion  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 428
xtender
А почему изначально задизейблено в RUR'ах на 12.2?

Для стабильности. Фиксы оптимизатора раньше в патчи не включали, теперь будут включать задизейблеными.
19 мар 19, 12:11    [21837136]     Ответить | Цитировать Сообщить модератору
 Re: 12.2 и OR Expansion  [new]
micis
Member

Откуда:
Сообщений: 75
Ещё позанудствую немного:

Если делать селект с 'not exist' то план всё ещё быстрый:
select * from t1 where (id1 = :b1 or id2 = :b1) and not exists (select 1 from t1 where id2=:b1); 

-----------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                       |      |     2 |    28 |     4   (0)| 00:00:01 |
|   1 |  UPDATE                                | T1   |       |       |            |          |
|   2 |   CONCATENATION                        |      |       |       |            |          |
|*  3 |    FILTER                              |      |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1   |     1 |    14 |     1   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | T1_1 |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                   | T1_2 |     1 |     6 |     1   (0)| 00:00:01 |
|*  7 |    FILTER                              |      |       |       |            |          |
|*  8 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1   |     1 |    14 |     1   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN                  | T1_2 |     1 |       |     1   (0)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN                   | T1_2 |     1 |     6 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Но если сделать update, то всё ломается:
update t1 set id2=id2+1 where (id1 = :b1 or id2 = :b1) and not exists (select 1 from t1 where id2=:b1);
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |     2 |    22 |   732   (6)| 00:00:01 |
|   1 |  UPDATE             | T1   |       |       |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T1   |     2 |    22 |   731   (6)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | T1_2 |     1 |     6 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Это происходит при
ALTER SESSION SET "_fix_control" = '26019148:1';
Alter session set "_optimizer_cbqt_or_expansion"=on;


Если новую фичу выключить, то план update-а приходит в норму.
11 апр 19, 10:16    [21858992]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить