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
