RE: 10g slowdown
- From: "Blanchard William" <William.Blanchard@xxxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Fri, 12 Dec 2008 10:15:56 -0600
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@xxxxxxxx]
Sent: Friday, December 12, 2008 10:13 AM
To: Blanchard William; 'Jack van Zanen'
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Blanchard William
Sent: Friday, December 12, 2008 9:57 AM
To: Jack van Zanen
Cc: oracle-l@xxxxxxxxxxxxx
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>
Other related posts:
