단일 레코드만 가져오는 경우에도 Oracle을 통해 예상되는 대량의 I/O

단일 레코드만 가져오는 경우에도 Oracle을 통해 예상되는 대량의 I/O

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▁a▁can▁be▁table▁using▁obviously다있▁accessed를 사용하여 접근할 수 있습니다.UNIQUE SCAN하지만 제가 그 테이블에 바보 같은 투영을 하는 순간, 전체 테이블(약 200만 줄)이 메모리에 읽힐 계획인 것처럼 보입니다.많은 I/O와 버퍼가 발생합니다.: " 리 쿼 투 제 사 다 니 라 집 큰 가 문 제 면 하 거 영 을 에 서 , 니 다 집 ▁when 라 사 ▁disappear s : ▁problem 큰 ▁the▁the ▁the

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

이 문제에 대한 해결 방법을 찾았습니다.하지만 저는 무엇이 잘못되고 있는지, 그리고 어떻게 하면 질문을 너무 바꾸지 않고 이 문제를 해결할 수 있는지에 대한 설명에 관심이 있습니다.

갱신하다

좋아요, 훨씬 더 많은 분석을 해본 결과, 실제로 문제가 있는 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이 쿼리의 앞부분...

실제로 v1.total을 선택하면 no_merge 보기가 트리거됩니다.

하위 선택에서 분석 기능을 사용하는 경우 하위 선택을 확인한 후 나머지와 결합해야 하므로, 이 경우 v1이 완전히 실행되고 전체 결과 집합이 결합되기 전에 "페치"됩니다.그리고 당신의 질문을 보면, 그것은 trxs에 대한 전체 스캔+ 분석 기능에 대한 정렬을 의미합니다.

v1.total을 주석 처리할 때 옵티마이저는 뷰를 병합하고 함수가 사용되지 않는 것으로 간주하여 해당 함수를 완전히 무시합니다.

갱신하다

저는 당신의 샘플을 사용했습니다. 여기 당신의 원래 쿼리와 당신의 솔루션을 위한 바이올린이 있습니다.계획 통계가 "Prod의 고유 스캔"에서 다르다는 것을 설명합니다.계획을 설명하면 select 절에서 쿼리 비용을 정확하게 추정할 수 없습니다. 행을 가져올 때 실행되는 방법을 보여주지만 실행 횟수를 알려주지 않으며 비용도 들지 않습니다.여기에 표시된 비용은 첫 번째 행을 가져오는 데 드는 비용이지만, 행을 가져올 때마다 쿼리가 실행되고 실행 계획에서는 몇 개를 가져올지 알 수 없습니다.이는 비용 및 IO 예측 차이를 설명합니다.

참고로, 선택 절의 쿼리는 크기를 조정하지 마십시오. 오버 쿼리가 유한하고 예측 가능하며 관리 가능한 수의 행을 반환할 것이라는 확신이 서지 않는 한 이러한 행은 사용하지 마십시오.그들은 나중에 와서 당신을 물 것입니다:)

y

언급URL : https://stackoverflow.com/questions/9671477/large-amount-of-projected-i-o-with-oracle-even-if-only-a-single-record-is-fetch