Examine Exhibit1 to view the query and its AUTOTRACE output.
Which two statements are true about tracing?
Exhibit:
A. The displayed plan will be stored in PLAN_TABLE.
B. Subsequent execution of this statement will use the displayed plan that is stored in v$SQL.
C. The displayed plan may not necessarily be used by the optimizer.
D. The query will not fetch any rows; it will display only the execution plan and statistics.
E. The execution plan generated can be viewed from v$SQLAREA.
Correct Answer: AC
Explanation/Reference:
A:The PLAN_TABLE is automatically created as a public synonym to a global temporary table. This temporary table holds the output of EXPLAIN PLAN statements for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans
Incorrect:
B: V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.
D:autotrace traceonly - Displays execution plan and statistics without displaying the returned rows. This option should be used when a large result set is expected.
E:V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
Note:
*The autotrace provides instantaneous feedback including the returned rows, execution plan, and statistics. The user doesn’t need to be concerned about trace file locations and formatting since the output is displayed instantly on the screen. This is very important data that can be used to tune the SQL statement.
*SET AUTOTRACE ON
The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY
Similar to SET AUTOTRACE ON, but suppresses the printing of the user’s query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
