Databases Reference
In-Depth Information
and communicated back to the calling application through a communication process,
such as TCP/IP. Unbelievably, for short online transaction processing (OLTP) transac-
tions this entire process can execute in fractions of a second.
11.2
What Do Query Execution Plans Look Like?
Each vendor has its own proprietary format for visualizing query execution plans and the
specific information these include. However, although the details vary, the basic theme is
the same. The query execution plan for a query is shown as a directed graph, with both the
database objects and the operators of the query execution plans shown as nodes with the
graph. Objects will include tables, indexes, materialized views, catalogs, temporary tables,
etc. Operators will include joins (and their type, such as hash join, nested-loop join, semi-
join, and merge join), sort, etc. The Oracle utility is called Explain Plan (Figure 11.2) and
Graphical Explain Plan. The Microsoft SQL Server utility is called Query Analyzer, and
the DB2 utility is called Explain and Visual Explain (Figure 11.3).
11.3
Nongraphical Explain
Oracle also provides a text-based Explain that allows administrators to view access paths
for queries without using the graphical user interface (GUI). Their usage is quite simple,
using the EXPLAIN PLAN FOR text followed by the SQL statement, as follows:
EXPLAIN PLAN FOR
<SQL statement text>
This generates the Explain output and stores it in the PLAN_TABLE table.
Administrators can then select the execution plan from PLAN_TABLE to view the
access path.
If you want to explain multiple SQL statements and store the access paths for all of
them (or some of them), you can specify a statement identifier on the EXPLAIN PLAN
FOR syntax to uniquely identify the specific execution plan for each statement. For
example:
EXPLAIN PLAN
SET STATEMENT_ID = <identifier> FOR
<SQL Statement>
The following example illustrates Oracle's text-based Explain, and shows how Ora-
cle formats the output.
