IT Innovation

Tune It Up

New features in Oracle Database 11g improve and speed SQL tuning.

By Sushma Jagannath

November/December 2009

It can be a challenge to manage changes to the database or system and simultaneously ensure that application SQL performance does not regress and that all SQL statements use the optimal SQL execution plans. This column focuses on key features introduced in Oracle Database 11g that help manage system change for SQL statements and ensure optimal SQL statement performance. It presents sample questions of the type you may encounter when taking the Oracle Database 11g New Features for Administrators and the Oracle Database 11g Performance Tuning exams.

SQL Performance Analyzer

You can use the SQL Performance Analyzer feature in Oracle Database 11g to predict the impact of system changes on a workload and prevent potential performance problems for any database environment change that affects the structure of the SQL execution plans. System changes include changes to parameters, schemas, hardware, and the operating system as well as Oracle software upgrades.

The following outlines the steps for using SQL Performance Analyzer:

1. Capture the SQL workload on the production system, using SQL tuning sets or Oracle’s automatic workload repository tool.

2. Move the production SQL workload to a test system.

3. On the test system, use SQL Performance Analyzer to compute the before-change performance.

4. Make the changes recommended by SQL Performance Analyzer.

5. Use SQL Performance Analyzer to compute the after-change performance.

6. Use SQL Performance Analyzer to compare and analyze SQL performance based on execution statistics, such as elapsed time, CPU time, and buffer gets.

7. Tune any regressed SQL statements.

What would a DBA use to check for regressed SQL statements in a SQL tuning set before implementing tuning recommendations?

A. Oracle’s SQL Repair Advisor tool
B. Oracle’s SQL Access Advisor tool
C. SQL Performance Analyzer
D. Oracle’s Automatic Database Diagnostic Monitor tool

The correct answer is C. Answer A is incorrect because SQL Repair Advisor is used to analyze a SQL statement that has failed with a critical error and to provide recommendations for repairing that statement. Answer B is incorrect because SQL Access Advisor provides recommendations on optimizing storage structures. Answer D is incorrect because Automatic Database Diagnostic Monitor is a diagnostic tool that analyzes the performance of the database at regular intervals and identifies any performance issues.

Which statements are true about SQL Performance Analyzer?

A. It can assess the impact of a change on SQL response time.
B. It can assess the impact that SQL statements have on each other.
C. It works on a single SQL execution.
D. It can assess application SQL to identify the set of SQL statements with changed performance.
E. It can assess the impact of parameter changes on SQL statements.

The correct answers are A, C, D, and E. Answer B is incorrect because SQL Performance Analyzer does not consider the impact that SQL statements have on each other.

You plan to use SQL Performance Analyzer to analyze a SQL workload, and you’ve created a SQL tuning set as a part of the workload capture. What information is captured as part of this process?

A. The SQL text
B. The execution context
C. The execution frequency
D. The performance statistics

The correct answers are A, B, and C. While creating the SQL tuning set, SQL Performance Analyzer captures the SQL text, the execution context, and the number of times the statement is executed. SQL Performance Analyzer then uses that information to analyze the performance of the SQL statement.

SQL Plan Management

After the optimizer provides an efficient execution plan for a SQL statement, there’s no guarantee that the optimizer will always use that execution plan. A plan can change for a variety of reasons, including changes to schema definitions, system settings, composition of data that affects selectivity and cardinality, database upgrades, new optimizer versions, and new statistics. Not being able to guarantee that a new execution plan will improve execution has caused some DBAs to freeze their execution plans or their optimizer statistics. Oracle Database 11g introduced the SQL Plan Management feature, which eliminates the need to freeze execution plans or optimizer statistics to control SQL plan execution.

SQL Plan Management automatically controls SQL plan evolution by using SQL plan baselines. With SQL Plan Management enabled, a newly generated SQL plan can become part of a SQL plan baseline only if that new plan will not result in performance regression. During execution of a SQL statement, only a plan that is part of the corresponding SQL plan baseline can be used.

You can start using SQL plan baselines either by bulk-loading them with the DBMS_SPM package or by setting the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE. With the SQL plan baseline in place, you can evolve the baseline by using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to add a new plan or by running Oracle’s SQL Tuning Advisor tool to provide recommendations on whether a new plan is better than the one that exists in the baseline.

What are the benefits of using SQL Plan Management?

A. It provides performance stability by preventing plan regressions.
B. It can help you temporarily freeze SQL execution plans when you migrate to a new release of Oracle Database.
C. A new execution plan is not used before verification that it is more efficient than the current plan.
D. You can predict the impact of system changes on a workload.

The correct answers are A, B, and C. Answer D is incorrect because it is the SQL Performance Analyzer that can evaluate the impact of system changes on a SQL workload.

While examining the SQL plan baseline for a SQL statement, you observe that a particular plan is not being used. You believe that the unused plan is more efficient than the one currently being used and would like the unused plan to be used—always. Which step do you perform to ensure that the optimizer always uses that plan?

A. Mark the unused plan as FIXED.
B. Use SQL Tuning Advisor.
C. Mark the unused plan as ACCEPTED.
D. Evolve the unused plan by using a function in the DBMS_SPM package.

The correct answer is A. Marking a plan as FIXED ensures that the optimizer uses only that plan and no others. Answer B is incorrect because SQL Tuning Advisor recommends a SQL profile. You would then need to accept the recommendation and include the unused plan in the SQL plan baseline for that SQL statement. Answer C is incorrect because marking the plan as ACCEPTED indicates that it has been validated as a good plan but does not guarantee that it will be used every time the SQL statement is executed. Answer D is incorrect because the EVOLVE_SQL_PLAN_BASELINE function in the DBMS_SPM package determines whether a new plan performs better than a plan from the SQL plan baseline, but it adds the plan to the baseline as ACCEPTED (not FIXED).

Next Steps

 LEARN more about the Oracle Certification Program and download a free exam guide

 EXPLORE the certification forum

 READ Inside OCP columns


Photography byDenys Nevozhai,Unsplash