Hint parallel oracle как работает

Обновлено: 07.07.2024

Представьте: есть селект, который возвращает записи, каждую из которых нужно обработать, и то ли много записей, то ли обработка каждой записи занимает много времени, а процесс обработки одной записи не зависит от процессов других записей.
Классический пример для того, чтобы задействовать многопоточность или в случае баз данных выполнять обработку в нескольких сессиях. В Оракле для этого используется hint /*+ parallel() */ и pipelined functions. Это здорово, но если у вас Oracle standard edition(где parallel не работает) или вы хотите обработать не каждую запись по отдельности(из соображений, что лучше накопить работу, а потом в bulk, одним ударом, выполнить), а поделить весь вывод селекта на куски и каждый обработать отдельно?

Задача ставится так:

Текст селекта
Имя процедуры, которая будет работать с порцией данных
Колличество потоков(Thread)
Данные, необходимые для подключения к базе

Java откроет по тексту селекта result set в default connection.
Первым делом надо выполнить
select count(*) from («Текст селекта»);
Создадим connection pool с размерностью, заданной в 3-м параметре.
Создадим отдельные сессии, присоединившись через jdbc connection.

Данные для этого возьмем из 4-го параметра, нам, по большому счету нужен только пароль, все остальное получим сами(может еще порт, если он отличен от 1521).

Будем получать данные из селекта в default connection и переписывать их в сессию из пула. Как только решим, что накопили достаточно, создадим thread, передадим ему эту connection как параметр и пусть работает, а мы продолжим со следующей сессией или, если все уже прочитано, подождем окончания всех потоков.

Напишем функцию обработки. Она получает все поля селекта как параметры.

Будет удобно, чтобы, например, первые два параметра были бы номер в порции и ее размерность. Это даст возможность в dbms info выводить процент выполнения в потоке.

По метадате селекта будем конструировать ее вызов в виде примерно так:

begin proc1(23,14000,'a1',3,'tratata',35,48); end;

Хранить будем только такую строку.

Вначале это был 2-х мерный массив (i,j), где i — это номер потока(в дальнейшем. ). Потом я увидел, что при большом числе записей, затраты Oracle на поддержку большого массива становятся чрезмерными и решил пользоваться также временной таблицей(temporary table).

Я положил границу в 200,000 записей. Если селект count(*) возвращает меньше 200,000 Java в-runtime использует 2-х мерный String массив, если больше — пишет во временную таблицу parallel_calls_tmp с одним полем varchar2(4000).

На стороне Java есть функция

Получение массива типов данных полей селекта

Так строим строку вызова:

Накапливаем в массиве или таблице

А теперь весь класс, который нужно загрузить в базу.

В принципе, я сейчас подумал, что сработает не только с Oracle, а с любой базой…

Если кому интересно, могу рассказать, что я добавил для того, чтобы работать не с симуляцией pipelined функции, а с выполнением отдельных batches…

Могу сказать, что я в результате получил выигрыш во времени: 12 часов в одной сессии против часа с половиной в 25 сессиях. При этом все 16 процессоров сервера были нагружены под 100%.

В теории этого достаточно, чтобы ускорить запрос в разы.
Но есть ряд ситуаций в которых параллельность наоборот мешает.

Для начала разберемся с терминологией - посмотрим на параллельный план с join 2 таблиц:
- Запрос 1

Таблица T2 имеет особенность: fk_id_skew неравномерно заполнен и имеет перекос в сторону 1 - она встречается значительно чаще других.
- Запрос 2

Итак, выполнил простой запрос:
- Запрос 3

* regexp_replace в этом запросе нужен, чтобы данные отбирались не мгновенно и были видны в статистике затраты CPU.
* Хинты вставлены, чтобы запрос в плане выглядел также как написан тут.

Время выполнения выполнения запроса = 49сек.

Добавим хинт parallel(8) замен no_parallel.
Время выполнения = 8с, что в 6 раз быстрей.
Разберем для понимания план запроса:
- План 1

Основопологающие фазы:
* PX BLOCK ITERATOR - чтение таблицы частями в несколько потоков
* PX SEND - 1 поток посылает данные другому. Важно знать, что только один producer (PX SEND) может быть активен в одно время, что накладывает ограничения на параллельный план выполнения, подробней: Вторая часть по распределению данных в параллельных запросах
** RANGE - данные будут разбиты на диапазоны (часто при сортировке)
** HASH - диапазон данных на основе их хэша (hash join, group by)
** RANDOM - случайная отправка
** BROADCAST - отправка таблицы во все потоки (часто на маленькой таблице, совместно с последующей ROUND ROBIN правой таблицы. Может быть проблемой производительности, если левая таблица значительно больше, чем указано в статистике, т.к. данные дублируются во все потоки)
** ROUND ROBIN - данные отправляются в потоки по кругу

Про способы распределения данных по потокам нужно поговорить отдельно:

Стоит заметить, что данные бьются по значениям в столбцах строк, а не просто по строкам.
Это нужно, чтобы один и тотже диапозон данных из разных таблиц попал в один поток для join.
Если бы Oracle делал не так, то в 1 поток могли бы попасть совершенно разные данные и join нельзя было бы совершить.
На это стоит обратить внимание, т.к. это может являться и причиной замедлений выполнения параллельного запроса при сильном перекосе данных (О причинах замделенния параллельных запросов дальше)

** P->P - данные из одной параллельной группы передаются в другую параллельную группу
** P->S - параллельность в последовательное выполнение (узкое место или конец запроса - вторая из основных причин замедления параллельного запроса)
** PCWP - параллельность с родителем: сканируем таблицу и сразу делаем join с другой
** PCWC - наоборот: передаем фильтр из внешнего потока и применяем при сканировании
* PX RECEIVE - получение данных из одного параллельного потока в другой
* PX SEND QC - отправка данных координатору
* PX COORDINATOR - приемник всех параллельных запросов
* TQ - Номер потока

1. Наличие в плане события "P->S - параллельность в последовательное выполнение", кроме перед "PX COORDINATOR"
Это говорит нам о том, что Oracle вынужден был собрать все потоки в одну последовательность (sequence), что дало бутылочное горлышко ожидания выполнения самого долго потока всеми остальными.

Приведу пример с rownum. Добавим отбор номера строки из каждой таблицы:
План поменялся,
* для расчета COUNT rownum параллельный процесс чтения таблицы с диска "PX BLOCK ITERATOR" выстраивается в очередь "P->S", что сводит на нет все перимещуство распределенного чтения.
* теперь JOIN не выполняется в отдельном потоке ( :TQ10002 )
т.к. оба потока уже были раньше преобразованы в последовательный набор данных и не могут использоваться одновременно.
Как следствие, время выполнения запроса стало даже больше ( 51 с ), чем не параллельная версия (49 с ) из-за лишних издержек на поддержку параллельности, которая не используется

2. PX SEND skew - Перекос данных
при формировании диапозонов данных в один из потоков.

Продемонстрировать это просто используя заранее созданный перекошенный столбец t_2.fk_id_skew.

Если выполнить запрос, но для join таблиц использовать условие: t_2.fk_id_skew = t_1.id
То общий план параллельного запроса не поменяется (см. План 1 ), но вот время выполнения возрастет до 38с.

Причина кроется в том, что в колонке t_2.fk_id_skew кроется 1 500 000 значений = 1 и 3 500 000 остальных. И при выполнении "PX SEND HASH" большая часть строк таблицы попадают в один поток для обработки, вместо того, чтобы равномерно распределиться.

Это хорошо видно в статистике выполнения. Для просмотра воспользуемся функцией "DBMS_SQLTUNE.REPORT_SQL_MONITOR".
Нас интересуют вкладки Parallel и Activity:

Рис.1 - Большую часть времени запрос выполнялся в один поток, остальные потоки его ждали.

Рис. 2. - Это же подтверждается на вкладке PARALLEL: 37c от общего времени работал 1 поток.

Oracle поступает верно, т.к. нельзя же сделать join данных из разных диапазонов.

Для сравнения взгляните статистику выполнения для хорошо распараллеленого запроса (План 1) с условием без перекосов t_2.fk_id_uniform = t_1.id

Рис. 3 и Рис. 4

Все выполнялось в 8 потоков и каждый поток равномерно обработал только свою равную часть.

3. Bloom filters
Не вдаваясь в механику создания битовых векторов bloom filter опишу преимущество их использования.
Пример запроса:
1. На таблице T1 с фильтром "filter("T1"."MOD"=42)" создается bloom filter - PX JOIN FILTER CREATE
2. Фильтр из п.1 применяется на таблицу T2 - PX JOIN FILTER USE
Тем самым ограничивая размер правой таблицы.
3. Отфильтрованная таблица T2 соединяется с HASH JOIN BUFFERED
Подробное описание bloom filter: Bloom filter

bloom filter хороши в:
* Параллельных запросах - уменьшается количество передаваемых данных между потоками за счет предфильтрации правой таблицы
* RAC системах - уменьшает число передаваемых по сети данных между нодами
* InMemory таблицах - осуществляя inmemory предфильтрацию таблицы и осуществление не inmemory join на быстро отфильтрованной правой таблице

4. Partition Wise
В целом похоже на предыдущий пункт, но фильтр накладывается на партиции правой таблицы, также уменьшая сканирования.
Дедектируется в плане по фразам:
* PART JOIN FILTER CREATE ( :BF0000 ) - создание фильтра на левой таблице
* Pstart| Pstop = :BF0000|:BF0000 - применение фильтра в операции чтения правой таблицы PCWC

Уменьшение времени пакетной (для olap/dwh) вставки данных:
Отличительная особенность olap: вставка одна, но очень большая.

1. Делаем таблицу не логируемой.
Что уменьшит затраты на вставку в redo log.
* Может не сработать, если в базе включено FORCE_LOGGING = YES

2. Добавляем /*+ append */ в insert операцию
* Данные добавляются в конец таблицы, вместо попытки поиска пустых мест.
* Данные пишутся напрямую в data файлы, минуя буферный кэш.

3. Отключаем constraint, trigger на таблице и явно вставляем значения в default колонки.
Замечу, что если надо ускорить вставку, то надо отключать FK на самой таблице, а если удаление, то FK на других таблицах, которые указывают на нашу.

4. Распараллеливаем запрос хинтом /*+ PARALLEL (8) */
Не забываем включать параллельность для DML, чтобы параллелился и insert, а не только select.

5. Если распаралеллить вставку нельзя, к примеру из-за доступа по dblink.
Можно физически распаралелить вставку через несколько одновременных вставок кусками части данных из источника.
Сделать это можно через dbms_parallel.
Очень хорошо подходит для одновременного копирования нескольких таблиц или если таблица партиционирована.
При вставке в одну таблицу незабываем про ограничения хинта append из п.2

6. Удаляем index и foreign key с внешних таблиц.
Пришлось именно удалять, т.к.
* DISABLE можно делать только у функциональных индексов
* UNUSABLE можно сделать на всех индексах, но DML запросы все равно будут валиться на UNIQUE index
http://docs.oracle.com/cd/B13789_01/server.101/b10755/initparams197.htm
Ничего страшного в этом нет, восстановление индексов заняло 5 минут по 10 млн записей, что все равно лучше 4 часов вставки.
Удаляем все, включая Prmary Key. Но тут не забываем, что каскадно удалятся и все FK. Их надо будет потом восстановить, ну или PK придется пожертвовать и оставить.
7. Делаем кэшируемым Sequence.
Если в insert используется sequence, то делаем его кэшируемым.
С "CACHE 50000" мне удалось сократить время вставки 10 млн записей с 50 минут до 5. Это в 10 раз!
При кэширумом sequence последовательность заранее подготавливает числа и хранит в памяти, а это значит, что накладных расходов обмена становится меньше.

8. IOT таблица
Если на таблице один индекс, который покрывает большую часть столбцов, то ее можно конвертировать в IOT таблицу. Так мы уменьшаем число обслуживаемых объектов до 1. Что уменьшает число буферных чтений с 3 (2 чтения индекса + 1 чтения таблицы) при любых DML/select до 2 (2 чтения индекса).

Уменьшение времени распределенной/многопользовательской (oltp) вставки данных:
отличительной особенности вставок в oltp является то, что их очень много, каждая из них создает микроскопическую нагрузку, но все вместе могут создать большое кол-во событий ожиданий (busy wait). Рассмотрим отдельно как обойти эти ожидания:

1. увеличение числа списка свободных блоков (free_list при создании таблицы)
+ уменьшение конкуренции за поиск свободных блоков за счет распараллеливания вставки
- раздувание таблицы, т.к. когда заканчивается free_list1, то он не будет использовать свободные блоки из free_list2, а выделит новые поверх HWM
- увеличивает фактор кластеризации индексов, т.к. данные физически раскидываются по разным местам таблицы, а не идут последовательно

2. сделать индекс реверсивным, если нет возможности отключить при вставке
+ уменьшение конкуренции за вставку данных в индекс, т.к. последовательные реверсивные данные будут использовать разные блоки индекса
- увеличение фактора кластеризации из-за разброса данных
- нельзя будет использовать range scan (сканирование по диапазону) индекса, т.к. в индексе уже не сами данные, а их инвертированные значения
Стоит заметить о факторе класетризации: чаще всего в oltp системе он не очень важен, т.к. доступ к данным идет по конкретному значению к одному конкретному блоку. Т.е. здесь нет скачков по разным блокам, как при сканировании по диапазону.

3. использование хинта append_values
+ запись данных не будет использовать free_list, а будет просто писаться поверх HWM
- разрастание таблицы

4. секционирование таблицы, таким образом, чтобы параллельные вставки шли в физически разные секции таблицы.
Т.е. секционирование по первичному ключу или по дате не подходит, нужно по какомуто столбцу, которые присутствует во всех вставках ежедневно и имеет одинаковый разброс.

5. Выполнение вставки используя prepared statement
что позволит исключить парсинг SQL перед его выполнением.

6. Вставка строк блоками (executeBatch)
Что позволит снизить задержки на network lookup - время на установку соединения и передачу данных по сети.

7. 7п. из пакетной вставки - кэшируемый индекс

8. остальные способы из пакетной вставки, если они применимы в текущей ситуации

You can use optimizer hints with SQL statements to alter execution plans. This chapter explains how to use hints to instruct the optimizer to use specific approaches.

The chapter contains the following sections:

19.1 Overview of Optimizer Hints

A hint is an instruction to the optimizer. When writing SQL, you may know information about the data unknown to the optimizer. Hints enable you to make decisions normally made by the optimizer, sometimes causing the optimizer to select a plan that it sees as higher cost.

In a test or development environments, hints are useful for testing the performance of a specific access path. For example, you may know that a certain index is more selective for certain queries. In this case, you may use hints to instruct the optimizer to use a better execution plan.

The disadvantage of hints is the extra code that must be managed, checked, and controlled. Changes in the database and host environment can make hints obsolete or even have negative consequences. For this reason, test by means of hints, but use other techniques to manage the SQL execution plans, such as SQL Tuning advisor and SQL Plan Baselines.

Oracle Database supports more than 60 hints, each of which may have zero or more parameters. A statement block can have only one comment containing hints, and that comment must follow the SELECT , UPDATE , INSERT , MERGE , or DELETE keyword. For example, the following hint directs the optimizer to pick the query plan that produces the first 10 rows from the employees table at the lowest cost:

Oracle Database SQL Language Reference to a complete list of hints supported by Oracle Database

19.1.1 Types of Hints

Hints can be of the following general types:

Single-table hints are specified on one table or view. INDEX and USE_NL are examples of single-table hints.

Multi-table hints are like single-table hints, except that the hint can specify one or more tables or views. LEADING is an example of a multi-table hint. Note that USE_NL(table1 table2) is not considered a multi-table hint because it is a shortcut for USE_NL(table1) and USE_NL(table2) .

Query block hints operate on single query blocks. STAR_TRANSFORMATION and UNNEST are examples of query block hints.

Statement hints apply to the entire SQL statement. ALL_ROWS is an example of a statement hint.

Comments within SQL statements are stored as part of the application code that executes the SQL statements.

Comments associated with individual schema or nonschema objects are stored in the data dictionary along with metadata on the objects themselves.

Comments Within SQL Statements

Comments can make your application easier for you to read and maintain. For example, you can include a comment in a statement that describes the purpose of the statement within your application. With the exception of hints, comments within SQL statements do not affect the statement execution. Refer to "Hints" on using this particular form of comment.

A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement in two ways:

Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.

Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.

Some of the tools used to enter SQL have additional restrictions. For example, if you are using SQL*Plus, by default you cannot have a blank line inside a multiline comment. For more information, refer to the documentation for the tool you use as an interface to the database.

A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.

Example These statements contain many comments:

Comments on Schema and Nonschema Objects

You can use the COMMENT command to associate a comment with a schema object (table, view, materialized view, operator, indextype, mining model) or a nonschema object (edition) using the COMMENT command. You can also create a comment on a column, which is part of a table schema object. Comments associated with schema and nonschema objects are stored in the data dictionary. Refer to COMMENT for a description of this form of comment.

Hints

Hints are comments in a SQL statement that pass instructions to the Oracle Database optimizer. The optimizer uses these hints to choose an execution plan for the statement, unless some condition exists that prevents the optimizer from doing so.

Hints were introduced in Oracle7, when users had little recourse if the optimizer generated suboptimal plans. Now Oracle provides a number of tools, including the SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to help you address performance problems that are not solved by the optimizer. Oracle strongly recommends that you use those tools rather than hints. The tools are far superior to hints, because when used on an ongoing basis, they provide fresh solutions as your data and database environment change.

Hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using the EXPLAIN PLAN statement. Changing database conditions as well as query performance enhancements in subsequent releases can have significant impact on how hints in your code affect performance.

The remainder of this section provides information on some commonly used hints. If you decide to use hints rather than the more advanced tuning tools, be aware that any short-term benefit resulting from the use of hints may not continue to result in improved performance over the long term.

A statement block can have only one comment containing hints, and that comment must follow the SELECT , UPDATE , INSERT , MERGE , or DELETE keyword.

The following syntax diagram shows hints contained in both styles of comments that Oracle supports within a statement block. The hint syntax must follow immediately after an INSERT , UPDATE , DELETE , SELECT , or MERGE keyword that begins the statement block.

The plus sign (+) causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after the comment delimiter. No space is permitted.

hint is one of the hints discussed in this section. The space between the plus sign and the hint is optional. If the comment contains multiple hints, then separate the hints by at least one space.

string is other commenting text that can be interspersed with the hints.

The --+ syntax requires that the entire comment be on a single line.

Oracle Database ignores hints and does not return an error under the following circumstances:

The hint contains misspellings or syntax errors. However, the database does consider other correctly specified hints in the same comment.

The comment containing the hint does not follow a DELETE , INSERT , MERGE , SELECT , or UPDATE keyword.

A combination of hints conflict with each other. However, the database does consider other hints in the same comment.

The database environment uses PL/SQL version 1, such as Forms version 3 triggers, Oracle Forms 4.5, and Oracle Reports 2.5.

A global hint refers to multiple query blocks. Refer to "Specifying Multiple Query Blocks in a Global Hint" for more information.

Specifying a Query Block in a Hint

You can specify an optional query block name in many hints to specify the query block to which the hint applies. This syntax lets you specify in the outer query a hint that applies to an inline view.

The syntax of the query block argument is of the form @ queryblock , where queryblock is an identifier that specifies a query block in the query. The queryblock identifier can either be system-generated or user-specified. When you specify a hint in the query block itself to which the hint applies, you omit the @queryblock syntax.

The system-generated identifier can be obtained by using EXPLAIN PLAN for the query. Pretransformation query block names can be determined by running EXPLAIN PLAN for the query using the NO_QUERY_TRANSFORMATION hint. See "NO_QUERY_TRANSFORMATION Hint".

The user-specified name can be set with the QB_NAME hint. See "QB_NAME Hint".

Specifying Global Hints

Many hints can apply both to specific tables or indexes and more globally to tables within a view or to columns that are part of indexes. The syntactic elements tablespec and indexspec define these global hints .

You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint. However, do not include the schema name with the table name within the hint, even if the schema name appears in the statement.

Specifying a global hint using the tablespec clause does not work for queries that use ANSI joins, because the optimizer generates additional views during parsing. Instead, specify @ queryblock to indicate the query block to which the hint applies.

When tablespec is followed by indexspec in the specification of a hint, a comma separating the table name and index name is permitted but not required. Commas are also permitted, but not required, to separate multiple occurrences of indexspec .

Specifying Multiple Query Blocks in a Global Hint

Oracle Database ignores global hints that refer to multiple query blocks. To avoid this issue, Oracle recommends that you specify the object alias in the hint instead of using tablespec and indexspec .

For example, consider the following view v and table t :

The following examples use the EXPLAIN PLAN statement, which enables you to display the execution plan and determine if a hint is honored or ignored. Refer to EXPLAIN PLAN for more information.

The LEADING hint is ignored in the following query because it refers to multiple query blocks, that is, the main query block containing table t and the view query block v :

The following SELECT statement returns the execution plan, which shows that the LEADING hint was ignored:

The LEADING hint is honored in the following query because it refers to object aliases, which can be found in the execution plan that was returned by the previous query:

The following SELECT statement returns the execution plan, which shows that the LEADING hint was honored:

When to use global hints and how Oracle interprets them

Using EXPLAIN PLAN to learn how the optimizer is executing a query

Hints by Functional Category

Table 3-21 lists the hints by functional category and contains cross-references to the syntax and semantics for each hint. An alphabetical reference of the hints follows the table.

Читайте также: