Oracle Database 11g: SQL Performance Analyzer, Part 1

October 25, 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 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 tablespace (LMT)
• 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 table (and vice versa)
• Transforming a heap-organized table into a partitioned table
• 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 index)
• 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 9.2.0.1.0 to 9.2.0.3.0 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 9.2.0.3.0 release. Needless to say, we immediately decided to abandon the rollout of the new patch set until all of our application code could be reviewed.

Migrating an Oracle Database to a Different Database Release. 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 1.1, 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 1.2 shows these SQL statements as they’ll be executed by the LDGN user account, and Listing 1.3 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 1.4, 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 procedure.

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 1.5 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 1.6 demonstrates how to create the “after” performance baseline.

6.) Compare the “before” and “after” baselines. Finally, I’ll execute the code in Listing 1.7 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 1.8.

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 affected 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 interface.

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