THEN IF right_now < hire_date THEN hdate := right_now; ELSIF ADD_MONTHS (right_now, -120) > hire_date THEN MESSAGE (' Hire date ' || TO_CHAR (hire_date, 'MM/DD/YY') || ' more than ten years past.'); RAISE FORM_TRIGGER_FAILURE; END IF; END IF; END; The performance gain from each of these conversions may be slight. If they were the only tuning steps you took (or the only ones left to take) in your PL/SQL programs, I doubt that you would notice a difference. Combined with all the other tuning tips, however, avoidance of host variables in PL/ SQL blocks will contribute to a more efficient PL/SQL layer in your application. 25.4.4 Use Package Data to Avoid Passing "Bulky" Parameter Values With the exception of cursor variables, PL/SQL passes arguments by value, instead of reference, in the parameter lists of procedures and functions. If the argument can be changed (i.e., it is an OUT or IN OUT parameter), then the runtime engine makes a local copy of your structure and applies changes to that copy. If the program terminates without error, the local data is copied to your structure, which is then returned to the calling program. This approach preserves the values of actual parameters against the possibility of program failure, but it introduces some potential performance problems. This is particularly the case when your OUT or IN OUT parameters are complex data structures such as records and PL/SQL tables. Suppose that a record has 15 columns in it. Every time you pass that record into a procedure or function as an OUT or IN OUT parameter, PL/SQL copies the entire record into an internal record structure, column by column. Suppose that a PL/SQL table has 100 rows defined in it. Every time you pass that table into a procedure or function as an OUT or IN OUT parameter, PL/SQL copies the entire table into an internal table structure, row by row. Suppose, now, that you have a record with 15 columns in it, three of which are PL/SQL tables, each with 100 rows. Then, every time you pass that record into a procedure or function as an OUT or IN OUT parameter, PL/SQL copies the entire record into an internal record structure, column by column and row by row -- for a total of 312 copies! And if that procedure calls another procedure, passing the record down to that inner procedure, well, PL/SQL executes the same copy process within the second procedure. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. As you can easily imagine, this copying could consume a noticeable amount of memory and CPU cycles in your application. So should you avoid using records and PL/SQL tables as parameters in programs? Absolutely not! These advanced, multi-component data structures are powerful constructs in your program -- you definitely should take advantage of them. However, you should be aware of the kinds of problems you might encounter when using these kinds of parameters. And you should be ready to implement a workaround if you find that performance in your application is dragged down as a result of record or PL/SQL table copying. The workaround for this problem is straightforward enough: don't pass the record or table as a parameter. But then how can you get your data inside your program? Replacing a record with a parameter list of 20 different parameters isn't really an answer, since PL/SQL will continue to execute 20 copies to internal variables. No, the answer is to stop PL/SQL from executing the copies altogether. To accomplish this, make use of package-level data, as explained in Chapter 16, Packages. Here's an example of the steps you could take to avoid passing these data structures as parameters. Suppose you have a procedure which accepts both a record and a PL/SQL table as parameters, as follows: PROCEDURE massage_mucho_data (io_columns_galore_rec IN OUT columns_galore%ROWTYPE, io_lotsarows_tab IN OUT lotsarows_tabtype); To use this procedure, I first declare and populate both the row and table. Then, I call the procedure: DECLARE galore_rec columns_galore%rowtype; TYPE lotsarows_tabtype IS TABLE OF VARCHAR2 INDEX BY BINARY_INTEGER; my_table lotsarows_tabtype; BEGIN fill_table (my_table); massage_mucho_data (galore_rec, my_table); END; As PL/SQL executes the statements in massage_mucho_data, the contents of the record and PL/SQL table are copied into local structures. Upon successful termination of the program, the data in those local structures is copied back to galore_rec and my_table. If, on the other hand, I create a package with the record and table types and instances in the specification as shown below, those data structures are "global" in my session. I can manipulate them directly in any procedure or function without passing them into those programs as parameters. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. PACKAGE mucho_data_pkg IS /* Declare the record in the package specification. */ galore_rec columns_galore%rowtype; /* Define the table structure and declare the table. */ TYPE lotsarows_tabtype IS TABLE OF VARCHAR2 INDEX BY BINARY_INTEGER; my_table lotsarows_tabtype; END mucho_data_pkg; After I populate the table, I can call the revised version of massage_mucho_data -- which no longer has any parameter at all! BEGIN fill_table; massage_mucho_data; END; I do not need to declare the package table. Nor do I need to declare the record. I simply modify the internals of massage_mucho_data to append the package name "mucho_data_pkg" to the names of the record and PL/SQL table. In other words, whereas a line in the original massage_mucho_data procedure might have looked like this: FOR row_count IN 1 100 LOOP io_lotsarows_tab (row_count) := row_count ** 2; END LOOP; this same loop in the new, parameter-less version of massage_mucho_data would appear as follows: FOR row_count IN 1 100 LOOP mucho_data_pkg.my_table (row_count) := row_count ** 2; END LOOP; I no longer pass the complex data structures as parameters. I reference them directly in the procedure. PL/SQL no longer copies the values to internal variables. There are, by the way, a number of downsides to globalizing your data as shown above. When you use global data structures, you increase the dependencies between modules in your system. The massage_mucho_data is no longer a black box, completely independent of other elements of the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. application. Instead, it requires the data structures in the mucho_data_pkg. When I passed in the table as a parameter to fill_table, I gave myself the flexibility to call fill_table for any table of the correct table type. If I need this flexibility, then I cannot push the data structure down inside the package. If, on the other hand, these programs really will be run only for that record and that table, then package- level data would be the way to go. The power of packages, particularly to provide package-level data, should make you want to place most, if not all, of your modules inside a package. You may not need to group additional modules into that package immediately. You may not need package-level data at this time. By using a package right from the start, however, you ensure that all references to the procedure or function are already prefaced with the package name. Otherwise, when you decide you need to wrap a package around a procedure, you either have to create a synonym for the newly-packaged object or change all references to the unqualified program name. Given these drawbacks, you should convert from parameters to global variables only when you have verified that the parameter-copying of these data structures has an unacceptable impact on your application. Document clearly the changes you have to make and, most importantly, the data structures which have become globals in your system. A later release of PL/SQL might fix this problem by no longer insisting on performing the copies for OUT and IN OUT parameters. When this is the case, you will want to consider converting back to a parameter interface. This will be practical only if you have documented your workarounds. 25.4.5 Use PLS_INTEGER for All Integer Operations When you need an integer variable, use the PLS_INTEGER type and not BINARY_INTEGER, INTEGER, or NUMBER. PLS_INTEGER is the most efficient implementation for integer types (available in PL/SQL 2.3 and above). Numeric types such as INTEGER and NUMBER are represented in the 22-byte Oracle number format. Arithmetic operations on these types are implemented using Oracle number libraries. Furthermore, the INTEGER type is a constrained subtype of NUMBER with a precision of 0. On assignments to INTEGER variables precision checking is done at runtime. Both PLS_INTEGER and BINARY_INTEGER are represented as a signed 4-byte quantity (sb4). But BINARY_INTEGER arithmetic is costly; the operands are first converted to an Oracle number, and then the Oracle number library is used to compute the result as another Oracle number. This results in increased use of temporaries, data conversion, and, hence, poor performance. On the other hand, arithmetic operations on PLS_INTEGERs are efficiently implemented using native integer arithmetic. Unfortunately, it is not possible to fix all the implementation inefficiencies with INTEGER and BINARY_INTEGER datatypes without breaking backward compatibility of old applications. For instance, Oracle cannot simply implement these types the same way as PLS_INTEGER without Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. changing the overflow/underflow semantics. (The sum of two BINARY_INTEGERs will result in an overflow only if the result exceeds the maximum value storable in an Oracle number. The sum of two PLS_INTEGERs will result in an overflow when the sb4 limit is exceeded.) The numeric types NATURAL, NATURALN, POSITIVE, POSITIVEN, and SIGNTYPE are subtypes of BINARY_INTEGER with narrower range constraints. There is considerable overhead (about three or four byte-code instructions) in the enforcement of these range constraints on every assignment (or parameter passing) to variables of these types. One caution about the use of PLS_INTEGER: this is a PL/SQL-specific datatype, so if you are constructing a function or procedure to be used in an environment which does not support that datatype, you could run into trouble. For example, a function which will be called inside SQL cannot have a PLS_INTEGER parameter. Instead, declare the parameter to be INTEGER, but then (if there is sufficient internal integer arithmetic) copy that value to a local variable of type PLS_INTEGER and perform computations on that variable. 25.4.6 Avoid NOT NULL Constraints Using NOT NULL constraints in PL/SQL comes with a performance penalty. Consider the program fragment below: procedure foo is m number not null; a number; b number; begin m := a + b; m := m * 1.2; m := m * m; end; Since "m" is a NOT NULL constrained number, the result of the expression "a+b" is first computed into a temporary, and the temporary is then tested for NULLity, If the temporary is NULL an exception is raised; otherwise the value of the temporary is moved to "m". On the other hand, if "m" were not constrained, then the result of the expression "a+b" could directly be computed into "m". So a more efficient way to rewrite the above fragment with reduced use of temporaries is: procedure foo is m number; -- Note: m doesn't have NOT NULL constraint a number; b number; begin Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. m := a + b; m := m * 1.2; m := m * m; -- enforce constraint programmatically if (m is null) then -- raise appropriate error end if; end; Another thing to note is that the types NATURALN and POSITIVEN are defined to be NOT NULL subtypes of NATURAL and POSITIVE, respectively; thus, you will incur the performance penalty described above when you use them. 25.4.7 Avoid Type Conversions When Possible PL/SQL does implicit conversions between structurally different types at runtime. Currently, this is true even when the source item is a literal constant. A common case where implicit conversions result in a performance penalty, but can be avoided, is with numeric types. For instance, assigning a PLS_INTEGER variable to a NUMBER variable or vice-versa will result in a conversion since their representations are different. Such implicit conversions can happen during parameter passing as well. Some examples of inefficient code and suggestions to fix them are shown below: 1. Prevent conversions between numeric types. Consider: number_variable := number_variable + 1; The literal constant 1 is represented as an sb4; it gets converted to Oracle number format before the addition. Instead, use: number_variable := number_variable + 1.0; The above is more efficient because literal floats (like 1.0) are represented as Oracle numbers, so no type conversion happens at runtime. Or better still, when dealing with integer data, use: pls_integer_variable := pls_integer_variable + 1; 2. Prevent numeric to character type conversion. Consider: char_variable := 10; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. The literal 10 is converted to CHAR at runtime, and then copied. Instead use: char_variable := '10'; 25.4.8 Use Index-By Tables of Records and Objects In Oracle 7.3, PL/SQL added support for index-by tables of records. (Index-by tables were formerly known as PL/SQL tables.) Prior to that, users modeled the same as a bunch of index-by tables of scalars (one for each record attribute). Users should strongly consider rewriting such applications to use table of records (or objects). The effort will pay off in two major ways: ● Better application performance: The number of index-by table lookup, insert, copy, and similar operations will be reduced dramatically if the data is packaged into an index-by table of records. ● Improved memory utilization: There is memory overhead associated with each index-by table (primarily due to its tree structured implementation). Having fewer index-by tables will help cut this overhead, and also reduce memory fragmentation caused due to the varying size allocations in different tables. Previous: 25.3 Tuning Access to Your Data Oracle PL/SQL Programming, 2nd Edition Next: 25.5 Overview of PL/ SQL8 Enhancements 25.3 Tuning Access to Your Data Book Index 25.5 Overview of PL/SQL8 Enhancements The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Previous: 25.4 Tuning Your Algorithms Chapter 25 Tuning PL/SQL Applications Next: 26. Tracing PL/SQL Execution 25.5 Overview of PL/SQL8 Enhancements Oracle Corporation has improved the peformance of PL/SQL programs with the release of Oracle8 and PL/SQL8. Without delving into detailed descriptions of the internal changes made to the engine, it is worth noting which elements of the technology have improved. This knowledge may well affect your choice of implementation for algorithms and your willingness to employ some features which in the past have come with a noticeable penalty. PL/SQL8 offers improved performance in the following areas:[ 2] [2] This information has been provided by Oracle Corporation and has not been independently confirmed. ● The memory required for PL/SQL code in the SGA has been reduced by about 20-25%. This will be especially important for large packages. In addition, Oracle8 uses a paging algorithm for code segments and data segments, leading to less fragmentation in the SGA. ● Large VARCHAR2 and RAW variables are dynamically allocated and resized as needed. For example, you might declare a package variable to be length 32,000 to handle all possible scenarios, but in most cases that variable only holds 255 bytes of data. You will no longer pay the penalty of all that extra memory. ● Anonymous blocks with bind variables execute much more rapidly. This is of special importance when you are working with DBMS_SQL or executing PL/SQL functions from within SQL. Oracle claims that the overhead of calling PL/SQL functions in SQL now is "negligible." If true, this will have significant implications for the deployment of PL/SQL code throughout SQL implementations. ● The use of temporary data structures has been minimized, resulting in performance improvements for such operations as concatenations and implicit conversions. ● PL/SQL records are now supported more efficiently for runtime operations. Prior to Oracle8, the compiler exploded records into individual scalar fields. Now, as you would expect with support for objects, PL/SQL provides native support for the RECORD composite type. ● The performance of index-by tables (formerly known as PL/SQL tables) has improved significantly due to a change in implementation from B*trees to a paged-array representation. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Previous: 25.4 Tuning Your Algorithms Oracle PL/SQL Programming, 2nd Edition Next: 26. Tracing PL/SQL Execution 25.4 Tuning Your Algorithms Book Index 26. Tracing PL/SQL Execution The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Previous: 25.5 Overview of PL/SQL8 Enhancements Chapter 26 Next: 26.2 Tracing for Production Support 26. Tracing PL/SQL Execution Contents: The PL/SQL Trace Facility Tracing for Production Support Free Format Filtering Structured Interface Filtering Quick-and-Dirty Tracing As you build more and more complex applications, it can be very difficult to keep track of which procedure calls which function; execution call stacks grow deep and bewildering. Yet there are times when it is very important to be able to trace the activity in your PL/SQL code base. Oracle offers a trace facility for your PL/SQL code which allows you to generate voluminous information about the particular paths your programs take to get their job done. Of course, Oracle has for years offered a SQL trace facility which provides extensive data on the processing of your SQL statements. See Oracle Performance Tuning for more information on this feature, as well as other tuning/tracing utilities like TKPROF. In addition to these standard Oracle facilities, you can build your own tracing utilities; the final section in this chapter offers an architecture and some implementational ideas for a utility designed specifically to trace execution within a running application. (Such a utility is particularly useful for production support.) 26.1 The PL/SQL Trace Facility PL/SQL8 offers a tracing tool for server-side PL/SQL. You can use this tool to trace the execution of PL/SQL programs and the raising of exceptions within those programs. The output from the trace is written to the Oracle Server trace file. On Windows NT, you can find this trace file in the \OraNT \RDBMS80\TRACE directory. In UNIX, check the $ORACLE_HOME\rdbms\trace directory. The name of the file has the format ORANNNNN.TRC, where NNNNN is a left zero-padded number assigned internally by the Oracle Trace facility. Order your directory by date to find the latest trace file. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. [...]... Stored Procedures from PL/SQL Version 1.1 Contents: Using Stubs to Talk to Server-Side PL/SQL Restrictions on Calling Stored Procedures The Oracle Developer/2000 tools use PL/SQL Version 1.1, while the PL/SQL versions on the server range from 2.0 to 8.x The PL/SQL version inside the Oracle Developer/2000 tools will be upgraded to PL/SQL Release 2.3 sometime in 1998 with Release 2 of Oracle Developer/2000... The PL/SQL Trace Facility 26.1 The PL/SQL Trace Facility Oracle PL/SQL Programming, 2nd Edition Book Index Next: 26.3 Free Format Filtering 26.3 Free Format Filtering The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates All rights reserved Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark Previous: 26.2 Tracing for Production Support Chapter 26 Tracing PL/SQL. .. 25.5 Overview of PL/SQL8 Enhancements 25.5 Overview of PL/SQL8 Enhancements Oracle PL/SQL Programming, 2nd Edition Book Index Next: 26.2 Tracing for Production Support 26.2 Tracing for Production Support The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates All rights reserved Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark Previous: 26.1 The PL/SQL Trace Facility... applies only to PL/SQL Version 2.0; this restriction goes away in Release 2.1 and beyond.) A stored procedure called from Oracle Developer/2000 cannot have only OUT argument types From Oracle Developer/2000, you can call stored procedures, but you cannot debug them from within the Oracle Developer/2000 tool (You will be able to do so with PL/SQL Release 2.2 and above; this release runs against Oracle7 Server... PDF Split-Merge on www.verypdf.com to remove this watermark Previous: A.1 Installing the Guide A.1 Installing the Guide Oracle PL/SQL Programming, 2nd Edition Book Index Next: B Calling Stored Procedures from PL/SQL Version 1.1 B Calling Stored Procedures from PL/SQL Version 1.1 The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates All rights reserved Please purchase PDF Split-Merge... more information about dynamic PL/SQL in Oracle Built-in Packages You can also overload the dynvar package to perform assignments and retrievals for dates, numbers, and so on in their native formats Previous: 26.4 Structured Interface Filtering 26.4 Structured Interface Filtering Oracle PL/SQL Programming, 2nd Edition Book Index Next: VII Appendixes VII Appendixes The Oracle Library Navigation Copyright... disk that accompanies this book Appendix B, Calling Stored Procedures from PL/SQL Version 1.1, describes the mechanism for allowing Oracle Developer/2000-based applications to call stored procedures Appendix C, Built-In Packages, offers a quick reference to many of the Oracle built-in functions Previous: 26.5 Quick-andOracle PL/SQL Dirty Tracing Programming, 2nd Edition 26.5 Quick-and-Dirty Tracing... directory It contains the Oracle PL/SQL Programming Companion Utilities Guide, an online tool designed to help you easily find additional resources The guide offers pointand-click access to nearly 100 files of source code and documentation prepared by the authors The goal of providing this material in electronic form is to give you a leg up on the development of your own PL/SQL programs Providing material... Appendixes Oracle PL/SQL Programming, 2nd Edition Book Index Next: A.2 Using the Guide A.2 Using the Guide The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates All rights reserved Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark Previous: A.1 Installing the Guide Appendix A What's on the Companion Disk? Next: B Calling Stored Procedures from PL/SQL Version... can only display a maximum of 255 bytes per call, that it does not display Booleans or combinations of data, and that it will not work in the Oracle Developer/2000 environment nor in Oracle WebServer.)[2] [2] See Chapter 7 of my book on packages, Advanced Oracle PL/SQL Programming with Packages, for details about the usage of DBMS_OUTPUT.PUT_LINE Ideally, you would like to be able to set up a trace mechanism . Tuning PL/SQL Applications Next: 26. Tracing PL/SQL Execution 25.5 Overview of PL/SQL8 Enhancements Oracle Corporation has improved the peformance of PL/SQL. Algorithms Oracle PL/SQL Programming, 2nd Edition Next: 26. Tracing PL/SQL Execution 25.4 Tuning Your Algorithms Book Index 26. Tracing PL/SQL Execution The Oracle