There are 2 "same" queries with same optimize environment, similar size of data sets, but the execution plans are different.
Query one:
SELECT A.BUSINESS_UNIT ,
A.VENDOR_ID ,
A.ADDR_SEQ_NUM_SHIP ,
B.COUNTRY ,
B.STATE
FROM A ,
B ,
C
WHERE A.PROCESS_INSTANCE = 132
AND C.PROCESS_INSTANCE = 132
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.VOUCHER_ID = C.VOUCHER_ID
AND
(
A.COUNTRY_SHIP_FROM = ' '
OR A.STATE_SHIP_FROM = ' '
)
AND B.SETID = A.VENDOR_SETID
AND B.VENDOR_ID = A.VENDOR_ID
AND B.ADDRESS_SEQ_NUM = A.ADDR_SEQ_NUM_SHIP
AND C.VAT_ENTITY <> ' '
AND B.EFF_STATUS = 'A'
AND B.EFFDT =
(
SELECT MAX(BB.EFFDT)
FROM B BB
WHERE BB.SETID = B.SETID
AND BB.VENDOR_ID = B.VENDOR_ID
AND BB.ADDRESS_SEQ_NUM = B.ADDRESS_SEQ_NUM
AND BB.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')
);
And the plan is:
----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 82569 (100)|
| 1 | FILTER | | | | |
| 2 | HASH JOIN | | 28643 | 3524K| 3899 (1)|
| 3 | TABLE ACCESS BY INDEX ROWID | C| 17308 | 591K| 0 (0)|
| 4 | INDEX RANGE SCAN | C| 1 | | 0 (0)|
| 5 | HASH JOIN | | 28641 | 2545K| 3898 (1)|
| 6 | TABLE ACCESS BY INDEX ROWID| A| 28641 | 1538K| 0 (0)|
| 7 | INDEX RANGE SCAN | A| 1 | | 0 (0)|
| 8 | TABLE ACCESS FULL | B| 369K| 12M| 3896 (1)|
| 9 | SORT AGGREGATE | | 1 | 27 | |
| 10 | INDEX RANGE SCAN | B| 1 | 27 | 3 (0)|
----------------------------------------------------------------------------------------
Query two:
SELECT A.BUSINESS_UNIT ,
A.VENDOR_ID ,
A.ADDR_SEQ_NUM_SHIP ,
B.COUNTRY ,
B.STATE
FROM A ,
B ,
C
WHERE A.PROCESS_INSTANCE = 231
AND C.PROCESS_INSTANCE = 231
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.VOUCHER_ID = C.VOUCHER_ID
AND
(
A.COUNTRY_SHIP_FROM = ' '
OR A.STATE_SHIP_FROM = ' '
)
AND B.SETID = A.VENDOR_SETID
AND B.VENDOR_ID = A.VENDOR_ID
AND B.ADDRESS_SEQ_NUM = A.ADDR_SEQ_NUM_SHIP
AND C.VAT_ENTITY <> ' '
AND B.EFF_STATUS = 'A'
AND B.EFFDT =
(
SELECT MAX(BB.EFFDT)
FROM B BB
WHERE BB.SETID = B.SETID
AND BB.VENDOR_ID = B.VENDOR_ID
AND BB.ADDRESS_SEQ_NUM = B.ADDRESS_SEQ_NUM
AND BB.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')
);
And the plan is:
----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 95182 (100)|
| 1 | FILTER | | | | |
| 2 | HASH JOIN | | 32817 | 4038K| 3986 (3)|
| 3 | TABLE ACCESS BY INDEX ROWID | C| 16544 | 565K| 0 (0)|
| 4 | INDEX RANGE SCAN | C| 1 | | 0 (0)|
| 5 | NESTED LOOPS | | 32817 | 2916K| 3985 (3)|
| 6 | TABLE ACCESS FULL | B| 369K| 12M| 3896 (1)|
| 7 | TABLE ACCESS BY INDEX ROWID| A| 1 | 55 | 0 (0)|
| 8 | INDEX RANGE SCAN | A| 1 | | 0 (0)|
| 9 | SORT AGGREGATE | | 1 | 27 | |
| 10 | INDEX RANGE SCAN | B| 1 | 27 | 3 (0)|
----------------------------------------------------------------------------------------
I did check the optimizer env values, all are the same. and checked the OS level, everything are same.
Table B are the same in both query.
In the first query:
TABLE A is about 17308 rows
TABLE C is about 27273 rows
In the 2nd query:
TABLE A is about 12659 rows
TABLE C is about 29829 rows
The 1st one took less than 2 seconds while the 2nd one took more than 6 hours.
Tried to reproduce the 2nd plan by adding hint "/*+ cardinality(A 29829) cardinality(C 12659) */", but cannot.
Any idea why 2nd one using NL, not HASH?
Thanks,
Edited by: Oracle Explorer on Jan 3, 2013 11:16 AM
Edited by: Oracle Explorer on Jan 3, 2013 11:24 AM
Edited by: Oracle Explorer on Jan 3, 2013 11:26 AM