Oracle mechanics

17.02.2018

Oracle 12.1: OR-Expansion and Adaptive features

Filed under: CBO,Oracle — Игорь Усольцев @ 11:01
Tags: ,

На нескольких запросах столкнулись со ситуацией, когда при по умолчанию включенными optimizer_adaptive_features=true план запроса может не применять преобразование OR-Expansion, что значительно увеличивает формальную стоимость/Cost и реальное время выполнения:

12.1.0.2.@ SQL> explain plan for
  2  select *
  3    from 
...
 64  /

Explained.

SQL> select * from table(dbms_xplan.display('','','+alias -predicate'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 1699389027

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |    10 | 17700 |       |  2197K  (1)| 00:00:43 |
|*  1 |  VIEW                                             |    10 | 17700 |       |  2197K  (1)| 00:00:43 |
|*  2 |   WINDOW SORT PUSHED RANK                         |    39 | 13767 |       |  2197K  (1)| 00:00:43 |
|   3 |    NESTED LOOPS OUTER                             |    39 | 13767 |       |  2197K  (1)| 00:00:43 |
...
|* 39 |      INDEX UNIQUE SCAN                            |     1 |       |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   1 - SEL$D48D1E2F / from$_subquery$_001@SEL$1
   2 - SEL$D48D1E2F
...

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 7 Sql Plan Directives used for this statement

114 rows selected.

Судя по трейсу 10053 оптимизатор выполняет попытки:

Trying or-Expansion on query block SEL$D48D1E2F (#2)

******** Next OR predicate ********

Trying or-Expansion on query block SEL$D48D1E2F (#2)

******** OR-branching ********

or-expansion-subheap (create addr=0x7eff31a940a8)

, не фиксируя невозможности/недопустимости операции в виде:

Or-expansion bypassed: No index driver found.

или по какой-то другой причине, но не доходит и до собственно трансформации:

Trying or-Expansion on query block SEL$D48D1E2F (#2)

******** Final costing  ********
Registered qb: SEL$D48D1E2F_1 0xea599fc0 (OR EXPANSION SEL$D48D1E2F; SEL$D48D1E2F; 8 LIST)

, наблюдаемой в трейсе при отключении адаптивных фич:

SQL> alter session set optimizer_adaptive_features=false;

Session altered.

SQL> explain plan for
  2  select *
  3    from
...
 64  /

Explained.

SQL> select * from table(dbms_xplan.display('','','+alias +outline -predicate'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 1801607928

-------------------------------------------------------------------------------------------------------
| Id  | Operation                                             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                      |    10 | 17700 |   125   (1)| 00:00:01 |
|*  1 |  VIEW                                                 |    10 | 17700 |   125   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK                             |    40 | 14120 |   125   (1)| 00:00:01 |
|   3 |    CONCATENATION                                      |       |       |            |          |
|   4 |     NESTED LOOPS                                      |     4 |  1412 |    13   (0)| 00:00:01 |
...
|* 83 |       INDEX UNIQUE SCAN                               |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   1 - SEL$D48D1E2F   / from$_subquery$_001@SEL$1
   2 - SEL$D48D1E2F
  14 - SEL$D48D1E2F_1 / I@SEL$4                               -- мультиплицирование Query Block SEL$D48D1E2F при OR-Expansion/CONCATENATION
...

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

  /*+
      BEGIN_OUTLINE_DATA
...
      USE_CONCAT(@"SEL$D48D1E2F" 8 OR_PREDICATES(1))          -- OR-Expansion hint *
      OUTLINE_LEAF(@"SEL$D48D1E2F_1")
...
      ALL_ROWS
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')    -- optimizer_adaptive_features=false
      OPT_PARAM('_optimizer_gather_feedback' 'false')         -- --//--
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') -- --//--
      OPT_PARAM('_optimizer_adaptive_plans' 'false')          -- --//--
      OPT_PARAM('_optimizer_dsdir_usage_control' 0)           -- --//--
      OPT_PARAM('_px_adaptive_dist_method' 'off')             -- --//--
      OPT_PARAM('_optimizer_use_feedback' 'false')            -- --//--
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

209 rows selected.

— и если уточнить анализ, оказывается, что в этом конкретном случае для достижения рез-та совсем не обязательно отключать все адаптивные фичи скопом (optimizer_adaptive_features=false) — достаточно отключить использование SQL Plan Directives параметром OPT_PARAM(‘_optimizer_dsdir_usage_control’ 0):

SQL> alter session set optimizer_adaptive_features=true;

Session altered.

SQL> explain plan for
  2  select/*+ OPT_PARAM('_optimizer_dsdir_usage_control' 0) */ *
  3    from
...
 64  /

Explained.

SQL> select * from table(dbms_xplan.display('','','+alias +outline -predicate +note'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 1801607928

-------------------------------------------------------------------------------------------------------
| Id  | Operation                                             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                      |    10 | 17700 |   125   (1)| 00:00:01 |
|   1 |  VIEW                                                 |    10 | 17700 |   125   (1)| 00:00:01 |
|   2 |   WINDOW SORT PUSHED RANK                             |    40 | 14120 |   125   (1)| 00:00:01 |
|   3 |    CONCATENATION                                      |       |       |            |          |
|   4 |     NESTED LOOPS                                      |     4 |  1412 |    13   (0)| 00:00:01 |
...
|  83 |       INDEX UNIQUE SCAN                               |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   1 - SEL$D48D1E2F   / from$_subquery$_001@SEL$1
   2 - SEL$D48D1E2F
  14 - SEL$D48D1E2F_1 / I@SEL$4
  15 - SEL$D48D1E2F_1 / I@SEL$4
...

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

  /*+
      BEGIN_OUTLINE_DATA
...
      USE_CONCAT(@"SEL$D48D1E2F" 8 OR_PREDICATES(1)) -- *
...
      ALL_ROWS
      OPT_PARAM('_optimizer_dsdir_usage_control' 0)
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

264 rows selected.

— секция Note плана в 2-х последних случаях пуста ввиду отключения цепочки SQL Plan Directives -> для Adaptive Dynamic Statistics (Sampling) параметрами

С другой стороны, если вернуть optimizer_adaptive_features в исходное/default-ное положение и воспользоваться готовой подсказкой секции Outline Data предыдущего плана (*) — запрос успешно использует OR-Expansion / CONCATENATION, показав значительно меньшую стоимость: 50 (**) против Cost=2197K первоначального плана:

SQL> alter session set optimizer_adaptive_features=true;

Session altered.

SQL> explain plan for
  2  select/*+ USE_CONCAT(@"SEL$D48D1E2F" 8 OR_PREDICATES(1)) */ *
  3    from 
...
 64  /

Explained.

SQL> select * from table(dbms_xplan.display('','','+alias'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 4041711624

-------------------------------------------------------------------------------------------------------
| Id  | Operation                                             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                      |    10 | 17700 |    50   (2)| 00:00:01 | -- **
|*  1 |  VIEW                                                 |    10 | 17700 |    50   (2)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK                             |     7 |  2471 |    50   (2)| 00:00:01 |
|   3 |    CONCATENATION                                      |       |       |            |          |
|   4 |     NESTED LOOPS                                      |     4 |  1412 |    13   (0)| 00:00:01 |
...
|* 83 |      INDEX UNIQUE SCAN                                |     1 |     5 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   1 - SEL$D48D1E2F   / from$_subquery$_001@SEL$1
   2 - SEL$D48D1E2F
  14 - SEL$D48D1E2F_1 / I@SEL$4
  15 - SEL$D48D1E2F_1 / I@SEL$4
...

Note
-----
   - dynamic statistics used: dynamic sampling (level=2) -- ****
   - 7 Sql Plan Directives used for this statement       -- ***

214 rows selected.

— используя в процессе построения плана всё те же 7 Sql Plan Directives (***) и как следствие тот же dynamic sampling (level=2) (****), что и в первоначальном плане — т.е. эти адаптивныя фичи явно / принципиально не являются несовместимыми с преобразованием OR-Expansion

Учитывая, что для другого подобного запроса аналогичного рез-та (использования OR-Expansion без подсказки USE_CONCAT) удалось добиться, отключив генерацию адаптивных планов параметром OPT_PARAM(‘_optimizer_nlj_hj_adaptive_join’ ‘false’), можно предположить временнЫе ограничения оптимизации или принципиальную не[полную] совместимость технологий, подобную несовместимости с также адаптивной технологией предыдущего релиза Cardinality Feedback:

SQL> @fix 12812697

     BUGNO  VALUE SQL_FEATURE                    DESCRIPTION                                    OPTIMIZER_FEATURE_ENABLE       EVENT IS_DEFAULT
---------- ------ ------------------------------ ---------------------------------------------- ------------------------- ---------- ----------
  12812697      1 QKSFM_STATS_FEEDBACK_12812697  Disable Cardinality Feedback for Or-Expansion  11.2.0.4                           0          1

, в описании которого в частности, сказано:

Cardinality feedback sometimes produces worse plans when or-expansion is used in the query.

Cardinaltiy feedback is used for nodes appearing inside an or-expanded query block.

В следующей большой версии 12.2 всё меняется, как в части OR-Expansion:

Oracle Database 12c Release 2 replaces the OR expansion with the Cost Base OR Expansion Transformation…:

  • CONCATENATION is replaced with UNION-ALL.
  • Each UNION-ALL branch can be subject to further query transformations, if applicable. This is not possible with CONCATENATION.
  • Parallel queries can execute UNION-ALL branches concurrently. Again, this is not possible with CONCATENATION.

— полезные практические детали в NENAD NOVELJIC.COST-BASED OR EXPANSION TRANSFORMATION

, так и в части 12.2.Adaptive features, где optimizer_adaptive_statistics=FALSE по умолчанию, отключая:

  • SQL plan directives [отключая попутно adaptive dynamic sampling for serial execution, что актуально для рассматриваемого случая]
  • Statistics feedback for joins [что может быть актуально]
  • Performance feedback
  • Adaptive dynamic sampling for parallel execution
Реклама

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

w

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: