Большое количество прогнозируемых операций ввода-вывода с Oracle, даже если выбирается только одна запись
Я часто сталкиваюсь со следующей ситуацией в моих планах выполнения Oracle:
Operation | Object | Order | Rows | Bytes | Projection
----------------------------+---------+-------+------+-------+-------------
TABLE ACCESS BY INDEX ROWID | PROD | 7 | 2M | 28M | PROD.VALUE
INDEX UNIQUE SCAN | PROD_PK | 6 | 1 | | PROD.ROWID
Это выдержка из более крупного плана выполнения. По сути, я получаю доступ (присоединяюсь) к таблице, используя ее первичный ключ. Как правило, есть другая таблица ACCO с ACCO.PROD_ID = PROD.ID, где PROD_PK является первичным ключом на PROD.ID, Очевидно, что к таблице можно получить доступ, используя UNIQUE SCAN, но как только у меня появляется какая-то глупая проекция на эту таблицу, кажется, что всю таблицу (около 2 миллионов строк) планируется читать в памяти. Я получаю много ввода-вывода и буфер получает. Когда я удаляю проекцию из большего запроса, проблема исчезает:
Operation | Object | Order | Rows | Bytes | Projection
----------------------------+---------+-------+------+-------+-------------
TABLE ACCESS BY INDEX ROWID | PROD | 7 | 1 | 8 | PROD.ID
INDEX UNIQUE SCAN | PROD_PK | 6 | 1 | | PROD.ROWID
Я не понимаю это поведение. Какие могут быть причины для этого? Обратите внимание, я не могу опубликовать полный запрос. Это довольно сложно и требует много вычислений. Модель, однако, часто одинакова.
ОБНОВЛЕНИЕ: я вычеркнул, чтобы свести мою довольно сложную установку к простой симуляции, которая в обоих случаях выдает схожий план выполнения (при проецировании PROD.VALUE или когда оставляешь это в стороне):
Создайте следующую базу данных:
-- products have a value
create table prod as
select level as id, 10 as value from dual
connect by level < 100000;
alter table prod add constraint prod_pk primary key (id);
-- some products are accounts
create table acco as
select level as id, level as prod_id from dual
connect by level < 50000;
alter table acco
add constraint acco_pk primary key (id);
alter table acco
add constraint acco_prod_fk foreign key (prod_id) references prod (id);
-- accounts have transactions with values
create table trxs as
select level as id, mod(level, 10) + 1 as acco_id, mod(level, 17) + 1 as value
from dual connect by level < 100000;
alter table trxs
add constraint trxs_pk primary key (id);
alter table trxs
add constraint trxs_acco_fk foreign key (acco_id) references acco (id);
create index acco_i on acco(prod_id);
create index trxs_i on trxs(acco_id);
alter table acco modify prod_id not null;
alter table trxs modify acco_id not null;
Запустите следующий запрос
select v2.*
from (
select
-- This calculates the balance for every transaction as a
-- running total, subtracting trxs.value from the product's value
--
-- This is the "projection" I mentioned that causes I/O. Leaving it
-- away (setting it to 0), would improve the execution plan
prod.value - v1.total balance,
acco.id acco_id
from (
select
acco_id,
sum(value) over (partition by acco_id
order by id
rows between unbounded preceding
and current row) total
from trxs
) v1
join acco on v1.acco_id = acco.id
join prod on acco.prod_id = prod.id
) v2
-- This is the single-row access predicate. From here, it is
-- clear that there can only be 1 acco and 1 prod
where v2.acco_id = 1;
анализировать
При анализе планов выполнения для вышеупомянутого запроса (с или без prod.value проекции), я могу воспроизвести чрезмерное количество строк / байтов в плане при доступе к prod Таблица.
Я нашел обходной путь для этой проблемы. Но я действительно заинтересован в объяснении того, что происходит не так, и как я могу исправить эту проблему, не слишком меняя запрос
Обновить
Хорошо, после гораздо большего анализа я должен сказать, что реальный проблемный ввод-вывод был вызван неправильным использованием индекса где-то совсем в другом месте. К сожалению, это не было достаточно хорошо спроектировано в общей статистике (или в плане выполнения), чтобы заметить.
Что касается этого вопроса, мне все еще интересно узнать о предполагаемом вводе / выводе в плане выполнения, поскольку это, кажется, смущает нашего администратора баз данных (и меня) снова и снова. И иногда это действительно источник проблем ввода-вывода...
2 ответа
Может быть интересно отметить, что я проверил различные сценарии, в том числе конкретное решение для конкретного примера. Перефразируйте пример запроса так, чтобы это решило проблему в этом случае:
select
-- Explicitly project value in a nested loop. This seems to be much cheaper
-- in this specific case
(select value from prod where id = v2.prod_id) - v2.balance,
v2.acco_id
from (
select
-- Now, balance is only a running total, not the running total
-- added to PROD.VALUE
v1.total balance,
acco.id acco_id,
acco.prod_id prod_id
from (
select
acco_id,
sum(value) over (partition by acco_id
order by id
rows between unbounded preceding
and current row) total
from trxs
) v1
-- The JOIN of PROD is no longer needed
join acco on v1.acco_id = acco.id
) v2
where v2.acco_id = 1;
Но я до сих пор не понимаю, почему Oracle спроектирует так много операций ввода-вывода в своем плане выполнения, если я присоединюсь prod ранее в этом запросе...
На самом деле, при выборе v1.total вы запускаете представление no_merge.
При использовании аналитической функции в подвыборках, подвыборы должны быть разрешены до объединения с остальными, поэтому в этом случае v1 выполняется полностью, и весь набор результатов "выбирается", прежде чем он присоединяется. И, глядя на ваш запрос, это означает, что полное сканирование на trxs+ сортировка для аналитической функции
При комментировании v1.total оптимизатор объединяет представление и полностью игнорирует функцию, так как видит, что она не используется.
Обновить
Я использовал ваш пример, вот скрипка для вашего исходного запроса и для вашего решения. Объясните планы статистики, отличаются "уникальным сканированием Prod". План объяснения не может точно оценить стоимость запросов в предложении select, он показывает, как он будет выполняться при извлечении строки, но не сообщает, сколько раз он будет выполнен, и не стоить это Цена, которую вы видите там, - это только стоимость извлечения 1-й строки, но запрос будет выполняться каждый раз, когда вы извлекаете строку, и план выполнения не знает, сколько вы получите. Это должно объяснить разницу в стоимости и прогнозировании IO.
Напомним, что запросы в предложении Select не масштабируются, если только вы не уверены, что запрос over вернет конечное, предсказуемое и управляемое количество строк, избегайте их использования. Они придут и укусят вас позже:)
Y