Индексированный запрос Oracle SQL 100% использование процессора
Я выполняю относительно простой запрос
SELECT * FROM confirm_v c
JOIN person p ON c.created_by=p.id
INNER JOIN invoice_confirm ic ON ic.confirm_id=c.id
WHERE c.id = (SELECT id FROM
(SELECT c2.id FROM confirm c2
JOIN invoice_confirm ic2 ON ic2.confirm_id=c2.id
WHERE ic2.invoice_id=11954081
AND c2.previous=0
AND c2.canceled=0
AND c2.confirm_type='INVOICE'
ORDER BY c2.id)
WHERE rownum=1);
что приводит к 100% загрузке процессора rdb. confirm_type - это varchar2(50 char), остальные - number(10), если это что-то значит.
Таблицы invoice_confirm и confirm покрыты индексами, и в плане выполнения этого запроса нет полных сканирований таблиц.
Этот запрос выполняется нечасто, но составляет почти 100% от общего использования процессора. Любые идеи приветствуются.
Обновлено: Текст объяснения плана запроса.
EXPLAIN PLAN FOR ...
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
Plan hash value: 1705859247
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 69 | 10 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 69 | 10 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 57 | 7 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 30 | 5 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | CONFIRM | 1 | 24 | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_CONFIRM | 1 | | 2 (0)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | VIEW | | 4 | 52 | 27 (4)| 00:00:01 |
|* 9 | SORT ORDER BY STOPKEY | | 4 | 132 | 27 (4)| 00:00:01 |
| 10 | NESTED LOOPS | | 4 | 132 | 26 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 11 | 132 | 26 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED| INVOICE_CONFIRM | 3 | 36 | 4 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | FKI_INVOICE_CONFIRM_INVOICE | 2 | | 3 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_CONFIRM | 1 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | CONFIRM | 1 | 21 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | FKI_INVOICE_CONFIRM_CONFIRM | 1 | 6 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | PERSON | 1 | 27 | 2 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_KASUTAJA | 1 | | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | FKI_INVOICE_CONFIRM_CONFIRM | 1 | | 2 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | INVOICE_CONFIRM | 1 | 12 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("CONFIRM"."ID"= (SELECT "ID" FROM (SELECT "C2"."ID" "ID" FROM "INVOICE_CONFIRM" "IC2","CONFIRM" "C2"
WHERE "IC2"."CONFIRM_ID" = "C2"."ID" AND "C2"."CANCELED"=0 AND "C2"."PREVIOUS"=0 AND "C2"."CONFIRM_TYPE"='INVOICE' AND
"IC2"."INVOICE_ID"=11954081 ORDER BY "C2"."ID") "from$_subquery$_006" WHERE ROWNUM=1))
7 - filter(ROWNUM=1)
9 - filter(ROWNUM=1)
13 - access("IC2"."INVOICE_ID"=11954081)
14 - access("IC2"."CONFIRM_ID" = "C2"."ID")
15 - filter("C2"."CANCELED"=0 AND "C2"."PREVIOUS"=0 AND "C2"."CONFIRM_TYPE"='INVOICE')
16 - access("IC"."CONFIRM_ID" = "CONFIRM"."ID")
18 - access("CONFIRM"."CREATED_BY" = "P"."ID")
19 - access("IC"."CONFIRM_ID" = "CONFIRM"."ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 2 Sql Plan Directives used for this statement
Я сомневаюсь, что нет полного сканирования таблицы, чтобы увидеть все эти !=. Невозможно изменить их, перечислив все возможности, кроме тех, которые вы ищете?
план: imgur.com/a/4xsh4Rv
Индексы по invoice_confirm.invoice_id, все идентификаторы. Нет указателя на те, подтверждающие. Предыдущие и т. д.
План неполный. Пожалуйста, включите примечания доступ и фильтр.
Не могли бы вы сгенерировать план в текстовом формате с помощью этих двух команд: EXPLAIN PLAN FOR your_query, затем SELECT * FROM TABLE( dbms_xplan.display ). Эта текстовая версия включает предикатную информацию (напечатана внизу), в плане, который вы показали, такой информации нет.
Я отредактировал вопрос и добавил вывод для плана объяснения.
Я бы сказал, что вы выполняете сортировку (операцию с интенсивным использованием ЦП) для каждой записи confirm_v, которую вы включаете в набор результатов. Если количество для сортировки велико, это будет загружать процессор. Кстати, order by выполняет полную сортировку за линейно-логарифмическое время, а min выполняет только полное сканирование набора за линейное время; вы могли бы рассмотреть возможность его использования. Чтобы проверить, так ли это, попробуйте закомментировать часть order by в подзапросе и посмотреть, работает ли она быстрее.
Ответы 1
Соберите статистику оптимизатора по соответствующим таблицам и выясните, почему статистика отсутствовала.
begin
dbms_stats.gather_table_stats(user, 'CONFIRM');
dbms_stats.gather_table_stats(user, 'INVOICE_CONFIRM');
dbms_stats.gather_table_stats(user, 'PERSON');
end;
/
Статистика оптимизатора критически важна для Oracle для достижения хорошей производительности. Примечание dynamic statistics used: dynamic sampling (level=2) подразумевает, что есть таблицы с отсутствующей статистикой оптимизатора. Этого никогда не должно происходить, если таблицы не были созданы в течение последнего дня.
Oracle автоматически собирает устаревшую и отсутствующую статистику. Проверьте, выполняется ли задание с этим запросом. Если последних строк нет, попросите администратора базы данных повторно включить задачу.
select *
from dba_optstat_operations
where operation like '%auto%'
order by start_time desc;
Автозадача достаточно хороша для большинства таблиц. Но если есть большой пакетный процесс, который обновляет много строк, статистику следует собирать вручную, как только задание будет завершено.
Какие у вас индексы? Сколько строк (приблизительная оценка) у всех четырех таблиц? Также опубликуйте, пожалуйста, план выполнения.