Oracle Built−in Packages- P45: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk Example The following SQL Plus script displays a screen message and pauses for ten seconds before continuing prompt prompt This is a very important message prompt BEGIN 10 END Applications using resources to which concurrent access is restricted may need to try again later if the resource is busy. The SLEEP procedure provides a mechanism for including low-overhead wait times into PL SQL programs. After waiting an application can retry the operation that failed to acquire the busy resource. Tips on Using DBMS_LOCK In this section I ve pulled together a number of best practices for using the DBMS_LOCK package. Named locks or lock ids Oracle provides two methods of identifying and manipulating user locks integer lock identifiers and handles for named locks. Using names and lockhandles to identify locks is considered safer than using integer identifiers directly because naming standards can be adopted to virtually guarantee that different applications will not use the same lock for different purposes. Therefore best practices for using DBMS_LOCK include the use of named locks and lockhandles. Issues with named locks There are a couple of drawbacks to using named locks that are worth pointing out. In particular Named locks are recorded in the catalog and thus may be slower. The procedure issues a COMMIT. Applications need to keep track of lockhandles for each named lock used. It is worth investigating these drawbacks and developing techniques to minimize their impact thus further encouraging the use of named locks. Performance of named locks We can investigate the performance penalty for using named locks and quantify that penalty in a relatively straightforward manner. Consider the following PL SQL script Filename on companion disk set timing on set serveroutput on size 100000 DECLARE The DBMS_LOCK Interface 211 Appendix A What s on
