RE: 10g slowdown
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_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
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.
<snip>Received on Fri Dec 12 2008 - 10:12:43 CST