| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Urgent problem with query in CBO Vs RBO
"Looks like the lid on the "jar of bugs" is leaking ... and it made it into
920 code as well."
I think this a pretty common occurrence at Oracle... I think it has to do with the way they branch their code streams... Subsequent patches are often not integrated into the latest stream.... I've run into this kind of thing several times...
Tim
-----Original Message-----
Sent: Friday, October 18, 2002 9:59 AM
To: Multiple recipients of list ORACLE-L
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 ...
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)))
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)))
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
QOTD: Any clod can have facts, but having an opinion is an art!
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: TJohnston_at_quallaby.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Oct 18 2002 - 10:54:18 CDT
 |
 |