Почему я не могу заставить Oracle 11g использовать больше процессоров для одного запроса SQL
У меня есть несколько огромных запросов, которые выполняются на гигантских таблицах. Эти запросы кажутся узкими местами ЦП и работают в течение нескольких часов. Я понимаю, что в Oracle появилось много новых функций с 11g, выпуск 2, для внутренней параллелизации выполнения запроса. Однако независимо от того, какие подсказки я добавляю в запрос, я не могу использовать более 1 процессора в поле базы данных. У меня очень респектабельная машина Solaris с 8 процессорами, но каждый раз, когда я выполняю этот запрос, я просто довожу один процессор до 100%, а затем нахожусь там часами.
Подсказки, которые я пробовал:
SELECT /*+ PARALLEL */ ...
SELECT /*+ PARALLEL(5) */ ...
SELECT /*+ PARALLEL(10) */ ...
Ничего из этого, похоже, не сработало, если посмотреть на общее потребление процессора на коробке. Казалось, он всегда привязывает один процессор к 100%. К сожалению, даже объяснить план, кажется, нужно вечно бежать. Я постараюсь получить другие объяснения планов с разными подсказками и посмотрю, поможет ли это. Возможно ли, что некоторые запросы просто не параллельны, даже если их время выполнения в часах?!!? Основная таблица в этом запросе насчитывает 335 миллионов строк.
Текст SQL-запроса:
Системные параметры:
Редактировать:
Подробный план объяснения - без параллелизма:
Параметры системы, связанные с оптимизатором:
Дальнейшее редактирование: мы связались с Oracle, чтобы понять, почему EXPLAIN PLAN занимает более 2 часов. Мы пытаемся выполнить различные планы объяснения.
3 ответа
Самая важная вещь для понимания параллелизма Oracle - это то, что он сложен. Оптимизация параллелизма требует больших знаний Oracle, чтения руководств, проверки многих параметров, тестирования длительных запросов и большого скептицизма.
Задайте правильные вопросы
Параллельные проблемы действительно включают три разных вопроса:
- Сколько параллельных серверов было запрошено?
- Сколько параллельных серверов было выделено?
- Сколько параллельных серверов было осмысленно использовано?
Используйте лучшие инструменты
Переходите прямо к лучшему инструменту - SQL Monitoring с активными отчетами. Найдите свой SQL_ID и сгенерируйте отчет HTML: select dbms_sqltune.report_sql_monitor(sql_id => 'your_sql_id', type => 'active') from dual;, Это единственный способ узнать, сколько времени было потрачено на каждый шаг в плане выполнения. И он скажет вам, сколько параллелизма было эффективно использовано и где. Например:
Еще один хороший вариант type => 'text', В нем не так много информации, но на него быстрее взглянуть и легче поделиться.
Мониторинг SQL также включает запрошенный DOP и выделенный DOP:
Параллель в 100 линий select может работать красиво, но тогда все останавливается на одном шаге из-за не кэшированной последовательности. Вы можете часами смотреть на план объяснения, трассировку или отчет AWR и не видеть проблемы. Активный отчет делает медленные шаги почти тривиальными для поиска. Не тратьте время на гадание, в чем проблема.
Тем не менее, другие инструменты все еще необходимы. План объяснения, созданный с explain plan for ... а также select * from table(dbms_xplan.display); предоставит несколько ключевых частей информации. В частности, Notes В разделе может быть много причин, по которым запрос не запрашивал параллелизма.
Но ПОЧЕМУ я получил это количество параллельных серверов?
Соответствующая информация распространяется в нескольких различных руководствах, которые очень полезны, но иногда неточны или вводят в заблуждение. Есть много мифов и много плохих советов о параллелизме. И технология существенно меняется с каждым выпуском.
Когда вы соберете все авторитетные источники, список факторов, влияющих на количество параллельных серверов, будет поразительно большим. Приведенный ниже список упорядочен в соответствии с наиболее важными факторами:
- Межоперационный параллелизм Любой запрос, использующий сортировку или группировку, выделит в два раза больше параллельных серверов, чем DOP. Это, вероятно, ответственно за миф "Oracle выделяет столько параллельных серверов, сколько возможно!".
- Подсказка к запросу Предпочтительно подсказка на уровне оператора, например
/*+ parallel */или, возможно, подсказка уровня объекта, как/*+ noparallel(table1) */, Если определенный шаг плана выполняется последовательно, это обычно происходит из-за подсказок уровня объекта только для части запроса. - Рекурсивный SQL Некоторые операции могут выполняться параллельно, но могут быть эффективно сериализованы с помощью рекурсивного SQL. Например, некэшированная последовательность на большой вставке. Рекурсивный SQL, сгенерированный для анализа оператора, также будет последовательным; например динамические выборочные запросы.
- Изменить сессию
alter session [force|enable] parallel [query|dml|ddl];Обратите внимание, что параллельный DML по умолчанию отключен. - Столовая степень
- Индекс степени
- Индекс был дешевле Параллельные подсказки говорят только оптимизатору, чтобы он рассматривал полное сканирование таблицы с определенным DOP. Они на самом деле не навязывают параллелизм. Оптимизатор по-прежнему может использовать последовательный индексный доступ, если считает его более дешевым. (The
FULLподсказка может помочь решить эту проблему.) - Управление планом SQL Plan Baseline, схемы, профили, расширенное переписывание и переводчики SQL могут изменить степень параллелизма за вашей спиной. Проверьте раздел примечания плана.
- Только для редакций Enterprise и Personal Edition разрешают параллельные операции. За исключением пакета DBMS_PARALLEL_EXECUTE.
- PARALLEL_ADAPTIVE_MULTI_USER
- PARALLEL_AUTOMATIC_TUNING
- PARALLEL_DEGREE_LIMIT
- PARALLEL_DEGREE_POLICY
- PARALLEL_FORCE_LOCAL
- PARALLEL_INSTANCE_GROUP
- PARALLEL_IO_CAP_ENABLED
- PARALLEL_MAX_SERVERS Это верхний предел для всей системы. Здесь есть компромисс. Запуск слишком большого количества параллельных серверов одновременно вреден для системы. Но понижение запроса до последовательного может быть катастрофическим для некоторых запросов.
- PARALLEL_MIN_PERCENT
- PARALLEL_MIN_SERVERS
- PARALLEL_MIN_TIME_THRESHOLD
- PARALLEL_SERVERS_TARGET
- PARALLEL_THREADS_PER_CPU
- Количество узлов RAC Еще один множитель для DOP по умолчанию.
- CPU_COUNT Если используется DOP по умолчанию.
- RECOVERY_PARALLELISM
- FAST_START_PARALLEL_ROLLBACK
- Профиль
SESSIONS_PER_USERтакже ограничивает параллельные серверы. - Менеджер ресурсов
- Загрузка системы Если значение параметра rallel_adaptive_multi_user равно true. Вероятно, невозможно угадать, когда Oracle начнет регулировать.
- ПРОЦЕССЫ
- Ограничения параллельного DML Параллельный DML не будет работать, если в одном из следующих случаев:
- СОВМЕСТИМОСТЬ < 9.2 для внутрираздельных
- ВСТАВИТЬ ЗНАЧЕНИЯ, таблицы с триггерами
- копирование
- ссылочная целостность или удаление каскадных или отложенных ограничений целостности
- доступ к столбцу объекта
- неразделенная таблица с LOB
- внутрираздельный параллелизм с LOB
- распределенная транзакция
- кластерные таблицы
- временные таблицы
- Скалярные подзапросы не работают параллельно? Это в руководстве, и я хотел бы, чтобы это было правдой, но мои тесты показывают, что параллелизм работает здесь в 11g.
- ENQUEUE_RESOURCES Скрытый параметр в 10g, это больше актуально?
- Таблицы, организованные по индексу Не удается параллельно вставить путь к IOT? (Это все еще правда?)
- Требования к параллельной конвейерной функции должны использовать
CURSOR(?). СДЕЛАТЬ. - Функции должны быть PARALLEL_ENABLE
- Тип утверждения Более ранние версии ограничивали параллелизм в DML в зависимости от разбиения. Некоторые из текущих руководств по-прежнему включают это, но это, безусловно, уже не так.
- Количество разделов Только для объединений по разделам в более старых версиях.(?)
- Ошибки В частности, я видел много ошибок при разборе. Oracle выделит правильное количество параллельных серверов, но ничего не произойдет, поскольку все они ждут таких событий, как
cursor: pin s wait on x,
Этот список, конечно, не является полным и не включает в себя функции 12c. И это не решает проблемы операционной системы и оборудования. И это не отвечает на ужасно сложный вопрос: "Какова лучшая степень параллелизма?" (Краткий ответ: чем больше, тем лучше, но за счет других процессов.) Надеемся, что это, по крайней мере, дает представление о том, насколько сложными могут быть эти проблемы, и является хорошим местом для начала поиска.
Таким образом, Oracle использует здесь star_transformation, который, по сути, является средством объединения нескольких индексов растрового изображения, чтобы предоставить набор rowid для доступа к основной таблице. Использование rowid для доступа к таблице не является параллельной операцией, как полное сканирование таблицы (или предпочтительно сканирование подмножества разделов).
Star_transformation используется потому, что, по оценкам Oracle, сравнительно небольшое количество строк будет соответствовать всем этим условиям - было бы интересно узнать, верна или нет оценка в 15 миллионов строк. 15/335 составляет около 4,4% строк таблицы, поэтому, на первый взгляд, решение использовать метод, основанный на индексах, является уместным, если только строки не окажутся полностью случайным образом разбросанными по блокам, и вы не получите доступ к 30% блоки.
В любом случае, у меня есть ощущение, что если Oracle выберет star_transformation, то параллельная подсказка станет неактуальной.
Альтернативой star_transformation было бы использование полного сканирования таблицы на большой таблице, поэтому я думаю, что было бы полезно сначала намекнуть на полное сканирование таблицы и параллелизм. Вы также можете выполнить команду alter session, чтобы временно отключить преобразования звезд, но важная часть подсказки - точно сказать, какой метод доступа вы хотите, прежде чем погрузиться в параллелизм.
Примечание: кстати, временные таблицы, которые вы видите в плане, связаны со звездообразным преобразованием - когда таблицы измерений сначала сканируются для поиска подходящих строк, Oracle устраняет необходимость повторного поиска полезного подмножества во время соединения. фаза запроса путем сохранения их во временной таблице. Вы можете отключить это поведение, но, вероятно, здесь все хорошо.
Еще одно примечание: я еще раз взглянул на план объяснения - эти времена 00:27:44, 00:27:45 и т. Д. Являются кумулятивными. Они включают в себя, например, 00:27:43 для ТАБЛИЦЫ ДОСТУПА ПО ИНДЕКСУ ROWID /B28/EUDIQSBV. Если принять это во внимание, вы увидите, что хеш-объединения занимают несколько секунд каждое, а скачки производительности - это HASH GROUP BY около 3 часов. Он использует 4 ГБ временного пространства в соответствии с оценкой. Это, вероятно, серьезная утечка - следите за выполнением, используя V$SQL_WORKAREA, чтобы увидеть, сколько проходов он выполняет, но PQ является ответом на это, а также при наличии пропускной способности хранилища.
Операции PARALLEL могут принести значение в некоторых конкретных случаях, и если вы просто поместите подсказку PARALLEL в запрос, это не означает, что Oracle будет параллельны чему-либо adhoc. Пожалуйста, смотрите пример ниже:
SQL> select * from t
2 where id = 14
3 /
Execution plan
----------------------------------------------------------
Plan hash value: 1859958591
--------------------------------------------------------------------------------
----------------------------
| Id | Operation | Name | Rows | Byt
es | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT | | 5210 | 21
67K| 478 (1)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 5210 | 21
67K| 478 (1)| 00:00:06 |
|* 2 | INDEX RANGE SCAN | T_FK_I | 5210 |
| 20 (0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=14)
Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
10607 consistent gets
0 physical reads
0 redo size
3734464 bytes sent via SQL*Net to client
994894 bytes received via SQL*Net from client
7256 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5295 rows processed
SQL> select /*+ parallel(4) */ * from t
2 where id = 14
3 /
Execution plan
----------------------------------------------------------
Plan hash value: 1859958591
--------------------------------------------------------------------------------
----------------------------
| Id | Operation | Name | Rows | Byt
es | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT | | 5210 | 21
67K| 478 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 5210 | 21
67K| 478 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_FK_I | 5210 |
| 20 (0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=14)
Note
-----
- Degree of Parallelism is 1 because of hint
Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
10607 consistent gets
0 physical reads
0 redo size
3734464 bytes sent via SQL*Net to client
994894 bytes received via SQL*Net from client
7256 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5295 rows processed
SQL> select /*+ full(t) */ * from t
2 where id = 14
3 /
Execution plan
----------------------------------------------------------
Plan hash value: 565085413
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 5210 | 2167K| 3858 (1)| 0
0:00:47 |
|* 1 | TABLE ACCESS FULL| T | 5210 | 2167K| 3858 (1)| 0
0:00:47 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=14)
Statistics
----------------------------------------------------------
17 recursive calls
1 db block gets
19468 consistent gets
507 physical reads
0 redo size
3734334 bytes sent via SQL*Net to client
994894 bytes received via SQL*Net from client
7256 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5295 rows processed
SQL> select /*+ parallel(4) full(t) */ * from t
2 where id = 14
3 /
Execution plan
----------------------------------------------------------
Plan hash value: 298470658
--------------------------------------------------------------------------------
-----------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
-----------------------------------------
| 0 | SELECT STATEMENT | | 5210 | 2167K| 1070 (1)
| 00:00:03 | | | |
| 1 | PX COORDINATOR | | | |
| | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 5210 | 2167K| 1070 (1)
| 00:00:03 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 5210 | 2167K| 1070 (1)
| 00:00:03 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| T | 5210 | 2167K| 1070 (1)
| 00:00:03 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
-----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"=14)
Note
-----
- Degree of Parallelism is 4 because of hint
Statistics
----------------------------------------------------------
12 recursive calls
65 db block gets
17262 consistent gets
14401 physical reads
0 redo size
3736075 bytes sent via SQL*Net to client
994894 bytes received via SQL*Net from client
7256 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5295 rows processed
Как вы можете видеть, когда я только добавил подсказку PARALLEL в запрос, я все еще получил доступ к индексу. И вы должны сначала проанализировать свой план выполнения и статистику и получить ответ на вопрос, насколько ваш текущий план настолько плох, как вы думаете? Может быть, это самый лучший. Как вы также можете видеть, параллельное полное сканирование выполняло значительно больше логических операций чтения (включая чтение с диска), поэтому, если степень параллелизма не имеет значения, вы можете получить снижение производительности вместо любого выигрыша. И последнее по порядку, но не по значению - вы рассматривали такой принцип как раздел? Это может быть очень полезно, если вам нужно получить только небольшую часть данных в соответствии со стабильными и предсказуемыми критериями.