我经常在我的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的主键.显然,可以使用独特的扫描程序来访问表格,但一旦我在表上有一些愚蠢的投影,就好像整个表格(大约200万行)都被计划在内存中读取.我得到了很多I / O和缓冲区.当我从更大的查询中删除投影时,问题消失了：
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,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表时,我可以在计划中重现过多的行/字节.
我找到了一个workaround for this issue.但是我真的很感兴趣的是解释出了什么问题,以及如何可以纠正这个问题,而不用改变查询太多
更新
好的,经过更多的分析,我不得不说,实际有问题的I / O是由于错误的索引被用于其他地方.不幸的是,在总体统计(或执行计划)中预测的这个数字还不够好.
就这个问题而言,我仍然对执行计划中的预计I / O感到好奇,因为这似乎混淆了我们的DBA(和我)一次又一次.有时,它真的是I / O问题的根源…
解决方法
可能有趣的是,我已经检查了各种场景,包括具体的具体方案.在这种情况下,将样例查询重新排序为这样可以解决问题：
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会在其执行计划中投入太多的I / O,如果我在此查询中加入了prod …
