Oracle Database 11g: SQL Plan Management, Part 2 - Page 2
February 28, 2008
SPM Scenario #2: Deploying a New Application
Whenever Ive deployed a new application against my production databases, I know that Im essentially at the mercy of my application developers and quality assurance analysts. Even though Ive insisted on tight database object naming standards, strict adherence to PL/SQL best practices, and extensive testing of SQL statements using various combinations of bind variable values, I also know that these guidelines are sometimes unrealistic. When my shops application development team and QA resources cant seem to agree upon a set of standards, or if my senior IT management decides that were spending too much time testing, I know theres a good chance that a newly-developed in-house application will wreak havoc on the performance of a (hitherto) well-tuned Oracle database.
Another vector for potentially poor database performance is those marvelous third-party applications that were sometimes forced to implement by executive fiat. Every experienced Oracle DBA has encountered this situation at least once: An outside application is causing extremely poor performance, and upon investigation, it turns out the application has been written using what I like to call agnostic SQL by an offshore development team whose marching orders are to insure that this application can run on any database in any OS environment. The end result, of course, is SQL whose source code is unavailable for direct tuning because its buried deep in the application layer.
The good news is that Oracle 11g does offer some hope for avoiding degraded performance when deploying a brand-new application in either circumstance:
Once Ive captured the new applications SQL statements, I can use the existing QA or development database to capture their corresponding SQL Plan Baselines, and then transfer those baselines directly to the production databases SMB. The end result? These execution plans will already be present when the application is eventually deployed, thus relieving the CBO of the responsibility for building the execution plans for these statements on the fly when the application is first deployed to production. Best of all, if better execution plans do eventually arise for these statements, the CBO will automatically evolve those improved plans.
Preparing the Simulation. Before I can simulate this scenario, I have some setup tasks to perform. Ive gathered them in Listing 2.5:
Capturing SQL Plan Baselines. Ive illustrated the steps to capture a simulated SQL workload for this new application in Listing 2.6:
Exporting SQL Plan Baselines From a Test Environment. As shown in Listing 2.7, Ill export the captured SQL Plan Baselines from my simulated testing environment:
Importing SQL Plan Baselines Into a Production Environment. To conclude this scenario, Ill simulate the deployment of the SQL Plan Baselines to a production environment (see Listing 2.8):
Proof of Concept. Ive verified the successful unpacking by querying data dictionary view DBA_SQL_PLAN_BASELINES. (I used the same query shown in Listing 1.3 in the prior article in this series.) The SQL Plan Baselines for my six target queries appear in the resulting output in Listing 2.9, tagged with an ORIGIN value of MANUAL-LOAD to indicate they originated via DBA intervention instead of the automatic SQL Plan Baseline capture method.
Ive demonstrated how SQL Plan Baselines can effectively limit (if not eliminate) unexpected SQL statement regression during database upgrades, and how to capture SQL Plan Baselines before brand-new application code is deployed to limit plan regression as well. In the next and final article in this series, Ill illustrate another scenario in which SQL Plan Management can help prepare the way for an upcoming database upgrade without having to export SQL Plan Baselines between Oracle 10g and 11g databases. Ill also discuss how to:
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