Oracle mechanics

17.03.2011

Отключение режима параллельного выполнения на уровне отдельного SQL

Filed under: CBO,Optimizer features,Oracle,parameters,PX — Игорь Усольцев @ 01:29

Бывает необходимо отключить режим параллельного выполнения на уровне одного запроса — точечно — для сравнительного тестирования производительности в разных режимах, или столкнувшись с одним из багов, для которых непараллельное (sequential) выполнение проблемного запроса является единственным быстрым workaround’ом.

Например, в версии 11.1.0.7 столкнулись с ошибкой:

SQL> insert into some_table (...)
 (select ... from some_other_tables, views, ... where ... group by ...)
 /
 insert
 *
 ERROR at line 1:
 ORA-12801: error signaled in parallel query server P005
 ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)

По способу решения проблема (точнее, по быстрому workaround’у, для действительного решения нужно работать с тех.поддержкой — трейсы, патчи и т.д.) схожа с проблемой Ora-04030 with high PGA allocation for «kxs-heap-p» under session heap [ID 873392.1] — исключить параллельные операции из запроса

Подсказка NO_PARALLEL помогает отключить режим параллельного выполнения при отдельном выполнении  подзапроса

select/*+ NO_PARALLEL*/ ... from some_other_tables, views, ... where ... group by ...

но не отключает параллельное выполнение всего DML

insert/*+ NO_PARALLEL*/ into some_table (...)
 (select/*+ NO_PARALLEL*/ ... from some_other_tables, views, ... where ... group by ...)

— по прежнему выполняется с использованием параллельных процессов и заканчивается указанной ошибкой, несмотря на то, что все таблицы и индексы, задействованные при выполнении этого INSERT имеют parallel degree = 1

Формально подсказка не предназначена для отключения режима параллельного выполнения: «NO_PARALLEL переопределяет значение параметра PARALLEL [degree], указанного при создании или изменении объекта бд«, для действительного же отключения параллельного режима Oracle рекомендует использовать:

«… команды ALTER SESSION DISABLE PARALLEL DML|DDL|QUERY. Все последующие DML (INSERT, UPDATE, DELETE), DDL (CREATE, ALTER), или запросы (SELECT) будут выполняться последовательно … независимо от использования подсказок PARALLEL в запросе или атрибутов parallel [degree] используемых в запросе таблиц и индексов»

Действительно, указанные команды отлично работают, в описанном случае для исключения ошибки (бага) оказалось достаточно выполнить

SQL> ALTER SESSION DISABLE PARALLEL QUERY;

поскольку параллельно норовил выполняться именно запрос, insert выполнялся последовательно. Но указанное решение работает на уровне сессии, что может быть не всегда желательно.

Для отключения режима параллельного выполнения на  уровне запроса можно попытаться выяснить, что меняется в системе после указанного ALTER SESSION? Поиск по параметрам бд / инстанса / сессии ничего не дал, а вот один из параметров оптимизатора  действительно изменился (используя лингвистический подход — like ‘parallel%’)

SQL> ALTER SESSION DISABLE PARALLEL QUERY;
Session altered.

SQL> select name, isdefault, value from v$ses_optimizer_env
 2  where sid = sys_context('USERENV', 'SID')
 3  and name like 'parallel%'
 4  /

NAME                                     ISD VALUE
---------------------------------------- --- --------
parallel_execution_enabled               YES true
parallel_threads_per_cpu                 YES 2
parallel_query_mode                      NO  disabled
parallel_dml_mode                        YES disabled
parallel_ddl_mode                        YES enabled
parallel_degree_policy                   YES manual
parallel_degree                          YES 0
parallel_min_time_threshold              YES 10
parallel_query_default_dop               YES 0
parallel_degree_limit                    YES 65535
parallel_force_local                     YES false
parallel_max_degree                      YES 8
parallel_autodop                         YES 0
parallel_ddldml                          YES 0

Некоторые параметры оптимизатора на уровне запроса позволяет менять подсказка OPT_PARAM, остаётся проверить работает ли она с найденным параметром parallel_query_mode

SQL> insert/*+ OPT_PARAM('parallel_query_mode' 'disabled') */ into some_table (...)
 (select ... from some_other_tables, views, ... where ... group by ...)
 /

— работает!

Также параллельный режим успешно отключается при переключении (более «сильного»?) параметра parallel_execution_enabled в значение false

SQL> insert/*+ OPT_PARAM('parallel_execution_enabled' 'false') */ into some_table (...)
 (select ... from some_other_tables, views, ... where ... group by ...)
 /

Параметры оптимизатора

parallel_query_mode
parallel_dml_mode
parallel_ddl_mode

имеют 3 допустимых значения (в полном соответствии с командами ALTER SESSION ENABLE | DISABLE | FORCE PARALLEL DML | DDL | QUERY)

enabled
disabled
forced

По умолчанию parallel_dml_mode в версиях 11.1, 11.2 отключен (disabled), остальные — включены (enabled)

Полезный whitepaper по параллельному выполнению Oracle database parallel execution fundamentals

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

  1. Синтаксис NO_PARALLEL требует указания таблицы. Если это не действует, значит это баг.

    комментарий от Timur Akhmadeev — 17.03.2011 @ 10:45 | Ответить

    • конечно указывал на уровне подзапроса
      Дело в том, что в подзапросе используются обзор и параллельное выполнение «порождалось» параллельной обработкой глобального индекса партицированной таблицы, используемой в подзапросе (и для индекса, и для таблицы parallel degree=1)
      Добавление хинтов NO_PARALLEL, NO_PARALLEL_INDEX с указанием таблиц и индексов в определение обзора, возможно, помогло бы

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

  2. По проблеме заведён баг https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=12585810&productFamily=Oracle
    , где неверно указано
    Issue reproducable only at the customer side
    — лично тестировал на свежей БД (ASM) с теми же данными — прекрасно воспроизводится, сообщил в поддержку, в ответ на что «аналитик» Oracle support задал невинный вопрос:
    Are you able to reproduce the issue on a normal database instance without using ASM?
    — чем немало озадачил :(
    Также не упомянут найденный workaround — описанное отключение режима параллельного выполнения

    комментарий от Igor Usoltsev — 29.06.2011 @ 15:30 | Ответить


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