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 Ive 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 its virtually impossible as well as unbelievably expensive to duplicate completely a production environment, information technology professionals have found this adages truth revealed many times over. Based on my experiences, Ive compiled a list of the more common vectors for database system change, but the only thing Im 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, theres always the possibility that an application could be executed in a way that our testing team couldnt 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 indexs 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 Databases Patch Level. This is one of the hairier scenarios Ive faced. I remember attempting what appeared to surely be a simple upgrade of an Oracle 9iR2 database from 184.108.40.206.0 to 220.127.116.11.0 shortly after that updated patch set was available. My QA tester immediately reported that our flagship OLTP applications 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 optimizers treatment of the FIRST_ROWS hint in the 18.104.22.168.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 Ive encountered as an Oracle DBA is when Ive 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 Databases Platform Configuration. This can be some of the most trying experiences Ive faced as a DBA. Some of the challenges Ive encountered include:
- Adding or removing a host servers CPUs
- Extending or reducing total memory available on the host
- Modifying or upgrading the host servers disk I/O subsystem, including switching between traditional file system based storage (e.g. NTFS or EXT3), raw disk partitions, or Oracles 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 databases 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, Ill 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. Ive 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, Ive constructed several SQL statements that access the SH.SALES_AGENTS table using different execution plans. Note that Ive provided several different access path scenarios: In some cases, Ive allowed the cost-based optimizer to choose the optimal execution plan using all available indexes, and in other cases Ive completely overridden the cost-based optimizer using inappropriate HINTs. Listing 1.2 shows these SQL statements as theyll be executed by the LDGN user account, and Listing 1.3 shows how Ill 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, Ill 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, Ill simulate the simplest of change scenarios. As Listing 1.5 illustrates, Ive removed two of the four original indexes against the SH.SALES_AGENTS table, and then I refreshed the tables statistics.
5.) Create an after performance baseline. To determine the results of these changes against the original, identical workload, Ill 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, Ill 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. Ill then produce a simple summary report of these differences via a call to the DBMS_SQLPA.REPORT_ANALYSIS_TASK procedure as Ive 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.
In the next article in this series, Ill 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. Ill 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 Im hopeful that Ive given you a thorough grounding in the technical aspects of the features Ive discussed in this article, Im also sure that there may be better documentation available since its 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 Ive 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