Oracle OCP 9i to 11g SQL Plan Management

Card Set Information

Author:
Tralala
ID:
208732
Filename:
Oracle OCP 9i to 11g SQL Plan Management
Updated:
2013-03-21 09:25:05
Tags:
Oracle OCP 9i 11g SQL Plan Management
Folders:

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

Home > Flashcards > Print Preview

The flashcards below were created by user Tralala on FreezingBlue Flashcards. What would you like to do?


  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?
    In SYSAUX
  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?
    10%
  9. What percentage of SYSAUX can be used by SQL Management base?
    1-50%
  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?
    DBA_SQL_MANAGEMENT_CONFIG
  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?
    DBS_SQL_MANAGEMENT_CONFIG
  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?
    • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
    • 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?
    DBMS_SPM.LOAD_PLANS_FROM_SQLSET
  26. How are SQL plan baselines plan loaded from Cursor Cache?
    DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
  27. What parameter enables the use SQL plan baselines, what is the default?
    • OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE
    • 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?
    DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function

What would you like to do?

Home > Flashcards > Print Preview