Skip to Content

What to do with the Expensive SQL statements in DBACOCKPIT?

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

swap.png (113.6 kB)
dba1.png (125.9 kB)
dba2.png (115.0 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Posted on Dec 10, 2014 at 10:17 AM

    Hi Praveen,

    Please follow SAP Note 329914 - Expensive SQL statements composite note

    Regards,

    Gaurav

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 10, 2014 at 12:11 PM

    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

    ST12 Trace - Step by step instruction on how to use it for analysis

    BR

    SS

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 11, 2014 at 09:30 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 11, 2014 at 09:55 AM

    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


    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.