Skip to Content
0
Former Member
Sep 12, 2007 at 03:59 PM

Performance Issue on All transactions

55 Views

Hi There,

We have huge performance problem, in out, testing box, and it is

holding our current project from going any further. We have this

performance problem in general all the t-codes

SAMPLE CJ20n or ME23n, etc,

This is happening literally on all the transactions.

Sample SQL Trace on CJ20n;

What could be the best approach and solution to resolve this issue?

Note: This system is database refresh of our production systems.

I have compared my Oracle Parameters and SAP Parameters, and everything seems to be fine but System is really has bad performance.

Any any idea?

for SQL trace I did on t-code: CJ20n

Thanks

Kumar


First SQL Query:  Duration 594, 536

SELECT STATEMENT ( Estimated Costs = 1 , Estimated #Rows = 1 )

       3 FOR UPDATE

           2 TABLE ACCESS BY INDEX ROWID USR41_MLD
             Estim. CPU-Costs = 3,129 Estim. IO-Costs = 0

               1 INDEX UNIQUE SCAN USR41_MLD~0
                 Search Columns: 3
                 Estim. CPU-Costs = 1,634 Estim. IO-Costs = 0


Second SQL Query: Duration 102,387

SELECT STATEMENT ( Estimated Costs = 3 , Estimated #Rows = 5 )

       1 TABLE ACCESS FULL MLICHECK
         ( Estim. Costs = 2 , Estim. #Rows = 5 )
         Estim. CPU-Costs = 8,871 Estim. IO-Costs = 2




Duration: 265,088
SELECT STATEMENT ( Estimated Costs = 3 , Estimated #Rows = 618 )

       2 TABLE ACCESS BY INDEX ROWID PRHI
         ( Estim. Costs = 3 , Estim. #Rows = 618 )
         Estim. CPU-Costs = 76,778 Estim. IO-Costs = 3

           1 INDEX RANGE SCAN PRHI___P
             ( Estim. Costs = 1 , Estim. #Rows = 618 )
             Search Columns: 2
             Estim. CPU-Costs = 29,163 Estim. IO-Costs = 1


This is where it took forever: 
Duration 501594563


SELECT STATEMENT ( Estimated Costs = 3 , Estimated #Rows = 2 )

       3 COUNT STOPKEY

           2 TABLE ACCESS BY INDEX ROWID MSEG
             ( Estim. Costs = 2 , Estim. #Rows = 2 )
             Estim. CPU-Costs = 46,305 Estim. IO-Costs = 2

               1 INDEX RANGE SCAN MSEG~R
                 ( Estim. Costs = 1 , Estim. #Rows = 41 )
                 Search Columns: 2
                 Estim. CPU-Costs = 5,913 Estim. IO-Costs = 1