Я часто сталкиваюсь со следующей ситуацией в моих планах исполнения 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 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
.
Я нашел обходной путь для этой проблемы. Но мне действительно интересно объяснить, что происходит не так, и как я могу исправить эту проблему, не меняя слишком много запросов.
Update
ОК, после гораздо большего анализа, я должен сказать, что фактический проблемный ввод-вывод был вызван неправильным индексом, используемым где-то совсем другим. К сожалению, это было недостаточно хорошо прогнозировать в общей статистике (или в плане выполнения), чтобы заметить.
Что касается этого вопроса, я все равно интересуюсь прогнозируемым вводом-выводом в плане выполнения, поскольку это, похоже, снова и снова путает наших администраторов баз данных (и меня). И иногда, это действительно является источником проблем ввода-вывода...