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 11gs new SQL Performance Analyzer (SPA) tool to:
In this article, Ill 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 Ill 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, Ill focus on using Oracle 11gs 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. Ive 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, Ill create a SQL Workload on the DB10G database. Ill 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 thatll 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. Ill 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 Ive executed the SQL workload and have gathered the resulting statistics into the STS_SPA_200 SQL Tuning Set, Ill transfer that workload and its corresponding execution statistics to my Oracle 11gR1 database. Ill 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, Ill 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 220.127.116.11. Now that my preparations are completed, Im 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. Ill use the Oracle Database 11gs 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, Ive 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, Ive specified the before and after optimizer versions as 10.2.0.1 and 18.104.22.168, respectively.
Figure 2.2. Creating an Optimizer Upgrade Simulation Task
Once Ive clicked on Submit, control returns to the SQL Performance Analyzer home panel, and the job that Ive 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, Ill 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, Im 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, Ive 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, Ive set up another SQL Performance Analyzer Task named SPA_OUS_200. This task is identical to SPA_OUS_100, except that Ive 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 havent changed in the least, Oracle 11gs 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
The third and final article in this series on Oracle 11gs 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. Ill also delve more deeply into some of the SQL Performance Analyzers 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 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