Home -> Mailing Lists -> Oracle-L -> RE: 10g slowdown

RE: 10g slowdown

From: Michael Fontana <mfontana_at_enkitec.com>
Date: Fri, 12 Dec 2008 13:20:25 -0600 (CST)
Message-ID: <31126405.18651229109625664.JavaMail.root@mail.enkitec.com> 





Yes.  I know that in Siebel, it changes optimizer settings with an 
alter session set parameter..... before it executes queries.  



Not sure if SAP does as well, but that would explain the different plan.



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



From: Blanchard William [mailto:William.Blanchard_at_kohler.com] 
Sent: Friday, December 12, 2008 1:18 PM


To: mfontana_at_enkitec.com; oracle-l_at_freelists.org
Subject: RE: 10g slowdown



We looked at the program in SAP and the table & indexes in Oracle and
nothing directly related to the query has changed.  Is there anything
that would cause Oracle to not use the right index in SAP but works fine
when run in sqlplus?




William 



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



From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Fontana
Sent: Friday, December 12, 2008 1:15 PM


To: mfontana_at_enkitec.com; oracle-l_at_freelists.org
Subject: RE: 10g slowdown



I would try executing the statement with sqlplus independent of SAP if
possible.  Sorry in advance if you've already done so....



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



From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]


On Behalf Of Michael Fontana


Sent: Friday, December 12, 2008 1:12 PM


To: oracle-l_at_freelists.org


Subject: RE: 10g slowdown



This is a good point - because of a logic error - or programming bug -
depending upon how you look at it - we had an innocent single-row query
which was in a called program that was executed millions of times.



This is not an uncommon situation with SAP, depending upon some
configuration settings.



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



From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]


On Behalf Of Johnson, William L (TEIS)


Sent: Friday, December 12, 2008 12:57 PM
To: somckit.khemmanivanh_at_weyerhaeuser.com;
'William.Blanchard_at_kohler.com'; Allen, Brandon
Cc: oracle-l_at_freelists.org


Subject: RE: 10g slowdown



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: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]


On Behalf Of Khemmanivanh, Somckit


Sent: Friday, December 12, 2008 1:31 PM


To: 'William.Blanchard_at_kohler.com'; Allen, Brandon
Cc: oracle-l_at_freelists.org


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



Thanks,





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



From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]


On Behalf Of Blanchard William


Sent: Friday, December 12, 2008 7:44 AM


To: Allen, Brandon


Cc: oracle-l_at_freelists.org


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).




William



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



From: Allen, Brandon [mailto:Brandon.Allen_at_OneNeck.com]
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-----



From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Blanchard William
Sent: Friday, December 12, 2008 8:08 AM


To: Bobak, Mark


Cc: oracle-l_at_freelists.org


Subject: RE: 10g slowdown



SELECT STATEMENT



  NESTED LOOPS


OUTER             1
    TABLE ACCESS                        ZKPACDATA                    BY
INDEX ROWID    1
      Filter Predicates
        AND
          T_00.WERKS='MS'
          T_00.LOEKZ<>'X'
      INDEX                             ZKPACDATA~Z1
RANGE SCAN        1
        Access Predicates
          AND
            T_00.MANDT='010'
            T_00.AUFNR='000012284021'
            T_00.VORNR='6100'
      INDEX                             ZKPACREASONCODES~0
UNIQUE SCAN       0
        Access Predicates
          AND
            T_01.WERKS(+)='MS'
            T_01.REASONCODE=T_01.REASONCODE(+)







William




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



From: Bobak, Mark [mailto:Mark.Bobak_at_proquest.com]
Sent: Friday, December 12, 2008 9:00 AM


To: Blanchard William; Jack van Zanen


Cc: oracle-l_at_freelists.org


Subject: RE: 10g slowdown



Can you post the execution plan?




From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On
Behalf Of Blanchard William [William.Blanchard_at_kohler.com]
Sent: Friday, December 12, 2008 9:56 AM


To: Jack van Zanen


Cc: oracle-l_at_freelists.org


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_at_gmail.com [mailto:jack.van.zanen_at_gmail.com] On
Behalf Of Jack van Zanen


Sent: Thursday, December 11, 2008 10:17 PM
To: Blanchard William


Cc: oracle-l_at_freelists.org


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_at_kohler.com<mailto:William.Blanchard_at_kohler.com>>



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





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.


--



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






--



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




--



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




--



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




--



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




--



http://www.freelists.org/webpage/oracle-l
Received on Fri Dec 12 2008 - 13:20:25 CST












Original text of this message