Oracle Database 11g: SQL Performance Analyzer, Part 3

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

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,

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

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles