Re: 10g slowdown
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: William.Blanchard@xxxxxxxxxx
- Date: Fri, 12 Dec 2008 15:27:26 +0000
what does the explain plan look like?
On Fri, Dec 12, 2008 at 2:56 PM, Blanchard William <
William.Blanchard@xxxxxxxxxx> wrote:
> 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>
>
>> 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
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
