sql – 使用Oracle的大量预计I / O,即使仅获取单个记录

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 …

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!