안녕하세요.
도무지 혼자서는 풀리지 않는 의문이 있어 질문 드립니다.

환경은 다음과 같습니다.
DB / Oracle 10g Ent. 64bit
OS / Windows2003 Server

쿼리에서 사용되는 개별 테이블 건수는 다음과 같습니다.
dataobj - 340,000 건 (약 80MB)
dataobjif - 7,400,000 건 (약 780MB)
dataobjpr - 9,000,000 건 (약 1.2GB)
건수가 좀 많죠;

그리고 특이사항은, SQL문의 구조를 변경할 수는 없습니다.
인덱스, 힌트(SQL Profile로 적용;;) 정도로만 튜닝이 가능한 상황입니다.


아래 구문이 돌아가는데 처음 실행시 약 3분 정도가 걸립니다.
캐시된 이후에는 바로 나오지만요;;

결과 건수는 2800건 정도 됩니다.
인스턴스 올리고 처음 순수 physical read 시에 3분이 소요되는데
건수 대비 소요 시간이 도저히 납득이 안되서요;


SELECT   o.OBID
  FROM   DATAOBJ o
INNER JOIN
DATAOBJIF i1 ON o.OBID = i1.OBJOBID
INNER JOIN
DATAOBJPR p11 ON p11.INTERFACEOBID = i1.OBID
INNER JOIN
DATAOBJIF i2 ON i2.OBJOBID = o.OBID
 WHERE    i1.INTERFACEDEFUID = 'ISPFDocumentRevision'
AND i1.TERMINATIONDATE = '9999/12/31-23:59:59:999'
AND i1.OBJOBID = i2.OBJOBID
AND p11.INTERFACEOBID = i1.OBID
AND p11.PROPERTYDEFUID = 'SPFRevState'
AND (p11.STRVALUE = 'e1WORKING' OR p11.STRVALUE = 'e1CURRENT')
AND p11.TERMINATIONDATE = '9999/12/31-23:59:59:999'
AND i2.INTERFACEDEFUID = 'ITESTDocumentRevision'
AND i2.TERMINATIONDATE = '9999/12/31-23:59:59:999'
AND (UPPER (o.OBJNAME) LIKE '%PRP%')
AND o.DOMAINUID IN ('ProcessD','SPFLLADesD','SPF','MechanicalD','LLAD','SPFAUTHORING','InstD')
AND o.TERMINATIONDATE = '9999/12/31-23:59:59:999'
AND (o.CONFIG IS NULL OR o.CONFIG = '' OR (o.CONFIG = 'PL_TEST'))


실행계획은 아래와 같이 아름답게(?) 잡히는데 말이죠...

------------------------------------------------------------------------------------------------------------
| Id  | Operation   | Name   | Rows  | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |    | 1 |   225 | 5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS   |    | 1 |   225 | 5   (0)| 00:00:01 |
|   2 |   NESTED LOOPS |    | 1 |   153 | 4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS     |    | 1 |   109 | 3   (0)| 00:00:01 |
|*  4 | TABLE ACCESS BY INDEX ROWID| DATAOBJPR | 1 |    57 | 2   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN   | IDX_DATAOBJPR_PROPDEFTERM |    26 |    | 1   (0)| 00:00:01 |
|*  6 | TABLE ACCESS BY INDEX ROWID| DATAOBJIF | 1 |    52 | 1   (0)| 00:00:01 |
|*  7 |   INDEX UNIQUE SCAN | PK_DATAOBJIF   | 1 |    | 1   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN    | IDX_DATAOBJIF | 1 |    44 | 1   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS BY INDEX ROWID  | DATAOBJ    | 1 |    72 | 1   (0)| 00:00:01 |
|* 10 |    INDEX UNIQUE SCAN    | PK_DATAOBJ     | 1 |    | 1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

능력자분들 도와주세요!