Section 23.7.  What You Need to Know

23.7. What You Need to Know So, do you really need to remember everything in this chapter? I certainly hope not, because I can't even remember it in my day-to-day work. Your database administrator, on the other hand, probably needs to know most of this stuff. In addition to satisfying healthy curiosity, my goal in presenting this material was to help allay any misgivings programmers might have about what happens under the hood. Whether or not you've ever had such concerns, there are a number of important points to remember about what goes on inside PL/SQL: To avoid compilation overhead, programs you plan to use more than a few times should be put in stored programs rather than stored in files as anonymous blocks. In addition to their unique ability to preserve state throughout a session, PL/SQL packages offer performance benefits. You should put most of your extensive application logic into package bodies. When upgrading Oracle versions, new features in the PL/SQL compiler warrant thorough application testing. In some (probably rare) cases when upgrading to Oracle Database 10g, slight changes in execution order, resulting from freedoms exploited by the optimizing compiler, could affect application results. While Oracle's automatic dependency management approach relieves a huge burden on developers, upgrading applications on a live production database should be undertaken with great care because of the need for object locking and package state reset. If you use signature-based remote dependency checking in remote procedure calls or with a loopback-link synonym as a way to avoid invalidations, you should institute (manual) procedures to eliminate the possibility of the signature check returning a false negative (which would cause a runtime error). Use definer rights to maximize performance and to help simplify the management and control of privileges on database tables. Use invoker rights only to address particular problems (for example, programs that use dynamic SQL and that create or destroy database objects). Oracle's sophisticated approaches aimed at minimizing the machine resources needed to run PL/SQL occasionally benefit from a little help from developers and DBAsfor example, by explicitly freeing unused user memory or pinning objects in memory. Where it makes sense to your application logic, use the cursor FOR loop idiom, rather than open/fetch loop/close, to take advantage of the automatic bulk binding feature in Oracle Database 10g. When your program does need to open an explicit cursor in a PL/SQL program, be sure to close the cursor as soon as fetching is complete. Native compilation of PL/SQL may not offer any performance advantages for SQL-intensive applications, but it can significantly improve the performance of compute-intensive programs. Calling remote packages entails some special programming considerations if you want to take advantage of anything in the package other than procedures, functions, types, and subtypes. Use program variables in embedded static SQL statements in PL/SQL, and bind variables in dynamic SQL statements, to avoid subverting Oracle's cursor sharing features. Now ... if you've made it through this entire chapter, I may one day come and bother you with my questions.