In my earlier posting on Oracle support
in Optim Development Studio, I briefly mentioned our support for Visual Explain. Now, I will provide more details and walk you through a couple of use case scenarios for Oracle Visual Explain.
Visual Explain for Oracle is modeled similarly to the Visual Explains for other platforms - DB2 LUW, DB2 for z/OS and Informix. The simple use case scenario is one in which you gather explain information for a SQL statement (SELECT, INSERT, DELETE, UPDATE or MERGE). Let's take a look at gathering and showing explain information for the following SQL statement:
select * from scott.emp union all
select * from scott.emp;
If you were using SQL *Plus you could enter the commands and view the output as shown in Figure 1.
Figure 1.Output for the sample SQL from Oracle SQL *Plus
In Optim Development Studio, there are several launch points for Visual Explain for any particular SQL statement:
- The Project Explorer
- The SQL Editor window
- The Java editor when using pureQuery
Once launched, using a wizard, you set the options including the trace settings,which plan
table to use (defaulted to PLAN_TABLE), and the optimizer mode to use
(defaults to CHOOSE). The result is graphical output with nodes
representing the operations and a parent/child relationship. Hovering over a node you can see details such as the node type, the cardinality, cost, operation name and additional details on the operation (the diagram shows abbreviated node names). Right clicking on a node and then clicking on Show Description
brings up further details. For example, if you click on the TBFULL node, this brings up details on the table EMP, with its columns, indexes and additional catalog information as shown in Figure 2.
Figure 2. Details are available for any highlighted node in the Explain graph
There's another way to get explain information via running a background explain from the SQL outline view. I'll review this procedure since this is really a great tool to get cost information for SQL statements.
- As Sonali explained in her article and in associated video, you can get explain information (and performance hot spot visualization) for Oracle by capturing the SQL from any JDBC application.
- After the SQL statements in the application are captured and are available in the SQL outline view, you can get explain data on them by enabling Background Explain. Do this by right clicking on the project and selecting Properties and then going to pureQuery->Background Explain and clicking on the check box as shown below in Figure 3.
Figure 3. Enable background explain as a project property
- Once the explain is enabled, you can view the Explain data for the SQL statements as shown in Figure 4.
Figure 4. SQL outline view with explain information
This feature is extremely useful for developers who want to screen the
SQL statements they are writing in their application. Any SQL statement
that shows up doing a number of joins and has a high cost can be taken
to a DBA for further analysis. When you download the Optim Development Studio, let us know what you think of the Visual Explain capability as well as the other capabilities for Oracle such as PL/SQL support and other pureQuery support.