Oracle Database 11g: SQL Performance Analyzer, Part 1

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 first in a series on new Oracle Database 11g SQL performance
improvement features – reviews how to set up and utilize these features and
provides a simple simulation of how SQL Performance Analyzer can detect changes
to an Oracle 11g database system.

One of the first rules I learned early in my information
technology career – and that I’ve seen proven over and over again in the past
quarter century – simply states: “Nothing works in production like it did
during testing.” Since the only constant in information technology is constant
change, and since it’s virtually impossible as well as unbelievably expensive
to duplicate completely a production environment, information technology
professionals have found this adage’s truth revealed many times over. Based on
my experiences, I’ve compiled a list of the more common vectors for database
system change, but the only thing I’m sure of is that there are some that I may
not yet have encountered:

Rolling Out New Versions of Existing Applications. I
dislike overstating the obvious, but interim application maintenance is the
vector for the majority of changes to Oracle database systems. DBAs are
constantly called upon to update the myriad PL/SQL objects that comprise the
bulwark of our applications’ infrastructure, including package specifications,
package bodies, stored procedures, stored functions, and triggers. Regardless
of how well these changes have been tested and evaluated, there’s always the
possibility that an application could be executed in a way that our testing
team couldn’t anticipate, and that may lead to extremely poor performance.

Modifying Database Structures. Somewhat less common
is modification of existing database structures intended to improve
performance. Examples of this include:

  • Upgrading a dictionary-managed tablespace (DMT) to a locally-managed
  • Moving a table or index to a LMT that uses Automatic Segment
    Storage Management
    (ASSM) instead of free lists for block space management
  • Changing a heap-organized table into an index-organized
    (and vice versa)
  • Transforming a heap-organized table into a partitioned
  • Changing the distribution of data within a partitioned
    table by adding, removing, splitting, or exchanging partitions
  • Adding a new index to an existing table, or removing
    an existing index
  • Changing a balanced-tree index to a bitmap index
    (or vice versa)
  • Changing a balanced-tree index’s internal organization
    (e.g. from simple head-organized to compressed, reverse-key, or descending
  • Creating a global or local partitioned index for a
    corresponding partitioned table
  • Refreshing the cost-based optimizer statistics for a table
    or index, including the reversion of statistics to a prior point in time

Modifying a Database’s Patch Level. This is one of
the hairier scenarios I’ve faced. I remember attempting what appeared to surely
be a simple upgrade of an Oracle 9iR2 database from to
shortly after that updated patch set was available. My QA tester immediately
reported that our flagship OLTP application’s order entry lookup screen – a Powerbuilder
DataWindow that used a simple SQL query to retrieve a specific subset of orders
from a database table that contained several million rows – had now increased
its response time from under one second to one to two minutes. We
eventually traced this poor response time to a change in the cost-based
optimizer’s treatment of the FIRST_ROWS hint
in the release. Needless to say, we immediately decided to abandon
the rollout of the new patch set until all of our application code could be

Migrating an Oracle Database to a Different Database
One of the most difficult challenges I’ve encountered as an Oracle
DBA is when I’ve upgraded a complete database system from its current Oracle
database release level to the next release level. While the actual upgrade is
usually relatively trivial to perform and prepare for, especially if I have a
QA or testing environment, the real challenges of an upgrade are the impacts on
the existing SQL and PL/SQL code base.

Changing a Database’s Platform Configuration. This
can be some of the most trying experiences I’ve faced as a DBA. Some of the
challenges I’ve encountered include:

  • Adding or removing a host server’s CPUs
  • Extending or reducing total memory available on the host
  • Modifying or upgrading the host server’s disk I/O subsystem,
    including switching between traditional file system based storage (e.g. NTFS or
    EXT3), raw disk partitions, or Oracle’s Automatic Storage Management
    (ASM) file system
  • Migrating to a radically (or event marginally!) different
    hardware platform
  • Migrating to a different OS (e.g. Windows to Linux)
  • Migrating from a 32-bit OS to a 64-bit OS

Oracle Database 11g SQL Performance Analyzer: A Brief Demonstration

Fortunately, Oracle Database 11g provides a brand-new tool
set called the SQL Performance Analyzer (SPA) that provides an Oracle DBA
with the capability to measure the impact of these types of changes between two
different database configurations because it allows me to:

  • Capture a set of SQL statements that comprise a representative
    sample of the database’s workload
  • Establish the current performance of the sample workload using
    the current database system to create a “before” image or baseline
  • Test the performance of the identical workload against the
    “after” configuration of the database system
  • Identify which components of the workload have been either
    positively or negatively affected by the proposed changes, and which have
    remained unchanged
  • Determine how to best correct the poorly performing SQL
    statements so that they will run most effectively in the new environment

To illustrate how SQL Performance Analyzer works, I’ll
perform the following steps to evaluate the performance of a reasonably simple
(but not necessarily well-tuned!) group of SQL statements against both the
“before” and “after” images of a sample Oracle 11g database:

1.) Preparation for Simulation. I’ve constructed a
new table, SH.SALES_AGENTS, and loaded it with
a sample set of approximately 420,000 rows. I then created five indexes for the
table: a UNIQUE index on its primary key column, SALESPERSON_ID,
and four additional indexes that would typically be used by either a decision
support system (DSS) application or an online transaction processing (OLTP)
application to speed retrieval of a specific subset of sales agent data. The
DDL I used to create the table, create the indexes, and update their cost-based
optimizer statistics are shown in Listing
, while the DML to perform an initial load of the table is presented
in a separate listing, LoadSalesAgents.sql.

2.) Prepare for SQL Gathering. Next, I’ve constructed
several SQL statements that access the SH.SALES_AGENTS
table using different execution plans. Note that I’ve provided several
different access path scenarios: In some cases, I’ve allowed the cost-based
optimizer to choose the optimal execution plan using all available indexes, and
in other cases I’ve completely overridden the cost-based optimizer using
inappropriate HINTs. Listing
shows these SQL statements as they’ll be executed by the LDGN user account, and Listing
shows how I’ll initiate the capture of these sample SQL statements
into a SQL Tuning Set (STS) called STS_SPA_100.

3.) Create a “before” performance baseline. Since my
“before” image of the database is now ready for testing, I’ll create a SQL
Performance Analysis Task and let Oracle 11g determine the name of the task (TASK_69). As shown in Listing
, this new task uses the STS_SPA_100
SQL Tuning Set as input to the SPA tuning session. I then created the “before”
image performance baseline with the DBMS_SQLPA.EXECUTE_ANALYSIS_TASK

4.) Change the database environment. Once the
baseline has been created successfully against the “before” image, I’ll
simulate the simplest of change scenarios. As Listing
illustrates, I’ve removed two of the four original indexes against
the SH.SALES_AGENTS table, and then I
refreshed the table’s statistics.

5.) Create an “after” performance baseline. To
determine the results of these changes against the original, identical
workload, I’ll execute the DBMS_SQLPA.EXECUTE_ANALYSIS_TASK
procedure to once again perform a test execution of the same SQL Tuning Set. Listing
demonstrates how to create the “after” performance baseline.

6.) Compare the “before” and “after” baselines. Finally,
I’ll execute the code in Listing
to determine the impact of the changes by comparing the results of
the “before” and “after” test cases. I’ll then produce a simple summary report
of these differences via a call to the DBMS_SQLPA.REPORT_ANALYSIS_TASK
procedure as I’ve illustrated in Listing

The resulting report is reproduced in text format here.
As the report clearly shows, the removal of the indexes had a definite effect
on the original set of eight (8) SQL statements:

  • Three (3) statements were completely unaffected even
    though two indexes were removed.
  • Not surprisingly, at least one (1) statement was negatively
    by the indexes’ removal.
  • However, four (4) statements were positively affected by
    the removal of the two indexes. This actually makes sense because these
    statements were being forced via optimizer hints to use those indexes, and this
    contributed to their original poorer performance.

Next Steps

In the next article in this series, I’ll demonstrate how
the SQL Performance Analyzer can analyze more complex Oracle database and
environment changes, including a scenario that illustrates how to analyze a SQL
workload while it endures the transition from an Oracle 10gR2 database
environment to an Oracle 11gR1 environment. I’ll also explore extensively how
to use the graphical user interface for the SQL Performance Analyzer that the
Oracle Database 11g Enterprise Manager (EM) Database Control panels provide to
easily create, execute, and navigate within the SQL Performance Analysis Task

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:

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

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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles