I don't think the reply I sent last night made it to the list.
We eventually traced it to the behavior of _unnest_subquery parameter. Setting it to false solved the problem. This was first reported in 817 and supposed to be fixed in 8173 and 901 code base. Looks like the lid on the "jar of bugs" is leaking ... and it made it into 920 code as well.
The biggest problem we found that the explain plan shows that it doesn't even look at a major portion of the sql statement when it fails. 10053 trace shows that the optimizer looking at all tables involved and checking for different paths, but the explain plan is not reporting all the tables involved.
Here is an example ...
-------------- this doesn't work ---------------------
select SUM(a.csed_dollars)
FROM client_supplied_ep_dollars a
WHERE a.csed_pob_id = 213841
AND a.csed_date = (SELECT MAX(b.csed_date)
FROM client_supplied_ep_dollars b
WHERE b.csed_ep_number = a.csed_ep_number
AND b.csed_pob_id = a.csed_pob_id)
AND EXISTS (SELECT 'x'
FROM v_log_master l,
episode_airings e,
units u
WHERE u.ut_ea_ep_number = a.csed_ep_number
AND u.ut_pob_id = a.csed_pob_id
AND u.ut_disposition_ind IS NULL
AND e.life_cycle_status > 2
AND e.ep_number = u.ut_ea_ep_number
AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/yyyy') and
to_date('03/31/2002','mm/dd/yyyy')
-- AND e.est_dt BETWEEN m_start_date AND m_end_date
AND l.log_date = e.est_dt
AND l.log_network = NVL(e.act_log_network, e.log_network)
AND l.log_no = NVL(e.act_log_number,e.log_number)
AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status = 9
OR
(NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date IS NULL)))
SQL> /
SUM(A.CSED_DOLLARS)
-------------------
1230000
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=41)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=13 Card=1 Bytes=41)
3 2 FILTER
4 3 SORT (GROUP BY) (Cost=13 Card=1 Bytes=42)
5 4 HASH JOIN (Cost=11 Card=1 Bytes=42)
6 5 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=12 Bytes=192)
7 5 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_SUPPLIED_EP_DOLLARS' (Cost=8 Card=12 Bytes=312)
8 7 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=1)
SQL> spool off
------------------------ end --------------------------------------------
And
--------------------------- this works ----------------------------------
select SUM(a.csed_dollars)
FROM client_supplied_ep_dollars a
WHERE a.csed_pob_id = 213841
AND a.csed_date = (SELECT MAX(b.csed_date)
FROM client_supplied_ep_dollars b
WHERE b.csed_ep_number = a.csed_ep_number
AND b.csed_pob_id = a.csed_pob_id
group by 1) -- this group by fixes the query ...
AND EXISTS (SELECT 'x'
FROM v_log_master l,
episode_airings e,
units u
WHERE u.ut_ea_ep_number = a.csed_ep_number
AND u.ut_pob_id = a.csed_pob_id
AND u.ut_disposition_ind IS NULL
AND e.life_cycle_status > 2
AND e.ep_number = u.ut_ea_ep_number
AND e.est_dt BETWEEN to_date('03/01/2002','mm/dd/yyyy') and
to_date('03/31/2002','mm/dd/yyyy')
-- AND e.est_dt BETWEEN m_start_date AND m_end_date
AND l.log_date = e.est_dt
AND l.log_network = NVL(e.act_log_network, e.log_network)
AND l.log_no = NVL(e.act_log_number,e.log_number)
AND (NVL(e.did_not_air_ind,'N') = 'N' OR e.life_cycle_status = 9
OR
(NVL(e.did_not_air_ind,'N') = 'Y' AND l.master_log_date IS NULL)))
SQL> /
SUM(A.CSED_DOLLARS)
-------------------
150000
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=19)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_SUPPLIED_EP_DOLLARS' (Cost=3 Card=1 Bytes=19)
3 2 INDEX (RANGE SCAN) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=1)
4 3 SORT (GROUP BY NOSORT) (Cost=2 Card=1 Bytes=16)
5 4 FIRST ROW (Cost=2 Card=1 Bytes=16)
6 5 INDEX (RANGE SCAN (MIN/MAX)) OF 'CSED_PRIM' (UNIQUE) (Cost=2 Card=12488)
7 3 NESTED LOOPS (Cost=8 Card=1 Bytes=65)
8 7 NESTED LOOPS (Cost=5 Card=1 Bytes=55)
9 8 NESTED LOOPS (Cost=5 Card=1 Bytes=41)
10 9 NESTED LOOPS (Cost=4 Card=1 Bytes=35)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'EPISODE_AIRINGS' (Cost=3 Card=1 Bytes=21)
12 11 INDEX (UNIQUE SCAN) OF 'EPI_PK' (UNIQUE) (Cost=2 Card=997448)
13 10 TABLE ACCESS (BY INDEX ROWID) OF 'LOGS' (Cost=1 Card=1 Bytes=14)
14 13 INDEX (UNIQUE SCAN) OF 'LOG_PK_PRIM' (UNIQUE)
15 9 TABLE ACCESS (BY INDEX ROWID) OF 'INVOICE_NETWORK_XREF' (Cost=1 Card=1 Bytes=6)
16 15 INDEX (UNIQUE SCAN) OF 'INVOICE_NETWORK_XREF_PK' (UNIQUE)
17 8 INDEX (UNIQUE SCAN) OF 'SYS_C0018280' (UNIQUE)
18 7 TABLE ACCESS (BY INDEX ROWID) OF 'UNITS' (Cost=3 Card=1 Bytes=10)
19 18 INDEX (RANGE SCAN) OF 'UT_POB_FRGN' (NON-UNIQUE)(Cost=2 Card=1)
SQL> spool off
------------------------ end --------------------------------------------
Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2
