Oracle mechanics

10.12.2018

12.2: JPPD/OJPPD и параметр _OPTIMIZER_PUSH_PRED_COST_BASED

Filed under: 12.2,Oracle,Scripts — Игорь Усольцев @ 12:42
Tags:

До обновления 12.1 -> 12.2 запрос удовлетворительно (SLA, timeouts,…) быстро выполнялся по плану:

SQL_ID 4dgcbuug0z2mk
--------------------
  
Plan hash value: 1982197705
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                                          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                   |        |       |     8 (100)|          |
|   1 |  VIEW                                              |      1 |  1258 |     8  (13)| 00:00:01 |
|   2 |   WINDOW SORT                                      |      1 |  1180 |     8  (13)| 00:00:01 |
|   3 |    SORT GROUP BY ROLLUP                            |      1 |  1180 |     8  (13)| 00:00:01 |
|   4 |     NESTED LOOPS                                   |      1 |  1180 |     7   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                                  |      1 |  1180 |     7   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                                 |      1 |  1171 |     6   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                                |      1 |    24 |     5   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID                |      1 |    12 |     4   (0)| 00:00:01 |
|   9 |          INDEX UNIQUE SCAN                         |      1 |       |     3   (0)| 00:00:01 |
|  10 |         TABLE ACCESS BY INDEX ROWID BATCHED        |      1 |    12 |     1   (0)| 00:00:01 |
|  11 |          INDEX RANGE SCAN                          |      1 |       |     0   (0)|          |
|  12 |        VIEW                                        |      1 |  1147 |     1   (0)| 00:00:01 | -- **
|  13 |         UNION-ALL PARTITION                        |        |       |            |          | -- ***
|  14 |          NESTED LOOPS                              |      1 |   156 |    24   (0)| 00:00:01 |
|  15 |           NESTED LOOPS                             |      1 |   156 |    24   (0)| 00:00:01 |
|  16 |            NESTED LOOPS OUTER                      |      1 |   100 |    22   (0)| 00:00:01 |
|  17 |             NESTED LOOPS                           |      1 |    60 |    21   (0)| 00:00:01 |
|  18 |              TABLE ACCESS BY INDEX ROWID BATCHED   |      1 |    50 |    20   (0)| 00:00:01 | -- ****
|  19 |               INDEX RANGE SCAN                     |     17 |       |     4   (0)| 00:00:01 | -- ****
|  20 |              MAT_VIEW ACCESS BY INDEX ROWID BATCHED|      1 |    10 |     1   (0)| 00:00:01 | -- ****
|  21 |               INDEX RANGE SCAN                     |      1 |       |     0   (0)|          | -- ****
|  22 |             TABLE ACCESS BY INDEX ROWID            |      1 |    40 |     1   (0)| 00:00:01 | -- ****
|  23 |              INDEX UNIQUE SCAN                     |      1 |       |     0   (0)|          | -- ****
|  24 |            INDEX RANGE SCAN                        |      1 |       |     0   (0)|          | -- ****
|  25 |           TABLE ACCESS BY INDEX ROWID              |      1 |    56 |     2   (0)| 00:00:01 | -- ****
...
|  52 |       INDEX UNIQUE SCAN                            |      1 |       |     0   (0)|          |
|  53 |      TABLE ACCESS BY INDEX ROWID                   |      1 |     9 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
...
      OLD_PUSH_PRED(@"SEL$641071AC" "MS"@"SEL$3" ("INVOICE"."CLIENT_ID")) -- *
...
      END_OUTLINE_DATA
  */

, с использованием OJPPD — Old-stype (non-cost-based) Join Predicate Push-Down (*) высокоизбирательного условия («INVOICE».»CLIENT_ID») в обзор (**) вида UNION-ALL PARTITION (***) с последующим быстрым индексным доступом (****)

В версии 12.2, похоже, что-то существенно изменилось, OJPPD в процессе компиляции плана перестал рассматриваться совсем, в то время как Cost-based JPPD, похоже, как и в 12.1 (возможно и раньше), отказывается работать с Union-All View под надуманным предлогом (*), меняя при этом устаревшую (?) операцию UNION-ALL PARTITION на UNION-ALL:

JPPD:  Considering Cost-based predicate pushdown from query block SEL$1 (#1)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$1 (#1)
kkqctdrvTD-start: :
    call(in-use=953248, alloc=1145736), compile(in-use=5977952, alloc=6965904), execution(in-use=7839080, alloc=7841608)

Check Basic Validity for Non-Union View for query block SET$1 (#3)
JPPD:     JPPD bypassed: View is a set query block. -- (*)

и формируя вследствие неудачный план, изменение стоимости которого качественно отражает увеличение времени выполнения — Cost увеличилась на 6 порядков, Elapsed — на 3:(

============
Plan Table
============
----------------------------------------------------------------------------------------+
| Id  | Operation                                   | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------------------------------------------+
| 0   | SELECT STATEMENT                            |       |       | 5021K |           |
| 1   |  VIEW                                       |     1 |  1258 | 5021K |  16:43:40 |
| 2   |   WINDOW SORT                               |     1 |   291 | 5021K |  16:43:40 |
| 3   |    SORT GROUP BY ROLLUP                     |     1 |   291 | 5021K |  16:43:40 |
| 4   |     NESTED LOOPS                            |     1 |   291 | 5021K |  16:43:40 |
| 5   |      NESTED LOOPS                           |     1 |   291 | 5021K |  16:43:40 |
| 6   |       HASH JOIN                             |     1 |   282 | 5021K |  16:43:40 |
| 7   |        NESTED LOOPS                         |     1 |    24 |     5 |  00:00:01 |
| 8   |         TABLE ACCESS BY INDEX ROWID         |     1 |    12 |     4 |  00:00:01 |
| 9   |          INDEX UNIQUE SCAN                  |     1 |       |     3 |  00:00:01 |
| 10  |         TABLE ACCESS BY INDEX ROWID BATCHED |     1 |    12 |     1 |  00:00:01 |
| 11  |          INDEX RANGE SCAN                   |     1 |       |     0 |           |
| 12  |        VIEW                                 | 8352K | 2104M | 5021K |  16:43:39 |
| 13  |         UNION-ALL                           |       |       |       |           |
| 14  |          HASH JOIN                          | 3799K |  579M | 1782K |  06:35:32 |
| 15  |           MAT_VIEW ACCESS FULL              |  1649 |   16K |    54 |  00:00:01 |
| 16  |           HASH JOIN                         | 3799K |  542M | 1782K |  06:35:31 |
| 17  |            HASH JOIN OUTER                  | 3234K |  284M | 1120K |  04:30:16 |
| 18  |             TABLE ACCESS FULL               | 3234K |  158M | 1008K |  03:09:14 |
| 19  |             TABLE ACCESS FULL               | 5329K |  208M |   87K |  00:16:25 |
| 20  |            TABLE ACCESS FULL                |   53M | 2940M |  456K |  01:26:35 |
| 21  |          HASH JOIN                          | 1302K |  267M | 1140K |  04:34:00 |
| 22  |           MAT_VIEW ACCESS FULL              |  1649 |   16K |    54 |  00:00:01 |
| 23  |           HASH JOIN                         | 1302K |  254M | 1140K |  04:34:00 |
| 24  |            TABLE ACCESS FULL                | 1275K |  100M |  6589 |  00:01:13 |
| 25  |            HASH JOIN OUTER                  | 1281K |  150M | 1119K |  04:30:01 |
| 26  |             HASH JOIN                       | 1281K |  100M | 1011K |  03:10:47 |
| 27  |              TABLE ACCESS FULL              | 1281K |   38M |  2972 |  00:00:33 |
| 28  |              TABLE ACCESS FULL              | 2438K |  119M |  997K |  03:07:14 |
| 29  |             TABLE ACCESS FULL               | 5329K |  208M |   87K |  00:16:25 |
| 30  |          HASH JOIN                          | 3252K |  445M | 2099K |  07:34:07 |
| 31  |           MAT_VIEW ACCESS FULL              |  1649 |   16K |    54 |  00:00:01 |
| 32  |           HASH JOIN                         | 3252K |  413M | 2099K |  07:34:07 |
| 33  |            HASH JOIN OUTER                  | 2438K |  214M | 1107K |  03:28:48 |
| 34  |             TABLE ACCESS FULL               | 2438K |  119M |  997K |  03:07:14 |
| 35  |             TABLE ACCESS FULL               | 5329K |  208M |   87K |  00:16:25 |
| 36  |            TABLE ACCESS FULL                |   81M | 3224M |  757K |  02:22:13 |
| 37  |       INDEX UNIQUE SCAN                     |     1 |       |     0 |           |
| 38  |      TABLE ACCESS BY INDEX ROWID            |     1 |     9 |     1 |  00:00:01 |
----------------------------------------------------------------------------------------+

Понижение OPTIMIZER_FEATURES_ENABLE в этом случае не помогает решить проблему, но если попытаться отключить Cost-based часть трансформации JPPD параметром _OPTIMIZER_PUSH_PRED_COST_BASED=FALSE (что Oracle допускает на уровне системы, или сессии, или запроса) — запускается упущенный по умолчанию Old-style OJPPD (**), задействуются нужный индексы (***) и далее по плану:!

OJPPD: Performing join predicate push-down in union view -- **
 from query block SET$1 (#3) to query block SEL$8A3193DA (#4)
OJPPD: Pushing predicate "CC2"."ID"="MS"."CLIENT_ID"

OJPPD: Used promoted index: INVOICE_CLIENT_IDX -- ***
Registered qb: SET$AD7CC163 0xdc0b9608 (OLD PUSHED PREDICATE SET$1; SEL$641071AC; "MS"@"SEL$3")
...
============
Plan Table
============
------------------------------------------------------------+-----------------------------------+
| Id  | Operation                                           | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                                    |       |       |     9 |           |
| 1   |  VIEW                                               |     1 |  1258 |     9 |  00:00:01 |
| 2   |   WINDOW SORT                                       |     1 |   152 |     9 |  00:00:01 |
| 3   |    SORT GROUP BY ROLLUP                             |     1 |   152 |     9 |  00:00:01 |
| 4   |     NESTED LOOPS                                    |     1 |   152 |     8 |  00:00:01 |
| 5   |      NESTED LOOPS                                   |     1 |   152 |     8 |  00:00:01 |
| 6   |       NESTED LOOPS                                  |     1 |   143 |     7 |  00:00:01 |
| 7   |        NESTED LOOPS                                 |     1 |    24 |     5 |  00:00:01 |
| 8   |         TABLE ACCESS BY INDEX ROWID                 |     1 |    12 |     4 |  00:00:01 |
| 9   |          INDEX UNIQUE SCAN                          |     1 |       |     3 |  00:00:01 |
| 10  |         TABLE ACCESS BY INDEX ROWID BATCHED         |     1 |    12 |     1 |  00:00:01 |
| 11  |          INDEX RANGE SCAN                           |     1 |       |     0 |           |
| 12  |        VIEW                                         |     1 |   119 |     2 |  00:00:01 |
| 13  |         UNION-ALL PARTITION                         |       |       |       |           |
| 14  |          NESTED LOOPS                               |     1 |   156 |    24 |  00:00:01 |
| 15  |           NESTED LOOPS                              |     1 |   156 |    24 |  00:00:01 |
| 16  |            NESTED LOOPS OUTER                       |     1 |   100 |    22 |  00:00:01 |
| 17  |             NESTED LOOPS                            |     1 |    60 |    21 |  00:00:01 |
| 18  |              TABLE ACCESS BY INDEX ROWID BATCHED    |     1 |    50 |    20 |  00:00:01 |
| 19  |               INDEX RANGE SCAN                      |    17 |       |     4 |  00:00:01 | -- ***
| 20  |              MAT_VIEW ACCESS BY INDEX ROWID BATCHED |     1 |    10 |     1 |  00:00:01 |
| 21  |               INDEX RANGE SCAN                      |     1 |       |     0 |           |
| 22  |             TABLE ACCESS BY INDEX ROWID             |     1 |    40 |     1 |  00:00:01 |
| 23  |              INDEX UNIQUE SCAN                      |     1 |       |     0 |           |
| 24  |            INDEX RANGE SCAN                         |     1 |       |     0 |           |
| 25  |           TABLE ACCESS BY INDEX ROWID               |     1 |    56 |     2 |  00:00:01 |
...
| 52  |       INDEX UNIQUE SCAN                             |     1 |       |     0 |           |
| 53  |      TABLE ACCESS BY INDEX ROWID                    |     1 |     9 |     1 |  00:00:01 |
------------------------------------------------------------+-----------------------------------+
...
Content of other_xml column
===========================
  db_version     : 12.2.0.1
  parse_schema   : SCOTT
  plan_hash_full : 833106835
  plan_hash      : 833106835
  plan_hash_2    : 833106835
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
...
      OLD_PUSH_PRED(@"SEL$641071AC" "MS"@"SEL$3" ("INVOICE"."CLIENT_ID"))
...
    END_OUTLINE_DATA
  */

Эволюция планов выполнения (PLAN), COST и среднего времени выполнения (ELA_PER_EXEC) в разрезе исторической статистики:

SQL> @dba_hist_sqlstat "sql_id = '4dgcbuug0z2mk' and (snap_id between 405864 and 407578 or snap_id between 408786 and 409044) and executions_delta > 0"
 
BEGIN_SNAP_ID BEGIN_SNAP_TIME EXECS SQL_ID              PLAN       COST ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC IOWAITS_PER_EXEC CLWAITS_PER_EXEC_US
------------- --------------- ----- ------------- ---------- ---------- ------------ ------------ ------------- ---------------- -------------------
       405863 16.11 06:00         1 4dgcbuug0z2mk 1982197705          8      1478250      1269762        407446           227338                3688 -- 12.1
       407577 28.11 04:56         2 4dgcbuug0z2mk 2373346498    5141738    346221518    216784323       8139622        131855940             3237512 -- 12.2 Cost-based JPPD
       408785 06.12 14:20         8 4dgcbuug0z2mk  271775912    5147616    330447624    183531145       5504444        142605894               20807
       408936 07.12 15:30        10 4dgcbuug0z2mk  833106835          9       123569        57672          2755            65832                1975 -- 12.2 Old-style OJPPD
       409043 08.12 09:20         1 4dgcbuug0z2mk  833106835          9       192274       173387          1116             1059                 699
 
5 rows selected

P.S. Параметр на уровне запроса фиксировал скриптом SQLPATCH+.SQL, модифицированным с связи с изменением формата SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH — в 12.2 это не процедура, а функция!:)

@sqlpatch+ 4dgcbuug0z2mk opt_param(''''_optimizer_push_pred_cost_based'''',''''false'''') 4dgcbuug0z2mk_OJPPD
SQL> @sqlpatch+
&SQL_ID: 4dgcbuug0z2mk
&SQL_PATCH_TEXT: opt_param(''''_optimizer_push_pred_cost_based'''',''''false'''')
&SQL_PATCH_NAME: 4dgcbuug0z2mk-OJPPD-122
 
PL/SQL procedure successfully completed
 
SQL_ID        NAME                           CATEGORY                       CREATED               STATUS   FMATCH
------------- ------------------------------ ------------------------------ --------------------- -------- ------
4dgcbuug0z2mk 4dgcbuug0z2mk-OJPPD-122        DEFAULT                        06.12.2018 14:20:33   ENABLED  YES
 
SQL_PATCH_HINTS
-----------------------------------------------------------------------------------------------------------------
opt_param('_optimizer_push_pred_cost_based','false')

P.P.S. Поиск по сайту поддержки выдаёт Bug 14341002 — Wrong result on «UNION-ALL partition» when «_optimizer_push_pred_cost_based=false» — superseded (Doc ID 14341002.8) — вместе со своими superseding исправлены в версиях 12.1.0.1-12.2.0.1

P.P.P.S. В процессе поисков причин JPPD bypassed: View is a set query block. наткнулся на короткое, с тесткейсом, обсуждение — pushing predicate into union-all view на обновлённом AskTom-е, где обновлённая команда приводит гениальное объяснение:)))

…The optimizer doesn’t currently consider pushing predicates into the view for query 2. You can see this in the 10053 trace: — тут

Check Basic Validity for Non-Union View for query block SET$1 (#3)
JPPD:     JPPD bypassed: View is a set query block...

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

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

RSS feed for comments on this post. TrackBack URI

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

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

Логотип WordPress.com

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

Google photo

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

Фотография Twitter

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

Фотография Facebook

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

Connecting to %s

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

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