Oracle mechanics

05.02.2012

First K Rows optimization

Filed under: CBO,Optimizer features,Oracle — Игорь Усольцев @ 00:28
Tags: , , , , ,

First K Rows optimization — достаточно известный подход при котором оптимальный план выполнения пересчитывается с учётом значения K —  целевого количества строк, которые будут получены в запросе. Применяется при использовании параметров optimizer_mode = first_rows, first_rows_n на уровне сессии/системы или соответствующих подсказок на уровне запросов. Тот же метод прозрачно (internally) применяется при оптимизации запросов с конструкцией ROWNUM

И если в версии 10.2 использование First K Rows optimization можно было регулировать скрытым параметром:

SQL> @param_ _optimizer_rownum_pred_based_fkr

NAME                              VALUE  IS_DEF  IS_MOD  IS_ADJ  DSC
--------------------------------- ------ ------- ------- ------- ------------------------------------------------------
_optimizer_rownum_pred_based_fkr  TRUE   TRUE    FALSE   FALSE   enable the use of first K rows due to rownum predicate

, то начиная с Oracle 11g управление усложнилось, очевидно, в связи с появлением множества других версионных улучшений/фич

Далее — описание проблемы и методов решения на примере непростого запроса — соответственно, немаленький, но достаточно показательный план выполнения — короче продемонстрировать не получилось :(

Проблемный запрос в версии 11.2.0.3 выглядел так:

11.2.0.3@SQL> SELECT
2    FROM (SELECT id AS id
3            FROM (SELECT id
4                    FROM (SELECT t_act.id AS id
5                            FROM t_act_trans
6                            JOIN t_act
7                              ON t_act.id = t_act_trans.act_id
8                            JOIN t_consume
9                              ON t_consume.id = t_act_trans.consume_id
10                            JOIN t_order
11                              ON t_order.id = t_consume.parent_order_id
12                           WHERE t_act.dt > to_date('2011.3.17 04:48:02','yyyy.mm.dd hh24:mi:ss')
13                             AND t_order.client_id IN
14                             (select distinct dst_client_id from
15                                 (SELECT dst_client_id, src_client_id FROM xxx_aclient_urls_1
16                                  union all
17                                  SELECT dst_client_id, src_client_id FROM xxx_aclient_urls_2)
18                              WHERE src_client_id = 2857505)
19                             AND t_act.hidden < 4
20                             )
21                   WHERE ROWNUM <= 1
22                   ));

Elapsed: 00:23:45.23

Почти 24 минут — большое время выполнения, учитывая что в 10.2.0.4 выполнение занимало секунды!

Execution Plan
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |     1 |       |    14   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                     |                        |     1 |       |            |          |
|   2 |   VIEW                              |                        |     1 |       |    14   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY                    |                        |       |       |            |          |
|*  4 |     FILTER                          |                        |       |       |            |          |
|   5 |      NESTED LOOPS                   |                        |       |       |            |          |
|   6 |       NESTED LOOPS                  |                        |     1 |    53 |    10   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                 |                        |     2 |    82 |     9   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                |                        |     3 |    87 |     6   (0)| 00:00:01 |
|*  9 |          TABLE ACCESS BY INDEX ROWID| T_ACT                  |  2732K|    44M|     5   (0)| 00:00:01 |
|* 10 |           INDEX RANGE SCAN          | DT_INDEX_T_ACT         |       |       |     4   (0)| 00:00:01 |
|  11 |          TABLE ACCESS BY INDEX ROWID| T_ACT_TRANS            |     2 |    24 |     1   (0)| 00:00:01 |
|* 12 |           INDEX RANGE SCAN          | T_ACT_TRANS_ACT_ID_IDX |     2 |       |     0   (0)| 00:00:01 |
|  13 |         TABLE ACCESS BY INDEX ROWID | T_CONSUME              |     1 |    12 |     1   (0)| 00:00:01 |
|* 14 |          INDEX UNIQUE SCAN          | T_CONSUME_PK           |     1 |       |     0   (0)| 00:00:01 |
|* 15 |        INDEX UNIQUE SCAN            | T_ORDER_PK             |     1 |       |     0   (0)| 00:00:01 |
|  16 |       TABLE ACCESS BY INDEX ROWID   | T_ORDER                |     1 |    12 |     1   (0)| 00:00:01 |
|  17 |      PX COORDINATOR                 |                        |       |       |            |          |
|  18 |       PX SEND QC (RANDOM)           | :TQ10000               |     2 |    26 |     4   (0)| 00:00:01 |
|  19 |        VIEW                         |                        |     2 |    26 |     4   (0)| 00:00:01 |
|  20 |         UNION-ALL                   |                        |       |       |            |          |
|  21 |          PX BLOCK ITERATOR          |                        |     1 |    12 |     2   (0)| 00:00:01 |
|* 22 |           TABLE ACCESS FULL         | XXX_ACLIENT_URLS_1     |     1 |    12 |     2   (0)| 00:00:01 |
|  23 |          PX BLOCK ITERATOR          |                        |     1 |    12 |     2   (0)| 00:00:01 |
|* 24 |           TABLE ACCESS FULL         | XXX_ACLIENT_URLS_2     |     1 |    12 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
...
   9 - filter("T_ACT"."HIDDEN"TO_DATE(' 2011-03-17 04:48:02', 'syyyy-mm-dd hh24:mi:ss'))
...
  22 - filter("SRC_CLIENT_ID"=2857505 AND "DST_CLIENT_ID"=:B1)
  24 - filter("SRC_CLIENT_ID"=2857505 AND "DST_CLIENT_ID"=:B1)

Note
-----
- dynamic sampling used for this statement (level=5)

В плане можно заметить:

1) При выполнении запроса применяется параллельное выполнение (DEGREE=5 у таблиц XXX_ACLIENT_URLS_1 / 2) и, как следствие, dynamic sampling меняется для всего запроса, что и отражено в секции Note плана. Собственно операция dynamic sampling применяется только к таблице T_ACT (как будет видно далее, ключевой для запроса), обрабатываемой непараллельно, и не применяется к таблицам с параллельным доступом (являющихся причиной этой операции),что можно увидеть в 10053 трейсе:

Access path analysis for T_ACT
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T_ACT[T_ACT]
...
** Dynamic sampling initial checks returning TRUE (level = 5)

2) Стоимость плана абсолютно точно складывается из 2-х частей: «основного» запроса (строки 6-16 плана) стоимостью 10 [условных единиц] и параллельного подзапроса с UNION ALL (строки 18-24) стоимость 4 , что в сумме даёт итоговую стоимость 14. В соответствии с планом (Rows) подзапрос UNION ALL предполагалось выполнить ровно один раз, при этом условия подзапроса Predicate Information 22, 24 — «DST_CLIENT_ID»=:B1 — через динамическую связанную переменную должны быть получены из «основного» запроса

3) Самые интересные результаты в смысле оценки кол-ва возвращаемых строк показывает основной запрос: при очень точной оценке кол-ва получаемых по условиям (Predicate Information 9, 10) строк из таблицы T_ACT ~ 2732K, весь запрос по оценке Oracle должен вернуть ОДНУ строку!

Трейс 10053 показывает, что в процессе трансформации запроса (Query transformations), стоимостных расчётов/оптимизации и cost-based query transformation (CBQT) Oracle находит правильный и оптимальный (как будет показано ниже) порядок доступа к таблицам:

***********************
Join order[4]:  VW_NSO_2[VW_NSO_2]#0  T_ORDER[T_ORDER]#1  T_CONSUME[T_CONSUME]#3  T_ACT_TRANS[T_ACT_TRANS]#4  T_ACT[T_ACT]#2

***********************
Best so far:  Table#: 0  cost: 5.1125  card: 1.0000  bytes: 13
Table#: 1  cost: 5.7796  card: 2.5849  bytes: 75
Table#: 3  cost: 14.5017  card: 44.4414  bytes: 1628
Table#: 4  cost: 32.3059  card: 50.4239  bytes: 2450
Table#: 2  cost: 43.4231  card: 11.9358  bytes: 792
***********************

— после чего начинается шаманство оптимизация First K Rows, порождающая реально неправильный, но формально более выгодный  план с итоговой стомостью 14 (приведён выше)

Ок, проблема понятна — остаётся найти способ отключить. Изменение дефолтного значения упомянутого параметра _optimizer_rownum_pred_based_fkr, прекрасно работавший в версии 10.2, в этом случае не помогает:

11.2.0.3@SQL> alter session set "_optimizer_rownum_pred_based_fkr"=false;

Session altered.
11.2.0.3@SQL> @param_ _optimizer_rownum_pred_based_fkr

NAME                                       VALUE
------------------------------------------ -----
_optimizer_rownum_pred_based_fkr           FALSE

11.2.0.3@SQL> SELECT count(1) AS count_1 ...

— план не меняется, однако есть вполне подходящий _fix_control оптимизатора 4887636, снимающий неведомые ограничения технологии first K row optimization, начиная с версии 11.1.0.6:

11.2.0.3@SQL> select * from v$system_fix_control where description like '%first K row optimization%';

BUGNO    VALUE  SQL_FEATURE               DESCRIPTION                                       OPTIMIZER_FEATURE_ENABLE  EVENT IS_DEFAULT
-------- ------ ------------------------- ------------------------------------------------- ------------------------- ----- ----------
4887636       1 QKSFM_FIRST_ROWS_4887636  remove restriction from first K row optimization  11.1.0.6                      0          1

Отключение этого фикса заметно меняет время выполнения — с 24 минут до одной секунды, что является ожидаемым временем выполнения запроса:

11.2.0.3@SQL> SELECT/*+ OPT_PARAM('_fix_control' '4887636:0') */ count(1) AS count_1
...
23                   /

Elapsed: 00:00:01.21

----------------------------------------------------------------------------------------
| Id  | Operation                              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |     1 |       |    43   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE                        |     1 |       |            |          |
|   2 |   VIEW                                 |     1 |       |    43   (3)| 00:00:01 |
|*  3 |    COUNT STOPKEY                       |       |       |            |          |
|   4 |     PX COORDINATOR                     |       |       |            |          |
|   5 |      PX SEND QC (RANDOM)               |       |       |            |          |
|*  6 |       COUNT STOPKEY                    |       |       |            |          |
|   7 |        NESTED LOOPS                    |       |       |            |          |
|   8 |         NESTED LOOPS                   |    12 |   792 |    43   (3)| 00:00:01 |
|   9 |          NESTED LOOPS                  |    50 |  2450 |    32   (4)| 00:00:01 |
|  10 |           NESTED LOOPS                 |    44 |  1628 |    14   (8)| 00:00:01 |
|  11 |            NESTED LOOPS                |     3 |    75 |     6  (17)| 00:00:01 |
|  12 |             VIEW                       |     2 |    26 |     4   (0)| 00:00:01 |
|  13 |              HASH UNIQUE               |     1 |    26 |            |          |
|  14 |               PX RECEIVE               |     2 |    26 |     4   (0)| 00:00:01 |
|  15 |                PX SEND HASH            |     2 |    26 |     4   (0)| 00:00:01 |
|  16 |                 VIEW                   |     2 |    26 |     4   (0)| 00:00:01 |
|  17 |                  UNION-ALL             |       |       |            |          |
|  18 |                   PX BLOCK ITERATOR    |     1 |    12 |     2   (0)| 00:00:01 |
|* 19 |                    TABLE ACCESS FULL   |     1 |    12 |     2   (0)| 00:00:01 |
|  20 |                   PX BLOCK ITERATOR    |     1 |    12 |     2   (0)| 00:00:01 |
|* 21 |                    TABLE ACCESS FULL   |     1 |    12 |     2   (0)| 00:00:01 |
|  22 |             TABLE ACCESS BY INDEX ROWID|     3 |    36 |     3   (0)| 00:00:01 |
|* 23 |              INDEX RANGE SCAN          |     3 |       |     0   (0)| 00:00:01 |
|  24 |            TABLE ACCESS BY INDEX ROWID |    17 |   204 |    16   (0)| 00:00:01 |
|* 25 |             INDEX RANGE SCAN           |    17 |       |     0   (0)| 00:00:01 |
|  26 |           TABLE ACCESS BY INDEX ROWID  |     1 |    12 |     2   (0)| 00:00:01 |
|* 27 |            INDEX RANGE SCAN            |     1 |       |     0   (0)| 00:00:01 |
|* 28 |          INDEX UNIQUE SCAN             |     1 |       |     0   (0)| 00:00:01 |
|* 29 |         TABLE ACCESS BY INDEX ROWID    |     1 |    17 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

— порядок доступа к таблицам становится гораздо более логичным

Влияние параметра отключения _optimizer_rownum_pred_based_fkr и _fix_control 4887636 на операции First K Rows можно оценить с помощью простых bash-скриптов с файлами 10053 трейса:

$ grep 'First K Rows' 10053.trc | wc -l # трейс оригинального "медленного" запроса
 212

$ grep 'First K Rows'  _optimizer_rownum_pred_based_fkr_false.trc | wc -l # трейс запроса с _optimizer_rownum_pred_based_fkr=false
 188

$ grep 'First K Rows'  _fix_control_4887636_off.trc | wc -l # трейс при _fix_control 4887636:OFF
 0

— механизм управления First K Rows начиная с Oracle 11g действительно изменился

Стоит отметить, что причиной описанного бага является не только некорректное применение First K Rows optimization, вызванное использованием ROWNUM в условиях запроса, но и параллельное исполнение части запроса — что можно понимать как проблему(баг) на пересечение технологий оптимизатора.

При непараллельном выполнении, например, с помощью подсказки /*+ NOPARALLEL*/ Oracle формирует  быстрый план с «правильным» порядком соединения таблиц, несмотря на использование First K Rows optimization, и даже более низкой стоимостью, чем при параллельном выполнении:

11.2.0.3@SQL> SELECT/*+ NOPARALLEL*/ count(1) AS count_1
...
23  /

-------------------------------------------------------------------------------------
| Id  | Operation                           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |     1 |       |    25   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE                     |     1 |       |            |          |
|   2 |   VIEW                              |     1 |       |    25   (4)| 00:00:01 |
|*  3 |    COUNT STOPKEY                    |       |       |            |          |
|   4 |     NESTED LOOPS                    |       |       |            |          |
|   5 |      NESTED LOOPS                   |     1 |    66 |    25   (4)| 00:00:01 |
|   6 |       NESTED LOOPS                  |     5 |   245 |    20   (5)| 00:00:01 |
|   7 |        NESTED LOOPS                 |     5 |   185 |    12   (9)| 00:00:01 |
|   8 |         NESTED LOOPS                |     1 |    25 |     7  (15)| 00:00:01 |
|   9 |          VIEW                       |     1 |    13 |     5  (20)| 00:00:01 |
|  10 |           HASH UNIQUE               |     1 |     6 |     5  (20)| 00:00:01 |
|  11 |            VIEW                     |     2 |    12 |     4   (0)| 00:00:01 |
|  12 |             UNION-ALL               |       |       |            |          |
|* 13 |              TABLE ACCESS FULL      |     1 |    12 |     2   (0)| 00:00:01 |
|* 14 |              TABLE ACCESS FULL      |     1 |    12 |     2   (0)| 00:00:01 |
|  15 |          TABLE ACCESS BY INDEX ROWID|     1 |    12 |     2   (0)| 00:00:01 |
|* 16 |           INDEX RANGE SCAN          |     1 |       |     0   (0)| 00:00:01 |
|  17 |         TABLE ACCESS BY INDEX ROWID |     5 |    60 |     5   (0)| 00:00:01 |
|* 18 |          INDEX RANGE SCAN           |     5 |       |     0   (0)| 00:00:01 |
|  19 |        TABLE ACCESS BY INDEX ROWID  |     1 |    12 |     2   (0)| 00:00:01 |
|* 20 |         INDEX RANGE SCAN            |     1 |       |     0   (0)| 00:00:01 |
|* 21 |       INDEX UNIQUE SCAN             |     1 |       |     0   (0)| 00:00:01 |
|* 22 |      TABLE ACCESS BY INDEX ROWID    |     1 |    17 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

, что является просто «ценовым» эффектом применения First K Rows optimization, который исчезает при отключении: стоимость и время запроса возрастают, несмотря на использование того же плана и такое же количество возвращаемых запросом строк:

11.2.0.3@SQL> SELECT/*+ noparallel OPT_PARAM('_fix_control' '4887636:0')*/ count(1) AS count_1
...
23  /

-------------------------------------------------------------------------------------
| Id  | Operation                           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |     1 |       |   176   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE                     |     1 |       |            |          |
|   2 |   VIEW                              |     1 |       |   176   (1)| 00:00:02 |
|*  3 |    COUNT STOPKEY                    |       |       |            |          |
|   4 |     NESTED LOOPS                    |       |       |            |          |
|   5 |      NESTED LOOPS                   |    12 |   792 |   176   (1)| 00:00:02 |
|   6 |       NESTED LOOPS                  |    50 |  2450 |   126   (1)| 00:00:02 |
|   7 |        NESTED LOOPS                 |    44 |  1628 |    46   (3)| 00:00:01 |
|   8 |         NESTED LOOPS                |     3 |    75 |     8  (13)| 00:00:01 |
|   9 |          VIEW                       |     2 |    26 |     4   (0)| 00:00:01 |
|  10 |           HASH UNIQUE               |     1 |    26 |            |          |
|  11 |            VIEW                     |     2 |    26 |     4   (0)| 00:00:01 |
|  12 |             UNION-ALL               |       |       |            |          |
|* 13 |              TABLE ACCESS FULL      |     1 |    12 |     2   (0)| 00:00:01 |
|* 14 |              TABLE ACCESS FULL      |     1 |    12 |     2   (0)| 00:00:01 |
|  15 |          TABLE ACCESS BY INDEX ROWID|     3 |    36 |     3   (0)| 00:00:01 |
|* 16 |           INDEX RANGE SCAN          |     3 |       |     0   (0)| 00:00:01 |
|  17 |         TABLE ACCESS BY INDEX ROWID |    17 |   204 |    16   (0)| 00:00:01 |
|* 18 |          INDEX RANGE SCAN           |    17 |       |     0   (0)| 00:00:01 |
|  19 |        TABLE ACCESS BY INDEX ROWID  |     1 |    12 |     2   (0)| 00:00:01 |
|* 20 |         INDEX RANGE SCAN            |     1 |       |     0   (0)| 00:00:01 |
|* 21 |       INDEX UNIQUE SCAN             |     1 |       |     0   (0)| 00:00:01 |
|* 22 |      TABLE ACCESS BY INDEX ROWID    |     1 |    17 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

По номеру фикса 4887636 на сайте поддержки можно найти несколько подобных багов для Oracle 11.1, с аналогичными предлагаемыми методами решения (workaround):

  • Setting «_fix_control» = «4887636:OFF»
SQL> alter session set "_fix_control" = "4887636:OFF";
  • Explicitly hint the query, or remove the ROWNUM predicate and only fetch the desired number of rows
  • Disable parallel query
SQL> alter session disable parallel query;

1 комментарий »

  1. […] Полтора года назад столкнулся с проблемой оптимизатора при параллельном выполнении запросов с ограничением ROWNUM в версиях 11g, записанной в First K Rows optimization […]

    Уведомление от First K Rows optimization-2 | Oracle mechanics — 05.08.2013 @ 17:55 | Ответить


RSS feed for comments on this post. TrackBack URI

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

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

Логотип WordPress.com

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

Фотография Twitter

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

Фотография Facebook

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

Google+ photo

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

Connecting to %s

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

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