First Things First
Ask the right questions before starting construction of your new PL/SQL-based application.
By Steven Feuerstein
July/August 2009
We are about to start construction of a brand-new PL/SQL-based application. What are the key best practices we should establish before we dive into writing the code for our next successful application rollout?
There can hardly be a more important question to ask (and have answered) before starting a new project. Before I answer, I first would like to take a step back and ponder this question: “What makes an application a success?”
For an application to be considered successful, it must satisfy several criteria, listed here in order of importance. The application must be
1. Correct. The application must meet user requirements. If it doesn’t do what the user wants it to do, the project will be a complete or partial failure, depending on the extent to which it falls short.
2. Fast enough. The code must run fast enough to (at least) minimize user frustration. If the application meets user needs but runs so slowly that users want to throw their monitors out the window, then it cannot be deemed a success.
3. Maintainable. The code we write today is the code users will run tomorrow, next year, and most likely next decade. If we don’t write our code so that it can be maintained easily and quickly, an initial success will soon turn into a failure, because keeping it running will require inordinate resources.
“Correct” Requires TestingThere is just one way to guarantee that our application code meets user requirements: testing it. Without rigorous, comprehensive, and repeatable testing, we have no way of knowing that the application works. We simply hope for the best.
Manual testing is doomed to disappoint. The best practice is simple to state and challenging to implement: you must commit to automated regression testing of your back-end code and then find a tool that will help you achieve this goal. Options for automated PL/SQL testing include Oracle SQL Developer’s unit testing module, utPLSQL, PLUTO, PL/Unit, DbFit, and Quest Code Tester for Oracle. Each has its own strengths and weaknesses, but any of these will help you establish regression tests that can confirm whether your application is correct.
“Fast Enough” Requires OptimizationThe most important thing you can do to ensure that your code’s performance meets user expectations is to rely on key optimization techniques in PL/SQL. Many of these techniques are covered in other PL/SQL Practices columns in Oracle Magazine , so I will simply summarize the important performance-related features of PL/SQL:
The optimizing compiler. Oracle Database 10g introduced a compiler that automatically optimizes your code to maximize performance. So the rule of thumb here is: use it!
Bulk processing with FORALL and BULK COLLECT. These two features will reduce by an order of magnitude or more the time it takes to execute multirow SQL operations (such as a cursor FOR loop containing an INSERT statement).
Pipelined table functions. Table functions can be called from within the FROM clause of a SELECT statement as if they were relational tables. Pipelined table functions are a specialized form that can be used to improve performance.
Function result cache. New to Oracle Database 11g, the function result cache can reduce execution time for functions that retrieve data from tables whose contents change less frequently than they are queried.
Native compilation. When this feature is used, the runtime PL/SQL engine calls a platform-specific dynamic-link library (DLL) rather than scanning the PL/SQL machine code that is produced with nonnative execution.
These performance features will almost always substantially reduce execution time. In addition, PL/SQL offers more-specialized techniques you should consider employing when you identify bottlenecks in your application and need to eke out every possible performance improvement. To identify bottlenecks, you can now (with Oracle Database 11g) use the traditional PL/SQL profiler (DBMS_PROFILER) or the new hierarchical profiler (DBMS_HPROF) to find the lines of code or entire subprograms that consume too much time.
“Maintainable” Requires StandardsIf everyone on your team has the freedom to write code to their own specifications, you will end up with a truly horrible mess on your hands. High-quality applications are always based on a smart and practical set of standards. The five key elements for which standards must be set to avoid code chaos are the following:
Naming conventions and syntax standards. All developers should follow the same rules for the names given to identifiers (tables, program units, variables, and so on). Beyond this relatively superficial aspect of standards, you should also develop a set of rules that outlines how best to use PL/SQL. For example: use local subprograms and other modularization techniques to keep your executable sections small and self-documenting, do not use GOTO unless absolutely necessary, and avoid multiple RETURNs in the executable sections of your functions.
Writing SQL in PL/SQL. The SQL statements in our applications are the root of most performance issues and must constantly be changed to reflect changes in the business model. I rarely encounter a development team that has even one single rule for how, when, and where to write SQL statements in their PL/SQL program units. And a single rule will do: never repeat the same logical statement in your application code. Repetition complicates efforts to optimize and maintain your code. Instead, build a separate data access layer, also known as a “table API” or “transaction API,” that hides all of your SQL. Your application code then simply calls procedures and functions in this API to perform SQL operations.
Error management. We’re not paid to write execution sections; we’re paid to implement user requirements. For that reason, among others, we seldom consider how errors are trapped, handled, logged, and communicated to users. Instead, everyone writes their own error management code, with terrible consequences: information is logged inconsistently, and key features of PL/SQL (such as the backtrace function and data manipulation language error logging) are ignored. The solution is straightforward: everyone on the team should use the same shared utility to log errors and communicate them to your users.
Application tracing. Most applications are complex, containing thousands of program units and tens (or hundreds) of thousands of lines of code. It is not unusual, therefore, to have trouble understanding what all that code is doing, especially when a user reports a problem you cannot reproduce. For such situations, tracing is a powerful analytical tool. Application tracing, a form of code instrumentation, should be standardized for an application team and used both proactively as you build code and retroactively to get more information out of particularly hard-to-understand parts of your code. Options for tracing include DBMS_APPLICATION_INFO and DBMS_MONITOR from Oracle; Quest Error Manager tracing; and Log4PLSQL, an open source framework built on the popular Log4J.
Version control and backups. You must control access to your software by using a tool that enables you to check changes in and out and ensures that you never experience a “lost update” on your code. In addition, you must have regular, automated backups in place to minimize vulnerability and avoid any kind of catastrophic loss.
Passive Standards Not EnoughSetting standards is just the first step to building code that is based on those standards. You also need to find ways to help developers follow the standards and to verify that they were followed. The best way to ensure that standards are followed is to introduce both peer and automated code review to your development lifecycle.
Have regular team meetings to look over each other’s code, offer ideas for improvement, and learn from each other. You should also complement peer review with automated review.
Automatic code analysis is difficult, but consider using these options:
- Queries against data dictionary views, including ALL_SOURCE, ALL_ARGUMENTS, ALL_DEPENDENCIES, and ALL_OBJECTS. You can search for text that should and should not be present in programs, clean up dependencies between program units, and much more.
- IDE-based, automated code analysis. Some PL/SQL editors offer built-in tools for parsing and analyzing code according to a predefined set of best practices.
- PL/SQL compile time warnings. In Oracle Database 10g and higher, with warnings enabled, the compiler will not only report errors but also will give you feedback on the quality of your code.
- PL/Scope. New to Oracle Database 11g, PL/Scope is a compiler feature that, when enabled, gathers information about how your identifiers (named elements such as variables, constants, subprograms, and exceptions) are used in your code. You can then execute queries on the contents of the ALL_IDENTIFIERS data dictionary view to perform operations that previously were not possible, such as “Find all the lines of code in my subprogram where the l_name variable is assigned a value.” You can also write queries to validate conformance to naming conventions.
Finally, if you want to be successful with your next application, you need to take some time up front to set standards and put processes in place that reinforce them. Before you start writing new code, make sure the answer to each of the following questions is a clear and emphatic “Yes!”:
- Are you going to build automated regression tests for your back-end code?
- Have you picked a tool for your testing?
- Has everyone agreed to naming standards and coding conventions for PL/SQL programs?
- Do you have rules in place for writing SQL that include putting the SQL statements inside a data access layer?
- Do you have a shared utility for handling, logging, and raising errors?
- Have you selected an application tracing utility?
- Do you know how you will verify conformance to your naming standards and coding conventions?
If you take the time to establish these procedures, you can be proud of the resulting application and know that it will be easy to maintain in the coming years.
READ more Best Practice PL/SQL
READ DOWNLOAD |
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.