Oracle mechanics

18.12.2012

Использование временного пространства при параллельном выполнении

Filed under: AWR,commonplace,Oracle,PX,temp segment — Игорь Усольцев @ 00:39
Tags: , ,

Обратил на себя внимание меняющийся объём используемого временного табличного пространства при выполнении ежедневного задания по неатомарному обновлению матвью, по данным периодически заполняемой таблицы TEMP_TEMP_SEG_USAGE:

SQL> select trunc(date_time) AS DATE_TIME,
  2         max(SUM_TEMP_USAGE_MB) as MAX_SUM_TEMP_USAGE_MB
  3    from (select date_time,
  4                 round(sum(blocks) * t.block_size / 1024 / 1024) as SUM_TEMP_USAGE_MB
  5            from SYSTEM.TEMP_TEMP_SEG_USAGE ttu, dba_tablespaces t
  6           where sql_id in ('7q1grk5zza7f0')
  7             and ttu.TABLESPACE = t.tablespace_name
  8             and date_time > sysdate - 8
  9           group by date_time, t.block_size)
 10   group by trunc(date_time)
 11   order by trunc(date_time) desc
 12  /

 

DATE_TIME   MAX_SUM_TEMP_USAGE_MB
----------- ---------------------
13.12.2012                   3279
12.12.2012                  28813 -- ???
11.12.2012                   3095
10.12.2012                   3276
09.12.2012                   3277
08.12.2012                   3276
07.12.2012                   3095
06.12.2012                  26766 -- ???

— ничего необычного, конечно же, в этом нет: статистика выполнения запроса показывает прямую зависимость потребляемого объёма TEMP от количества использованных при выполнении PX процессов:

SQL> select
  2      trunc(sn.begin_interval_time) as SNAP_TIME,
  3      st.plan_hash_value            as PLAN,
  4      st.optimizer_cost             as COST,
  5      max(round(st.elapsed_time_delta/decode(st.executions_delta,0,1,st.executions_delta)))     as ELA_PER_EXEC,
  6      max(round(st.parse_calls_delta/decode(st.executions_delta,0,1,st.executions_delta)))      as PARSE_PER_EXEC,
  7      max(round(st.px_servers_execs_delta/decode(st.executions_delta,0,1,st.executions_delta))) as PX_PER_EXEC
  8  from dba_hist_sqlstat st, dba_hist_snapshot sn
  9  where sql_id in ('7q1grk5zza7f0')
 10        and st.snap_id = sn.snap_id
 11        and st.instance_number = sn.instance_number
 12  group by trunc(sn.begin_interval_time),
 13      st.plan_hash_value,
 14      st.optimizer_cost
 15  order by trunc(sn.begin_interval_time) desc
 16  /

 

SNAP_TIME         PLAN       COST ELA_PER_EXEC PARSE_PER_EXEC PX_PER_EXEC
----------- ---------- ---------- ------------ -------------- -----------
13.12.2012  1389495461   36787387  10575957956             37          36
12.12.2012  1389495461   36808393   3605495142              1           0 -- непараллельно
11.12.2012  1389495461   36805685  10985607998             36          36
10.12.2012  1389495461   36829356   9496760135             37          36
09.12.2012  1389495461   36806474  10085614455             37          36
08.12.2012  1389495461   36857290  10008910917             37          36
07.12.2012  1389495461   36819197    551762078             37           0 -- неточность в статистике, имхо
06.12.2012  1389495461   36899295   3594440685              1           0 -- непараллельно

— два (из восьми) случая непараллельного выполнения потребляли на порядок бОльший объём временного пространства

Аналогично обратно пропорционально степени параллелизма менялось время выполнения задания по обновления матвью (включающего также перестройку индексов, из-за чего полное время выполнения задания RUN_DURATION ~ вдвое дольше чем ELA_PER_EXEC из статистики запроса):

SQL> select trunc(ACTUAL_START_DATE), RUN_DURATION
  2    from dba_scheduler_job_run_details
  3   where JOB_NAME = 'MV_REFRESH_JOB'
  4     and ACTUAL_START_DATE > sysdate - 8
  5   order by ACTUAL_START_DATE desc
  6  /

START_DATE  RUN_DURATION
----------- -------------
13.12.2012  +000 00:20:48
12.12.2012  +000 02:02:08 -- непараллельно
11.12.2012  +000 00:21:05
10.12.2012  +000 00:19:25
09.12.2012  +000 00:18:59
08.12.2012  +000 00:19:23
07.12.2012  +000 00:34:24
06.12.2012  +000 02:06:52 -- непараллельно

Из AWR можно видеть, что при непараллельном выполнении максимальное суммарное по процессам использование «виртуальной памяти» Oracle — PGA+TEMP — превышает 30 ГБ:

SQL> --serial execution
SQL> select sql_plan_line_id as plan_line_id,
  2         plan_operation,
  3         plan_op_frequency,
  4         max(proc_count),
  5         to_char(max(sum_pga_allocated), '999,999,999,999') as MAX_SUM_PGA_ALLOCATED,
  6         to_char(max(sum_temp_allocated), '999,999,999,999') as MAX_SUM_TEMP_ALLOCATED
  7    from (select sql_plan_line_id,
  8                 sql_plan_operation || ' ' || sql_plan_options as PLAN_OPERATION,
  9                 count(sample_id) over(partition by sql_plan_line_id) PLAN_OP_FREQUENCY,
 10                 count(distinct session_id) over(partition by sample_time) as PROC_COUNT,
 11                 sum(pga_allocated) over(partition by sample_time) SUM_PGA_ALLOCATED,
 12                 sum(temp_space_allocated) over(partition by sample_time) SUM_TEMP_ALLOCATED
 13            from dba_hist_active_sess_history
 14           where sql_id = '7q1grk5zza7f0'
 15             and sql_exec_id = 16777218
 16             and snap_id between 42329 and 42332)
 17   group by sql_plan_line_id,plan_operation, plan_op_frequency
 18   order by plan_op_frequency desc
 19  /

 

PLAN_LINE_ID PLAN_OPERATION       PLAN_OP_FREQUENCY MAX(PROC_COUNT) MAX_SUM_PGA_ALLOCATED MAX_SUM_TEMP_ALLOCATED
------------ -------------------- ----------------- --------------- --------------------- ----------------------
           4 HASH UNIQUE                        384               1    2,624,806,912        31,198,281,728
          24 MAT_VIEW ACCESS FULL               161               1    2,633,326,592         4,621,074,432
          27 MAT_VIEW ACCESS FULL                83               1    2,637,914,112         4,623,171,584
           7 HASH JOIN BUFFERED                  55               1    2,599,641,088        30,646,730,752
           1 LOAD AS SELECT                      33               1    1,576,886,272        28,152,168,448
          14 MAT_VIEW ACCESS FULL                 3               1      157,573,120           460,324,864
          20 VIEW                                 1               1    2,425,053,184         4,289,724,416

, а при параллельном выполнении не превышает 10 ГБ:

SQL> --parallel execution

PLAN_LINE_ID PLAN_OPERATION       PLAN_OP_FREQUENCY MAX(PROC_COUNT) MAX_SUM_PGA_ALLOCATED MAX_SUM_TEMP_ALLOCATED
------------ -------------------- ----------------- --------------- --------------------- ----------------------
           6 PX SEND HASH                       350              26    6,506,414,080         3,441,426,432
           4 HASH UNIQUE                        259              26    6,506,414,080         3,441,426,432
          27 MAT_VIEW ACCESS FULL               231              22      658,849,792           679,477,248
           7 HASH JOIN BUFFERED                 157              26    6,506,414,080         3,441,426,432
           5 PX RECEIVE                          57              26    6,506,414,080         3,441,426,432
           1 LOAD AS SELECT                      43               3      892,215,296
          17 MAT_VIEW ACCESS FULL                 6              14      417,415,168            96,468,992
           3 PX SEND QC (RANDOM)                  2               2      572,637,184
             INSERT STATEMENT                     1               1        5,529,600
           2 PX COORDINATOR                       1               1        5,529,600
          19 PX SEND HASH                         1              18      160,350,208
          21 UNION-ALL                            1              18      160,350,208

— при том что в обоих случая операция прямой вставки LOAD AS SELECT выполняется непараллельно и занимает незначительную часть общего времени  выполнения

Во всех случаях используется «параллельный» план с фантастическими цифрами, расчитанный при следующих значениях:

  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
...
_pga_max_size                       = 2097152 KB -- при pga_aggregate_target = 15G
...
parallel_query_default_dop          = 32         -- = total_cpu_count * parallel_threads_per_cpu
  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
...
parallel_threads_per_cpu            = 2
...
parallel_degree_limit               = 65535      -- при parallel_degree_limit = CPU
...
parallel_max_degree                 = 32
total_cpu_count                     = 16

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1389495461

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                  |  5899M|   692G|       |  3749K  (1)| 14:34:57 |       |       |        |      |            |
|   1 |  LOAD AS SELECT                   |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                  |       |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)            |  5899M|   692G|       |  3749K  (1)| 14:34:57 |       |       |  Q1,03 | P->S | QC (RAND)  |
|   4 |     HASH UNIQUE                   |  5899M|   692G|   841G|  3749K  (1)| 14:34:57 |       |       |  Q1,03 | PCWP |            |
|   5 |      PX RECEIVE                   |  5899M|   692G|       |   138K  (2)| 00:32:14 |       |       |  Q1,03 | PCWP |            |
|   6 |       PX SEND HASH                |  5899M|   692G|       |   138K  (2)| 00:32:14 |       |       |  Q1,02 | P->P | HASH       |
|*  7 |        HASH JOIN BUFFERED         |  5899M|   692G|       |   138K  (2)| 00:32:14 |       |       |  Q1,02 | PCWP |            |
|   8 |         PX RECEIVE                |    46M|   528M|       |  3739   (2)| 00:00:53 |       |       |  Q1,02 | PCWP |            |
|   9 |          PX SEND HASH             |    46M|   528M|       |  3739   (2)| 00:00:53 |       |       |  Q1,00 | P->P | HASH       |
|  10 |           VIEW                    |    46M|   528M|       |  3739   (2)| 00:00:53 |       |       |  Q1,00 | PCWP |            |
|  11 |            UNION-ALL              |       |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|* 12 |             FILTER                |       |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|  13 |              PX BLOCK ITERATOR    |    23M|   264M|       |  1870   (2)| 00:00:27 |       |       |  Q1,00 | PCWC |            |
|  14 |               MAT_VIEW ACCESS FULL|    23M|   264M|       |  1870   (2)| 00:00:27 |       |       |  Q1,00 | PCWP |            |
|* 15 |             FILTER                |       |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|  16 |              PX BLOCK ITERATOR    |    23M|   264M|       |  1870   (2)| 00:00:27 |       |       |  Q1,00 | PCWC |            |
|  17 |               MAT_VIEW ACCESS FULL|    23M|   264M|       |  1870   (2)| 00:00:27 |       |       |  Q1,00 | PCWP |            |
|  18 |         PX RECEIVE                |    85M|  9308M|       |   133K  (1)| 00:31:10 |       |       |  Q1,02 | PCWP |            |
|  19 |          PX SEND HASH             |    85M|  9308M|       |   133K  (1)| 00:31:10 |       |       |  Q1,01 | P->P | HASH       |
|  20 |           VIEW                    |    85M|  9308M|       |   133K  (1)| 00:31:10 |       |       |  Q1,01 | PCWP |            |
|  21 |            UNION-ALL              |       |       |       |            |          |       |       |  Q1,01 | PCWP |            |
|* 22 |             FILTER                |       |       |       |            |          |       |       |  Q1,01 | PCWC |            |
|  23 |              PX BLOCK ITERATOR    |    42M|  3591M|       |   115K  (1)| 00:26:58 |     1 |1048575|  Q1,01 | PCWC |            |
|* 24 |               MAT_VIEW ACCESS FULL|    42M|  3591M|       |   115K  (1)| 00:26:58 |     1 |1048575|  Q1,01 | PCWP |            |
|* 25 |             FILTER                |       |       |       |            |          |       |       |  Q1,01 | PCWC |            |
|  26 |              PX BLOCK ITERATOR    |    42M|  3593M|       | 18057   (1)| 00:04:13 |     1 |1048575|  Q1,01 | PCWC |            |
|* 27 |               MAT_VIEW ACCESS FULL|    42M|  3593M|       | 18057   (1)| 00:04:13 |     1 |1048575|  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------

Для стабилизации времени выполнения и / или экономии места во временном табличном пр-ве можно снизить порог запуска параллельного выполнения параметром:

SQL> @param parallel_servers_target

NAME                    VALUE DSC
----------------------- ----- ------------------------------------------------------
parallel_servers_target 36    instance target in terms of number of parallel servers

или использовать механизм очерёдности параллельного выполнения (parallel statement queuing)

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

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

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