RE: 10g slowdown
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <William.Blanchard@xxxxxxxxxx>, "'Jack van Zanen'" <jack@xxxxxxxxxxxx>
- Date: Fri, 12 Dec 2008 11:12:43 -0500
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:
