Tuning PL/SQL Code

In this conclusion to a nine-part series on managing PL/SQL code, you will learn how to analyze and optimize your code’s performance. This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O’Reilly; ISBN: 0596009771). Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

Analyzing Performance of PL/SQL Code

Before you can tune your application, you need to figure out what is running slowly and where you should focus your efforts. Oracle and third-party vendors offer a variety of products to help you do this; generally they focus on analyzing the SQL statements in your code, offering alternative implementations, and so on. These tools are very powerful, yet they can also be very frustrating to PL/SQL developers. They tend to offer an overwhelming amount of performance data without telling you what you really want to know: how fast did a particular program run and how much did the performance improve after making this change?

To answer these questions, Oracle offers a number of built-in utilities. Here are the most useful:


This built-in package allows you to turn on execution profiling in a session. Then, when you run your code, Oracle uses tables to keep track of detailed information about how long each line in your code took to execute. You can then run queries on these tables or—much preferred—use screens in products like Toad or SQL Navigator to present the data in a clear, graphical fashion.


Use this built-in function to calculate the elapsed time of your code down to the hundredth of a second. The scripts tmr.ot and plvtmr.pkg (available on the book’s web site) offer an interface to this function that allows you to use “timers” (based on DBMS_UTILITY.GET_TIME) in your code. These make it possible to time exactly how long a certain operation took to run and even to compare various implementations of the same requirement.

In Oracle Database 10g, you can also call DBMS_UTILITY.GET_CPU_TIME to calculate elapsed CPU time.

In case you do not have access to a tool that offers an interface to DBMS_PROFILER, here are some instructions and examples.

First of all, Oracle does not install DBMS_PROFILER for you automatically. To see if DBMS_PROFILER is installed and available, connect to your schema in SQL*Plus and issue this command:


If you then see the message:

  ORA-04043: object dbms_profiler does not exist

you will have to install the program.

For early Oracle versions, such as Oracle7 and Oracle8 Database, you need to ask your DBA to run the following scripts under a SYSDBA account (the first creates the package specification, the second the package body):

$ORACLE_HOME/rdbms/admin/dbmspbp.sq l


For later versions, you need to run the $ORACLE_HOME/rdbms/admin/profload.sql file instead, also under a SYSDBA account.

You then need to run the $ORACLE_HOME/rdbms/admin/proftab.sql file in your own schema to create three tables populated by DBMS_PROFILER:

Parent table of runs

Program units executed in run

Profiling data for each line in a program unit

Finally, you will probably find it helpful to take advantage of some sample queries and reporting packages offered by Oracle in the following files:



Once all these objects are defined, you gather profiling information for your application by writing code like this:

          ‘showemps ‘ ||

Once you have finished running your application code, you can run queries against the data in the PLSQL_PROFILER_ tables. Here is an example of such a query that displays those lines of code that consumed at least 1% of the total time of the run:

  /* File on web: slowest.sql */
                 p1.total_time / 10000000,
|| ‘-‘
           || TO_CHAR (p1.total_occur) AS time_count,
              p2.unit_owner || ‘.’ || p2.unit_name unit,
TO_CHAR (p1.line#)
           || ‘-‘
           || p3.text text
      FROM plsql_profiler_data p1,
           plsql_profiler_units p2,
           all_source p3,
           plsql_profiler_grand_total p4 

     WHERE p2.unit_owner NOT IN (‘SYS’, ‘SYSTEM’)
       AND p1.runid = &&firstparm
       AND (p1.total_time >= p4.grand_total / 100)
       AND p1.runid = p2.runid
       AND p2.unit_number = p1.unit_number
       AND p3.owner = p2.unit_owner
       AND p3.line = p1.line#
       AND p3.NAME = p2.unit_name
ORDER BY p1.total_time DESC;

As you can see, these queries are fairly complex (I modified one of the canned que ries from Oracle to produce the above four-way join). That’s why it is far better to rely on a graphical interface in a PL/SQL development tool.

After you’ve analyzed your code and identified bottlenecks, the following sections can help you determine what kinds of changes to make to improve code performance.

{mospagebreak title=Optimizing PL/SQL Performance}

This section contains brief recommendations for ways to improve the performance of your code and points you to other sections in the book that cover each topic more thoroughly.

Use the most aggressive compiler optimization level possible

Oracle Database 10g Release 1 introduced an optimizing compiler for PL/SQL programs. The default optimization level of 2 takes the most aggressive approach possible in terms of transforming your code to make it run faster. You should use this default level unless compilation time is unacceptably slow, and you are not seeing benefits from optimization. See the “The Optimizing Compiler” section in this chapter for detailed information.

Use BULK COLLECT when querying multiple rows

The BULK COLLECT statement retrieves multiple rows of data through either an implicit or an explicit query with a single round trip to and from the database. BULK COLLECT reduces the number of context switches between the PL/SQL and SQL engines and thereby reduces the overhead of retrieving data. Rather than using a cursor FOR loop or other row-by-row querying mechanism, switch to BULK COLLECT for a dramatic improvement in performance. See the “BULK COLLECT” section in Chapter 15 for more about this feature.

Use FORALL when modifying multiple rows

As with BULK COLLECT, FORALL greatly reduces context switching between the PL/SQL and SQL engines, but this time for updates, inserts, and deletes. You can expect to see an order of magnitude (or greater) improvement in performance for multiple-row DML execution with FORALL. See the “Bulk DML with the FORALL Statement” section in Chapter 14 for detailed information.

Use the NOCOPY hint when passing large structures

The NOCOPY parameter hint requests that the PL/SQL runtime engine pass an IN OUT argument by reference rather than by value. This can speed up the performance of your programs, because by-reference arguments are not copied within the program unit. When you pass large, complex structures like collections, records, or objects, this copy step can be expensive. See the “The NOCOPY Parameter Mode Hint” section in Chapter 17.

Use PLS_INTEGER for intensive integer computations.

When you declare an integer variable as PLS_INTEGER, it will use less memory than INTEGER and rely on machine arithmetic to get the job done more efficiently. In a program that requires intensive integer computations, simply changing the way that you declare your variables could have a noticeable impact on performance. See the section “The PLS_INTEGER Type” in Chapter 9 for a more detailed discussion.

In Oracle8i Database and Oracle9i Database, PLS_INTEGER will perform more efficiently than BINARY_INTEGER. In Oracle Database 10g, they are equally performant.

Use BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic

Oracle Database 10g introduces two, new floating-point types: BINARY_FLOAT and BINARY_DOUBLE. These types conform to the IEEE 754 floating-point standard and use native machine arithmetic, making them more efficient than NUMBER or INTEGER variables. See “The BINARY_FLOAT and BINARY_DOUBLE Types” section in Chapter 9.

Group together related programs in a package

Whenever you reference any single element in a package for the first time in your session, the entire package is cached in the shared memory pool. Any other calls to programs in the package require no additional disk I/O, thereby improving the performance of calling those programs. Group related programs into a package to take advantage of this feature. See the “Packaging to improve memory use and performance” section in Chapter 23 for details.

Pin into shared memory large and frequently executed programs.

Pin frequently accessed programs in the shared memory pool with the DBMS_SHARED_POOL.PIN procedure. A pinned program will not be flushed out of the pool using the default least-recently-used algorithm. This guarantees that the code will already be present when it is need. See the “What to Do if You Run Out of Memory” section in Chapter 23.

{mospagebreak title=Protecting Stored Code}

Virtually any application we write contains propriety information. If I write my application in PL/SQL and sell it commercially, I really don’t want to let customers (or worse, competitors) see my secrets. Oracle offers a program known as wrap that hides or obfuscates most, if not all, of these secrets.

Some people refer to “wrapping” code as “encrypting” code, but wrapping is not true encryption. If you need to deliver information, such as a password, that really needs to be secure, you should not rely upon this facility. Oracle does provide a way of incorporating true encryption into your own applications using the built-in package DBMS_CRYPTO (or DBMS_OBFUSCATION_TOOLKIT in releases before Oracle Database 10g). Chapter 22 describes encryption and other aspects of PL/SQL application security.

When you wrap PL/SQL source, you convert your readable ASCII text source code into unreadable ASCII text source code. This unreadable code can then be distributed to customers, regional offices, etc., for creation in new database instances. The Oracle database maintains dependencies for this wrapped code as it would for programs compiled from readable text. In short, a wrapped program is treated within the database just as normal PL/SQL programs are treated; the only difference is that prying eyes can’t query the USER_SOURCE data dictionary to extract trade secrets.

Oracle has, for years, provided a wrap executable that would perform the obfuscation of your code. With Oracle Database 10g Release 2, you can also use the DBMS_DDL.WRAP and DBMS_DDL.CREATE_WRAPPED programs to wrap dynamically constructed PL/SQL code.

Restrictions on and Limitations of Wrapping

You should be aware of the following issues when working with wrapped code:

  1. Wrapping makes reverse engineering of your source code difficult, but you should still avoid placing passwords and other highly sensitive information in your code.
  2. You cannot wrap the source code in triggers. If it is critical that you hide the contents of triggers, move the code to a package and then call the packaged program from the trigger.
  3. Wrapped code cannot be compiled into databases of a version lower than that of the wrap program. Wrapped code is upward-compatible only.
  4. You cannot include SQL*Plus substitution variables inside code that must be wrapped.

Using the Wrap Executable

To wrap PL/SQL source code, you run the wrap executable. This program, named wrap.exe, is located in the bin directory of the Oracle instance. The format of the wrap command is:

  wrap iname=infile [oname=outfile]

where infile points to the original, readable version of your program, and outfile is the name of the file that will contain the wrapped version of the code. If infile does not contain a file extension, then the default of sql is assumed.

If you do not provide an oname argument, then wrap creates a file with the same name as infile but with a default extension of plb, which stands for “PL/SQL binary” (a misnomer, but it gets the idea across: binaries are, in fact, unreadable).

Here are some examples of using the wrap executable:

  • Wrap a program, relying on all the defaults:

      wrap iname=secretprog 
  • Wrap a package body, specifying overrides of all the defaults. Notice that the wrapped file doesn’t have to have the same filename or extension as the original:

      wrap iname=secretbody.spb oname=shhhhhh.bin

{mospagebreak title=Dynamic Wrapping with DBMS_DDL}

Oracle Database 10g Release 2 provides a way to wrap code that is generated dynamically: the WRAP and CREATE_WRAPPED programs of the DBMS_DDL package:

Returns a string containing an obfuscated version of
   your code

Compiles an obfuscated version of your code into the

Both programs are overloaded to work with a single string and with arrays of strings based on the DBMS_SQL.VARCHAR2A and DBMS_SQL.VARCHAR2S collection types. Here are two examples that use these programs:

  • Obfuscate and display a string that creates a tiny procedure:

    l_program   VARCHAR2 (32767);
    l_program := ‘CREATE OR REPLACE PROCEDURE dont_look IS BEGIN NULL; END;’;
    DBMS_OUTPUT.put_line (SYS.DBMS_DDL.wrap (l_program));
        7 /
      CREATE OR REPLACE PROCEDURE dont_look wrapped

      XtQ19EnOI8a6hBSJmk2NebMgPHswg5nnm7+ fMr2ywFy4CP6Z9P4I/v4rpXQruMAy/tJepZmB 
  • Read a PL/SQL program definition from a file, obfuscate it, and compile it into the database:

      /* File on web: obfuscate_from_file.sql * /
      CEATE OR REPLACE PROCEDURE obfuscate_from_file (
        dir_in   IN   VARCHAR2
    , file_in  IN   VARCHAR2
    l_file   UTL_FILE.file_type;
         l_lines  DBMS_SQL.varchar2s;

    PROCEDURE read_file (lines_out IN OUT NOCOPY DBMS_SQL.varchar2s)
         IS BEGIN … not critical to the example … END read_file;
         read_file (l_lines);
         SYS.DBMS_DDL.create_wrapped (l_lines, l_lines.FIRST, l_lines.LAST);

      END obfuscate_from_file;

{mospagebreak title=Guidelines for Working with Wrapped Code}

I have found the following guidelines useful in working with wrapped code:

  • Create batch files so that you can easily, quickly, and uniformly wrap one or more files. In Windows NT, I create bat files that contain lines like this in my source code directories:

      c:orantbinwrap iname=plvrep.sps oname=plvrep.pls

    Of course, you can also create parameterized scripts and pass in the names of the files you want to wrap.
  • You can only wrap package specifications and bodies, object type specifications and bodies, and standalone functions and procedures. You can run the wrapped binary against any other kind of SQL or PL/SQL statement, but those files will not be changed.
  • You can tell that a program is wrapped by examining the program header. It will contain the keyword WRAPPED, as in: 

       PACKAGE BODY package_name WRAPPED

    Even if you don’t notice the keyword WRAPPED on the first line, you will immediately know that you are looking at wrapped code because the text in USER_SOURCE will look like this:

          LINE TEXT
      ——– —-
            45 abc d
            46 95a425ff
            47 a2
            48 7 PACKAGE:

    and no matter how bad your coding style is, it surely isn’t that bad! 
  • Wrapped code is much larger than the original source. I have found in my experience that a 57 KB readable package body turns into a 153 KB wrapped package body, while an 86 KB readable package body turns into a 357 KB wrapped package body. These increases in file size do result in increased requirements for storing source code in the database. The size of compiled code stays the same, although the time it takes to compile may increase.
[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye