Oracle OCP 9i to 11g SQL Plan Management

Card Set Information

Oracle OCP 9i to 11g SQL Plan Management
2013-03-21 09:25:05
Oracle OCP 9i 11g SQL Plan Management

Oracle OCP 9i to 11g SQL Plan Management
Show Answers:

  1. What does SQL Plan Management allow?
    Prevention of performance regressions for sudden changes to execution plan of a SQL statement.
  2. What can SQL Plan execution be affected by?
    • Optimizer Version
    • Stats
    • Application of SQL profiles
    • Dropping of an index
    • System changes
  3. What produces SQL profiles?
    SQL Tuning Advisor
  4. How does SQL Plan Management work?
    By creating SQL plan baselines known to be efficient.
  5. What do the baselines created by SQL Plan Management do?
    Preserve the performance of SQL statements regardless of changes to a system.
  6. Where is the SQL Management base stored?
  7. What is the SQL Management base comprised of?
    • Statement logs
    • Plan histories
    • SQL plan baselines
    • SQL profiles
  8. What percentage of SYSAUX is used by SQL Management base by default?
  9. What percentage of SYSAUX can be used by SQL Management base?
  10. How is the space used by SQL Management base monitored?
    By a weekly task
  11. If SYSAUX is not available, what happens to SQL plan management and SQL tuning features?
    They are no longer available
  12. What will happen when the space defined for SQL Management base is exceeded?
    An alert is generated
  13. How long will an unused plan remain in SMB before being purged by default?
    53 Weeks
  14. How are unused plans in SMB identified
    By the LAST_EXECUTED timestamp
  15. How can an unused plan be prevented from being purged from SMB be altered?
    DBMS_SPM.CONFIGURE('plan_retention_weeks', 105);
  16. What is the range of time that plan_retention_weeks can be set?
    5 to 523 weeks
  17. Which view shows the current settings for SMB?
  18. How can an alert from SQL Management base be cancelled?
    • Increase SQL Management base limit
    • Increase size of SYSAUX
    • Change the limits for SQL Management base
  19. What package is used to change the percentage limit of space in SYSAUX by SQL Management base?
    DBMS_SP<.CONFIGURE('space_budget_percent', 30)
  20. Which view is used to look at current SQL Management base settings?
  21. What happens when Automatic Plan Capture is enabled?
    Oracle will create a maintain a plan history provided by the optimizer
  22. What is captured in Automatic Plan Capture?
    • SQL Text
    • Outline
    • Bind variables
    • Compilation Environment
  23. What parameter enables Automatic Plan Capture and what is default?
    • Default=FALSE
  24. From what sources can SQL plan baselines loaded manually?
    • SQL Tuning Sets
    • AWR Snapshots
  25. How are SQL plan baselines plan loaded from a SQL Tuning set?
  26. How are SQL plan baselines plan loaded from Cursor Cache?
  27. What parameter enables the use SQL plan baselines, what is the default?
    • Default=TRUE
  28. What is a Fixed SQL baseline plan?
    • A plan where at least one enabled plan has FIXED attribute set to Yes.
    • Used to limit possible plan
  29. What happens when there are fixed and non-fixed plans for a given SQL baseline?
    • Oracle will prefer the fixed plan even if the non-fixed plan has a lower cost.
    • Oracle will not add plans to a fixes SQL plan baseline
  30. What happens is SQL Tuning Advisor creates a profile for a fixed baseline plan?
    • The profile will not be used...IT'S NOT FIXED
    • Need to manually fix is by setting FIXED attribute to YES.
  31. How are SQL Plan baselines displayed?