In addition, you can use this table function to display any plan (with or without statistics) stored in a table as long as the columns of this table are named the same as columns of the plan table (or V$SQL_PLAN_STATISTICS_ALL if statistics are included). This table function displays the contents of the plan table. Table 6-1 DBMS_XPLAN Display Functions Display Functions Name column in the same row shows the argument to CONTAINERS.
Operation column as the value CONTAINERS FULL.
#ALLPLAN TUTO FULL#
| 8 | CONTAINERS FULL | DBA_TABLES | 234K| 2970K| 139 (100)| 00:00:01 | | |Īt Row 8 of this plan, CONTAINERS is shown in the | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | SQL> explain plan for select con_id, count(*) from containers(sys.dba_tables) where con_id hash value: 891225627 If two lines are indented equally, then the top line is normally executed first. The next step is the parent of that line. The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. Query Block Name / Object Alias (identified by operation id):ģ - SEL$1 / - SEL$1 / Information (identified by operation id):Ģ - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")Ĭolumn Projection Information (identified by operation id):ġ - (#keys=1) INTERNAL_FUNCTION("E"."SALARY"), SELECT e.last_name, d.department_name, e.salaryįor example, the following plan shows a hash join: SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL')) The following example explains the plan for a query of the employees table: Include the EXPLAIN PLAN FOR clause immediately before the SQL statement. Start SQL*Plus or SQL Developer, and log in to the database as a user with the requisite permissions. Parallel execution, such as the distribution method of join inputs Partitioning, such as the set of accessed partitions Optimization, such as the cost and cardinality of each operation In addition to the row source tree, the plan table contains information about the following: In the preceding plan, the optimizer filters on last names that begin with T and matches on department_id. In the preceding plan, the optimizer chooses a nested loops join.ĭata operations like filter, sort, or aggregation In the preceding plan, the optimizer chooses to access employees using an index scan and departments using a full scan.Ī join method for tables affected by join operations in the statement In the preceding plan, employees is the outer row source and departments is the inner row source.Īn access path for each table mentioned in the statement The join order of the tables referenced by the statement The tree shows the following information: The row source tree is the core of the execution plan.
Predicate Information (identified by operation id):Ĥ - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
| Id | Operation | Name |Rows | Bytes |Cost (%CPU)| Time | SELECT employee_id, last_name, first_name, department_name fromĮmployees e, departments d WHERE e.department_id = d.department_id and The following plan shows a join of the employees and departments tables: Every step either retrieves rows of data physically from the database or prepares them for the user issuing the statement.