Oracle 12c Upgrade: 14 – SQL Tuning

Card Set Information

Oracle 12c Upgrade: 14 – SQL Tuning
2015-03-12 03:46:53
SQL Tuning
Oracle 12c Upgrade
Oracle 12c Upgrade: 14 – SQL Tuning
Show Answers:

  1. What is Adaptive Query Optimization based on?
    • Adaptive execution plans
    • Adaptive Statistics
  2. What are adaptive execution plans?
    Plan that are adjusted during runtime as supplemental information that could potentially result in better execution plans is gathered
  3. What are Adaptive statistics?
    • Dynamic statistics
    • Automatic reoptimization
    • SQL plan directives
  4. Which 12c feature is meant to solve problems caused by inexact or incomplete statistics leading the Oracle Optimizer to create suboptimal execution plans.
    Adaptive Execution Plans
  5. In Adaptive Execution Plans when is the final final plan decision made?
    During the the first part of the sql execution
  6. What is required to allow Adaptive Execution Plans?
    • The adaptive execution plan feature runs in the reporting-only mode
    • The information is not used in plans
  8. If OTIMIZER_ADAPTIVE_REPORTING_ONLY=TRUE, where can the unused adaptive plan viewed?
  9. In Adaptive Execution Plans what is a subplan?
    Part of the execution plan that the optimizer can switch to at run time.
  10. In Adaptive Execution Plans which plan holds all the sub-plans?
    The default plan
  11. In Adaptive Execution Plans what is the final plan?
    The one that is executed?
  12. When does Adaptive Execution run?
    At the first execution of a query. Subsequent executions use the final plan generated.
  13. In Adaptive Execution Plans, where can the final plan be viewed?
    • In Adaptive Query Optimization what's the difference between EXPLAIN_PLAN and DBMS_XPLAN.DISPLAY_CURSOR?
    • EXPLAIN_PLAN shows the initial, default, plan
    • DBMS_XPLAN.DISPLAY_CURSOR show the final plan used by the query
  14. In V$SQL, what does IS_RESOLVED_ADAPTIVE_PLAN show?
    • Y - has an adaptive plan and a final plan has been selected
    • N - has an adaptive plan and a final plan has not been selected
    • NULL- as no adaptive plan
  15. What is dynamic sampling?
    • Used before 12c
    • Capture statistics on the fly when they are missing.
    • Levels can be set
  16. In 12c what is dynamic statistics
    • Enhanced form of dynamic sampling
    • Optimizer decides whether to collect dynamic statistic and what level to use
  17. What does the parameter OPTIMIZER_DYNAMIC_SAMPLING do?
    Lets you define at instance or session level when dynamic statistics should be gathered
  18. What are the possible values for OPTIMIZER_DYNAMIC_SAMPLING
    • 2 - (default) dynamics statistics will be gathered if at least one table in the query has no statistics.
    • 11 - the database will use dynamic statistics automatically when statistics are missing, stale or deemed insufficient.
    • 0 - the dynamic statistics gathering is disabled.
  19. Is OPTIMIZER_DYNAMIC_SAMPLING=11, what triggers statistics gathering?
    • Statistics are missing
    • Statistics are stale - 10% of rows in a table have been modified
    • Statistics are insufficient
  20. For when does Automatic Reoptimization change a plan?
    For subsequent executions not the current execution
  21. How does Automatic Reoptimization make its decisions?
    Looking for differences between the actual and estimated cardinality statistics, if significant, a new plan is chosen and the actual cardinality statistics stored
  22. What does Automatic Reoptimization use to make decisions?
    • Statistics Feedback
    • Performance Feedback
  23. Which part of Automatic Reoptimization detects the difference between the actual and estimated cardinality statistics?
    Statistics Feedback
  24. Which part of Automatic Reoptimization is concerned with cardinality of statistics?
    Statistics Feedback
  25. How does Statistics Feedback work?
    • Some Tables have no statistics
    • There are multiple conjunctive or disjunctive filter predicates on a table
    • The predicates containing complex operators for which the optimizer cannot accurately compute selectivity estimates
  26. What happens when Statistics Feedback finds a significant differences between the actual and estimated cardinality statistics
    A SQL plan directive is also created so other SQL statements can benefit from the information.
  27. Which part of Automatic Reoptimization is concerned with cardinality of parallel execution?
    Performance Feedback
  28. What is Performance Feedback?
    Helps improve the degree of parallelism chosen for repeatable SQL statements
  29. Which parameter controls Performance Feedback and the automatic degree of parallelism?
  30. What does Performance Feedback compare?
    After the first execution, the optimizer compares the degree of parallelism chosen by the optimizer with the one computed based on the actual query execution performance. If they differ significantly, the optimizer marks the statement for reparsing and stores this information for use in subsequent executions.
  31. What are the options for PARALLEL_DEGREE_POLICY?
    • MANUAL (default) - Disable automatic degree of parallelism.
    • AUTO
  32. What is a SQL Plan Directive?
    A order for the optimizer to collect supplementary data if it thinks these would help generate a better plan
  33. When are SQL Plan Directives created?
    • During compilation and execution
    • What can trigger a SQL Plan Directive to be created?
    • Missing Stats
    • Misestimated Cardinality
    • etc
  34. How often are SQL Plan Directives purged?
    If unused, after 52 weeks
  35. Where are SQL Plan Directives stored?
  36. Why can SQL Plan Directives be used against different SQL statements?
    • Because they are defined on a query expression and not a specific
    • SQL ID
  37. Which command will force Oracle to write SQL Plan Directives from memory to disk?
  38. Which command will force Oracle to remove a SQL Plan Directive from disk?
    exec DBMS_SPD.DRIP_SQL_PLAN_DIRECTIVE (directive_id => 12312121);
  39. Which views are used to monitor SQL Plan Directives?
  40. Describe online statistics gathering for bulk load operations.
    • Oracle will create statistics for a load operation with the data is loaded/
    • ie CTAS, INSERT INTO, /*+ APPEND */
  41. How does Online Statistics Gathering for Bulk-Load or for a partitioned table
    • TABLE LEVEL - Only global stats are gathered, not partition
    • PARTITION LEVEL - Only local stats are gathered, not global
  42. Which table will reveal if a online statistics gathering for bulk load operations was used?
    • NOTES column reads STATS_ON_LOAD
  43. When online statistics gathering for bulk load operations gathers stats, are index stats gathered?
  44. When won’t Online Statistics Gathering for Bulk-Load be used?
    • If object is SYS
    • Nested table
    • IOT
    • External Table
    • Virtual Columns
    • There are a couple of others
  45. Which 12c feature can significantly speed up statistics gathering?
    Concurrent Statistics Gathering
  46. Against what does Concurrent Statistics Gathering work?
    • A parallel operation against multiple objects not a parallel operation on a single object.
    • Multiple partitions on a partitioned table/index OK
  47. How is Concurrent Statistics Gathering enabled?
    • Resource Manager enabled
  48. What are the options for DBMS_STATS.SET_GLOBAL_PERFS('CONCURRENT'…?
    • MANUAL - Concurrency is enabled only for manual statistics gathering
    • AUTOMATIC - Concurrency is enabled only for auto statistics gathering
    • ALL - Concurrency is enabled for all statistics gatherin
  49. Where can the current configuration for concurrent statistics collection be obtained?
  50. Which 3 components are involved in Concurrent Statistics Gathering?
    • Oracle Scheduler
    • Oracle Database Advanced Queuing (AQ)
    • Oracle Database Resource Manager (the Resource Manager)
  51. What are the two new histograms introduced in 12c
    • Top Frequency Histograms
    • Hybrid Histograms
  52. To use Top Frequency Histograms and Hybrid Histograms, what must the Sampling percentage be set to?
  53. What are extended statistics?
    Statistics on a group of columns within a table
  54. In extended statistics, how are column group candidates determined?
    Using workload monitoring (A SQL tuning set)
  55. In extended statistics, how what are the steps in Workload Monitoring?
    • Enable - exec dbms_stats.seed_col_usage
    • Run - Let database do its shit
    • Review - select dbms_stats.report_col_usage('OWNER', 'TABLE') from dual;
    • Create Column Groups - select dbms_stats.create_extended_stats ('OWNER', 'TABLE') from dual;
  56. What is the command to enable extended statistics Workload Monitoring to the entire database for 10mins?
    exec dbms_stats.seed_col_usage(null,null,600);
  57. What is the purpose of SQL Plan management?
    To guarantee that the performance of a sql statement doesn’t degrade due to an execution plan change.
  58. What is the plan history?
    • A history of the plans is stored for sql statements executed more than once.
    • The history contains the SQL_TEXT. bind variables and the compilation environment.
    • This information can be use to recreate an execution plan.
  59. What causes the Optimizer to capture plans automatically?
  60. When are plans evolved automatically?
    • During the maintenance window.
  61. What process verifies plan changes?
  62. In 12c, what does DBMS_XPLAN.DISPLAY_PLAN_BASELINE show?
    Stored plan data
  63. What is Automatic SQL Plan Management?
    • In 12c, an automated way of evolving plans
  64. How are results from SYS_AUTO_SPM_EVOLVE_TASK viewed?
  65. Which feature evolves plans recently added to the SQL plan baseline, instead of the DBAs having to manually perform the evolution chores?
    SPM Evolve Advisor