You executed the following statements: 
Which two statements are true about the query execution?
A.
The execution plan is generated and fetched from the library cache.
B.
The query executes and displays the execution plan and statistics.
C.
The query executes and inserts the execution plan in PLAN_TABLE.
D.
The query executes and execution plan is stored in the library cache and can be viewed using
v$SQL_PLAN.
E.
The query will always use the plan displayed by the AUTOTRACE output.
Explanation:
B: set autotrace traceonly:Displays the execution plan and the statistics (as set
autotrace on does), but doesn’t print a query’s result.
Note:
/ Autotrace
Autotrace can be configured to run the SQL & gives a plan and statistics afterwards or just give
you an explain plan without executing the query. To achieve this use the following:
* Explain only
set autotrace traceonly explain
* Execute with stats and explain plan
set autotrace on explain stat (with data returned by query)
or
autotrace traceo expl stat (without data returned by query)
* To make the output from an autotrace more readable
col plan_plus_exp format a100
* Turn off autotrace
set autotrace off
/ V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library
cache.
B,D
It’s strange.
B is correct
C is correct too because with AUTOTRACE ON TRACEONLY EXPLAIN STAT, the query is executed but under de AUTOTRACE ON EXPLAIN Oracle uses the Explain Plan command. And EXPLAIN PLAN command explains the plan into the PLAN_TABLE table.
D is correct too because by executing query the Execution plan of cursor is also stored in V$SQL_PLAN.
I don’t think C correct.
Just perform the example in your own database and make sure than PLAN_TABLE doesn’t contain the execution plan for the query.
Finally agree with vasya after testing.
So B,D are correct.
A,B
But question ambiguous :(. A and D talks about the same, but AUTOTRACE automatically prints PLAN from v$SQL_PLAN, and we don’t need query v$sql_plan. Plan can be viewed using
v$SQL_PLAN Or DIrectly from console after this command.