Home » Oracle » 1z0-117 » Which three statements are true about EXPLAIN PLAN?
You executed the following statement:
Which three statements are true about EXPLAIN PLAN?
A. The execution plan is saved in PLAN_TABLE without executing the query.
B. The execution plan for the query is generated and displayed immediately as the output.
C. The execution plan generated may not necessarily be the execution plan used during query execution.
D. The execution plan is saved in DBA_HIST_SQL_PLAN without executing the query.
E. The execution plan generated can be viewed using the DBMS_XPLAIN.DISPLAY function.
F. The execution plan generated can be fetched from the library cache by using the DBMS_XPLAIN.DISPLAY function.
Correct Answer: ACE
Explanation/Reference:
* (A, not D): The explain plan process stores data in the PLAN_TABLE.
*EXPLAIN PLAN
The EXPLAIN PLAN method doesn’t require the query to be run(A), greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE.
E: Use the DBMS_XPLAN.DISPLAY function to display the execution plan.
*The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views.
Note:
*
First the query must be explained.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = ‘SMITH’;
Explained.
SQL>
Then the execution plan displayed.(not B)
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | | | | | |
| NESTED LOOPS | | | | | | |
| TABLE ACCESS FULL |EMP | | | | | |
| TABLE ACCESS BY INDEX RO|DEPT | | | | | |
| INDEX UNIQUE SCAN |PK_DEPT | | | | | |
--------------------------------------------------------------------------------
8 rows selected.
SQL>
For parallel queries use the "utlxplp.sql" script instead of "utlxpls.sql".
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
