You identified poorly performing SQL by analyzing the V$SQL and V$SQLSTATS views. You want
to investigate the plans for these SQL statements.Which two methods can you use to pull out the
execution plan from the library cache for the already executed SQL? (Choose two.)
A.
Query V$SQL_PLAN to view the execution plan.
B.
Query DBA_HIST_SQL_PLAN to view the execution plan.
C.
Copy and paste the SQL text from the V$SQL view and use EXPLAIN PLAN to generate the
execution plan.
D.
Use the dbms_xplan.display_cursor function with the SQL ID and child number to generate the
execution plan.
V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.
DBA_HIST_SQL_PLAN displays the execution plan information for each child cursor in the workload repository.
Doc ID 879677.1
…
The timestamp column of DBA_HIST_SQL_PLAN hold information about when a particular execution plan was produced
…
So, DBA_HIST_SQL_PLAN stores plans in a, let´s say, historical way.
The plans does not come from the library cache directly.
V$SQL lists statistics on shared SQL areas 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.
A is right. v$sql_plan from library cache
B is wrong. DBA_HIST_SQL_PLAN not from lib cache
C is wrong.
D is right. DBMS_XPLAN.display_cursor is right