Oracle 11.2 имеет задержку 2 секунды для простого SQL в случайное время
Простое объединение таблиц выполняется обычно за 0,0XX секунды, а иногда и за 2,0XX секунды (в соответствии с выполнением PL/SQL Developer SQL). Это происходит при запуске из SQL Plus.
Если я запускаю SQL 10 раз, 8 раз он работает нормально и 2 раза за 2+ секунды.
Это чистая установка Oracle 11.2.0.4 для Linux x86_64 на Centos 7. Я установил рекомендованные Oracle исправления:
- Патч 19769489 - Обновление 11.2.0.4.5 набора исправлений базы данных (включает CPUJan2015)
- Исправление 19877440 - Компонент Oracle JavaVM 11.2.0.4.2 БД базы данных (январь 2015 г.)
Без изменений после исправления.
2 таблицы имеют: LNK_PACK_REP: 13 строк ПАКЕТЫ: 6 строк
В SQL Plus я включил всю статистику и запускал SQL несколько раз. Время от времени изменяется только время от 0,1 до 2,1. Никакая другая статистика не изменяется, если я сравниваю пробег за 0,1 секунды с пробегом за 2,1 секунды. Сервер имеет 16 Гб оперативной памяти и 8 процессорных ядер. Загрузка сервера составляет менее 0,1 (на данный момент ни один пользователь не использует сервер).
Выход:
SQL> выбрать PACKAGE_ID, id, имя_пакета из LNK_PACK_REP LNKPR INNER ВСТУПИТЬ ПАКЕТЫ P ON LNKPR.PACKAGE_ID = P.ID;
PACKAGE_ID ID PACKAGE_NAME
3 3 RAPOARTE
3 3 RAPOARTE
121 121 VANZARI
121 121 VANZARI
121 121 VANZARI
2 2 PACHETE
2 2 PACHETE
1 1 DEPARTAMENTE
1 1 DEPARTAMENTE
81 81 ROLURI
81 81 ROLURI
PACKAGE_ID ID PACKAGE_NAME
101 101 UTILIZATORI
101 101 UTILIZATORI
13 строк выбрано.
Прошло: 00: 00: 02.01
План выполнения
Значение хэша плана: 2671988802
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 351 | 3 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 13 | 351 | 3 (0)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 13 | 351 | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 6 | 84 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | 6 | 84 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 6 | 84 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 7 | TABLE ACCESS FULL| PACKAGES | 6 | 84 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 9 | PX RECEIVE | | 13 | 169 | 1 (0)| 00:00:01 | Q1,02 | PCWP | |
| 10 | PX SEND HASH | :TQ10000 | 13 | 169 | 1 (0)| 00:00:01 | | S->P | HASH |
| 11 | INDEX FULL SCAN | UNQ_PACK_REP | 13 | 169 | 1 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------------
Информация предиката (идентифицируется по идентификатору операции):
3 - доступ ("LNKPR"."PACKAGE_ID"="P"."ID")
Заметка
- динамическая выборка, используемая для этого утверждения (уровень =2)
Статистика
24 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
923 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
13 rows processed
Структура таблицы 1:
-- Create table
create table PACKAGES
(
id NUMBER(3) not null,
package_name VARCHAR2(150),
position NUMBER(3),
activ NUMBER(1)
)
tablespace UM
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table PACKAGES
add constraint PACKAGES_ID primary key (ID)
using index
tablespace UM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index PACKAGES_ACTIV on PACKAGES (ID, ACTIV)
tablespace UM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
Структура таблицы 2:
-- Create table
create table LNK_PACK_REP
(
package_id NUMBER(3) not null,
report_id NUMBER(3) not null
)
tablespace UM
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table LNK_PACK_REP
add constraint UNQ_PACK_REP primary key (PACKAGE_ID, REPORT_ID)
using index
tablespace UM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index LNK_PACK_REP_REPORT_ID on LNK_PACK_REP (REPORT_ID)
tablespace UM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
В Oracle Enterprise Manager в SQL Monitor я вижу SQL, который запускается несколько раз. У всех прогонов есть "Время базы данных" 0,0 с (менее 10 микросекунд, если я наведу на список) и "Длительность" 0,0 с для обычного прогона и 2,0 с для толщины с задержкой. Если я пойду в Monitored SQL Executions для этого запуска 2.0s, у меня будет:
- Продолжительность: 2.0 с
- Время базы данных: 0.0 с
- PL / SQL и Java: 0,0
- Ожидание активности: % (здесь нет номера)
- Буфер получает: 10
- IO запросов: 0
- IO Байт: 0
- Вызовов: 2
- Параллельно: 4
Эти числа соответствуют быстрому прогону, за исключением того, что Duration даже меньше, чем Time Database (10 163 микросекунд Database Time и 3748 микросекунд Duration), и отображаются как 0.0 с, если мышь не зависает.
Я не знаю, что еще проверить.
2 ответа
Параллельные запросы не могут быть существенно настроены в течение нескольких секунд. Они предназначены для запросов, которые обрабатывают большие объемы данных в течение длительного времени.
Лучший способ оптимизировать параллельные операторы с небольшими наборами данных - это временно отключить их:
alter system set parallel_max_servers=0;
(Это хороший пример преимуществ разработки на рабочих станциях, а не на серверах. На сервере это изменение затрагивает всех, и у вас, вероятно, даже нет привилегий для запуска команды.)
Запрос может быть простым, но параллелизм добавляет много сложности в фоновом режиме.
Трудно сказать точно, почему это медленнее. Если у вас есть отчет по мониторингу SQL, могут помочь события ожидания. Но даже эти числа могут быть просто общими ожиданиями, такими как "CPU". Параллельные запросы имеют много накладных расходов, ожидая ресурсоемких, длительных запросов. Вот некоторые типы накладных расходов, которые могут объяснить, откуда берутся эти 2 секунды:
- Динамическая выборка - параллелизм может автоматически вызывать динамическую выборку, которая считывает данные из таблиц. Хотя
dynamic sampling used for this statement (level=2)может просто означать отсутствие статистики оптимизатора. - Запуск потокаОС - оператор SQL, вероятно, должен запустить 8 дополнительных потоков ОС и подготовить большой объем памяти для хранения всех промежуточных данных. Возможно, параметр
PARALLEL_MIN_SERVERSможет помочь предотвратить некоторое время, используемое для создания этих потоков. - Дополнительный мониторинг - Параллельные операторы контролируются автоматически, что требует рекурсивных SELECT и INSERT.
- Кэширование. Параллельные запросы часто читаются непосредственно с диска и пропускают чтение и запись в буферный кеш. Правила кеширования данных сложны и недокументированы.
- Понижение - найти правильную степень параллелизма сложно. Например, я составил список из 39 факторов, которые влияют на DOP. Возможно, что один из них вызывает понижение рейтинга, делая одни запросы быстрыми, а другие медленными.
И есть, вероятно, десятки других типов накладных расходов, о которых я не могу думать. Параллелизм отлично подходит для массового улучшения времени выполнения огромных операций. Но это не работает хорошо для крошечных запросов.
Задержка вызвана параллелизмом, как предложили Дэвид Олдридж и Джон Хеллер, но я не согласен с предложенным Джоном Хеллером решением отключить параллелизм для всех запросов (на системном уровне). Вы можете поиграть с "alter session", чтобы отключить его и снова включить перед выполнением больших запросов. Точная причина задержки до сих пор неизвестна, так как запрос завершается быстро в 8 из 10 запусков, и я ожидал бы быстрое выполнение 10/10.