EM: Databases/SQL: Databases > Query Analysis
Description
The
Databases, 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.
Note: selecting a database from
Databases name list, opens its configuration interface, and enabling access to Query Analysis.
Location
EM Navigator →
Databases/SQL →
Databases → Query Analysis
In BBj 12.00 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.
Database Query Analysis
Query Analysis analyzes 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.
Query Analysis Settings
| Settings | Description |
|---|---|
|
Choose the database from the dropdown and click View Data to load the selected database’s data in the column section. |
|
|
Filters tables by name before applying the filter criteria to query analysis results. |
|
|
When clicked, loads the selected database’s query analysis data into the results table. |
Query Analysis Column Section
Lists query analysis results by status, table, columns, counts, index optimization, score, and percentages.
Query Analysis Settings
| Settings | Description | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Displays the status of query analysis on a specific table.
|
|||||||||||||||||
|
Name of the table. |
|||||||||||||||||
|
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. |
|||||||||||||||||
|
Number of times Query Analysis has been run on that table. |
|||||||||||||||||
|
Whether this combination of columns has an index on them or not. |
|||||||||||||||||
|
Is the number of queries that have been run that used that combination of columns in their WHERE clause. |
|||||||||||||||||
|
Record count. |
|||||||||||||||||
|
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:
|