Oracle 12c Upgrade: 14 – SQL Tuning
Home > Flashcards > Print Preview
The flashcards below were created by user
on FreezingBlue Flashcards
. What would you like to do?
What is Adaptive Query Optimization based on?
- Adaptive execution plans
- Adaptive Statistics
What are adaptive execution plans?
Plan that are adjusted during runtime as supplemental information that could potentially result in better execution plans is gathered
What are Adaptive statistics?
- Dynamic statistics
- Automatic reoptimization
- SQL plan directives
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
In Adaptive Execution Plans when is the final final plan decision made?
During the the first part of the sql execution
What is required to allow Adaptive Execution Plans?
- OPTIMIZER_ADAPTIVE_REPORTING_ONLY=FALSE (This is default)
What happens if OPTIMIZER_ADAPTIVE_REPORTING_ONLY=TRUE?
- The adaptive execution plan feature runs in the reporting-only mode
- The information is not used in plans
If OTIMIZER_ADAPTIVE_REPORTING_ONLY=TRUE, where can the unused adaptive plan viewed?
In Adaptive Execution Plans what is a subplan?
Part of the execution plan that the optimizer can switch to at run time.
In Adaptive Execution Plans which plan holds all the sub-plans?
The default plan
In Adaptive Execution Plans what is the final plan?
The one that is executed?
When does Adaptive Execution run?
At the first execution of a query. Subsequent executions use the final plan generated.
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
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
What is dynamic sampling?
- Used before 12c
- Capture statistics on the fly when they are missing.
- Levels can be set
In 12c what is dynamic statistics
- Enhanced form of dynamic sampling
- Optimizer decides whether to collect dynamic statistic and what level to use
What does the parameter OPTIMIZER_DYNAMIC_SAMPLING do?
Lets you define at instance or session level when dynamic statistics should be gathered
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.
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
For when does Automatic Reoptimization change a plan?
For subsequent executions not the current execution
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
What does Automatic Reoptimization use to make decisions?
- Statistics Feedback
- Performance Feedback
Which part of Automatic Reoptimization detects the difference between the actual and estimated cardinality statistics?
Which part of Automatic Reoptimization is concerned with cardinality of statistics?
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
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.
Which part of Automatic Reoptimization is concerned with cardinality of parallel execution?
What is Performance Feedback?
Helps improve the degree of parallelism chosen for repeatable SQL statements
Which parameter controls Performance Feedback and the automatic degree of parallelism?
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.
What are the options for PARALLEL_DEGREE_POLICY?
- MANUAL (default) - Disable automatic degree of parallelism.
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
When are SQL Plan Directives created?
- During compilation and execution
- What can trigger a SQL Plan Directive to be created?
- Missing Stats
- Misestimated Cardinality
How often are SQL Plan Directives purged?
If unused, after 52 weeks
Where are SQL Plan Directives stored?
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
Which command will force Oracle to write SQL Plan Directives from memory to disk?
Which command will force Oracle to remove a SQL Plan Directive from disk?
exec DBMS_SPD.DRIP_SQL_PLAN_DIRECTIVE (directive_id => 12312121);
Which views are used to monitor SQL Plan Directives?
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 */
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
Which table will reveal if a online statistics gathering for bulk load operations was used?
- NOTES column reads STATS_ON_LOAD
When online statistics gathering for bulk load operations gathers stats, are index stats gathered?
When won’t Online Statistics Gathering for Bulk-Load be used?
- If object is SYS
- Nested table
- External Table
- Virtual Columns
- There are a couple of others
Which 12c feature can significantly speed up statistics gathering?
Concurrent Statistics Gathering
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
How is Concurrent Statistics Gathering enabled?
- Resource Manager enabled
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
Where can the current configuration for concurrent statistics collection be obtained?
SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;
Which 3 components are involved in Concurrent Statistics Gathering?
- Oracle Scheduler
- Oracle Database Advanced Queuing (AQ)
- Oracle Database Resource Manager (the Resource Manager)
What are the two new histograms introduced in 12c
- Top Frequency Histograms
- Hybrid Histograms
To use Top Frequency Histograms and Hybrid Histograms, what must the Sampling percentage be set to?
What are extended statistics?
Statistics on a group of columns within a table
In extended statistics, how are column group candidates determined?
Using workload monitoring (A SQL tuning set)
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;
What is the command to enable extended statistics Workload Monitoring to the entire database for 10mins?
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.
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.
What causes the Optimizer to capture plans automatically?
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE (default FALSE)
When are plans evolved automatically?
- During the maintenance window.
What process verifies plan changes?
In 12c, what does DBMS_XPLAN.DISPLAY_PLAN_BASELINE show?
Stored plan data
What is Automatic SQL Plan Management?
- In 12c, an automated way of evolving plans
- Uses SYS_AUTO_SPM_EVOLVE_TASK
How are results from SYS_AUTO_SPM_EVOLVE_TASK viewed?
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
What would you like to do?
Home > Flashcards > Print Preview