Examine Exhibit1 to view the query and its AUTOTRACE output.
Which two statements are true about tracing?
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.
Explanation:
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.
imho C,D correct
No A, because we have SET AUTOTRACE TRACEONLY, but not EXPLAIN PLAN FOR.
No rows go into plan_table in this case.
With set AUTOTRACE, the subsequent query is executed but behind AUTOTRACE Oracle execute in fact an EXPLAIN PLAN command and display this plan. So “A” is correct.
B is false because no plan is store in v$SQL
C should be correct because the plan from PLAN_TABLE may be different from plan stored in V$SQL_PLAN (i.e. the execution plan of the cursor executed). It could be the case when we used bind variable to filter skewed data.
D is false because the query is executed and rows are fecthed but not display in trace.
E No plan are stored in v$SQLAREA
I’ve checked the plan_Table after executing the statment and you’re right, plant_table is empty. So it seems that AUTOTRACE doesn’t store execution plan in PLAN_TABLE. And so, “A” should be incorrect.
I believe correct answer should be A & D
A :
The main difference between the AUTOTRACE and EXPLAIN PLAN commands in Oracle is that AUTOTRACE actually executes the query (in the way TRACE does) and automatically queries the plan table, whereas EXPLAIN PLAN does neither.
Since this option – Autotrace will execute query hence it will show actual plan getting used.
so C is not correct.
D , should be the correct,
Yes, agree.
A,C are correct
but D is not, as it will fetch the data but not print
Agree, A and C are correct
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.
http://docs.oracle.com/cd/B10500_01/server.920/a96533/autotrac.htm
A,D correct. C not.
Why do you guys say that the displayed plan will be stored in PLAN_TABLE? It is not stored there. This table is used as a temporary table for AUTOTRACE but the result is stored in v$sql_plan view. PLAN_TABLE table is totaly empty after AUTOTRACE. I think A is incorrect.
I am totally confused about this question!
Today I made some test to see what will happen with the query under sql*plus:
step 1: set autotrace traceonly
step 2: select /*xxx*/ from dual;
step 3: see results in v$sql and v$sql_plan
v$sql: there are 2 rows, one with SQL_TEXT=EXPLAIN PLAN SET STATEMENT_ID=’PLUS60971170′ FOR select /*xxx*/ from dual
and second one with SQL_TEXT=select /*xxx*/ from dual
v$sql_plan: there is an execution plan for: “select /*xxx*/ from dual”
Conclusion: when doing AUTOTRACE Oracle will create explain plan for the query and will execute that query too. That means that PLAN_TABLE is used for a while and then ereased as it is used temporarily. The plan for autotraced query is stored in v$sql_plan and can be displayed to the user.
So “A” is partially true. What the author wanted to say by that – don’y know at all…
I think “C” and “D” is true.
One more thing to: C, D.
When doing SET AUTOTRACE TRACEONLY STATISTICS Oracle does not create explain plan at all. There is no entry in v$sql_plan with EXPLAIN PLAN. There is one row with the executed query however.
“The execution plan output is generated using the EXPLAIN PLAN command.” – Database Performance Tuning Guide and Reference
So what we see on screen (Exhibit1) is the result of the explain plan. PLAN_TABLE is deleted and the execution plan is moved to the v$sql_plan table. It is still the plan made by explain plan so it can not neccessarily be used by the optimizer.
B,C
C is correct 100%
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:296280200346630999
B and E are obvious not correct.
D is neither – to show statistics, autotrace MUST fetch rows but it will not output results (TRACEONLY).
A is true, but it will not premanent store plan in PLAN_TABLE.
In my opinion for A, author could be more specific…
So, A and C are correct.