Which three statements are true about SQL plan directives?
A.
They are tied to a specific statement or SQL ID.
B.
They instruct the maintenance job to collect missing statistics or perform dynamic sampling to
generate a more optimal plan.
C.
They are used to gather only missing statistics.
D.
They are created for a query expression where statistics are missing or the cardinality
estimates by the optimizer are incorrect.
E.
They instruct the optimizer to create only column group statistics.
F.
Improve plan accuracy by persisting both compilation and execution statistics in the SYSAUX
tablespace.
Explanation:
During SQL execution, if a cardinality misestimate occurs, then the database
creates SQL plan directives. During SQL compilation, the optimizer examines the query
corresponding to the directive to determine whether missing extensions or histograms exist (D).
The optimizer records any missing extensions. Subsequent DBMS_STATS calls collect statistics
for the extensions.
The optimizer uses dynamic sampling whenever it does not have sufficient statistics
corresponding to the directive. (B, not C)
E: Currently, the optimizer monitors only column groups. The optimizer does not create an
extension on expressions.
Incorrect:
Not A: SQL plan directives are not tied to a specific SQL statement or SQL ID.Note:
* A SQL plan directive is additional information and instructions that the optimizer can use to
generate a more optimal plan. For example, a SQL plan directive can instruct the optimizer to
record a missing extension.
B, D & F
From Ora 12c Doc
SQL plan directives
…The optimizer does not create an extension on expressions..
…SQL plan directives are not tied to a specific SQL statement or SQL ID…
…Currently, the optimizer monitors only column groups…
…The database initially creates directives in the shared pool. The database periodically writes the directives to the SYSAUX tablespace….
https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL347
So not A,D,F
Correct is BCE
Agree – BDF
Good tutorial: http://www.slideshare.net/pachot/soug-2014-sqlplandirectives
Clearly not A. Per C, 12c proactive feedback (vs reactive in 11g) for misestimated cardinality.
BDF +1
This information is invaluable. How can I find out more?|
I am regular visitor, how are you everybody? This piece of writing posted at this site is genuinely nice.|
BDF
BDF