Oracle mechanics

22.12.2012

Join Predicate Push-Down, допускающий картезианское произведение

Filed under: CBO,heuristics,hints,Oracle — Игорь Усольцев @ 19:54
Tags: , ,

SQL трейс уровня 8+ («с ожиданиями») выявил медленный запрос, частовыполняемый при элементарных бизнес операциях:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.17       0.18          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     47.84      50.76        180     270283         34           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     48.02      50.94        180     270283         34           0

Статистика выполнения из того же трейса показывает, что при построении плана запроса не срабатывает ожидаемая операция Join Predicate Push-Down (JPPD), приводя к TABLE ACCESS FULL по хорошо индексированной таблице T2:

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  HASH GROUP BY (cr=270337 pr=180 pw=0 time=50763274 us cost=101306 size=206 card=2)
         0          0          0   VIEW  (cr=270337 pr=180 pw=0 time=50763199 us cost=101305 size=206 card=2)
         0          0          0    UNION-ALL  (cr=270337 pr=180 pw=0 time=50763199 us)
         0          0          0     HASH GROUP BY (cr=269601 pr=4 pw=0 time=48336723 us cost=101202 size=171 card=1)
         0          0          0      FILTER  (cr=269601 pr=4 pw=0 time=48336663 us)
         1          1          1       NESTED LOOPS OUTER (cr=269601 pr=4 pw=0 time=48336545 us cost=101201 size=171 card=1)
         1          1          1        NESTED LOOPS  (cr=269598 pr=4 pw=0 time=48336526 us cost=101199 size=144 card=1)
         1          1          1         NESTED LOOPS  (cr=269593 pr=4 pw=0 time=48336476 us cost=101196 size=110 card=1)
         1          1          1          HASH JOIN  (cr=269591 pr=4 pw=0 time=48336454 us cost=101196 size=105 card=1)                                 -- практически всё время выполнения
         1          1          1           NESTED LOOPS  (cr=20 pr=4 pw=0 time=172755 us)
                                           ...
   4181113    4181113    4181113           VIEW  (cr=269571 pr=0 pw=0 time=24145350 us cost=101116 size=204460520 card=4646830)
   4181113    4181113    4181113            UNION-ALL  (cr=269571 pr=0 pw=0 time=23397447 us)
         0          0          0             FILTER  (cr=27 pr=0 pw=0 time=1031 us)
         0          0          0              TABLE ACCESS BY INDEX ROWID T1 (cr=0 pr=0 pw=0 time=0 us cost=25633 size=24479334 card=741798)
         0          0          0               INDEX SKIP SCAN T1_WRONG_IDX (cr=0 pr=0 pw=0 time=0 us cost=14514 size=0 card=741798)(object id 938167) -- skip scan по плохому индексу
   4181113    4181113    4181113             FILTER  (cr=269544 pr=0 pw=0 time=21804605 us)
   4181113    4181113    4181113              TABLE ACCESS FULL T2 (cr=269517 pr=0 pw=0 time=21101505 us cost=75483 size=128866056 card=3905032)       -- медленно, 21 сек
...

План выполнения также показывает, что основную стоимость запроса составляют TABLE ACCESS FULL по T2 и неоптимальный INDEX SKIP SCAN доступ к таблице T1:

11.2.0.3.@ SQL> select
  2   x.party_id, sum(x.end_saldo) end_saldo, x.k_header_id k_header_id
  3    from (SELECT party_id, end_saldo, k_header_id
  4            from VIEW_1 v
  5           where party_id = 58799) x
  6   group by x.party_id, x.k_header_id
  7  /

Execution Plan
---------------------------
Plan hash value: 3313391478

---------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
...
|   3 |    VIEW                                  |              |     1 |    21 |   107K  (3)| 00:21:33 |
|   4 |     HASH GROUP BY                        |              |     1 |   185 |   107K  (3)| 00:21:33 |
|*  5 |      FILTER                              |              |       |       |         |             |
|   6 |       NESTED LOOPS OUTER                 |              |     1 |   185 |   107K  (3)| 00:21:33 |
|   7 |        NESTED LOOPS                      |              |     1 |   158 |   107K  (3)| 00:21:33 |
|   8 |         NESTED LOOPS                     |              |     1 |   124 |   107K  (3)| 00:21:32 |
|*  9 |          HASH JOIN                       |              |     2 |   228 |   107K  (3)| 00:21:32 |
|  10 |           NESTED LOOPS                   |              |       |       |         |             |
|  11 |            NESTED LOOPS                  |              |     1 |    70 |    62   (0)| 00:00:01 |
|  12 |             NESTED LOOPS                 |              |     1 |    49 |     7   (0)| 00:00:01 |
|  13 |              NESTED LOOPS                |              |     1 |    32 |     4   (0)| 00:00:01 |
|* 14 |               TABLE ACCESS BY INDEX ROWID|              |     1 |    19 |     3   (0)| 00:00:01 |
|* 15 |                INDEX UNIQUE SCAN         |              |     1 |       |     2   (0)| 00:00:01 |
|* 16 |               TABLE ACCESS BY INDEX ROWID|              |     1 |    13 |     1   (0)| 00:00:01 |
|* 17 |                INDEX UNIQUE SCAN         |              |     1 |       |     0   (0)| 00:00:01 |
|* 18 |              TABLE ACCESS BY INDEX ROWID |              |     1 |    17 |     3   (0)| 00:00:01 |
|* 19 |               INDEX RANGE SCAN           |              |     1 |       |     2   (0)| 00:00:01 |
|* 20 |             INDEX RANGE SCAN             |              |    53 |       |     3   (0)| 00:00:01 |
|* 21 |            TABLE ACCESS BY INDEX ROWID   |              |     3 |    63 |    55   (0)| 00:00:01 |
|  22 |           VIEW                           |              |  4662K|   195M|   107K  (3)| 00:21:31 | -- основная стоимость:
|  23 |            UNION-ALL                     |              |       |       |         |             |
|* 24 |             FILTER                       |              |       |       |         |             |
|  25 |              TABLE ACCESS BY INDEX ROWID | T1           |   741K|    23M| 29529   (1)| 00:05:55 | -- здесь
|* 26 |               INDEX SKIP SCAN            | T1_WRONG_IDX |   745K|       | 18215   (1)| 00:03:39 |
|* 27 |             FILTER                       |              |       |       |         |             |
|* 28 |              TABLE ACCESS FULL           | T2           |  3920K|   123M| 78052   (4)| 00:15:37 | -- и здесь
...
---------------------------------------------------------------------------------------------------------

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

  ...
  26 - access("T1"."END_DATE"="PARAM_PACKAGE"."GET_DATE"('END_DATE'))
  ...
  28 - filter("T2"."END_DATE"="PARAM_PACKAGE"."GET_DATE"('END_DATE'))

— при доступе по достаточно слабым условиям, передаваемым к тому же через переменные пакета, что само по себе — дурная идея, но не является проблемой в этом случае, т.к. данные по датам распределены равномерно

Трейс оптимизатора показывает причину неприменения JPPD — вероятное появление картезианского продукта в результате:

***********************************
Cost-Based Join Predicate Push-down
***********************************
JPPD: Checking validity of push-down in query block SEL$0568A91F (#2)
JPPD:   Checking validity of push-down from query block SEL$0568A91F (#2) to query block SET$1 (#3)
JPPD:     Passed validity checks
JPPD:     JPPD bypassed: Cartesian product found

, что объясняется в Bug 9380298 Allow JPPD heuristics to produce cartesian product:

By design JPPD does not consider to push join predicates into a view if this results in a cartesian product between the tables involved in the pushed predicates

, где рекомендуется рекомендуется управлять подобным поведением с помощью:

SQL> @fix 9380298

  BUGNO VALUE SQL_FEATURE        DESCRIPTION                                OPTIMIZER_FEATURE_ENABLE  EVENT IS_DEFAULT
------- ----- ------------------ ------------------------------------------ ------------------------- ----- ----------
9380298     0 QKSFM_JPPD_9380298 JPPD for cartesian joins under fix control                               0   1

Проверка:

SQL> select--+ opt_param('_fix_control' '9380298:1')
  2   x.party_id, sum(x.end_saldo) end_saldo, x.k_header_id k_header_id
  3    from (SELECT party_id, end_saldo, k_header_id
  4            from VIEW_1 v
  5           where party_id = 58799) x
  6   group by x.party_id, x.k_header_id
  7  /

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( '','','OUTLINE'));

PLAN_TABLE_OUTPUT
---------------------------

Plan hash value: 3313391478                  -- план выполнения не изменился :(

...

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('_fix_control' '9380298:1') -- хотя изменение параметра на уровне запроса отаботало успешно
      ALL_ROWS
      ...
      END_OUTLINE_DATA
  */

— показывает, что изменение значения этого фикса действительно устраняет запись JPPD bypassed: Cartesian product found из трейса оптимизатора, однако никак не влияет на план

Непосредственное использование подсказки PUSH_PRED форсирует использование JPPD, что заметно ускоряет запрос — столбец Time в приведённых планах достаточно хорошо отражает пропорции изменений времени реального выполнения запроса:

SQL> select--+ push_pred(x.v.t)
  2   x.party_id, sum(x.end_saldo) end_saldo, x.k_header_id k_header_id
  3    from (SELECT party_id, end_saldo, k_header_id
  4            from VIEW_1 v
  5           where party_id = 58799) x
  6   group by x.party_id, x.k_header_id
  7  /

Plan hash value: 1143412740

--------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
...
|   3 |    VIEW                                 |              |     1 |    21 |   455   (1)| 00:00:06 |
|   4 |     HASH GROUP BY                       |              |     1 |   185 |   455   (1)| 00:00:06 |
|*  5 |      FILTER                             |              |       |       |         |             |
|   6 |       NESTED LOOPS OUTER                |              |     1 |   185 |   454   (1)| 00:00:06 |
|   7 |        NESTED LOOPS                     |              |     1 |   158 |   452   (1)| 00:00:06 |
|   8 |         NESTED LOOPS                    |              |     3 |   342 |   397   (1)| 00:00:05 |
|*  9 |          HASH JOIN                      |              |     1 |    93 |   342   (1)| 00:00:05 |
|  10 |           MERGE JOIN CARTESIAN          |              |     1 |    83 |    17   (0)| 00:00:01 | -- появился MJC
|  11 |            NESTED LOOPS                 |              |     2 |   132 |    11   (0)| 00:00:01 |
|  12 |             NESTED LOOPS                |              |     1 |    32 |     4   (0)| 00:00:01 |
|* 13 |              TABLE ACCESS BY INDEX ROWID|              |     1 |    19 |     3   (0)| 00:00:01 |
|* 14 |               INDEX UNIQUE SCAN         |              |     1 |       |     2   (0)| 00:00:01 |
|* 15 |              TABLE ACCESS BY INDEX ROWID|              |     1 |    13 |     1   (0)| 00:00:01 |
|* 16 |               INDEX UNIQUE SCAN         |              |     1 |       |     0   (0)| 00:00:01 |
|* 17 |             TABLE ACCESS BY INDEX ROWID |              |     2 |    68 |     7   (0)| 00:00:01 |
|* 18 |              INDEX RANGE SCAN           |              |     9 |       |     2   (0)| 00:00:01 |
|  19 |            BUFFER SORT                  |              |     1 |    17 |    10   (0)| 00:00:01 |
|* 20 |             TABLE ACCESS BY INDEX ROWID |              |     1 |    17 |     3   (0)| 00:00:01 |
|* 21 |              INDEX RANGE SCAN           |              |     1 |       |     2   (0)| 00:00:01 |
|  22 |           TABLE ACCESS FULL             |              | 41319 |   403K|   325   (1)| 00:00:04 |
|* 23 |          TABLE ACCESS BY INDEX ROWID    |              |     3 |    63 |    55   (0)| 00:00:01 |
|* 24 |           INDEX RANGE SCAN              |              |    53 |       |     3   (0)| 00:00:01 |
|  25 |         VIEW                            |              |     1 |    44 |    18   (0)| 00:00:01 |
|  26 |          UNION ALL PUSHED PREDICATE     |              |       |       |         |             | -- JPPD работает
|* 27 |           FILTER                        |              |       |       |         |             |
|* 28 |            TABLE ACCESS BY INDEX ROWID  | T1           |     1 |    33 |     9   (0)| 00:00:01 |
|* 29 |             INDEX RANGE SCAN            | T1_RIGHT_IDX |     6 |       |     3   (0)| 00:00:01 | -- используется RANGE SCAN по правильному индексу
|* 30 |           FILTER                        |              |       |       |         |             |
|* 31 |            TABLE ACCESS BY INDEX ROWID  | T2           |     1 |    33 |     9   (0)| 00:00:01 |
|* 32 |             INDEX RANGE SCAN            | T1_RIGHT_IDX |     4 |       |     4   (0)| 00:00:01 | -- --//--
|* 33 |        INDEX RANGE SCAN                 |              |     1 |    27 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      ...
      PUSH_PRED(@"SEL$124B6A1D" "T"@"SEL$3" 7 2)
      ...
      END_OUTLINE_DATA
  */

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

  29 - access("T1"."BILL_TRX_NUMBER"="TRX_NUMBER")
  ...
  32 - access("T2"."BILL_TRX_NUMBER"="TRX_NUMBER")
  ...

— при этом:

  • в плане появляется MERGE JOIN CARTESIAN, из-за которого оптимизатор не использовал JPPD ранее
  • несмотря на то, что JPPD относится к Cost Based Query Transformation , использованное по умолчанию при построении первого плана правило JPPD bypassed: Cartesian product found только увеличивало общую стоимость, в то время как форсированное применение JPPD снизило общую стоимость более чем на 2 порядка — с 107,000 до 455

Из чего разумно следует попытка отключить Cost Based функционал JPPD, как указано в Bug 8882518 : JPPD SOMETIMES NOT OCCURING FOR UNION ALL, например, — баг старый (11.1.0.7), но формально неисправлениый до сих пор:

SQL> @param_ _optimizer_push_pred_cost_based

NAME                            VALUE  IS_DEF   DSC
------------------------------- ------ -------- --------------------------------------------------------------
_optimizer_push_pred_cost_based TRUE   TRUE     use cost-based query transformation for push pred optimization

dd

SQL> select--+ opt_param('_optimizer_push_pred_cost_based' 'false')
  2   x.party_id, sum(x.end_saldo) end_saldo, x.k_header_id k_header_id
  3    from (SELECT party_id, end_saldo, k_header_id
  4            from VIEW_1 v
  5           where party_id = 58799) x
  6   group by x.party_id, x.k_header_id
  7  /

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( '','','OUTLINE'));

Plan hash value: 3433443061

--------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
...
|   3 |    VIEW                                 |              |     1 |    21 |    74   (2)| 00:00:01 | -- лучшая цена!
|   4 |     HASH GROUP BY                       |              |     1 |   167 |    74   (2)| 00:00:01 |
|   5 |      NESTED LOOPS                       |              |       |       |         |             |
|   6 |       NESTED LOOPS                      |              |     1 |   167 |    73   (0)| 00:00:01 |
|   7 |        NESTED LOOPS OUTER               |              |     1 |   133 |    70   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                    |              |     1 |   106 |    68   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                   |              |     1 |    96 |    67   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                  |              |     1 |    70 |    62   (0)| 00:00:01 |
|  11 |            NESTED LOOPS                 |              |     1 |    49 |     7   (0)| 00:00:01 |
|  12 |             NESTED LOOPS                |              |     1 |    32 |     4   (0)| 00:00:01 |
|* 13 |              TABLE ACCESS BY INDEX ROWID|              |     1 |    19 |     3   (0)| 00:00:01 |
|* 14 |               INDEX UNIQUE SCAN         |              |     1 |       |     2   (0)| 00:00:01 |
|* 15 |              TABLE ACCESS BY INDEX ROWID|              |     1 |    13 |     1   (0)| 00:00:01 |
|* 16 |               INDEX UNIQUE SCAN         |              |     1 |       |     0   (0)|          |
|* 17 |             TABLE ACCESS BY INDEX ROWID |              |     1 |    17 |     3   (0)| 00:00:01 |
|* 18 |              INDEX RANGE SCAN           |              |     1 |       |     2   (0)| 00:00:01 |
|* 19 |            TABLE ACCESS BY INDEX ROWID  |              |     3 |    63 |    55   (0)| 00:00:01 |
|* 20 |             INDEX RANGE SCAN            |              |    53 |       |     3   (0)| 00:00:01 |
|  21 |           VIEW                          |              |     1 |    26 |     5   (0)| 00:00:01 |
|  22 |            UNION-ALL PARTITION          |              |       |       |         |             |
|* 23 |             FILTER                      |              |       |       |         |             |
|* 24 |              TABLE ACCESS BY INDEX ROWID| T1           |     1 |    33 |     9   (0)| 00:00:01 |
|* 25 |               INDEX RANGE SCAN          | T1_RIGHT_IDX |     6 |       |     3   (0)| 00:00:01 | -- доступ по правильным индексам
|* 26 |             FILTER                      |              |       |       |         |             |
|* 27 |              TABLE ACCESS BY INDEX ROWID| T2           |     1 |    33 |     9   (0)| 00:00:01 |
|* 28 |               INDEX RANGE SCAN          | T2_RIGHT_IDX |     4 |       |     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')
      OPT_PARAM('_optimizer_push_pred_cost_based' 'false')                                  -- модифицированный параметр
      ALL_ROWS
      ...
      OLD_PUSH_PRED(@"SEL$124B6A1D" "T"@"SEL$3" ("T_REC_BALANCES_MONTH"."BILL_TRX_NUMBER")) -- "старая" реализация JPPD
      ...
      END_OUTLINE_DATA
  */

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

  ...
  25 - access("T1"."BILL_TRX_NUMBER"="TRX_NUMBER") -- доступ по условиям с высокой избирательностью
  ...
  28 - access("T2"."BILL_TRX_NUMBER"="TRX_NUMBER") -- --//--
  ...

— что в результате даёт интересный эффект:

  • быстрый INDEX RANGE SCAN доступ к таблицам — что было целью рассматриваемой оптимизации и ожидаемо
  • как это не странно, план с наименьшей стоимостью получен с помощью использования основанной НЕ на стоимости трансформации Join Predicate Push-Down. Т.е. с использованием варианта трансформации  «старого типа», без обновлённой в 11g Cost-Based составляющей и сопутствующих правил/ heuristics

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

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

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 такие блоггеры, как: