You can optionally include a set Statement_ID clause to label
the explain plan in PLAN_TABLE. Following the keyword for, the query to be analyzed is listed.
The account that is running this command must have a plan table in its schema. Oracle
provides the create table commands needed for this table. The file, named utlxplan.sql, is located
in the $ORACLE_HOME/rdbms/admin directory. Users may run this script to create the table in
their schemas.
?–
?–
?–
Chapter 8: Database Tuning 251
NOTE
You should drop and re-create the plan table following each Oracle
upgrade because new columns may be added by the upgrade scripts.
Query the plan table using the DBMS_XPLAN procedure:
select * from table(DBMS_XPLAN.DISPLAY);
You can also use the Oracle-supplied script in $ORACLE_HOME/rdbms/admin/utlxpls.sql to
query the plan table for serial execution, or the $ORACLE_HOME/rdbms/admin/utlxplp.sql for
parallel execution.
This query will report on the types of operations the database must perform to resolve the
query. The output will show the steps of the query execution in a hierarchical fashion, illustrating
the relationships between the steps. For example, you may see an index-based step that has a
TABLE ACCESS BY INDEX ROWID step as its parent, indicating that the index step is processed
first and the RowIDs returned from the index are used to retrieve specific rows from the table.
You can use the set autotrace on command in SQL*Plus to automatically generate the explain
plan output and trace information for every query you run.
Pages:
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422