Oracle Database 11g: SQL Performance Analyzer, Part 2

November 29, 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 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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers