Top SQL View (DB2 LUW Performance Analyst)
One or two bad queries can cause a lot of trouble for the remaining sessions in a database, and so can one or two bad queries. It is important to find them before they get into a production system, but sometimes a few sneak through.
By applying custom filters and performance-related thresholds, the Top SQL view locates inefficient SQL. By applying filters to certain I/O and statistical counters, you hope to isolate queries that far exceed their nearest competitors in the same area (like disk reads). When you find them, you should reduce the number of sorts a query performs. Or, for a query that returns only a few records, you should try to minimize the number of rows a query processes.
When you begin to look for inefficient SQL in a database, there are two primary questions you need to answer:
- What has been the worst SQL that’s historically been run in my database?
- What is the worst SQL that’s running right now in my database?
When troubleshooting a slow system, you should be on the lookout for any query that shows an execution count that is significantly larger than any other query on the system. It may be that the query is in an inefficient loop, or other problematic programming construct. Only by bringing the query to the attention of the application developers will you know if the query is being mishandled from a programming standpoint.
The following tabs are available on the Top SQL page:
- 1 Summary tab
- 2 Top SQL Statements
- 3 Top SQL Details
- 4 Criteria tab
- 5 SQL Details tab
- 6 Top SQL Results
- 7 Selected SQL Text
- 8 Selected SQL Last Snapshot Details
- 9 Dynamic SQL Cache tab
- 10 Dynamic SQL Cache Details
- 11 Dynamic SQL Statement
- 12 Execution and Preparation Details
- 13 Bufferpool Analysis
- 14 Execution Time Details
The Summary tab is divided into two sections:
Top SQL Statements
Before you can identify problem SQL in your database, you have to ask the question of what ‘bad SQL’ is. What criteria do you use when you begin the hunt for problem SQL in your critical systems? Understand that even the seasoned experts disagree on what constitutes efficient and inefficient SQL; so there is no way to sufficiently answer this question to every SQL Server professional’s satisfaction. The Top SQL results grid displays the top SQL statements across various performance categories including Reads, Writes, CPU statistics, and Elapsed Time.
Top SQL Details
This section shows the details of the top SQL statements. The columns presented here include:
- Usage: % of all operations of this type performed by the selected sql statement.
- SQL text: Text of the selected SQL statement.
- Total Reads: Total number of read operations performed by the selected SQL statement.
- Total Writes: Total number of write operations performed by the selected SQL statement.
- Executions: Number of times the selected SQL statement was executed.
- First Start Time: First time the selected SQL statement was executed.
- Avg CPU Time: Avg CPU time used by the selected SQL statement.
- Avg Elapsed Time: Avg Elapsed time for selected SQL statement.
The Top SQL view displays all SQL running or executed in the current datasource. You can use the lists and boxes to reorder the display according to your specific needs. The Criteria tab of the Top SQL page includes the following sections that you can mix and match to give you the information you want to find.
- Demographic Criteria
- Statistical Criteria
- Sort Criteria
SQL Details tab
The SQL Details tab presents the following sections:
Top SQL Results
This section shows the details of the SQL statements and (their metrics) captured at each refresh of the performance analyst. The information presented here includes
- SQL text: Partial text of the captured SQL statement. The full text is shown in the selected SQL text section.
- Agents Top: The maximum number of agents that were used when executing the selected statement at the time of last refresh
- Max Agents Top: the maximum number of agents that were used when executing the selected statement over all captured executions
- Fetch Count: Number of successful fetches performed for the specific statement at the time of last refresh
- Avg Fetch Count: Avg number of successful fetches for the selected statement over all its captured executions
- Int. Rows Deleted: Number of internal rows deleted for the selected statement as a result of internal activity
- Avg Int Rows Deleted: over all its captured executions
- Int Rows Inserted
- Avg Int Rows Inserted over all its captured executions
- Int. Rows Updated
- Avg Internal Rows Updated over all its captured executions
- Rows Estimate
- Avg # Rows Estimate over all its captured executions
- Cost Estimate
- Avg Cost Estimate over all its captured executions
- Rows Read
- Avg Rows Read over all its captured executions
- Rows Written
- Avg Rows Written over all its captured executions
- Avg Sorts over all its captured executions
- Sys CPU Time
- Avg Sys CPU Time over all its captured executions
- Usr CPU Time
- Avg Usr CPU Time over all its captured executions
- Total Sort Time
- Avg Total Sort Time over all its captured executions
- Buffer pool Hit Ratio
- Overall Buffer pool Hit Ratio over all its captured executions
- Temp Data Hit Ratio
- Overall Temp Data Hit Ratio over all its captured executions
Selected SQL Text
The Selected SQL Text window allows you to select any of the Top SQL result rows and get the complete SQL text. From here you can open an Explain Plan or Tune the SQL.
Selected SQL Last Snapshot Details
This displays attributes for the last snapshot of the selected SQL statement
Dynamic SQL Cache tab
The Dynamic SQL Cache tab of the Top SQL view, displays the following sections:
- Dynamic SQL Cache Details
- Dynamic SQL Statement
- Execution and Preparation Details
- Bufferpool Analysis
- Execution Time Details
Dynamic SQL Cache Details
The DB2 statement cache stores packages and statistics for frequently used SQL statements. This section shows the statements in this cache and the metrics associated with each statement.
By examining the contents of this cache, you can identify the dynamic SQL statements that are most frequently executed and the queries that consume the most resources. Using this information, you can examine the most commonly executed and most expensive SQL operations, to determine if SQL tuning could result in better database performance.
Dynamic SQL Statement
The Dynamic SQL Text window allows you to select any of the Dynamic SQL Cache Detail and get the complete SQL text. From here you can open an Explain Plan or Tune the SQL.
Execution and Preparation Details
Here you see the number of times the selected SQL statement was compiled and executed.
You can use the execution information to identify the most commonly executed SQL statements in your system. You will probably see the greatest performance benefit from tuning them.
You can use the compilation information to determine the number of different compilations for the selected SQL statement. Use this information together with the number of executions to determine if a bad compilation environment is skewing the metrics.
This section shows the various bufferpool usage metrics for the selected SQL statement.
This information helps you determine the scope when you tune bufferpools.
Execution Time Details
The information presented here may be used to identify the longest running SQL.
The longest running SQL statements together with the most frequently used SQL statements help you identify the most likely candidates for SQL tuning.