Synopsis. Oracle Database 11gR1’s new SQL Plan Management tool set gives any Oracle DBA the ability to capture and preserve the most efficient execution plans for any SQL statement. This article – the second in this series – explains how SQL Plan Management can be used during the upgrade of an existing Oracle 10gR2 database to an Oracle 11g environment, as well as during the deployment of brand new application code, to effectively limit unexpected regression of SQL statement performance.
The previous article in this series provided a primer for Oracle Database 11g’s new SQL Plan Management (SPM) features, including some rudimentary examples of how SPM tools help sift through different execution plans to identify and isolate only the best plans to improve SQL statement performance.
Since I’ve already explained and demonstrated the basic architecture of SQL Plan Management, I’ll now shift our focus to discuss two scenarios that every Oracle DBA has encountered: the upgrade of an existing Oracle database to a newer Oracle release, and the deployment of brand new application code against an existing database. While the previous article demonstrated how to use Oracle 11g’s new DBMS_SPM package to capture new SQL Plan Baselines, I’ll use these two scenarios to illustrate the power of Oracle 11g Enterprise Manager Database Control’s SQL Plan Control to capture new candidates for SQL Plan Baseline creation as well as manage existing SQL Plan Baselines.
SPM Scenario #1: Upgrading an Existing Database
In my humble opinion, the upgrade of an existing database to the next software release is one of the most stressful situations even an experienced Oracle DBA can undergo because it can be extremely difficult to determine exactly which statements are performing poorly after the upgrade. In pre-Oracle 11g environments, I’ve found the best method to limit this uncertainty is to construct as nearly a perfect duplicate of my production environment on my QA server, capture an adequate SQL workload of the most critical statements for my applications, and capture EXPLAIN PLANs for those statements. Then once I’ve “thrown the switch” to upgrade the QA database and environment to the next database release, I’d again generate EXPLAIN PLANs for these same statements and compare the results to find any regressing statements.
While this brute force testing method has served me quite well prior to Oracle 11g, I’ve always hoped for a more reliable method to determine exactly what the impact of an upgrade would be upon the performance of existing SQL statements. But as I’ve demonstrated in the prior article series on SQL Performance Analyzer, it’s now extremely simple to isolate any SQL statements whose performance would regress as a result, even for relatively minor intra-release upgrades (e.g. 11.1.0.5.0 to 11.1.0.6.0). Once all regressing SQL statements have been identified with SQL Performance Analyzer, I’ll bring the full power of SQL Plan Management to bear by capturing those statements into a SQL Tuning Set (STS) before I perform the upgrade to the database.
Since an STS captures the statements’ SQL text, bind variables, execution plans, and execution statistics, I’ll retain them until just after the database version upgrade is completed, at which time I’ll transform these statements’ execution plans into SQL Plan Baselines. When these statements are executed for the first time against the upgraded database, however, the cost-based optimizer (CBO) detects that a SQL Plan Baseline is already available. If the CBO decides that the SQL Plan Baseline offers a more efficient execution plan, it will use the baselined plan instead. The end result is that a potentially serious SQL plan regression is completely avoided.
Gathering a SQL Workload. To demonstrate these concepts, I’ll first create a SQL Workload against an Oracle 10gR2 database. I’ll use the five queries against several tables in the Sales History (SH) schema shown in SPM_2_1.sql to simulate a SQL workload that would typically appear in a data warehousing application. Before I start the workload, however, I’ll initiate the code shown in Listing 2.1. It uses DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET to capture the workload’s SQL statements into a SQL Tuning Set named STS_SPM_200.
Packaging and Exporting the SQL Tuning Set. Once I’ve captured the SQL workload into a SQL Tuning Set, I’ll prepare to transfer it to an Oracle 11gR1 database. Listing 2.2 shows how to:
- Create staging tables as containers for the SQL Tuning Set STS_SPM_200
- Transfer the SQL Tuning Set into those staging tables via procedure DBMS_SQLTUNE.PACK_STGTAB_SQLSET
- Export those populated staging tables via DataPump Export into a dumpset named DumpStagingTable.dmp
Transferring the SQL Tuning Set. After I’ve copied the DataPump dump set into the default DataPump directory of my target Oracle 11g database, I’ll import the staging tables into the target Oracle 11gR1 database using Oracle DataPump Import and an appropriate parameter file. I’ll then use the DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET procedure to “unpack” the SQL Workload stored in those staging tables. Listing 2.3 shows the details of the transfer process.
Loading the SQL Tuning Set Contents Into SPM. To complete the transformation of the statements stored in the SQL Tuning Set, I’ll load these statements directly into the SQL Management Base. Instead of using DBMS_SPM procedures to accomplish this, I’ll utilize Enterprise Manager Database Control ’s SQL Plan Control interface, the link to which is accessed from the Server page:
Figure 2.1. SQL Plan Control Home Panel
Note that the prior SQL Plan Baselines created during my prior experiments are listed here, including their current status and availability. As shown in Figure 2.2 below, I’ll select SQL Tuning Set STS_SPM_200 from those available on this panel:
Figure 2.2. Loading a SQL Tuning Set Into the SMB
Once I’ve chosen the appropriate SQL Tuning Set and clicked the Load button, Oracle 11g automatically loads the SQL statements from the selected STS directly into the SMB, as shown in Figure 2.3 below:
Figure 2.3. Results of SQL Tuning Set Load
Note that the state of all five SQL Plan Baselines is ENABLED and ACCEPTED, which means that they are immediate candidates for use by the CBO when a SQL statement with a matching hash value is encountered. I can also view the details of the corresponding EXPLAIN PLANs for each SQL Plan Baseline by clicking on the link in the Name column. Here’s the result from selecting the baseline named SYS_SQL_685ea4c28ec1a586:
Figure 2.4.1. SQL Statement SPM_2.1.3 Explain Plan, Part 1
Figure 2.4.2. SQL Statement SPM_2_1.3 Explain Plan, Part 2
Proof of Concept. To prove that potential SQL statement performance regression is curtailed or eliminated, I’ll now simply execute the same five SQL statements in SPM_2_1.sql and verify that the CBO is indeed choosing the pre-loaded SQL Plan Baselines instead of a newly parsed and less effective execution plan. The easiest way to determine this is to execute the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE procedure for these five statements while passing a value of TYPICAL +NOTE to the FORMAT parameter to request the display of the plan that the CBO has chosen. The +NOTE directive instructs the procedure to display a note if the CBO has indeed selected an existing SQL Plan Baseline for its execution plan. Listing 2.4 shows the results of executing this procedure for the five statements in SPM_2_1.sql.
