As Published In
Oracle Magazine
March/April 2009


Improving PL/SQL

By Sushma Jagannath

Improve performance in Oracle Database 11g with new PL/SQL features.

Oracle Database 11g introduced several new PL/SQL features and tools that help you analyze the performance of large applications and improve application performance. This column focuses on some of those new features and also presents sample questions of the type you may encounter when taking the Oracle Database 11g: Advanced PL/SQL exam. Successful completion of this exam enables you to earn the Oracle Advanced PL/SQL Developer Certified Professional certificate.

PL/SQL Function Result Cache

In versions prior to Oracle Database 11g, Oracle Database cached database blocks, which were then used to build result sets. If you wanted your PL/SQL application to cache the results of a function, you had to design and code the cache and cache-management subprograms, and the cache could not be shared across sessions.

Starting with Oracle Database 11g, the database can cache result sets. Each time a result-cached function is invoked with different parameter values, those parameters and their results are stored in the cache. Subsequently, when the same function is invoked with stored parameter values, the result is retrieved from the cache instead of being recomputed.

The best candidates for result caching are functions that are invoked frequently but depend on information that changes infrequently. Key advantages of the function result cache include automatic invalidation of cache contents when a dependent table is changed, parameter values and results shared across sessions, and the application of the least recently used algorithm to the memory in the cache.

To use the PL/SQL function result cache, add the RESULT_CACHE clause (and, optionally, RELIES ON) to each PL/SQL function whose results you want cached. The DBMS_RESULT_CACHE package provides a PL/SQL API for result cache management, and the STATUS function displays the current status of the result cache.

Which statements are true of the code in Listing 1?

Code Listing 1: The effect of result caching on loops


  RESULT_CACHE RELIES_ON (res_cache_test_data)
  l_value res_cache_test_data.value%TYPE;
  RETURN l_value;
END get_val;

  l_loops NUMBER := 10;
  l_value res_cache_test_data.value%TYPE;
-- first loop
  FOR i IN 1 .. l_loops LOOP
    l_value := get_val(i);
-- second loop
  FOR i IN 1 .. l_loops LOOP
    l_value := get_val(i);
END run_cache_test;

EXEC run_cache_test;

A. Only the second loop will take very little time if the data has changed since the last execution.
B. Only the second loop will take very little time if this is the first time the procedure is being executed.
C. Both the first and the second loops will take the same amount of time to execute if the data has changed since the last execution.
D. Both the first and the second loops will take the same amount of time to execute if this is the first time the procedure is being executed.

The correct answers are A and B. If the procedure is being executed for the first time or if the data in the underlying table has changed since the last execution of the procedure, the results in the cache will be invalidated. So the first loop will take the typical time required when it is run the first time, whereas the second loop will run almost instantaneously. You executed the following command to gather information about the memory allocation for storing query results:


SQL> execute 

View and examine the output for the execution of the DBMS_RESULT_CACHE.MEMORY_REPORT procedure in Listing 2. Which two statements are true, based on the output in Listing 2?

Code Listing 2: Result cache memory report


Result Cache Memory Report
Block Size = 1K bytes
Maximum Cache Size = 1056K bytes (1056 blocks)
Maximum Result Size = 52K bytes (52 blocks)
Total Memory = 103536 bytes [0.055% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.052% of the Shared Pool]
....... Overhead =  65628 bytes
...........Unused Memory = 29 blocks
...........Used Memory =  3 blocks
...................PLSQL = 1 blocks (1 count)

PL/SQL procedure successfully completed.

A. In total, the result cache uses four blocks.
B. In total, the result cache uses seven blocks.
C. Currently, 32 kilobytes are allocated for the result cache.
D. The result cache can dynamically grow up to 98396 bytes.

The correct answers are C and D. As shown in Listing 2, 32 kilobytes have been allocated to the result cache (cache memory), and it can use a maximum of 98,396 bytes of memory (dynamic memory).

PL/SQL Hierarchical Profiler

Starting in Oracle Database 11g, the hierarchical profiler is available to help developers identify hotspots and performance tuning opportunities in PL/SQL applications. It reports the dynamic execution profile of a PL/SQL program, organized by subprogram calls and accounting for SQL and PL/SQL execution times separately. You can view function-level summaries that include


  • The number of calls to a function
  • The time spent in the function itself
  • The time spent in the entire subtree under the function
  • Detailed parent/children information for each function

The hierarchical profiler consists of the DBMS_HPROF package, which is similar to the DBMS_PROFILER and DBMS_TRACE packages, and the plshprof command-line utility for converting the profile information into HTML format. You can use this profile information to tune PL/SQL applications and understand the structure, flow, and control of complex programs. These reports, when viewed in a browser, can provide a powerful way to analyze the performance of large applications, improve application performance, and lower development costs.

Here is a syntax example for profiling PL/SQL code with DBMS_HPROF:


-- Start Profiling
  'PROFILE_DATA', 'profile_test.txt');

-- run the code that you want profiled

-- Stop Profiling

Which information can be gathered about functions and parent/children functions through the PL/SQL hierarchical profiler?

A. The amount of time spent in a function, excluding descendants
B. The functions that were called from a particular function
C. The subtree time, including descendants
D. The number of times function A called function B

The correct answers are B, C, and D. The hierarchical profiler provides fine-grained information with respect to all the functions that were invoked from a particular function, and it also provides the subtree time, including the times of the descendant functions. It also provides details about the number of times function A called function B and vice versa. Answer A is incorrect because the profiler provides details on the amount of time spent whenever a function is invoked, including the times of the descendant functions. 

Next Steps


 EXPLORE the certification forum

 READ Inside OCP columns

READ more about PLSQL
Oracle Database PL/SQL Language Reference


This column focused on some of the PL/SQL enhancements introduced in Oracle Database 11g that improve the performance of PL/SQL applications: 

  • The PL/SQL function result cache can save significant space and time by retrieving the result from the cache instead of having the database recompute a function.
  • The PL/SQL hierarchical profiler provides performance tuning opportunities by enabling developers to gather and analyze hierarchical profiler data for PL/SQL programs.

The correct answers are A and B. By using the switch_io_reqs, switch_io_megabytes, and switch_time parameters, you can control the I/O usage per session and switch to another consumer group or kill or abort a session if it reaches the threshold. Answer C is incorrect because the limiting of I/O per session either switches the session to another consumer group or kills the session but does not retain the session within the consumer group. Answer D is incorrect because I/O consumption per session is limited, irrespective of the availability of the resources.

Sushma Jagannath

Send us your comments