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 11gs new SQL Performance Analyzer (SPA) tool set can:
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. Ill also explore more of the SQL Performance Analyzers 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. Ill 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. Ill 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, Ill generate a new SQL Workload that consists of just four statements:
Assessing Parameter Changes: Scenario #1
Im 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. Ill use the Oracle Database 11gs 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, Ive 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. Ive 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 Ive clicked on Submit, control returns to the SQL Performance Analyzer home panel, and the job that Ive 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 Ill 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: