Oracle mechanics

07.12.2014

ROWID доступ к партицированной таблице

Filed under: Oracle,Plan Management,SQL Tuning — Игорь Усольцев @ 10:53
Tags: ,

С Леонидом Борчуком разбирали страшное:

SQL ordered by Elapsed Time           DB/Inst: OEBS/OEBS1  Snaps: 77632-77633

        Elapsed                  Elapsed Time                                   
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id    
---------------- -------------- ------------- ------ ------ ------ -------------
        49,260.9            139        354.39   72.4    9.0   93.5 2b01txt3hnjha -- запрос потребляет более 70% DB Time
Module: e:SQLAP:bes:oracle.apps.xla.accounting.postproce
UPDATE /*+ PARALLEL (AEL) */ XLA_AE_LINES AEL SET AEL.ANALYTICAL_BALANCE_FLAG = 
:B8 ,AEL.LAST_UPDATE_DATE = :B5 ,AEL.LAST_UPDATED_BY = :B7 ,AEL.LAST_UPDATE_LOGI
N = :B6 ,AEL.PROGRAM_UPDATE_DATE = :B5 ,AEL.PROGRAM_APPLICATION_ID = :B4 ,AEL.PR
OGRAM_ID = :B3 ,AEL.REQUEST_ID = :B2 WHERE (AEL.ROWID) IN (SELECT ...

         5,312.4          1,801          2.95    7.8   99.2     .0 7r2xtc0ru1h0s -- , на порядок опережая конкурентов
...

Один из стандартных запросов OEBS, судя по истории обычно выполнялся небыстро, потребляя значительные I/O ресурсы на direct path read (судя по соотношению DISK_READS_PER_EXEC и READS_PER_EXEC) большим количеством параллельных процессов (PX_PER_EXEC):

11.2.0.4@ SQL> @dba_hist_sqlstat "sql_id = '2b01txt3hnjha' and snap_id between 77342 and 77632 and executions_delta > 0"
 
INST BEGIN_SNAP_ID BEGIN_SNAP_TIME EXECS       PLAN  COST PARSE_PER_EXEC ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC DISK_READS_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC ROWS_PER_EXEC FETCHES_PER_EXEC IOWAITS_PER_EXEC CLWAITS_PER_EXEC_US APWAITS_PER_EXEC CCWAITS_PER_EXEC PARSE_PER_EXEC PX_PER_EXEC
---- ------------- --------------- ----- ---------- ----- -------------- ------------ ------------ ------------- ------------------- ---------------- -------------- ------------- ---------------- ---------------- ------------------- ---------------- ---------------- -------------- -----------
   1         77631 03.12 15:30       146 3585506175   234         69,308    319482564     32718053        577840              340483                0              0             4                0        296034209              624732             4272             9133             69          70
   1         77630 03.12 15:00       115 3585506175   234         69,713    308568713     33401748        578416              340428                0              0             4                0        284977096                7877            32791            11000             70          70
   1         77611 03.12 05:30         1 3585506175   234             70   1013793602     37859244       1246557              734333                0              0          1710                0        979548374                   0             2179              667             70          70
   1         77373 28.11 06:30         1 3585506175  2314             68    811476699     39598982       1198943              731294                0              0           164                0        773514667                   0             2861            23334             68          69
   1         77371 28.11 05:30         1 3585506175  2314             71   1010986052     38207183       1239919              731135                0              0          1819                0        973563385              588397             4722            91485             71          70
   1         77367 28.11 03:30         1 3585506175  2314             69     27257365      5582147        198106               43041                0              2           436                0         11315637            11608477                0            48606             69          70
   1         77364 28.11 02:00         2 3585506175  2314             70    302700251     29491015        523623              309740                0              0          2213                0        281824284                   0             4216             3828             70          70
   2         77364 28.11 02:00         1 1503883654     5              1       380062       375942         10880                   0                0              0          9982                0                0                   0                0              679              1           0 -- *
   1         77341 27.11 14:30         1 3585506175  2314             70   1000168888     37917232       1241287              732130                0              1            46                0        965830662                4538             2324             1427             70          70

используя, в основном, красивый параллельный план 3585506175, сформированный благодаря хинту /*+ PARALLEL (AEL) */, заботливо добавленному разработчиками OEBS :)

Plan hash value: 3585506175
 
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                 |                         |        |       |   395 (100)|          |       |       |        |      |            |
|   1 |  UPDATE                          | XLA_AE_LINES            |        |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                 |                         |        |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)           | :TQ10002                |  19038 |    73M|   395   (1)| 00:00:05 |       |       |  Q1,02 | P->S | QC (RAND)  |
|   4 |     HASH JOIN RIGHT SEMI BUFFERED|                         |  19038 |    73M|   395   (1)| 00:00:05 |       |       |  Q1,02 | PCWP |            |
|   5 |      BUFFER SORT                 |                         |        |       |            |          |       |       |  Q1,02 | PCWC |            |
|   6 |       PX RECEIVE                 |                         |   5673 |    21M|    17   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|   7 |        PX SEND HASH              | :TQ10000                |   5673 |    21M|    17   (0)| 00:00:01 |       |       |        | S->P | HASH       |
|   8 |         TABLE ACCESS FULL        | XLA_BAL_ANACRI_LINES_GT |   5673 |    21M|    17   (0)| 00:00:01 |       |       |        |      |            |
|   9 |      PX RECEIVE                  |                         |   1903K|   105M|   378   (1)| 00:00:05 |       |       |  Q1,02 | PCWP |            |
|  10 |       PX SEND HASH               | :TQ10001                |   1903K|   105M|   378   (1)| 00:00:05 |       |       |  Q1,01 | P->P | HASH       |
|  11 |        PX BLOCK ITERATOR         |                         |   1903K|   105M|   378   (1)| 00:00:05 |   KEY |   KEY |  Q1,01 | PCWC |            |
|  12 |         TABLE ACCESS FULL        | XLA_AE_LINES            |   1903K|   105M|   378   (1)| 00:00:05 |   KEY |   KEY |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------------------
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_dynamic_sampling' 6)
      ...
      END_OUTLINE_DATA
  */
Note
-----
   - dynamic sampling used for this statement (level=6)

— для gtt XLA_BAL_ANACRI_LINES_GT, как видно, используется dynamic sampling и, обнаружив в таблице 5673 строк в момент создания курсора, под влиянием хинта создаётся план с параллельным выполнением запроса ( но не DML / update), что хорошо видно из профиля ожиданий запроса с планом 3585506175:

SQL> @ash_sqlmon_hist 2b01txt3hnjha 3585506175 "" 77365 77633
 
ID PLAN_OPERATION                           OBJECT_OWNER OBJECT_NAME             COST CARDINALITY WAIT_PROFILE
-- ---------------------------------------- ------------ ----------------------- ---- ----------- ---------------------------------------------------------------------------------------------
 0   UPDATE STATEMENT                                                             395             ON CPU(1);
 1     UPDATE                               XLA          XLA_AE_LINES                             
 2       PX COORDINATOR                                                                           ON CPU(4); enq: KO - fast object checkpoint(1);
 3         PX SEND QC (RANDOM)              SYS          :TQ10002                 395       19038 
 4           HASH JOIN RIGHT SEMI BUFFERED                                        395       19038 ON CPU(24);
 5             BUFFER SORT                                                                        
 6               PX RECEIVE                                                        17        5673 
 7                 PX SEND HASH             SYS          :TQ10000                  17        5673 
 8                   TABLE ACCESS FULL      XLA          XLA_BAL_ANACRI_LINES_GT   17        5673 
 9             PX RECEIVE                                                         378     1903801 ON CPU(215); PX Deq: reap credit(52); PX Deq: Table Q Normal(1);
10               PX SEND HASH               SYS          :TQ10001                 378     1903801 ON CPU(460); PX Deq: reap credit(23); IPC send completion sync(4);
11                 PX BLOCK ITERATOR                                              378     1903801 PX Deq: reap credit(1);
12                   TABLE ACCESS FULL      XLA          XLA_AE_LINES             378     1903801 direct path read(13551); ON CPU(604); db file sequential read(61); gc current block 2-way(1);

Однако, в вышеприведённой исторической статистике (@dba_hist_sqlstat) можно также заметить быстрый и дешёвый план 1503883654 (*):

SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('2b01txt3hnjha', 1503883654));

Plan hash value: 1503883654

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                         |       |       |     8 (100)|          |       |       |
|   1 |  UPDATE                      | XLA_AE_LINES            |       |       |            |          |       |       |
|   2 |   NESTED LOOPS               |                         |   117K|   454M|     8  (13)| 00:00:01 |       |       |
|   3 |    SORT UNIQUE               |                         |   162 |   633K|     2   (0)| 00:00:01 |       |       |
|   4 |     TABLE ACCESS FULL        | XLA_BAL_ANACRI_LINES_GT |   162 |   633K|     2   (0)| 00:00:01 |       |       |
|   5 |    TABLE ACCESS BY USER ROWID| XLA_AE_LINES            | 23186 |  1335K|     1   (0)| 00:00:01 | ROWID | ROWID |
------------------------------------------------------------------------------------------------------------------------

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

, аналогично сформированный с использованием dynamic sampling для той же gtt XLA_BAL_ANACRI_LINES_GT в момент, когда в ней было 162 строки, где несмотря на подсказку /*+ PARALLEL (AEL) */ оптимизатор счёл параллельное выполнение невыгодным

Из той же статистики выполнения можно заметить, что непараллельный план оказался не только значительно дешевле и быстрее параллельного — 380 ms против 20+ секунд, но и запрос при этом обрабатал/обновил наибольшее кол-во строк — 9982, что вселяет надежды

Для выправления ситуации (кроме логичного SR в направлении разработчиков OEBS) можно:

  • установить alter session disable parallel query/DML пррямо в приложении, или через триггер, etc…
  • попробовать SQL Patch либо Profile для корректировки/фиксации плана — не уверен, требует тестов
  • либо собрать/поправить/жёстко зафиксировать статистику для gtt XLA_BAL_ANACRI_LINES_GT, после чего мы получили «пустую» статистику:
SQL> select * from dba_tables where table_name = 'XLA_BAL_ANACRI_LINES_GT';
 
STATUS LOGGING NUM_ROWS     BLOCKS DEGREE LAST_ANALYZED PARTITIONED TEMPORARY GLOBAL_STATS USER_STATS DURATION
------ ------- -------- ---------- ------ ------------- ----------- --------- ------------ ---------- ---------------
VALID  NO             0          0      1 03.12.2014 16 NO          Y         YES          NO         SYS$TRANSACTION

и ситуация с запросом заметно поправилась:

SQL> @v$sqlstats 2b01txt3hnjha
 
INST    EXECS SQL_ID              PLAN ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC UIO_PER_EXEC ROWS_PER_EXEC PX_PER_EXEC
---- -------- ------------- ---------- ------------ ------------ ------------- ------------- ------------ ------------ ------------- -----------
   1    11337 2b01txt3hnjha 1503883654          797          776            19             0            1            2            15           0
   2       34 2b01txt3hnjha 1503883654        78656        74724          2111           192          476         1164          1926           0

— автоматически выбираемый теперь Oracle непараллельный план 1503883654 оказался выгоднее и для относительно больших размеров XLA_BAL_ANACRI_LINES_GT — до несколько тысяч, как и предсказывала DBA_HIST_SQLSTAT

Всё неплохо, остаётся два вопроса:

1) как фиксация статистики GTT отразится на других запросах, использующих эту же временную таблицу?

Найдём эти запросы:

SQL> select distinct sql_id, dbms_lob.substr(sql_text, 200)
  2    from dba_hist_sql_plan
  3    join dba_hist_sqltext
  4   using (sql_id)
  5   where object_name = 'XLA_BAL_ANACRI_LINES_GT'
  6  /
 
SQL_ID        DBMS_LOB.SUBSTR(SQL_TEXT,200)
------------- --------------------------------------------------------------------------------
2b01txt3hnjha UPDATE /*+ PARALLEL (AEL) */ XLA_AE_LINES AEL SET AEL.ANALYTICAL_BALANCE_FLAG =

74tqrna6c5vdk    INSERT ALL
                   ...
                   INTO xla_bal_anacri_lines_gt...
 
cmbfr8hj6hf8g UPDATE /*+ PARALLEL (xbat) */ XLA_BAL_ANACRI_LINES_GT XBAT SET ( BALANCE_STATUS_

— которые, как легко заметить, отрадают весь жизненный цикл GTT: наполнение (74tqrna6c5vdk), изменение данных в GTT (cmbfr8hj6hf8g), и собственно использование GTT для обновления XLA_AE_LINES — 2b01txt3hnjha — запрос, вызвавший проблемы

INSERT ALL…INTO xla_bal_anacri_lines_gt, очевидно, интереса не представляет, а вот на UPDATE /*+ PARALLEL (xbat) */ XLA_BAL_ANACRI_LINES_GT XBAT, в который с той же заботой добавлена подсказка, нужно проверить:

SQL> @dba_hist_sqlstat "sql_id = 'cmbfr8hj6hf8g' and snap_id between 77639 and 77645 and executions_delta > 0"
 
INST BEGIN_SNAP_ID BEGIN_SNAP_TIME  EXECS SQL_ID              PLAN  COST ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC ROWS_PER_EXEC IOWAITS_PER_EXEC CLWAITS_PER_EXEC_US APWAITS_PER_EXEC CCWAITS_PER_EXEC PX_PER_EXEC
---- ------------- --------------- ------ ------------- ---------- ----- ------------ ------------ ------------- ------------- ---------------- ------------------- ---------------- ---------------- -----------
   1         77644 03.12 22:00       1162 cmbfr8hj6hf8g 2895363257    11        61762        31371            60            16             2720                 291             9003             6690          70
   1         77643 03.12 21:30        525 cmbfr8hj6hf8g 2895363257    11        64555        31622            60            16             2427                 300             6240            10481          70
   1         77642 03.12 21:00        164 cmbfr8hj6hf8g 2895363257    11        71630        31977            26             4             2146                 279            16190             9433          70
   1         77641 03.12 20:30        918 cmbfr8hj6hf8g 2895363257    38        94450        36127            78            26             2753                 728            26328            15281          70
   2         77640 03.12 20:00          2 cmbfr8hj6hf8g 2895363257    38      1917231      1463271           375            36           203111               29465             3799           583452        3395
   1         77639 03.12 19:30        978 cmbfr8hj6hf8g 2895363257    38        75709        34562            27             5             2520                 247            11573            11649          70
   1         77638 03.12 19:00        958 cmbfr8hj6hf8g 2895363257    38        75050        35639            34             7             2997                 368            10676             9197          70

— хорошо, план и ср.время выполнения ELA_PER_EXEC в период изменений статистики не изменились, ожидаемо уменьшилась лишь стоимость COST, что нестрашно)

2) и второй остающий вопрос, в получившемся ранее хорошем непараллельном плане выполнения:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('2b01txt3hnjha', format => 'typical +peeked_binds'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  2b01txt3hnjha, child number 0
-------------------------------------

Plan hash value: 1503883654

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                         |       |       |     4 (100)|          |       |       |
|   1 |  UPDATE                      | XLA_AE_LINES            |       |       |            |          |       |       |
|   2 |   NESTED LOOPS               |                         |     1 |  4060 |     4  (25)| 00:00:01 |       |       |
|   3 |    SORT UNIQUE               |                         |     1 |  4002 |     2   (0)| 00:00:01 |       |       |
|   4 |     TABLE ACCESS FULL        | XLA_BAL_ANACRI_LINES_GT |     1 |  4002 |     2   (0)| 00:00:01 |       |       |
|*  5 |    TABLE ACCESS BY USER ROWID| XLA_AE_LINES            |   229K|    12M|     1   (0)| 00:00:01 | ROWID | ROWID | -- **
------------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   9 - :B1 (NUMBER): 200

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

   5 - filter("AEL"."APPLICATION_ID"=:B1)

— отсутствует dynamic sampling, что следует из присутствия статистики на gtt XLA_BAL_ANACRI_LINES_GT, но оценка cardinality таблицы XLA_AE_LINES в процессе TABLE ACCESS BY USER ROWID выглядит, как минимум, странно: Rows = 229K, учитывая операцию NESTED LOOPS с внешней таблицей XLA_BAL_ANACRI_LINES_GT, не содержащей по статистике строк (что оптимистично округляется оптимизатором до Rows = 1)

Чтобы оценить происхождение этой странности (отображения) можно, например, потрейсить EXPLAIN PLAN:

SQL> var B1 number
SQL> exec :B1 := 200 -- на всякий случай установил

PL/SQL procedure successfully completed.

SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL> explain plan for
  2  UPDATE /*+ PARALLEL (AEL) */ XLA_AE_LINES AEL
  3     SET AEL.ANALYTICAL_BALANCE_FLAG = :B8,
  4         AEL.LAST_UPDATE_DATE        = :B5,
  5         AEL.LAST_UPDATED_BY         = :B7,
  6         AEL.LAST_UPDATE_LOGIN       = :B6,
  7         AEL.PROGRAM_UPDATE_DATE     = :B5,
  8         AEL.PROGRAM_APPLICATION_ID  = :B4,
  9         AEL.PROGRAM_ID              = :B3,
 10         AEL.REQUEST_ID              = :B2
 11   WHERE (AEL.ROWID) IN (SELECT /*+ leading(XBCT)  */
 12                          XBCT.LINE_ROWID
 13                           FROM XLA_BAL_ANACRI_LINES_GT XBCT)
 14     AND AEL.APPLICATION_ID = :B1
 15  /

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1503883654

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                         |     1 |  4061 |     4  (25)| 00:00:01 |       |       |
|   1 |  UPDATE                      | XLA_AE_LINES            |       |       |            |          |       |       |
|   2 |   NESTED LOOPS               |                         |     1 |  4061 |     4  (25)| 00:00:01 |       |       |
|   3 |    SORT UNIQUE               |                         |     1 |  4002 |     2   (0)| 00:00:01 |       |       |
|   4 |     TABLE ACCESS FULL        | XLA_BAL_ANACRI_LINES_GT |     1 |  4002 |     2   (0)| 00:00:01 |       |       |
|*  5 |    TABLE ACCESS BY USER ROWID| XLA_AE_LINES            |   613K|    34M|     1   (0)| 00:00:01 | ROWID | ROWID | -- ***
------------------------------------------------------------------------------------------------------------------------

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

   5 - access(("XBCT"."LINE_ROWID"))
       filter("AEL"."APPLICATION_ID"=TO_NUMBER(:B1)) -- ****

— и получить ещё более чудной в оценке cardinality XLA_AE_LINES (***) план, при этом в трейсе оптимизатора:

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for XLA_AE_LINES[AEL]
...
  Column (#3):
    NewDensity:0.000027, OldDensity:0.000000 BktCnt:18448, PopBktCnt:18447, PopValCnt:7, NDV:8
  Column (#3): APPLICATION_ID(                                           -- столбец по кот.выполняется операция filter (****)
    AvgLen: 4 NDV: 8 Nulls: 0 Density: 0.000027 Min: 140 Max: 801        -- , и по которому же партицирована XLA_AE_LINES
    Histogram: Freq  #Bkts: 8  UncompBkts: 18448  EndPtVals: 8
  Table: XLA_AE_LINES  Alias: AEL
    Card: Original: 490621849.000000  Rounded: 61327731  Computed: 61327731.12  Non Adjusted: 61327731.12 -- кол-во строк XLA_AE_LINES
... -- оценивается как 61327731 = 490621849 / 8, т.е. как общее кол-во строк всех партиций таблицы / NDV столбца фильтра/партиционирования -- *****
NL Join
  Outer table: Card: 0.00  Cost: 3.00  Resp: 3.00  Degree: 1  Bytes: 4002
Access path analysis for XLA_AE_LINES
  Inner table: XLA_AE_LINES  Alias: AEL
  Access Path: TableScan                                                 -- очевидно проигрышный путь
    NL Join:  Cost: 209002.49  Resp: 4840.95  Degree: 48
      Cost_io: 205999.00  Cost_cpu: 67676716661
      Resp_io: 4770.45  Resp_cpu: 1588608752
  Inner table: XLA_AE_LINES  Alias: AEL
  Access Path: Rowid                                                     -- разумный путь с низкой стоимостью
    NL Join:  Cost: 4.00  Resp: 4.00  Degree: 48
      Cost_io: 3.00  Cost_cpu: 22548116
      Resp_io: 3.00  Resp_cpu: 22548116
...
  Best NL cost: 4.00
          resc: 4.00  resc_io: 3.00  resc_cpu: 22548116
...
Join Card:  0.000613 = outer (0.000000) * inner (61327731.125000) * sel (0.010000) -- правильная оценка соединения
Join Card - Rounded: 1 Computed: 0.00
  Outer table:  XLA_BAL_ANACRI_LINES_GT  Alias: XBCT
    resc: 3.00  card 0.00  bytes: 4002  deg: 1  resp: 3.00
  Inner table:  XLA_AE_LINES  Alias: AEL
    resc: 208999.49  card: 61327731.12  bytes: 59  deg: 48  resp: 4837.95
...
Plan cardinality mismatch: best card= 613277.31125000003   curr card= 0.00061327731-- , кот. не соответствует т.н. best cardinality
Best:: JoinMethod: NestedLoop                                                      -- , полученной на этапе (*****) и равной 61327731 / 100
       Cost: 4.00  Degree: 48  Resp: 4.00  Card: 0.00 Bytes: 4061
***********************
Best so far:  Table#: 1  cost: 3.0003  card: 0.0000  bytes: 4002
              Table#: 0  cost: 4.0007  card: 0.0006  bytes: 4061
***********************
...
============
Plan Table
============
---------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                     | Name                   | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
---------------------------------------------------------------+-----------------------------------+---------------+
| 0   | UPDATE STATEMENT              |                        |       |       |     4 |           |       |       |
| 1   |  UPDATE                       | XLA_AE_LINES           |       |       |       |           |       |       |
| 2   |   NESTED LOOPS                |                        |     1 |  4061 |     4 |  00:00:01 |       |       | -- точная cardinality
| 3   |    SORT UNIQUE                |                        |     1 |  4002 |     2 |  00:00:01 |       |       |
| 4   |     TABLE ACCESS FULL         | XLA_BAL_ANACRI_LINES_GT|     1 |  4002 |     2 |  00:00:01 |       |       | -- точная cardinality
| 5   |    TABLE ACCESS BY USER ROWID | XLA_AE_LINES           |  599K |   35M |     1 |  00:00:01 | ROW LOCATION| ROW LOCATION| -- best cardinality
---------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
5 - filter("AEL"."APPLICATION_ID"=TO_NUMBER(:B1))
...
Dumping Hints
=============
  atom_hint=(@=0xd7a20240 err=0 resol=1 used=1 token=177 org=1 lvl=3 txt=PARALLEL ("AEL",DEFAULT) ) -- ******
====================== END SQL Statement Dump ======================

интересно заметить, что при трейсировке выполнения команды EXPLAIN PLAN вышеустановленное значение связанной переменной в трейсе не отражается и не учитываются никоим образом, как впрочем и при попытке сгенерировать трейс существующего в Shared Pool курсора с помощью DBMS_SQLDIAG.DUMP_TRACE, хотя в последнем случае значение переменной :B1 = 200 известно, отражается в трейсе и могло бы учитываться при генерации плана выполнения,
чем и объясняется отличие cardinality XLA_AE_LINES при выполнении EXPLAIN PLAN / DBMS_SQLDIAG.DUMP_TRACE и в плане выполнения действующего актуального курсора

При создании реального рабочего курсора значение переменной :B1 = 200 учитывается в генерации плана и следовательно для расчётов используется кол-во строк не всей таблицы XLA_AE_LINES, а лишь конкретной партиции, определяемой этой переменной:

SQL> select * from dba_tab_partitions where table_name = 'XLA_AE_LINES';
 
HIGH_VALUE  HIGH_VALUE_LENGTH PARTITION_POSITION   NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
----------- ----------------- ------------------ ---------- ---------- ------------ ----------- ----------- -------------
200                         3                  1   23764337     755063            0         189    23764337 04.12.2014 22
...

(******) — любопытно, что хинт /*+ PARALLEL (AEL) */ оптимизатор видит и used при создании непараллельного плана

2 комментария »

  1. Игорь,

    хинт /*+ PARALLEL (AEL) */ лишь добавляет коэффициент снижающий стоимость параллельных планов, поэтому выбран будет непараллельный план, если он дешевле даже при заниженной стоимости параллельного плана.

    комментарий от Sayan Malakshinov — 07.12.2014 @ 11:11 | Ответить

    • ты, конечно, прав, Саян

      но на практике если, например, искусственно поднять cardinality(XBCT 10000), хинт /*+ PARALLEL (AEL) */ не только добавляет снижающий коэф-нт, но и, как следствие, принципиально меняет план до TABLE ACCESS FULL немаленькой XLA_AE_LINES, естественно не учитывая при этом конкурентности / плотности выполнения таких запросов:

      SQL> explain plan for
        2  UPDATE /*+ PARALLEL (AEL) */ apps.XLA_AE_LINES AEL
        3     SET AEL.ANALYTICAL_BALANCE_FLAG = :B8,
        4         AEL.LAST_UPDATE_DATE        = :B5,
        5         AEL.LAST_UPDATED_BY         = :B7,
        6         AEL.LAST_UPDATE_LOGIN       = :B6,
        7         AEL.PROGRAM_UPDATE_DATE     = :B5,
        8         AEL.PROGRAM_APPLICATION_ID  = :B4,
        9         AEL.PROGRAM_ID              = :B3,
       10         AEL.REQUEST_ID              = :B2
       11   WHERE (AEL.ROWID) IN (SELECT /*+ leading(XBCT) cardinality(XBCT 10000)  */
       12                          XBCT.LINE_ROWID
       13                           FROM apps.XLA_BAL_ANACRI_LINES_GT XBCT)
       14     AND AEL.APPLICATION_ID = :B1
       15  /
      
      Explained.
      
      Plan hash value: 3585506175
      
      ---------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                        | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT                 |                         |   613K|  2375M|  4846   (2)| 00:00:59 |       |       |        |      |            |
      |   1 |  UPDATE                          | XLA_AE_LINES            |       |       |            |       |          |       |        |      |            |
      |   2 |   PX COORDINATOR                 |                         |       |       |            |       |          |       |        |      |            |
      |   3 |    PX SEND QC (RANDOM)           | :TQ10002                |   613K|  2375M|  4846   (2)| 00:00:59 |       |       |  Q1,02 | P->S | QC (RAND)  |
      |*  4 |     HASH JOIN RIGHT SEMI BUFFERED|                         |   613K|  2375M|  4846   (2)| 00:00:59 |       |       |  Q1,02 | PCWP |            |
      |   5 |      BUFFER SORT                 |                         |       |       |            |       |          |       |  Q1,02 | PCWC |            |
      |   6 |       PX RECEIVE                 |                         | 10000 |    38M|     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
      |   7 |        PX SEND HASH              | :TQ10000                | 10000 |    38M|     2   (0)| 00:00:01 |       |       |        | S->P | HASH       |
      |   8 |         TABLE ACCESS FULL        | XLA_BAL_ANACRI_LINES_GT | 10000 |    38M|     2   (0)| 00:00:01 |       |       |        |      |            |
      |   9 |      PX RECEIVE                  |                         |    61M|  3450M|  4838   (2)| 00:00:59 |       |       |  Q1,02 | PCWP |            |
      |  10 |       PX SEND HASH               | :TQ10001                |    61M|  3450M|  4838   (2)| 00:00:59 |       |       |  Q1,01 | P->P | HASH       |
      |  11 |        PX BLOCK ITERATOR         |                         |    61M|  3450M|  4838   (2)| 00:00:59 |   KEY |   KEY |  Q1,01 | PCWC |            |
      |  12 |         TABLE ACCESS FULL        | XLA_AE_LINES            |    61M|  3450M|  4838   (2)| 00:00:59 |   KEY |   KEY |  Q1,01 | PCWP |            |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         4 - access("AEL".ROWID=("XBCT"."LINE_ROWID"))

      Непаралельный же план, в свою очередь, при тех же условиях именно из-за неправильной оценки cardinality нижней операции TABLE ACCESS BY USER ROWID планирует обновить 613K строк(!), что абсурдно и, конечно же, выходит дороже чем FTS при параллельном выполнении:

      SQL> explain plan for
        2  UPDATE /*+ NO_PARALLEL (AEL) */ apps.XLA_AE_LINES AEL
        3     SET AEL.ANALYTICAL_BALANCE_FLAG = :B8,
        4         AEL.LAST_UPDATE_DATE        = :B5,
        5         AEL.LAST_UPDATED_BY         = :B7,
        6         AEL.LAST_UPDATE_LOGIN       = :B6,
        7         AEL.PROGRAM_UPDATE_DATE     = :B5,
        8         AEL.PROGRAM_APPLICATION_ID  = :B4,
        9         AEL.PROGRAM_ID              = :B3,
       10         AEL.REQUEST_ID              = :B2
       11   WHERE (AEL.ROWID) IN (SELECT /*+ leading(XBCT) cardinality(XBCT 10000)  */
       12                          XBCT.LINE_ROWID
       13                           FROM apps.XLA_BAL_ANACRI_LINES_GT XBCT)
       14     AND AEL.APPLICATION_ID = :B1
       15  /
      
      Explained.
      
      Plan hash value: 1503883654
      
      ------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      ------------------------------------------------------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT             |                         |   613K|  2375M|  8341   (1)| 00:01:41 |       |       |
      |   1 |  UPDATE                      | XLA_AE_LINES            |       |       |            |          |       |       |
      |   2 |   NESTED LOOPS               |                         |   613K|  2375M|  8341   (1)| 00:01:41 |       |       |
      |   3 |    SORT UNIQUE               |                         | 10000 |    38M|     2   (0)| 00:00:01 |       |       |
      |   4 |     TABLE ACCESS FULL        | XLA_BAL_ANACRI_LINES_GT | 10000 |    38M|     2   (0)| 00:00:01 |       |       |
      |*  5 |    TABLE ACCESS BY USER ROWID| XLA_AE_LINES            |   613K|    34M|     1   (0)| 00:00:01 | ROWID | ROWID |
      ------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         5 - access(("XBCT"."LINE_ROWID"))
             filter("AEL"."APPLICATION_ID"=TO_NUMBER(:B1))

      комментарий от Игорь Усольцев — 07.12.2014 @ 12:01 | Ответить


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