This table function simply displays the contents of the plan table.
Execute an explain plan command on a SELECT statement:
EXPLAIN PLAN FOR SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'benoit';Display the plan using the DBMS_XPLAN.DISPLAY table function:
SET LINESIZE 130 SET PAGESIZE 0 SELECT * FROM table(DBMS_XPLAN.DISPLAY);This query produces the following output:
Plan hash value: 3693697075 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 57 | 6 (34)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 57 | 6 (34)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (34)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (34)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPTNO"="D"."DEPTNO") 2 - filter("E"."ENAME"='benoit') 15 rows selected.
You can also use the SET STATEMENT_ID directive to identify a specific plan:
EXPLAIN PLAN SET STATEMENT_ID='EXPL1' FOR SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename='benoit'; SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table','EXPL1','serial'));
If no statement_id is specified, the function will show you the plan of the most recent explained statement.
Source: Oracle Documentation
No comments:
Post a Comment