Somethings about EXPLAIN PLAN

1. Creation of plan table:
@?/rdbms/admin/utlxplan.sql

2. Explaining an sql statment:

EXPLAIN PLAN FOR
SELECT ..............................statement....

3. View EXPLAIN PLAN out put:

set pagesize 0
set linesize 130
@?/rdbms/admin/utlxpls.sql

The above sql shows plan table output for serial processing.

@?/rdbms/admin/utlxpls.sql

The above sql shows output with parallel execution colums.

Delete Operation Explain Plan















DELETE FROM EPDBA01.t_bo_sec_new
WHERE ROWID IN (SELECT tbo.ROWID
FROM EPDBA01.t_bo_sec_new tbo, EPDBA01.t_plan tp
WHERE tbo.plan_id = tp.plan_id
AND NOT tbo.person_id IN (SELECT person_id
FROM EPDBA01.t_sys_admin)
AND tbo.role_id = 2000
AND tp.plan_access = 'S')
Plan hash value: 4027940639


Step Explaination:

1. This plan step retrieves all rows from table T_PLAN.
2. This plan step retrieves all rows from table T_BO_SEC_NEW.
3. This plan step accepts two sets of rows, each from a different table.
A hash table is built using the rows returned by the first child.
Each row returned by the second child is then used to probe the hash table to find row pairs
which satisfy a condition specified in the query's WHERE clause.
Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables.
It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make
the wrong choice.
4. This plan step retrieves all ROWIDs from the B*-tree index PK_T_SYS_ADMIN by walking the index starting with its smallest key.
5. This plan step accepts multiple sets of rows. Rows from the first set are eliminated using the data found in the second through n sets.
6 This plan step accepts a row set (its only child) and sorts it in order to identify and eliminate duplicates.
7. This plan step represents the execution plan for the subquery defined by the view VW_NSO_1.
8. This plan step retrieves rows from table T_BO_SEC_NEW through ROWID(s) specified in the WHERE clause of the statement.
9. This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.
10. This plan step deletes rows from table T_BO_SEC_NEW which satisfy the WHERE clause of the DELETE statement.
11. This plan step designates this statement as a DELETE statement.