Enterprise Manager: Database > Query Analysis

To view this topic for the preceding Enterprise Manager, see EM Java App: Database - Query Analysis Tab and EM Java App: Automatic Database Analysis.

The Query Analysis tab provides an interface for the administrator to analyze information about all of the queries run on the selected database. This information can be used to help determine if the database tables are indexed in a way that allows the SQL engine to effectively optimize the most common queries run against the database.

In BBj 12.0 and higher, the administrator determines that it would be beneficial to add or remove an index on a particular combination of columns in the list, this can be done right from the user interface.

Specify the filter criteria for the tables in the "Table Name Filter" field and press the [Filter] button.

Each row in the GUI table displays the following information:

Status

Displays the status of query analysis on a specific table.

Icon	Status
	Indicates there is no table analysis data available for the table.
	Indicates table analysis was successfully run on this table and no alerts were discovered.
	Indicates table analysis was successful, but there is some information available that may be useful to the administrator or developers about the table.
	Indicates a critical problem of some kind. The table may still function in queries, but analysis either failed to run due to an error or there were critical alerts discovered with the data in the file during table analysis.
	Indicates warning alerts generated for the table or that table analysis should be run again because there are new analysis features available in the current version of BBj installed.

Table

Name of the table.

Column List

List of columns (in index segment order) that were included in the WHERE clause that could have possibly been used for optimization if they were indexed.

Count

Number of times Query Analysis has been run on that table.

Optimizable Index

Whether this combination of columns has an index on them or not.

Score

Is the number of queries that have been run that used that combination of columns in their WHERE clause.

Rec. Count

Record count.

Pct. (Percentage)

A score that relates to the how often this combination of columns was used.

When a statement is executed that includes a WHERE clause, it updates the underlying information used to populate this table. For example, the following statement will update information for the "LAST_NAME, FIRST_NAME", "FIRST_NAME, LAST_NAME", "FIRST_NAME", and "LAST_NAME" items in the list:

SELECT * FROM customer WHERE last_name = 'Doe' and first_name = 'John'

Enterprise Manager: Database > Query Analysis

See Also