Oracle OCP 9i to 11g SQL Tuning

Card Set Information

Author:
Tralala
ID:
275447
Filename:
Oracle OCP 9i to 11g SQL Tuning
Updated:
2014-05-28 12:44:56
Tags:
Oracle OCP 9i 11g SQL Tuning
Folders:

Description:
Oracle OCP 9i to 11g SQL Tuning
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 are the two modes of query optimizer?
    • NORMAL MODE
    • TUNING MODE
  2. What are the four modes of Automatic Tuning Advisor?
    • Statistical Analysis
    • SQL Profiling
    • Access Path Analysis
    • SQL Structural Analysis
  3. How is query optimizer referred to when in TUNING MODE?
    Automatic Tuning Optimizer
  4. What are the step to run SQL Repair Advisor
    • DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK
    • DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK
    • DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK
  5. What does SQL Access Advisor do?
    Offers recommendations for Indexes, Materialized Views, Materialized View Logs and Partitions for a given workload.
  6. What are the modes of operation for SQL Access Advisor?
    • Problem Solving (Default)
    • Evaluation
  7. In which mode of operation will SQL Access Advisor suggest new objects?
    Problem Solving
  8. In which mode of operation will SQL Access Advisor identify unused indexes and MVs?
    Evaluation
  9. How is an advisor tasks task deleted?
    DBMS_SQLTUNE.DROP_TUNING_TASK (task_name => 'tash_name');
  10. Which Oracle product can help make recommendation about new objects to improve performance?
    SQL Access Advisor
  11. What are the steps in creating a SQL Access Advisor analysis session?
    • Create an Advisor task
    • Create a SQL Tuning Set workload
    • Link SQL Tuning Set workload to Advisor task
    • Add any Workload parameters
    • Execute taskView results
  12. When a SQL Access Advisor task has been run, how are the results viewed?
    SELECT DBMS_ADVISOR.GET_TASK_SCRIPT('script_name') FROM DUAL;
  13. Which view has basic information about existing advisor tasks?
    DBA_ADVISOR_TASKS
  14. Which view s shows status information about existing advisor tasks?
    DBA_ADVISOR_LOG
  15. Which view shows findings identified for an existing advisor task?
    DBA_ADVISOR_FINDINGS
  16. Which view shows recommendations for the problems identified by an existing advisor task?
    DBA_ADVISOR_RECOMMENDATIONS
  17. Which view show actions based on recommendations for the problems identified by an advisor task?
    DBA_ADVISOR_ACTIONS
  18. Which Procedure is used to create an Access Advisor Task?
    DBMS_ADVISOR.CREATE_TASK
  19. What permissions is required to create an Access Advisor Task?
    ADVISOR
  20. What procedure is used to copy workload to a SQL Tuning Set?
    DBMS_ADVISOR.COPY_SQLWKLD_TO_STS
  21. What procedure is used to link a task to a SQL Tuning Set?
    DBMS_ADVISOR.ADD_STS_REF
  22. What procedure is used to unlink a task to a SQL Tuning Set?
    DBMS_ADVISOR.DELETE_STS_REF
  23. What procedure is used to define task parameters?
    DBMS_ADVISOR.SET_TASK_PARAMATER
  24. How is an Access Advisor Task run?
    DBMS_ADVISOR.EXECUTE_TASK('MYTASK')
  25. How is candidate SQL chosen for Automatic SQL Tuning?
    High repeating, high load statement from AWR
  26. What SQL is ignored for Automatic SQL Tuning?
    • Recursive SQL
    • Recently tunes statements (within 1 month)
    • Parallel Queries
    • DML
    • DDL
  27. In Automatic SQL Tuning when are SQL Profiles automatically accepted?
    If performance is improved X3ACCEPT_SQL_PROFILES is TRUE
  28. If there are stale statistics, how are SQL Profiles accepted?
    They are not
  29. How can SQL Profiles that have automatically been accepted be identified?
    TYPE=AUTO in  DBA_SQL_PROFILES
  30. How is ACCEPT_SQL_PROFILES set?
    • BEGIN
    • DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (task_name => ‘SYS_AUTO_SQL_TUNING_TASK’,
    • parameter => ‘ACCEPT_SQL_PROFILES’,
    • values => ‘TRUE’);
    • END;
  31. In which view can current tuning advisor parameter be viewed?
    DBA_ADVISOR_PARAMETERS
  32. How is a SQL Tuning Task report viewed?
    DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK
  33. What are the secions on a SQL Tuning Task report?
    • General information - High-level information about the SQL tuning task.
    • Summary
    • Tuning findings
    • Explain plans The old and new execution plans.
    • Errors

What would you like to do?

Home > Flashcards > Print Preview