Scenario: Retrieving performance metrics for SQL scripts

In this scenario, Joe, an application developer, uses the workbench to connect to his performance management repository database and collect performance metrics for a SQL script that he is developing.

Joe is an application developer for the Sample Company. Recently, he heard complaints that his customer order application is slow to respond. He wants to tune his SQL statements to make sure that his statements are performing optimally, so he decides to collect performance data when he runs the SQL statements against the database.

First, Joe ensures that his team is running the minimum version of Optim Performance Manager Extended Edition. He makes sure that the database that contains the customer order data is configured in Optim Performance Manager for Extended Insight monitoring. He collects the connection information for the Optim Performance Manager repository database, including the host name and the port number that is used for Extended Insight. He already knows the connection information for the customer order database.

Joe uses the Performance Metrics page of the Preferences window in the workbench to specify that he wants to allow the workbench to connect to the repository database for Optim Performance Manager Extended Edition to retrieve performance data to display within the workbench.

Next, Joe opens an existing SQL script within the workbench. He uses the Connection tab to specify that he wants to run this script against the customer order database. Then, he opens the Performance metrics tab to specify an Optim Performance Manager profile that he uses to specify information that the workbench can use to connect to the repository database, PERFDB. This repository database stores the performance metrics data that is gathered by Optim Performance Manager for the monitored customer order database.

If Joe does not currently have an Optim™ Performance Manager profile specified within his workspace, he can specify one, or he can manage existing profiles by clicking the Manage button next to the Profile field. After Joe selects a profile and specifies other options, he clicks the Run and collect performance metrics button. The SQL statements in the script are run against the customer order database, and the Optim Performance Manager repository database monitors the database for performance data. The workbench opens the Performance Metrics view and waits for Optim Performance Manager to collect and process the performance metrics data that is stored in the repository database. This action may take 2 to 6 minutes to complete, so Joe continues to work on another application while he waits for the data to be returned. He can check the progress of the action in the Progress view.

The results are returned in the Performance Metrics view. Joe notices that one SQL statement in the script has a particularly high CPU time. He right-clicks the SQL statement in the Performance Metrics view, then selects Open to inspect the statement in a new SQL editor view. He determines that a particularly large table is being called by the query, which slows performance because DB2® returns all rows of the table. After meeting with the business team that uses that table, he determines that it would be acceptable to return only the first 100 rows of the table. He adds the following line to the statement:
FETCH FIRST 100 ROWS ONLY

Joe saves the changes, then runs the script against the customer order database again. The Performance Metrics view saves the results of previous actions, making it easy to compare the performance of various updates. When the Performance Metrics view updates, he can see that the CPU time for the updated SQL statement has significantly decreased, improving the performance of his application.

To save the improvement for future reference, Joe selects the line in the Performance Metrics view that contains the original SQL statement, then he presses CTRL to select the updated SQL statement. He right-clicks in the Performance Metrics view and selects Export to save the two lines to an XML file as proof that the performance has improved. He can view these results outside of the workbench or share the performance improvements with other team members.


Feedback