How to Identify Resource Intensive SQL ("TOP SQL")
Last updated on MAY 16, 2018
Applies to:Oracle Database - Personal Edition - Version 22.214.171.124 and later
Oracle Database - Standard Edition - Version 126.96.36.199 and later
Oracle Database - Enterprise Edition - Version 188.8.131.52 and later
Information in this document applies to any platform.
This article provides guidance on how to identify the most resource intensive SQL statements in a database for tuning purposes.
If there is a performance issue related to the performance of SQL, it may be that one or two statements are consuming the majority of the resources or it could be that a lot of SQL statements consume small amounts of resources adding up to a large amount. In terms of tuning it is easier to handle the first case with less statements to examine. There are a number of places that you can look to find the highest resource users and how you approach this depends on the area where you are seeing the problem. If the resource issue is high CPU usage then focus on the highest CPU users. If it is I/O then focus on the highest under that criteria.
The techniques described here can be used when initial diagnosis of a database performance problem suggests that further investigation needs to concentrate on finding and tuning the most resource intensive SQL statements according to specific criteria, e.g.
- Using the most CPU
- Performing the most disk I/O operations
- Having the most executions
- Taking the longest time to execute (elapsed time)
As with any performance issue, once you have identified and tuned a statement, re-test the criteria looking for improvements. If your goal for this statement/overall is met then stop! If your goal is not met then re-tune. If your goal for the statement is met but the overall performance is still not as desired, look for the highest resource user and tune that.
The article should be of use to Database Administrators, Support Engineers, Consultants and Database Performance Analysts.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms