Oracle OCP 9i to 11g SQL Performance Analyzer

Card Set Information

Oracle OCP 9i to 11g SQL Performance Analyzer
2014-05-02 05:23:53
Oracle OCP 9i 11g SQL Performance Analyzer

Oracle OCP 9i to 11g SQL Performance Analyzer
Show Answers:

  1. What does SQL Performance Analyzer do?
    Allows an accurate forecast of the potential impact of system changes on SQL performance.
  2. What tool allows a system to be tuned before changes are made?
    SQL Performance Analyzer
  3. What does the SQL Performance Analyzer produce?
    A report showing net impact of changes in the workload performance and performance divergence of each SQL statement
  4. Where is SQL Performance Analyzer run?
    On a production database or a test database that closely resembles production.
  5. Where do Oracle recommended SQL Performance Analyzer is run?
    On a test system
  6. What is the difference between SQL Performance Analyzer and SQL Tuning advisor?
    • SQL Performance Analyzer is designed to determine the impact of changes on a system
    • SQL Tuning Advisor help tune SQL on a currently running system.
  7. From where does SQL Performance Analyzer source SQL statements?
    From a production system.
  8. How are captured SQL statements from a database stored?
    As a tuning set
  9. Define a tuning set?
    A database object that includes one or more SQL statements with execution stats and execution context.
  10. What does DBA_ADVISOR_TASKS show?
    Displays descriptive information about the SQL Performance Analyzer task that was created.
  11. What does DBA_ADVISOR_EXECUTIONS show?
    Displays information about task executions.
  12. What does DBA_ADVISOR_SQLSTATS show?
    Displays a list of all SQL compilations and execution statistics.
  13. What does V$ADVISOR_PROGRESS show?
    Displays the operation progress of SQL Performance Analyzer.
  14. What are the steps in analyzing performance changes?
    • 1. Capture Workload
    • 2. Create a performance analyzer task
    • 3. Build a pre-change SQL trial
    • 4. Perform system changes
    • 5. Build a post-change SQL trial
    • 6. Compare results and tune the SQL that requires tuning
  15. Which package is used to manage tuning sets?
  16. How does one create a tuning set?
  17. How does one populate a tuning set?
  18. What creates a staging table to allow import and export of tuning sets?
  19. Which package contains procedures and functions specific to the SQL Performance Analyzer
  20. Which procedure is used to create analysis task?
  21. Which procedure executes a analysis task?
  22. Which procedure displays the results of an analysis task?
  23. What does DBA_ADVISOR_FINDINGS show?
    Displays the SQL Performance Analyzer findings.
  24. What does DBA_ADVISOR_SQLPLANS show?
    Displays a list of all execution plans.