The first utility is the SQL EXPLAIN PLAN statement. When you use EXPLAIN
PLAN, followed by the keyword FOR and the SQL statement whose execution plan
you want to view, the Oracle cost-based optimizer returns a description of the execution
plan it will use for the SQL statement and inserts this description into a database
table. You can subsequently run a query on that table to get the execution plan, as
shown in SQL*Plus in Figure 4-5.
The execution plan is presented as a series of rows in the table, one for each step
taken by Oracle in the process of executing the SQL statement. The optimizer also
includes some of the information related to its decisions, such as the overall cost of
each step and some of the statistics that it used to make its decisions.
The optimizer writes all of this information to a table in the database. By default, the
optimizer uses a table called PLAN_TABLE; make sure the table exists before you
use EXPLAIN PLAN. (The utlxplan.sql script included with your Oracle database
Understanding the Execution Plan | 119
creates the default PLAN_TABLE table.
Pages:
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277