Performance Issue on All transactions

[Former Member]

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

[former_member214498]

Hi

CJ20n is a standard T-code and if thats giving you such huge response time then you must ask your basis team to check out the system settings, server hardware and run the virus check.

Re-check the sizing of the hardware for SAP system.

<b><REMOVED BY MODERATOR></b>

Message was edited by:

Alvaro Tejada Galindo

[Former Member]

Waz, thanks for the quick response. I am part of the basis team, so I need to resolve this issue for project team.

Waz, we have performance problem on all the transaction literally sometime, it is even taking 20+ minutes.

I have check Oracle, and SAP Parameters are they look fine. Hardware sizing also looks fine.

Please let me know where and how to look at things?

This is the first I am dealing with performance issue like this?

Let me know

Thanks

Kumar

[Former Member]

If this is a copy of your productive instance and that is not giving you problems, then it is a hardware or basis problem. the basis people should look into it.

Rob

[Former Member]

Rob,

I am the basis guy to do this. I am not sure where to look into for these kind of error. First time facing this problem.

any other approach

Kumar

[former_member214498]

Hi

1. Check for a virus. Run the virus check on the system.

Option 2. Reboot the system.

Option 3. if your main server and clients are on seperate physical location, look for the WAN Link. and check the OS settings.

I am quite sure that you will be able to resolve this problem by focussing on these issues.

My basis team in one of projects in Pakistan faced a quite similar issue and that issue turned out to be the increased users and bandwidth of WAN.

<b><REMOVED BY MODERATOR></b>

Message was edited by:

Alvaro Tejada Galindo

[Former Member]

Waz, thanks again.

I have rebooted the system many times. Our WAN looks fine and so do OS.

You wouldn't believe this, we have only 2 users in teh system for now, just me and another person trying to resolve this issue.

Kumar

[Former Member]

to fix this problem:

It was stupid SAP sapdab_role script, eventhough I ran this script after refresh, I guess sap didn't fix this privelege problelm.

then I ran it again

cd usr/sap/PR2/SYS/exe/run

Sqlplus /nolog @sapdba_role R3

bash-2.03$ brconnect -c -u / -f stats -t all

BR0301W SQL error -1031 at location brc_dblog_write-5, SQL statement:
'INSERT INTO SAP_SDBAD (BEG, FUNCT, POS, LINE) VALUES ('20070912103654', 'sta', '0000', 'A 0000000000000000 00000000 00000000 0
ORA-01031: insufficient privileges
BR0325W Writing to database log failed
BR0280I BRCONNECT time stamp: 2007-09-12 10.38.21
BR0301W SQL error -1031 at location brc_dblog_open-1, SQL statement:
'INSERT INTO SAP_SDBAH (BEG, FUNCT, SYSID, OBJ, RC, ENDE, ACTID, LINE) VALUES ('20070912103654', 'sta', 'PR2', 'ALL', '9999', '
ORA-01031: insufficient privileges
BR0324W Insertion of database log header failed
 
BR0806I End of BRCONNECT processing: cdwdgbnq.sta2007-09-12 10.38.20
BR0280I BRCONNECT time stamp: 2007-09-12 10.38.21
BR0803I BRCONNECT completed successfully with warnings
External program terminated with exit code 1
BRCONNECT returned error status E
Job finished

I have no issues with performance now.

SAP SAP SAP.....

Kumar

[former_member214498]

Well done kumar.keep it up.

[Former Member]

have you looked intot he index statistics of the database tables? if they get out of date, many time you can experience SEVERE performace issues. you can rebuild them at the oracle level or from within SAP. i would try this on the idexes involved above, but we rebuild statistics each night for tables that require it.

Sorry, i am not the basis person, so i cannot give you chapter and verse on how to do this.