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