RE: 10g slowdown

Can you post the execution plan?
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf 
Of Blanchard William [William.Blanchard@xxxxxxxxxx]
Sent: Friday, December 12, 2008 9:56 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

________________________________
From: jack.van.zanen@xxxxxxxxx [mailto:jack.van.zanen@xxxxxxxxx] On Behalf Of 
Jack van Zanen
Sent: Thursday, December 11, 2008 10:17 PM
To: Blanchard William
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: 10g slowdown

are you saying that a query on a single table takes 6 hours?

How big is the table in GB/TB?

Jack

2008/12/12 Blanchard William 
<William.Blanchard@xxxxxxxxxx<mailto:William.Blanchard@xxxxxxxxxx>>

We have a query that began taking a long time about a week ago.  The program, 
in SAP, ran for 10 - 15 minutes but is now taking about 6 hours.  The table has 
5.1 million rows.  The explain plan shows a simple index range scan.  We just 
reran statistics on the table and all indexes but no luck We are concentrating 
on the one query that took about 4.75 hours.

Does anyone see something glaring or know of a simple test to locate the 
problem?

Let me know if you need any other info.


Thank you,

William B.



--
J.A. van Zanen

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

Other related posts: