OCP 8 SQL Tuning

  1. Which SQL statements does Automatic SQL Tuning tune?
    SQL statements identified as top SQL statements in the AWR
  2. In which two ways can you invoke SQL Repair Advisor?
    Using Enterprise Manager, or from PL/SQL using the DBMS_SQLDIAG package
  3. When using SQL Profiling, what does the Automatic Tuning Optimizer (ATO) use to optimize the SQL statement?
    The historical information about the specified SQL statement's execution
  4. Which action can you take to prevent Automatic SQL Tuning from automatically accepting and implementing SQL profiles?
  5. Which tool builds a SQL Profile?
    Automatic Tuning Optimizer (ATO)
  6. How can you use Enterprise Manager to invoke SQL Repair Advisor for a SQL statement that crashed and created an incident, if the incident is no longer active?
    From the Advisor Central page, click the SQL Advisors link and click the Click here to go to Support Workbench link in the SQL Advisors section.
  7. What output is produced when running the SQL Test Case Builder?
    A SQL script you can use to re-create the environment and the affected objects
  8. For SQL Access Advisor to make recommendations on partitioning of a table, what must be true of the workloads?
    They must have some predicates and joins on NUMBER or DATE columns.
  9. When using the DBMS_ADVISOR package, you should include code to perform which three actions before executing the tuning task?
    Create the task, set the task's parameters, and associate the task with a workload.
  10. By default, when does the Automatic SQL Tuning Advisor task run?
  11. What information is gathered by the SQL Test Case builder about a SQL-related problem?
    The SQL query, optimizer statistics, initialization parameter settings, and table, index, function, procedure, and package definitions, but not the data
  12. What are the four valid input sources for the SQL Tuning Advisor?
    Automatic Database Diagnostic Monitor (ADDM), high-load SQL statements, cursor cache, and SQL Tuning Sets (STS)
  13. After the SQL Tuning Advisor recommends that a SQL Profile be used and you have accepted it, in which location is the information about the profile stored?
    In the data dictionary
  14. When reviewing the result details from Automatic SQL Tuning, what does a check mark in the Statistics column indicate?
    That statistics should be gathered
  15. When using SQL Profiling, what must you do to ensure that the SQL Profile created by the Automatic Tuning Optimizer (ATO) is used to optimize the query?
    Nothing, the query optimizer will automatically use the optimizer settings generated by the ATO for the given profile.
  16. What is the result of executing the QUICK_TUNE procedure to analyze a SQL statement?
    A task and workload is created, the task is executed, and the results are stored.
  17. When reviewing the result details from Automatic SQL Tuning, which formula is used to calculate the benefit percentage for each recommendation?
    benefit % = (time_old - time_new) / time_old
  18. For SQL Access Advisor to make recommendations on partitioning of a table, how many rows must the table have?
    At least 10,000
  19. When reviewing the result details from Automatic SQL Tuning, what does a high benefit percentage in the Index column indicate?
    That additional recommended indexes would improve performance
  20. When optimizing queries with SQL Access Advisor, which recommendations can the advisor make?
    To modify the table's partitioning, or to create, modify, or drop indexes, materialized views, or materialized view logs
  21. Which recommendations are automatically implemented as part of the Automatic SQL Tuning task?
    SQL profile recommendations with a benefit percentage of more than 66 percent
  22. Which package is used as the command-line interface for Automatic SQL Tuning?
    The DBMS_SQLTUNE package
Card Set
OCP 8 SQL Tuning
OCP 8 SQL Tuning