DB2 V9 new features: Can view top SQL, similar to Oracle
Category: Linux operating systems
2007-07-04 18:42:59
and Oracle's top SQL corresponds to Sysibmadm.top_dynamic_sql, the following view is in the same mode as the Sysibmadm
Example: Using the snapshot management view to identify high-cost applications
The recent workload growth on the Shopmart database has begun to affect overall database performance. Jessie is a shopmart DBA who tries to use the following administrative views to identify the larger resource consumer in the daily workload:
Application_performance
This view helps Jessie identify applications that may be performing large table scan operations:
Connect to Shopmart;
Select agent_id, rows_selected, rows_read from Application_performance;
The rows_selected value displays the number of rows returned to the application, and the Rows_read value displays the number of rows accessed in the base table. If the selection rate is low, the application may be performing a table scan operation, and the application can be prevented from performing the operation by creating an index. Jessie uses this view to identify queries that might be problematic, and then she can investigate further, looking at SQL to see if it can reduce the number of rows that the query reads at execution time.
Long_running_sql
Jessie uses the Long_running_sql management view to identify the longest running query that is currently executing:
Connect to Shopmart;
Select Elapsed_time_min, Appl_status, agent_id from Long_running_sql
ORDER BY elapsed_time_min DESC-Fetch First 5 rows only;
By using this view, she can determine the length of time these queries have been run and the status of those queries. If a query has been executing for a long time and is waiting for a lock, she can investigate further by using the Lockwaits or Lock_held management view that executes the query against a specific agent identity. The Long_running_sql view also indicates the statement being executed and allows her to identify a potentially problematic SQL.
Query_prep_cost
Jessie uses Query_prep_cost to troubleshoot queries that have identified problems. This view can indicate how often queries are run and the average execution time for each query in those queries:
Connect to Shopmart;
Select Num_executions, average_execution_time_s, prep_time_percent from
Query_prep_cost ORDER BY num_executions Desc;
The Prep_time_percent value indicates to Jessie the percentage of time spent in the query execution time that is consumed when the query is prepared. If the query is compiled and optimized at almost the same time as the query execution time, Jessie can recommend that the owner of the query change the optimization class for that query. Lowering the Optimization class allows the query to complete optimizations faster, which results in faster returns. However, if a query takes a considerable amount of time to prepare, but to execute thousands of times (without having to prepare again), changing the optimization class will not improve query performance.
Top_dynamic_sql
Jessie uses the Top_dynamic_sql view to identify dynamic SQL statements with the highest execution frequency, longest running time, and the most ordered. With this information, Jessie can focus on queries that represent some of the largest resource users when doing SQL tuning work.
To identify the most frequently-run dynamic SQL statement, Jessie issues the following statement:
Connect to Shopmart;
SELECT * from Top_dynamic_sql ORDER by num_executions
DESC FETCH first 5 rows only;
This statement returns all execution times, sort executions, and statement text details for the 5 dynamic SQL statements that perform the most frequently.
To identify the longest executing dynamic SQL statement, Jessie checks for the maximum of 5 queries with a average_execution_time_s value:
Connect to Shopmart;
SELECT * from Top_dynamic_sql ORDER by average_execution_time_s
DESC FETCH first 5 rows only;
In order to see the details of the dynamic SQL statement with the most sort times, Jessie issues the following statement:
Connect to Shopmart;
Select Stmt_sorts, Sorts_per_execution, substr (stmt_text,1,60) as Stmt_text
From Top_dynamic_sql ORDER BY stmt_sorts DESC-Fetch First 5 rows only;
DB2 View Top SQL
