Format explain plan with functions

The output of the EXPLAIN Plan command is one of the most important diagnostic tools for resolving SQL inefficiencies. This command loads a work table (the default name is Plan_table), and the table contains the execution plan steps that the Oracle optimizer calculates for the SQL statement. For example:

EXPLAIN Plan for

SELECT empno, ename

From EMP

WHERE ename like ' s% ';

Because the rows in plan_table form a hierarchy, querying them requires the application of two complex child statements in the SELECT statement--start with and connect by. The first Dbms_xplan that appears in Oracle 9i and is expanded in 10g makes it easier to format and display execution plans.

Dbms_xplan. Display is a table-valued function that displays the contents of a plan_table. Unlike the general scalar function substr or total function sum, the table function returns a complete set of rows (rowset). They are used in the from child statement of the SELECT statement and must precede the Word table. Such as:

SELECT *

From TABLE (Dbms_xplan. DISPLAY);

List A shows the results of the above explain plan statement, and is represented by Dbms_xplan. Display format.

It is noteworthy that Dbms_xplan also displays the "Live" execution plan from pointers stored in the system global area, and SGA. List B shows the same SQL statement that was executed by user Scott. By looking at the V$session view, you can find the last SQL ID to perform the Scott task. This, in turn, can be entered into the Dbms_xplan. Display_cursor, get the execution plan used by the pointer.

Finally, Dbms_xplan. The Display_awr function can be used to search the historical SQL statements obtained by Oracle 10g's automated load information base (Automatic workload Repository, AWR) and to display its execution plan. It shows a scrolling window with a seven-day history that you can access.

All three functions are powerful tools for coordinating SQL statements.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience, working as a technical trainer, consultant and database administrator.