Once there is an explain plan
table in place regardless of which method was
chosen from those previously listed, and when
Oracle informs the DBA of the internal
processing algorithm for a given SQL statement,
issue an EXPLAIN SQL command as shown here:
EXPLAIN PLAN SET STATEMENT_ID
= 'BERT1' FOR SELECT * FROM MOVIES.CUSTOMER;
Oracle has now populated the
explain plan table with the procedural logic
steps it will most likely perform to return the
desired results. Prior to Oracle 9i, the only
way to format and display the results stored
within the explain plan table was to construct a
complex hierarchical (tree-walk) query such as
the one shown here:
select lpad('
',2*level)||operation||' '||options||' '||object_name||
decode(partition_start,NULL,NULL,
' PARTS('||partition_start||'-'||partition_stop||')')||
decode(level,1,'
[Cost = '||Cost||']',Null) QUERY_PLAN,
Object_Node,
Other_Tag
from plan_table
where statement_id = '&plan_user'
connect by prior statement_id
= statement_id and
prior id =
parent_id
start with id = 1
order by statement_id;
In order
to keep the complexities of such a query less
painful, many people would encapsulate the
entire explain plan information retrieval
process into a single script such as the explain_plan.sql
script shown next. Then one would simply need to
cut and paste the SQL statement of concern where
the script contains the text “<<< place your SQL
query here >>>”.
explain_plan.sql script
set echo
off
set verify
off
set pagesize 60
set linesize 132
define plan_user='BERT'
DELETE FROM plan_table WHERE
statement_id = '&plan_user';
COMMIT;
EXPLAIN PLAN SET STATEMENT_ID
= '&plan_user' INTO plan_table FOR
<<< place your SQL query here
>>>;
col QUERY_PLAN format a80
col Object_Node format a11
col Other_tag format a19
break on ID
select lpad('
',2*level)||operation||' '||options||' '||object_name||
decode(partition_start,NULL,NULL,
' PARTS('||partition_start||'-'||partition_stop||')')||
decode(level,1,'
[Cost = '||Cost||']',Null) QUERY_PLAN,
Object_Node,
Other_Tag
from plan_table
where statement_id = '&plan_user'
connect by prior statement_id
= statement_id and
prior id =
parent_id
start with id = 1
order by statement_id;
There were scenarios where
this simplistic approach was not 100% accurate
nor reliable, such as some algorithms for some
advanced constructs that could not be shown in a
simple single level hierarchy. Thus, Oracle
provided the DBMS_XPLAN PL/SQL package and the
DISPLAY table function. The valid values for
FORMAT are BASIC, TYPICAL, SERIAL and ALL.
|
Argument
|
Type
|
In/Out
|
Default Value
|
|
TABLE_NAME
|
VARCHAR2
|
IN
|
PLAN_TABLE
|
|
STATEMENT_ID
|
VARCHAR2
|
IN
|
NULL
|
|
FORMAT
|
VARCHAR2
|
IN
|
‘TYPICAL’
|
|
FILTER_PREDS
|
VARCHAR2
|
IN
|
NULL
|
Table
5.2:
DISPLAY Table
Functions
So to fetch the explain plan
steps, there is a very simple and singular SQL
statement, as shown here. Plus, it is now so
much simpler than before. The internal process
to generate accurate explain plans has been both
encapsulated and reduced to a single function
that Oracle now maintains.
SET LINESIZE 132
SET PAGESIZE 0
SELECT * FROM
table(DBMS_XPLAN.DISPLAY);
The DBMS_XPLAN.DISPLAY table
function will yield well formatted and easily
readable output like the following example
explain plan for a query:
Figure
5.8:
Example
Explain Plan Output
 |
For more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
