Using Query Plans to Reduce the Cost of Operating a DBMS
By Eric Miner
Sr. Software Engineer
There are many ways to reduce the total cost of ownership of a Relational Database Management System. Certainly, decreasing the response time and increasing the overall performance of an enterprise-class DBMS allows you to make more efficient use of your existing hardware and reduce the overall administration needed thus reducing operating costs. In an enterprise DBMS, the optimizer's job is to determine the most efficient way to access the data and pass this information on for execution of the query. The query plan is the information on how to execute the query, which is produced by the optimizer. Basically, the query plan contains a series of specific instructions on how to most efficiently retrieve the required data.
Sybase Adaptive Server Enterprise makes each query plan fully available to you so they can be captured, associated to their original query, and reused over and over, bypassing the optimizer fully or partially. They can even be edited and included in a query using the new T-SQL PLAN statement, or can be used without adding them to the SQL text. This feature is called Abstract Plans. You don't have to use Abstract Plans (AP's), of course; in the vast majority of cases the optimizer will indeed choose the most efficient plan. In some cases, of course, there will be no change; and in some there may be performance regressions. APs can be captured before applying the upgrade, and then, once the upgrade has been completed, queries can be rerun to see if there is any negative change in performance. If a performance regression is identified after the upgrade, the AP from the previous version can then be used in the new version to run the query and Sybase can be contacted regarding the problem. In the case of a possible optimizer bug, you may write an AP to workaround the problem while Sybase investigates it. In both cases, once the problem is fixed, you can stop using the AP if you choose. It is the possibility of performance regressions between versions of ASE and optimizer-related bugs that APs were first designed to alleviate. These are very good uses for APs, but the power and flexibility that APs provide can be used in other ways too to ensure high performance of the DBMS and reduce overall costs.
Eric Miner has been with Sybase since 1992, working with the optimizer team in engineering, as well as focusing on optimizer issues for technical and product support engineering. He can be reached at firstname.lastname@example.org.