What to do with the Expensive SQL statements in DBACOCKPIT?

[former_member183044]

Hello Experts,

When i checked the Swaps in st02, the number is 15. Then i increased the parameters (zcsa/table_buffer_area, zcsa/db_max_buftab) in RZ10 and after restarting the instances,i checked st02. Still the number of swaps is 11.

Then i searched in net, and found that expensive sql statements are causing slowness.

Then i looked into DBACOCKPIT, and found the current statement.

My Question is what can we do with these SQL statements? We can't delete these statements, right. Then how can we rectify the slowness??

Please help me because i searched in net about these but i can't find a proper solution.

Regards

Praveen

[willi_eimler]

Hi Alex,

1. swapping of generic key buffer

you only need to raise the value of zcsa/table_buffer_area add 30% to the value in the instance profile. The buffer will alloc. aprox 90 MB. This is a little amount of memory and I don't think you need to change the memory settings of the Virtual Memory of the OS System. But test it:

Test your new parametersettings with

sappfpar check pf=</sapmnt/<SID>/profile/Instanceprofile>

2. expensive SQL statement.

In the screenshot I see 26 ms statement runtime. This means this is NOT an expensive SQL statement. So, you don't have a problem with that statement.

Best regards

Willi Eimler

[srinivasan_vinayagam]

Hi Praveen,

You can increase your Virtual Memory and Restart the whole Operation system.

If you in Linux clear the memory using cleanipc

Regards,

V Srinivasan

[Sriram2009]

Hi Praveen

If you known the report or transaction  you can enable to transaction code ST12 trace. and check the report or transaction how we can do the optimize .

you can refer the SCN link how to use the transaction ST12

BR

SS

[former_member182657]

Hi Praveen,

Please follow SAP Note  329914 - Expensive SQL statements composite note

Regards,

Gaurav