1. Home
  2. Flashcards
  3. Preview

Oracle OCP 9i to 11g SQL Performance Analyzer

  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?
    DBMS_SQLTUNE
  16. How does one create a tuning set?
    DBMS_SQLTUNE.CREATE_SQLSET
  17. How does one populate a tuning set?
    DBMS_SQLTUNE.LOAD_SQLSET
  18. What creates a staging table to allow import and export of tuning sets?
    DBMS_SQLTUNE.CREATE_SGTAB_SQLSE
  19. Which package contains procedures and functions specific to the SQL Performance Analyzer
    DBMS_SQLPA
  20. Which procedure is used to create analysis task?
    DBMS_SQLPA.CREATE_ANALYSIS_TASK
  21. Which procedure executes a analysis task?
    DBMS_SQLPA.EXECUTE_ANALYSIS_TASK
  22. Which procedure displays the results of an analysis task?
    DBMS_SQLPA.REPORT_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.
Author
Tralala
ID
206661
Card Set
Oracle OCP 9i to 11g SQL Performance Analyzer
Description
Oracle OCP 9i to 11g SQL Performance Analyzer
Updated

  1. Home
  2. Flashcards
  3. Preview