Identifying expensive SQLs using ST04 transaction

This article answers the following query:

How to identify expensive SQLs using ST04 transaction ?

As shown below, go to transaction ST04 and navigate to SQL statement analysis -> Shared cursor cache

 

Double-click on the shared cursor cache while leads you to the following screen.

In the above screen, please provide Buffer gets as 50,000 for example and in the List sort options select Buffer gets radio button and execute.

It displays all the SQL statements whose buffer gets are more than 50,000 and sorts them in descending of buffer gets as shown below

As highlighted in the above screen, identify the top 5 or 10 statements as per buffer gets. These are expensive or costly SQL statements. 

Also look at executions column and figure out the value. If value is greater for executions it means that the SQL is frequently getting executed and it is worth tuning that statement. 

Afterwards click on the corresponding SQL statement which opens the SQL statement as below

In the above screen,  click on explain icon (highlighted) which opens up another window as below

 

In the above window, you can view :

• Estimated costs to execute the SQL
• Estimated rows to be fetched
• Estimated CPU costs
• Estimated IO costs

Ideally, all of these parameter values should be low for an optimized SQL statement. For expensive SQL statements, these values will be of very high impacting system performance.

Also, in the above window find out whether FULL SCAN is present. If present it means indexing is not proper for the table involved in this SQL statement.

Please double click on the table name or index name which is going for full scan, to view similar window as below :

In the screen, you can note “Last statistics date”. If this date is too old, it is recommended 
run update statistics for this table or index. This improves performance of the SQL statement as it provides correct recent statistics so that execution plans for SQL statement will be done properly.

Due to outdated statistics, execution plan won’t be proper leading to high row fetches, increasing CPU and IO costs.

Enter your email address:


Delivered by FeedBurner