SQL Optimizer for SAP ASE 3.8 - 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.

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

SQL Collector for Monitor Server Tutorial

The SQL Collector for Monitor Serverallows you to capture from the ASE Monitor Server any currently executing SQL statements according to your user-defined criteria. Each SQL statement captured is categorized according to suspected levels of performance problem.

To use the collector

1. Create the Collector

   1. Click . The SQL Collector window displays, followed by the Add Collector wizard if you have not created a Collector before. If the Add Collector wizard does not display, click .

   2. Enter a Collector name and the Adaptive Server Enterprise Monitor Server name. Check that the other settings satisfy your requirements. Define a monitoring end time on the Schedule page on the Add Collector wizard.

   3. Click Finish.

2. Start the Collector

1. Click to begin capturing executing statements.

2. Details display in the SQL Collector grid as the information accumulates.

3. The monitoring process stops at the end time you define under the Schedule page in the Add Collector wizard.

3. View the results

1. To view the retrieve SQL statements, select the newly added Collector row.

2. In the SQL Text pane, click the tabs, SQL1, SQL2, and so forth to see the captured SQL statements. The SQL Information pane displays the query plan information.

3. Select one SQL statement you want to analyze for performance improvement. Click to optimize the SQL statement in the SQL Optimizer. Alternatively, you can also send the SQL statement to the Index Advisor. Click .

Related Topic

SQL Collector for Monitor Server Overview