RE: 10g slowdown

From: Johnson, William L (TEIS) <>
Date: Fri, 12 Dec 2008 13:57:22 -0500
Message-ID: <>

Are you sure that this statement is not being executed multiple times? We have run into sql statements that look pretty simple - until a program executes them multiple times...

-----Original Message-----

From: [] On Behalf Of Khemmanivanh, Somckit Sent: Friday, December 12, 2008 1:31 PM
To: ''; Allen, Brandon Cc:
Subject: RE: 10g slowdown

Just a thought...

Can you reproduce the issue in a QA system?

Is your QA system periodically refreshed?

Another idea would be to run a quick check of your Oracle Parameters against the SAP recommended values...

SAP OSS Note has an automated check utility to this:

Note 1171650 - Automated Oracle DB parameter check


-----Original Message-----

From: [] On Behalf Of Blanchard William Sent: Friday, December 12, 2008 7:44 AM
To: Allen, Brandon
Subject: RE: 10g slowdown

Unfortunately, there aren't any. The SAP Admin said that the explain plan looks the same as it was before (using the same index).


-----Original Message-----

From: Allen, Brandon [] Sent: Friday, December 12, 2008 9:43 AM
To: Blanchard William
Subject: RE: 10g slowdown

Did you try checking for previous explain plans as previously suggested?

-----Original Message-----

[] On Behalf Of Blanchard William Sent: Friday, December 12, 2008 8:08 AM
To: Bobak, Mark
Subject: RE: 10g slowdown


OUTER             1
    TABLE ACCESS                        ZKPACDATA                    BY
      Filter Predicates
      INDEX                             ZKPACDATA~Z1
RANGE SCAN        1
        Access Predicates
      INDEX                             ZKPACREASONCODES~0
        Access Predicates


-----Original Message-----

From: Bobak, Mark [] Sent: Friday, December 12, 2008 9:00 AM
To: Blanchard William; Jack van Zanen
Subject: RE: 10g slowdown

Can you post the execution plan?

From: [] On Behalf Of Blanchard William [] Sent: Friday, December 12, 2008 9:56 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.


From: [] On Behalf Of Jack van Zanen
Sent: Thursday, December 11, 2008 10:17 PM To: Blanchard William
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?


2008/12/12 Blanchard William

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


Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.


-- Received on Fri Dec 12 2008 - 12:57:22 CST

Original text of this message