The tuner performs the index and SQL analysis as part of the tuner run job performed on the Overview tab if Perform Detail Analysis is selected. Otherwise, the analysis is performed when you click the Analysis tab.

DB Optimizer can parse an SQL query and analyze the indexes and constraints on the tables in the query and display the query in graphical format on the Visual SQL Tuning (VST) diagram. The VST diagram can be displayed in either Summary Mode or Detail Mode. The VST diagram helps developers, designers, and DBAs see flaws in the schema design such as Cartesians joins, implied Cartesians joins and many-to-many relationships. The VST diagram also helps the user to more quickly understand the components of an SQL query, thus accelerating trouble- shooting and analysis.

This section is comprised of the following topics:

Finding Missing Indexes

Missing indexes are color-coded orange in the Collect and create indexes area of the Overview tab. Creating a missing index can improve the execution path of the SQL statement being analyzed.

Indexes that are used are color-coded green. Indexes that exist in the table but are not used in this execution path are color-coded grey. Indexes that are usable but not used by the current execution path are color-coded blue.

Exploring the VST Diagram

Displaying or Hiding the Diagram Legend

Click the Diagram Legend toggle [] to view the legend, and then click it again to hide it. All of the icons used in the VST diagram are identified in the Diagram Legend.

Displaying or Hiding the Explain Plan

The Explain Plan is available only for the Oracle 10g and 11g platforms.

Hover the mouse over the VST diagram to see the Explain Plan icon [], and then click it to view the Explain Plan Overlay. Click the Explain Plan icon again to hide the overlay.

The additional nodes shown in the Explain Plan overlay provide details on the flow of the query plan, with operations (such as nested loops, sorts, and joins) showing connecting tables and other operations.

Hover the mouse over the objects or relationships in the overlay to view additional details.

Displaying or Hiding Table Counts and Ratios

To view or hide table counts, two table join sizes and filtered result set ratios, click the Ratios and Counts icon [].

Green numbers at the top left of table represent the total number of rows in that table. In the above example, the MOVIERENTAL (MR) table has 5000 rows.

Blue percentage at the bottom right of the table represent the percentage of rows in that table that meet the selection criteria. In the above example, 100 percent of the rows in the RENTALITEM (RI) table have met the selection criteria.

The numbers on the table joins indicate the total number of rows that meet the selection criteria for both tables.

You can also view the SQL Query that created a relationship by hovering over the relationship. If the tooltip content is larger than the size of the tooltip rectangle, you can hover the mouse on top of the tooltip for a second, and then it will turn into a dialog you can re-size, scroll in, and select text from to copy into the Clipboard.

Displaying or Hiding Table Columns and Indexes

Click the Detail/Summary switch to display or hide details of the table display, including table columns and indexes.

Only fields that are used in the WHERE clause are displayed in detail mode. For information on interpreting the icons used in Detail mode, activate the Diagram Legend.

You can also switch between Summary Mode and Detail Mode for a specific table or view by double-clicking the object name.

You can move tables in the diagram by clicking and dragging them to the desired location. The position of the connecting lines is automatically adjusted.

Viewing All Fields in a Table

Hover the mouse over the table name to view all fields in the table in a pop-up window.

The following illustration shows the pop-up window that appears when hovering over the table.

Viewing Object SQL

Hovering the mouse over the table name, field, or index displays the SQL required to create that object.

Viewing Relationships

Hovering over the join between two tables displays the relationship between the two tables.

Expanding Views in the VST Diagram

For example, the following is the default layout from query join table CLIENT (c) to view TRANSACTIONS (t).

Right-click on the view, and then choose Expand View.

Now you can see the objects in the view.