Oracle Database 11g: SQL Performance Analyzer, Part 2

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 next in a series on new Oracle Database 11g SQL performance
improvement features – discusses how to use the SQL Performance Analyzer to
evaluate a SQL workload generated from an Oracle 10gR2 database to detect
potential performance issues for that workload if it were executed within an
Oracle 11g database system.

The previous
article
in this series illustrated the basics of using Oracle Database
11g’s new SQL Performance Analyzer (SPA) tool to:

  • Capture a SQL workload
  • Record that workload’s performance against the current
    database configuration before changes have been made to that
    configuration
  • Record that same workload’s performance after the database
    configuration changes have been implemented
  • Compare the performance of that workload before and after
    the changes were made

In this article, I’ll demonstrate how the SQL Performance
Analyzer can analyze more complex Oracle environment changes – specifically,
the upgrade of the cost-based optimizer (CBO) between Oracle database releases
— and I’ll show how to transfer a SQL workload generated from an Oracle 10gR2
database to an Oracle 11gR1 database. In addition, since I concentrated on the
PL/SQL interface to measure SQL workload performance in the last article, I’ll
focus on using Oracle 11g’s Enterprise Manager (EM) Database Control interface
for the SQL Performance Analyzer to demonstrate how easy it is to analyze a SQL
workload with a few mouse clicks.

SQL Performance Analysis Scenario: Simulating an Optimizer Upgrade

1.) Prepare for Simulation. I’ve built an Oracle
10gR2 database named DB10G using the standard
“seed” database within an Oracle 10gR2 home. To carry forward the identical example
from the previous article, I then constructed the,
SH.SALES_AGENTS table on that database, loaded it with the same sample
set of approximately 420,000 rows, and then constructed the same five indexes
against that table. I used the identical DDL that I presented in Listing
1.1
for the previous article to accomplish this, and I utilized the identical
DML statements (LoadSalesAgents.sql) to perform an
initial load of this table.

2.) Prepare for SQL Workload Gathering. Next, I’ll
create a SQL Workload on the DB10G database. I’ll
use the same SQL statements that access the SH.SALES_AGENTS
table that I used in the previous article, along with a few additional SQL
statements that’ll access some of the other demo schema tables. The statements
that comprise this SQL workload are shown in GenerateSQLWorkload.sql.
Note that the last four SQL statements – the ones tagged as LDGN 4.1 through LDGN 4.4
– make use of older optimizer hints that are still available in Oracle 9i but
no longer fully supported in Oracle 10g. I’ll use these statements to
demonstrate how the SQL Performance Analyzer can simulate performance using
cost-based optimizer settings from other older Oracle database environments. The
code shown in Listing
2.1
captures these SQL statements into a SQL Tuning Set named STS_SPA_200.

3.) Stage and Export SQL Tuning Set. Now that I’ve
executed the SQL workload and have gathered the resulting statistics into the STS_SPA_200 SQL Tuning Set, I’ll transfer that workload
and its corresponding execution statistics to my Oracle 11gR1 database. I’ll do
this by creating staging tables to hold that information, transferring
the SQL workload and its performance information into that table, and then transporting
those data to the targeted Oracle 11g database. Listing
2.2
shows how I captured (or “packed”) the SQL Tuning Set into staging
tables with the DBMS_SQLTUNE.PACK_STGTAB_SQLSET
procedure. I then used Oracle DataPump to export those staging tables into a DataPump
Export dumpset named DumpStagingTable.dmp.

4.) Import, unpack, and prepare SQL Tuning Set for test
execution.
After copying the DataPump dump set into the default DataPump
directory for my Oracle 11g database, I’ll need to transfer the information
contained within the staging tables for the SQL Tuning Set into my Oracle 11g
target database for analysis. Listing
2.3
illustrates how I imported the staging tables from Oracle 10gR2
into the target Oracle 11gR1 database using Oracle DataPump Import and an
appropriate parameter file. I then used the DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET
procedure to “unpack” the SQL Workload stored in those staging tables into my
target Oracle 11gR1 database.

5.) Simulate an Optimizer Upgrade From 10.2.0.1 to
11.0.1.6.
Now that my preparations are completed, I’m ready to turn the SQL
Performance Analyzer loose to let it find any noticeable performance impacts on
the SQL statements in the imported SQL Workload if it were executed against the
target Oracle 11gR1 database
. I’ll use the Oracle Database 11g’s Enterprise
Manager Database Control interface to perform this analysis. Figure 2.1
shows the initial state of the SQL Performance Analyzer panel.

Figure 2.1. SQL Performance Analyzer Home Panel

When I select the Optimizer Upgrade Simulation link from
this panel, Enterprise Manager presents the Optimizer Upgrade Simulation panel.
As shown in Figure 2.2, I’ve specified a task name of SPA_OUS_100, SYS.STS_SPA_200
as the SQL Tuning Set, and a brief description of the task. Note that for this
task, I’ve specified the “before” and “after” optimizer versions as 10.2.0.1
and 11.1.0.6, respectively.

Figure 2.2. Creating an Optimizer Upgrade
Simulation Task

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 2.3 below).

Figure 2.3. Successful execution of SQL
Performance Analyzer task SPA_OUS_100

To view the completed task, I simply click on the Task Name link, and then Enterprise Manager presents
a summary of the successful task execution as shown below in Figure 2.4:

Figure 2.4. SQL Performance Analyzer Task
SPA_OUS_100 Execution Details

To see the details of the initial run of this task, I’ll
click on the corresponding “eyeglasses” icon under the Comparison Report column
near the bottom of this panel, and this displays the results shown in Figure
2.5.1
:

Figure 2.5.1. Results of Elapsed Time Comparison

At last, some interesting results – and mostly good news!
The SQL Performance Analyzer shows that it believes there will be at least some
improvement in execution time – or at least, no degradation in execution time –
for all 12 SQL statements if they were executed in an Oracle 11gR1 database
environment. However, I’m not satisfied with merely comparing execution times;
I also want to know more about the change to any of the execution plans.
Therefore, I reran the same detailed analysis, but this time I compared the
estimated changes in optimizer costs as well. Figure 2.5.2
and Figure 2.5.3 speak for themselves: Though there are some
pretty apparent improvements in execution time, only one statement had any
significant improvements in optimizer cost,

Figure 2.5.2. Elapsed Time Comparison Details Within
SQL Statement

Figure 2.5.3. Optimizer Cost Comparison Details
Within SQL Statement

6.) Simulate an Optimizer Upgrade From 9.2.0 to 11.0.1.
Out of curiosity, I’ve also experimented with using the same SQL Tuning Set (STS_SPA_200) to simulate an upgrade from the Oracle
9i optimizer to the Oracle 11g optimizer. To accomplish this, I’ve set up
another SQL Performance Analyzer Task named SPA_OUS_200.
This task is identical to SPA_OUS_100, except that
I’ve selected a “before” optimizer setting of 9.2.0 instead of 10.2.0.1.
A summary of the execution of this new simulated optimizer upgrade task is
shown in Figure 2.6, while Figure 2.7 shows the results page for
that same task:

Figure 2.6. SQL Performance Analyzer Task SPA_OUS_200
Execution

Figure 2.7. SQL Performance Analyzer Task
SPA_OUS_100 Results

Figures 2.8.1, 2.8.2, and 2.8.3
show the results for just one of the SQL statements for which the optimizer
upgrade simulation was performed. Note that while the actual number of buffer
gets and the number of rows processed for this statement haven’t changed in the
least, Oracle 11g’s SQL Performance Analyzer is apparently smart enough to
project that the 9.2.0 optimizer cost (1001) would have been slightly higher
(995) if the statement were executed under the 11.1.0 optimizer. Also, note
that the EXPLAIN PLAN for the two simulated executions were slightly different
for the identical SQL statement.

Figure 2.8.1. SQL Performance Analyzer Task SPA_OUS_200
Result (Statement 5pkmwau93fg58)

Figure 2.8.2. “Before” EXPLAIN PLAN Results for
Statement 5pkmwau93fg58

Figure 2.8.3. “After” EXPLAIN PLAN Results for
Statement 5pkmwau93fg58

Next Steps

The third and final article in this series on Oracle
11g’s new SQL Performance Analyzer tool set will discuss how to use this tool
to conquer one of the more frustrating SQL performance tuning challenges: the
impact of modified database initialization parameters upon SQL statement
performance. I’ll also delve more deeply into some of the SQL Performance
Analyzer’s reporting and analyses options to get 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.

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.

Latest Articles