SQL Optimizer for SAP ASE 3.9 - User Guide

Synchronize the Data Dictionary

Specific database information, such as tables, indexes, data volumes, and so on, from the data dictionary is used during the optimization process, SQL analysis, and other functions throughout program. This information can be loaded into memory of your PC each time you connect to a database. If your database has lots of database object, this process can take several moments, therefore you can choose to have the specific information loaded as it is needed in the program by clearing the Load database dictionary after database connection checkbox in the Preferences window.

If changes are made to the database while you are using the program, it is important to keep the information in the data dictionary up to date. Using the Synchronize Data Dictionary function will ensure that the changes to the database are directly reflected in program. This function does not break the connection to the database but updates the new database information in the memory on your computer.

To update the database information in the memory of the computer

Select Database | Synchronize Data Dictionary.

About Product Improvement Program

To prioritize enhancements in future releases, Quest SQL Optimizer for SAP ASE collects data about the use of its different features, and periodically, this data is communicated back to us. Initially, this usage data includes an IP address. Upon its receipt at a temporary server in the U.S.A., the IP address is removed, and then the anonymous data is aggregated before it is sent to our servers in California. Our product team analyses the aggregated data to understand our user community’s preferences and common practices. This analysis influences our future releases. Click here for more information on the data we collect and on our privacy policy.

• No personal information is collected
• You can stop participating at any time

To initiate participation in Product Improvement Program

Select Help | Product Improvement Program and select Yes, I want to participate

To cancel participation in Product Improvement Program

Select Help | Product Improvement Program and select No, thank you

Tutorials

SQL Inspector Tutorial

The SQL Inspector monitors SQL statements and extracts SQL performance statistics from the Adaptive Server monitoring tables. The SQL Inspector also extracts SQL statements from the QP Metrics (sysquerymetrics view). The SQL Inspector graphically displays and compares SQL activity statistics to diagnose performance bottlenecks. With the SQL Inspector you can identify the Top-N most resource intensive SQL statements.

Create the Inspector for Monitor Tables

1. Click .

2. If this is the first time you have used the SQL Inspector, the Add Inspector wizard displays over the SQL Inspector window. Otherwise, click .

3. In the General Information page, enter an Inspector name and select Monitor Tables.

4. In the Monitor Process page, specify whether you want to monitor the entire database server, any specific process IDs, or a connection identity, which allows you to focus on a specific user or application.

5. In the Retrieval Criteria page, specify how to retrieve records from the Adaptive Server monitoring tables.

6. In the Schedule Setting page, specify the monitoring schedule.

7. Click Finish.

Note: To use the SQL Inspector to access the monitoring tables, your database logon must have mon_role privilege.

Create the Inspector for QP Metrics

8. Click .

9. If this is the first time you have used the SQL Inspector, the Add Inspector wizard displays over the SQL Inspector window. Otherwise, click .

10. In the General Information page, enter an Inspector name and select QP Metrics.

11. In the Retrieval Criteria page, select the Users and Group IDs for the SQL you would like to extract and the number of SQL statements you would like.

12. In the Schedule Time page, specify when to start the inspection.

13. Click Finish.

Start the Inspector

14. Click .

View the results

15. Review the Top-N SQL statements chart used for filtering statements and a table with detailed SQL performance statistics.

16. Click if you want to add multiple charts to filter captured SQL statements by specifying a Top-N SQL criteria of performance statistics.

17. To filter collected SQL statements, use the No. of Top Consumption SQL and Percentage of Total Consumption sliders.

18. Click the performance statistics line on the grid to view the SQL text in the bottom pane of the window.

19. Once you have identified potentially problematic SQL statements you can send the SQL using the Edit menu to the SQL Optimizer or Index Advisor.

Related Topic

SQL Inspector Overview