Subscribe

Share

Database, SQL and PL/SQL

Optimize Execution Plans

Test your knowledge of SQL Plan Management and query optimization in Oracle Database 12c.

By Anita Mukundan

September/October 2015

This column contains sample questions, answers, and explanations about the enhancements to two SQL tuning innovations in Oracle Database 12c: SQL Plan Management and query optimization. The sample questions are of the type you may encounter when taking the Upgrade Oracle9i/10g/11g OCA to Oracle Database 12c OCP (1Z1-067), Upgrade to Oracle Database 12c (1Z0-060), or Oracle Database 12c: Performance Management and Tuning (1Z0-064) exams.


Adaptive SQL Plan Management

SQL Plan Management, which enables the Oracle Database query optimizer to automatically manage execution plans, uses a SQL plan baseline, a set of accepted plans the optimizer can use for a SQL statement. Typically, the database accepts a plan into the plan baseline only after verifying that the plan performs well.

Oracle Database 12c introduces an Evolve Advisor, part of SQL Plan Management.


Which two automated features are enabled by default in SQL Plan Management?

a. Creation of new SQL plan baselines for repeatable SQL statements

b. Evolution of unaccepted plans in existing SQL plan baselines

c. Addition of new plans to the SQL plan baseline as unaccepted plans

d. Addition of new plans to the SQL plan baseline as accepted plans


What is the outcome for unaccepted plans in plan baselines after the evolution process runs in the daily maintenance window?

a. All unaccepted plans that meet the performance criteria are converted to accepted plans in the span of a single maintenance window.

b. All unaccepted plans that meet the performance criteria and get converted to accepted plans have their LAST_EXECUTED attribute updated with the current time stamp.

c. All unaccepted plans that fail to meet the performance criteria remain unaccepted, and their LAST_VERIFIED attribute is updated with the current time stamp.

d. All unaccepted plans that fail to meet the performance criteria are dropped from the baseline.

e. All unaccepted plans that fail to meet the performance criteria remain unaccepted and are evaluated again by the evolution process during the next maintenance window.


Examine this PL/SQL code:

BEGIN
    DBMS_SPM.SET_EVOLVE_TASK_PARAMETER
    (task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
     parameter => 'ACCEPT_PLANS',
     value     => 'true');
   END;
   /

What will be the outcome when the code is executed?

a. The Evolve Advisor runs during the maintenance window to generate recommendations for all unaccepted plans in existing SQL plan baselines.

b. The Evolve Advisor runs during the maintenance window to evolve unaccepted plans in existing SQL plan baselines.

c. All unaccepted plans that were manually evolved and found to perform better than the existing accepted plan in the SQL plan baseline are accepted.

d. Any unaccepted plans in existing SQL plan baselines will be dropped, ensuring that only accepted plans exist in all SQL plan baselines.


Examine this command:

EXECUTE :exe_name := 
DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>'TASK_1');

Which two statements are true?

a. It manually evolves unaccepted plans in TASK_1.

b. It is invoked independent of the Evolve Advisor to forcibly evolve all unaccepted plans in TASK_1 to accepted plans.

c. It accepts all the unaccepted plans in TASK_1 that are found to meet the performance criteria.

d. It creates a SQL plan baseline for a query with an accepted plan.

e. It generates recommendations that must be implemented by a separate function.


Adaptive Query Optimization

In previous Oracle Database releases, after the optimizer’s execution plan was generated, no further improvements could be made to the plan at runtime. In Oracle Database 12c, adaptive query optimization enables the optimizer to make adjustments to suboptimal execution plans based on runtime statistics. Adaptive query optimization is enabled by default and controlled by initialization parameters.

In your database, OPTIMIZER_FEATURES_ENABLE is set to 12.1.0.1 and OPTIMIZER_ADAPTIVE_FEATURES is set to TRUE.

Which statement is true about adaptive query optimization in this scenario?

a. It is enabled only if OPTIMIZER_DYNAMIC_SAMPLING is set to 2.

b. It is enabled only if OPTIMIZER_DYNAMIC_SAMPLING is set to 11.

c. It is enabled only if OPTIMIZER_ADAPTIVE_REPORTING_ONLY is set to FALSE.

d. It is enabled only if OPTIMIZER_ADAPTIVE_REPORTING_ONLY is set to TRUE.

e. It is enabled if OPTIMIZER_ADAPTIVE_REPORTING_ONLY is set to either TRUE or FALSE.


Adaptive Plans

An adaptive plan contains multiple predetermined subplans and an optimizer statistics collector. The optimizer determines the default plan and then includes multiple subplans for some major join operations in the plan. On the first execution of a SQL statement, the statistics collector gathers the latest statistics, which the optimizer then uses to adapt its default plan or a portion of it to improve performance.

What happens once an optimal final plan is chosen by the optimizer for the first execution of a SQL statement?

a. The statistics collector continues to collect statistics, but no new subplans are generated by the optimizer.

b. The statistics collector continues to collect statistics, and new subplans are generated in subsequent executions.

c. The final plan becomes a fixed baseline for the lifetime of that SQL statement.

d. The final plan is used until it ages out of the cache or is invalidated.


Next Steps

 LEARN more about the Oracle Certification Program.

 READ Inside OCP columns.

 

Photography by Dmitri Popov, Unsplash