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
