Oracle Database 11g: SQL Performance Analyzer, Part 3

Friday Dec 21st 2007 by Jim Czuprynski

This article - the final one in this series- explores how the SQL Performance Analyzer can effectively analyze changes in SQL statement performance due to modified database initialization parameters.

Synopsis. Oracle Database 11g Release 1 (11gR1) features the new SQL Performance Analyzer that promises to ease significantly the workload of busy Oracle DBAs because it provides a way to accurately evaluate complete database workloads for “before versus after” performance changes. This article – the final one in this series– explores how the SQL Performance Analyzer can effectively analyze changes in SQL statement performance due to modified database initialization parameters.

The previous article in this series continued to explore how Oracle Database 11g’s new SQL Performance Analyzer (SPA) tool set can:

  • Transfer a SQL Workload from an Oracle 10gR2 database to an Oracle 11gR1 database
  • Use the SPA Enterprise Manager interface to execute and analyze a SQL Workload
  • Estimate how the performance of each statement in the SQL Workload would be affected if run against two different versions of the cost-based optimizer (CBO)

This article – the final one in this series – will show how to use SPA to tackle one of the more frustrating SQL performance tuning challenges: the impact of modified database initialization parameters upon SQL statement performance. I’ll also explore more of the SQL Performance Analyzer’s reporting and analysis options to obtain a complete picture of what is causing a SQL statement to regress, and how this new tool facilitates creation of SQL Plan Baselines for SQL Plan Management.

Assessing Initialization Parameter Changes: Preparations

Just as the prior two articles showed, the most important first steps in SQL Performance Analyzer task execution is to identify, prepare, and capture the target SQL statements for evaluation:

Prepare for Simulation. I’ll carry forward the identical example from the two previous articles, except that in this case I only need to access my sample Oracle 11gR1 database. I’ll be using the same specially-created table, SH.SALES_AGENTS, and other tables in the sample schema as the targets for my explorations. The DDL and DML for creating SH.SALES_AGENTS and performing its initial data load can be found in Listing 1.1 and LoadSalesAgents.sql, respectively

Gather SQL Workload. So that it will be simpler to identify any progressing or regressing SQL statements once different initialization values are compared, I’ll generate a new SQL Workload that consists of just four statements:

  • Statement LDGN 5.1 uses a predicate that searches for all Sales Agent entries in the SH.SALES_AGENTS table whose last name starts with the string “Pitt.” If the OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a value lower than its default of 100, the cost-based optimizer (CBO) will tend to favor an index search over a table scan. Since the LAST_NAME column is indexed, the (CBO) might determine that this query’s performance might benefit from that index to retrieve the result set more quickly – provided, of course, that using the index produces a lower cost than simply performing a full table scan.
  • Statement LDGN 5.2 aggregates sales amounts within the Customer and Product dimensions from the SALES table in the Sales History (SH) schema. The CBO might determine that this statement could benefit from a different setting for the OPTIMIZER_INDEX_CACHING initialization parameter. When set to a number higher than its default value of zero, this parameter tells the CBO how often it should expect to find index blocks already cached in the database buffer cache.
  • Statement LDGN 5.3 gathers and aggregates sales data from several tables in the Sales History (SH) schema, and the statement’s predicates make it a great candidate for better performance if the STAR_TRANSFORMATION_ENABLED initialization parameter were to be set to TRUE (instead of its default value, FALSE).
  • Finally, statement LDGN 5.4 employs a predicate that searches for a large number of entries in the SH.SALES_AGENTS table based on the primary key, SALESPERSON_ID. As in the case of statement LDGN 5.1, the CBO will tend to utilize the primary key index to find these rows more quickly if the OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a value lower than its default of 100.

The statements that comprise this SQL workload are shown in GenerateSPAWorkload_3.sql, and the code shown in Listing 3.1 captures these SQL statements into a SQL Tuning Set named STS_SPA_300.

Assessing Parameter Changes: Scenario #1

I’m now ready to turn the SQL Performance Analyzer loose to see if it can detect any performance progression or regression for these four SQL statements. I’ll use the Oracle Database 11g’s Enterprise Manager Database Control interface to perform this analysis. Figure 3.1 shows the initial state of the SQL Performance Analyzer panel.

Figure 3.1. SQL Performance Analyzer Home Panel

When I select the Parameter Change link from this panel, Enterprise Manager presents the Parameter Change panel. As shown in Figure 3.2, I’ve specified a task name of SPA_IPC_100, the appropriate SQL Tuning Set name of SYS.STS_SPA_300, and a brief description of the task. I’ve also specified a base value of zero (0) and a changed value of 75 for the optimizer_index_caching initialization parameter that SPA will evaluate.

Figure 3.2. Creating Parameter Change Comparison Task SPA_IPC_100

Once I’ve clicked on Submit, control returns to the SQL Performance Analyzer home panel, and the job that I’ve submitted eventually shows it has completed (see Figure 3.3 below).

Figure 3.3. Successful execution of SQL Performance Analyzer task SPA_IPC_100

To see the results of this execution, I simply click on the Task Name link. As shown below in Figure 3.4, Enterprise Manager then presents a summary of the successful task execution. Note that the default comparison metric, Elapsed Time, has been used to rank the SQL statements in order of performance progression or regression; a bit later I’ll demonstrate how to choose a different ranking dimension for the analysis reports.

Figure 3.4. SQL Performance Analyzer Task SPA_IPC_100 Execution Details

When I click on the corresponding “eyeglasses” icon under the Comparison Report column near the bottom of this panel, Enterprise Manager displays the results of the analysis in Figure 3.5:

Figure 3.5. Results of Elapsed Time Comparison

The results of this analysis show that the elapsed time for all four statements has improved, and statement LDGN_5.4 (with a hash value of 20jkfbgrk50rm) has improved most of all, by a factor of almost 78%. The details of this dramatic improvement are shown in Figures 3.6.1 and 3.6.2 below:

Figure 3.6.1. SPA Task SPA_IPC_100 Result for Statement 20jkfbgrk50rm

Figure 3.6.2. SPA Task SPA_IPC_100 Result for Statement 20jkfbgrk50rm

Assessing Parameter Changes: Scenario #2

How would a change to the STAR_TRANSFORMATION_ENABLED initialization parameter influence the performance of these same four SQL statements? I’ve prepared a new SQL Performance Analyzer task named SPA_IPC_200 to answer this question. As shown in Figure 3.7 below, I once again selected SQL Tuning Set SYS.STS_SPA_300 as the target for my analysis. I’ve chosen a base value of FALSE (the default value) and a changed value of TRUE for this initialization parameter. The results of this analysis are shown in Figure 3.8 below.

Click for larger image

Figure 3.7. Creating Parameter Change Comparison Task SPA_IPC_200

Click for larger image

Figure 3.8. SQL Performance Analyzer Task SPA_IPC_200 Execution Details

Assessing Parameter Changes: Scenario #3

Is there a way to actually execute a series of SQL*Plus and PL/SQL commands to submit the same type of analysis task without using Enterprise Manager? The good news is that it’s definitely possible. After some judicious and extensive SQL statement tracing, I determined exactly how the SPA wizard was submitting these tasks.

I’ve used these statements to generate my final initialization parameter change scenario: the impact of changing the OPTIMIZER_INDEX_COST_ADJ initialization parameter from its non-default value of 25 to its default value of 100. As mentioned previously, I’d expect to see an impact on the optimizer cost for statements LDGN 5.1 and LDGN 5.4 because this parameter affects the CBO’s decision to use an available index versus a table scan.

I’ve listed the code I used to perform the analysis in Listing 3.2. These statements:

  • Create a SQL Performance Analyzer Task named SPA_IPC_300
  • Execute a “before” performance impact analysis
  • Execute an “after” performance impact analysis
  • Create a comparison report between the “before” and “after” performance impact analysis

Oracle 11g uses a simple method to perform the “before” and “after” performance impact analyses: It simply issues an ALTER SESSION command to modify the initialization parameter setting before performing the analysis, and then it executes the SPA analysis task to capture the results. Figure 3.9 below shows the summary page from this SQL Performance Analyzer task’s successful execution, and Figures 3.9.1 through 3.9.3 show the detailed analysis for SQL statement LDGN 5.1:

Figure 3.9. SQL Performance Analyzer Task SPA_IPC_300 Execution Summary

Figure 3.9.1. SPA Task SPA_IPC_300 Result for Statement 1zu2z1n6b6ytx

Figure 3.9.2. SPA Task SPA_IPC_300 Result for Statement 1zu2z1n6b6ytx

Customizing SQL Performance Analyzer Reporting Results

What if I’m not satisfied with the appearance of the comparison report that the SQL Performance Advisor comparison reporting mechanism generates? For example, what if I’d like to have the SQL statements that I’ve targeted for analysis to be sorted in a different relative order – say, within optimizer cost instead of elapsed time (the default)? The good news is that I can specify a different reporting dimension for the summary report simply by changing the value that’s supplied for the execution_params argument of the DBMS_SQLPA.EXECUTE_ANALYSIS_TASK procedure. I’ve illustrated this in the immediately prior example by selecting OPTIMIZER_COST as the comparison metric.

Here are the values that I’ve tested for this parameter; as of this article’s publication date, these aren’t yet documented in the Oracle 11g online manuals:

Table 3.1. SQL Performance Analyzer Analysis Dimensions




The estimated total cost to execute the statement, as computed by the cost-based optimizer


The number of times the statement has been executed


The total time it took to execute the statement (i.e. wall time). This is the default setting


The total amount of CPU time it took to execute the statement


The total time it took to parse the statement


The total buffer gets (i.e. buffers read + consistent reads) the statement needed to complete


The number of disk reads the statement performed


The number of disk writes the statement performed


The number of rows the statement processed


The number of fetches the statement performed

On the Horizon: SQL Plan Management

While SQL Performance Analyzer’s features are admittedly impressive, and while it certainly promises to make short work of the uncertainty any DBA faces when contemplating system changes, database upgrades, and application modifications, it still begs one question: How can these analyses be used effectively to limit poor SQL statement performance?

What I really need to accomplish is twofold:

  • Insure that any SQL statements that will unquestionably perform better in the post-change production environment will immediately begin using better execution plans once all changes are complete; and
  • Insure that any SQL statements that perform well in the current production environment will continue to use their current execution plan in the post-change production environment.

Achieving these goals is the focus of my next series of articles: Oracle 11g’s new SQL Plan Management (SPM) features.


As this article series has shown, Oracle Database 11g’s new SQL Performance Analyzer offers an impressive tool set for analyzing the impact to the performance of SQL statements without having to construct separate test environments for each test scenario. SQL Performance Analyzer therefore has the potential to reduce significantly the enormous time outlay that Oracle DBAs, QA Analysts, and application developers have had to invest in the past to identify, verify, and isolate SQL statements that may perform poorly after a major database release is implemented, a new version of an existing application is released, or even a seemingly harmless change to an initialization parameter is made.

References and Additional Reading

While I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that I’ve drawn upon the following Oracle Database 11gR1 documentation for the deeper technical details of this article:

B28313-02 Oracle Database 11gR1 Data Warehousing Guide

B28274-01 Oracle Database 11gR1 Performance Tuning Guide

B28279-02 Oracle Database 11gR1 New Features Guide

B28419-02 Oracle Database 11gR1 PL/SQL Packages and Types Reference

» See All Articles by Columnist Jim Czuprynski

Mobile Site | Full Site