RE: 10g slowdown

I agree with your assessment. This code is generated by SAP and there isn't anything I can do to change it. The explain plan shows a cost of 0 for the joins which tells me the CBO is smart enough to recognize this and ignore it.  

William

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Friday, December 12, 2008 10:13 AM To: Blanchard William; 'Jack van Zanen'
Cc: oracle-l_at_freelists.org
Subject: RE: 10g slowdown

Your comment that the join is a filter is very strange to me when I look at your query. It appears that you do not reference anything from the t_01 table and that the two column equijoin is outer on both columns of the equijoin. So what exactly do you believe you are filtering. From a very quick read of your query I believe the join does nothing but add the overhead of the t_01 table. Please tell me how I am wrong.  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Blanchard William Sent: Friday, December 12, 2008 9:57 AM
To: Jack van Zanen
Cc: oracle-l_at_freelists.org
Subject: RE: 10g slowdown  

The query is technically on one table. It uses a join as a filter only. The table is 5.1 million rows (~3.4G) and stays relatively constant.  

SELECT t_00.uname, t_00.erdat, t_00.aezeit, t_00.kpackey, t_00.trantype, t_00.lmnga

    FROM sapr3.zkpacdata t_00, sapr3.zkpacreasoncodes t_01     WHERE (t_00.reasoncode = t_01.reasoncode(+)

                AND t_00.werks = t_01.werks(+))
        AND t_00.mandt = '010'
        AND t_00.loekz <> 'X'
        AND t_00.vornr = '6100'
        AND t_00.aufnr = '000012284021'
        AND t_00.werks = 'MS'

 

This query took 4.5 hours. When I run an explain plan the cost is 1. That's why I'm confused. My initial thought was that there was something locking the table but even that wouldn't necessarily explain the 4.5 hours. The program was run again during a maintenance window and it still took 6 hours.    

William  

<snip>

--
http://www.freelists.org/webpage/oracle-l