Oracle Database 11g: SQL Performance Analyzer, Part 3

December 21, 2007

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.

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.

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).

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.

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:

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: