Oracle mechanics

03.07.2013

Параллельное выполнение непараллельных запросов

Filed under: Oracle,PX,SQL Tuning — Игорь Усольцев @ 12:38
Tags: ,

(или какую практическую пользу можно извлечь, стимулируя параллельное выполнение комплексных запросов, использующих непараллельные функции без ключевого слова PARALLEL_ENABLE ?)

Проблема:

Fri Jun 14 14:32:11 2013
ORA-01555 caused by SQL statement below (SQL ID: 619hu75mw2770, Query Duration=152691 sec, SCN: 0x07a9.da2c32b7):
with AP_STANDARD as ...

при выполнении традиционно крупногабаритного (~ 500 строк и в тексте, и в плане) запроса в бд OEBS

Характерными причинами неспособности Oracle сгенерировать быстрый план кроме избыточной нормализации схем OEBS и, как следствие, громозкости получающихся запросов, является распостранённое использование «непараллельных», в смысле активно читающих переменные пакета, функций типа FND_PROFILE.VALUE как в явном виде — в условиях запроса, так и в составе параметризованных обзоров

Судя по тексту функций основных схем OEBS, разработчики Oracle Applications по какой-то причине не склонны использовать/стимулировать параллельное выполнение:

11.2.0.3.OEBS2@APPS SQL> select * from dba_source where owner like 'APP%' and UPPER(text) like '%PARALLEL\_ENABLE%' and name like 'FND\_%' escape '\';

no rows selected

Однако форсирование параллельного выполнения изменением параметров сессии:

SQL> alter session force parallel query parallel N;

, или подсказкой /*+ PARALLEL(N)*/ на уровне запроса может быть полезным и в этом случае

Запрос при этом будет по-прежнему выполняться одним процессом (последовательно), что отражено операцией PX COORDINATOR FORCED SERIAL, по псевдопараллельному плану:

SQL> set autotrace traceonly explain
SQL> with AP_STANDARD as
  2                     (
...
404        )
405        select--+ parallel(8)
...
527  /

312 rows selected.

Elapsed: 01:10:24.73

Execution Plan
----------------------------------------------------------
Plan hash value: 822534075                                 -- первый план от команды EXPLAIN

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                              |     2 |   834 |       |  1509K  (1)| 00:50:20 |
|   1 |  PX COORDINATOR FORCED SERIAL                                |                              |       |       |       |            |          |
|   2 |   PX SEND QC (ORDER)                                         | :TQ40023                     |     2 |   834 |       |  1509K  (1)| 00:50:20 |
...
|*495 |                    TABLE ACCESS BY INDEX ROWID               | AP_SUPPLIERS                 |     1 |    26 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=7)
   - Degree of Parallelism is 8 because of hint

— но начиная с 11.2 для запросов с параллельным планом может применяться dynamic sampling на усмотрение оптимизатора:

For parallel statements, the optimizer automatically decides whether to use dynamic sampling and which level to use. The decision depends on the size of the tables and the complexity of the predicates. The optimizer expects parallel statements to be resource-intensive, so the additional overhead at compile time is worth it to ensure the best plan

— случай по причине complexity of the predicates как раз подходящий, и благодаря использованию dynamic sampling время выполнения запроса уменьшилось с 2-х суток до 1 часа

Важно, как оказалось, что при таком неявном стимулировании dynamic sampling получаемый план выполнения не подвержен (или слабо подвержен) воздействию cardinality feedback — т.е. второе и последующие выполнения запроса стабильно используют один и тот же удовлетворительный план (естественно до тех пор пока курсор находится в Shared Pool):

SQL> /

312 rows selected.

Elapsed: 00:33:43.05

Execution Plan
---------------------------
Plan hash value: 1034711645                                -- отличающийся план при втором выполнении EXPLAIN PLAN
...

SQL> @v$sqlstats 06p3bx2bks6t6

EXECS SQL_ID              PLAN ELA_PER_EXEC ROWS_PER_EXEC PX_PER_EXEC
----- ------------- ---------- ------------ ------------- -----------
    2 06p3bx2bks6t6 1468238853   3121916489           311           0 -- PX_PER_EXEC=0, т.е. запрос выполнился полностью непараллельно

— в качестве иллюстрации комплексности запроса можно отметить, что при каждом выполнении команда EXPLAIN PLAN генерировала различные Plan hash value, ни разу не совпадающие с действительным планов выполнения из v$sql / v$sqlstats

Явное же стимулирование dynamic sampling параметром OPTIMIZER_DYNAMIC_SAMPLING , либо подсказкой /*+ DYNAMIC_SAMPLING(7)*/ на уровне запроса (которая в отличие от предыдущих версий прогнозируемо отрабатывает в 11.2 для всех таблиц запроса) показывает противоречивые результаты:

SQL> with AP_STANDARD as
  2                     (
...
404        )
405        select--+ DYNAMIC_SAMPLING(7)
...
527  /

312 rows selected.

Elapsed: 00:03:54.69                                   -- случайно можно получить отличное время выполнения, и создать из такого плана
                                                       -- красивый BASELINE, который для такого сложного запроса вряд ли будет стабилен

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

Plan hash value: 3243241023

---------------------------------------------------------------------------------------------------------
| Id  | Operation                                               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                        |       |       | 47528 (100)|          |
|   1 |  SORT ORDER BY                                          |   321 |   130K| 47528   (1)| 00:09:31 |
...

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

SQL> /

ORA-01013: user requested cancel of current operation   -- но трудно достичь стабильности - план неповторим уже при следующем выполнении

Elapsed: 00:53:50.19                                    -- пришлось остановить выполнение

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('0pqygbv2jwpgz', 1));

Plan hash value: 2233069740

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                                              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                       |       |       |       |   766K(100)|          | -- стоимость и время выполнения
|   1 |  SORT ORDER BY                                         |   321 |   130K|       |   766K  (1)| 02:33:15 | -- формально сильно проигрывают первому плану
...
Note
-----
   - dynamic sampling used for this statement (level=7)
   - cardinality feedback used for this statement

— похоже, что прямая установка параметра OPTIMIZER_DYNAMIC_SAMPLING способствует более внимательному отношению к статистике выполнения запроса (и, как следствие, к применению технологии cardinality feedback), что со стороны Oracle вполне логично

В качестве следующего метода оптимизации запроса можно использовать частичную материализацию (которая напрашивается, раз уж разработчики сами используют конструкцию WITH) + уточнение кол-ва возвращаемых строк подсказкой CARDINALITY на основании данных трассировки / статистики плана выполнения запроса (обе подсказки недокументированы / нерекомендованы, но выбирать не из чего):

SQL> with AP_STANDARD as
  2                     (
...
223        ,AP_PREPAYMENT as
224        (
225          select--+ materialize cardinality(1000000)
...
404        )
405        select--+ parallel(8)
...
527  /

312 rows selected.

Elapsed: 00:16:38.00

Plan hash value: 39902770

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                              |       |       |       | 15143 (100)|          |        |      |            |
|   1 |  TEMP TABLE TRANSFORMATION             |                              |       |       |       |            |          |        |      |            | -- эта часть (WITH) выполняется параллельно
|   2 |   PX COORDINATOR                       |                              |       |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)                 | :TQ10005                     |  1000K|   134M|       |            |          |  Q1,05 | P->S | QC (RAND)  |
|   4 |     LOAD AS SELECT                     |                              |       |       |       |            |          |  Q1,05 | PCWP |            |
...
|  59 |   PX COORDINATOR FORCED SERIAL         |                              |       |       |       |            |          |        |      |            | -- основной запрос выполняется по-прежнему сериально
|  60 |    PX SEND QC (ORDER)                  | :TQ60033                     |  1000K|   397M|       | 14632   (1)| 00:02:56 |  Q6,33 | P->S | QC (ORDER) |
|  61 |     SORT ORDER BY                      |                              |  1000K|   397M|   434M| 14632   (1)| 00:02:56 |  Q6,33 | PCWP |            |
...
| 554 |                      TABLE ACCESS FULL | SYS_TEMP_0FD9FC8A4_2BCAC7BD  |  1000K|    99M|       |   721   (1)| 00:00:09 |  Q6,25 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=7)
   - Degree of Parallelism is 8 because of hint

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

SQL> @dba_hist_sqlstat 80hr7qzxd2gtd

SNAP_ID BEGIN_INTERVAL EXECS SQL_ID              PLAN   COST ELA_PER_EXEC  ROWS_PER_EXEC FETCHES_PER_EXEC PARSE_PER_EXEC PX_PER_EXEC
------- -------------- ----- ------------- ---------- ------ ------------  ------------- ---------------- -------------- -----------
  55830 21.06.13 13:54     1 80hr7qzxd2gtd   39902770  15143    875075604              0                0             17          16
  55831 21.06.13 14:54     1 80hr7qzxd2gtd   39902770  15101    354293941            312                3             17          16

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

  1. День добрый!
    Игорь, спасибо за ваш блог!
    Уточните пожалуйста, как я понял «лекарство» получается связка whith с хинтами : materialize, cardinality и parallel…но почему «- dynamic sampling used for this statement (level=7)» ?
    Ведь в этой конструкции вы не выставляли явно dynamic sampling, верно ?

    комментарий от Павел — 03.07.2013 @ 14:43 | Ответить

    • Верно, Павел, явно dynamic sampling я не использовал
      Решение о применении и уровне dynamic sampling для запросов с параллельным планом выполнения, начиная с 11.2, Oracle принимает самостоятельно (в посте есть ссылка на доку) — этим известным фактом я и воспользовался. Важно, что такой автоматический dynamic sampling срабатывает даже для запросов, которые в принципе не могут выполняться параллельно по каким-то причинам

      комментарий от Igor Usoltsev — 03.07.2013 @ 16:11 | Ответить


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