A. SQL statement performance is improved.
B. The SQL statement no longer needs to be cached in the library cache.
C. Improved diagnosability, as the execution plan of a cached SQL statement in memory is preserved.
D. The execution plan is available for diagnosis after the SQL statement is aged out of the library cache.
Answer: C
Explanation:
Oracle stores the execution plan of a SQL statement to enable a database administrator (DBA) to analyze the execution plan of a poorly performing SQL statement without having to rerun the query. Oracle9i has introduced the V$SQL_PLAN view to display the cached execution plan, the compilation environment, and the execution statistics of each variable. It also helps the database administrators to determine the actual plan of a query at the time of reported performance problem.
Incorrect Answers
A: The Cached Execution Plan feature helps to diagnose the execution plan, not to improve SQL statement performance itself.
B: The SQL statement still needs to be cached in the library cache. The Cached Execution Plan does not eliminate this need.
D: The execution plan is available for diagnosis when the SQL statement is in the library cache.
OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 217-218
Chapter 4: Performance and Scalability Enhancements
Oracle 9i New Features, Robert Freeman, p. 185-186
Chapter 6: Oracle9i SQL, PL/SQL New Features
No comments:
Post a Comment