Improving PL/SQLBy 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
CREATE OR REPLACE FUNCTION get_val (p_in IN NUMBER) RETURN NUMBER RESULT_CACHE RELIES_ON (res_cache_test_data) AS l_value res_cache_test_data.value%TYPE; BEGIN ... RETURN l_value; END get_val; / CREATE OR REPLACE PROCEDURE run_cache_test AS ... l_loops NUMBER := 10; l_value res_cache_test_data.value%TYPE; BEGIN -- first loop FOR i IN 1 .. l_loops LOOP l_value := get_val(i); END LOOP; -- second loop FOR i IN 1 .. l_loops LOOP l_value := get_val(i); END LOOP; 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.
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 dbms_result_cache.memory_report
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 [Parameters] Block Size = 1K bytes Maximum Cache Size = 1056K bytes (1056 blocks) Maximum Result Size = 52K bytes (52 blocks) [Memory] 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 ....... Cache Memory = 32K bytes (32 blocks) ...........Unused Memory = 29 blocks ...........Used Memory = 3 blocks ...............Dependencies = 2 blocks (2 count) ...............Results = 1 blocks ...................PLSQL = 1 blocks (1 count) PL/SQL procedure successfully completed.
A. In total, the result cache uses four blocks.
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 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 BEGIN DBMS_HPROF.START_PROFILING( 'PROFILE_DATA', 'profile_test.txt'); END; BEGIN -- run the code that you want profiled END; -- Stop Profiling BEGIN DBMS_HPROF.STOP_PROFILING; END;
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
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.
This column focused on some of the PL/SQL enhancements introduced in Oracle Database 11g that improve the performance of PL/SQL applications:
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 is a certification exam development manager at Oracle. She has been with the company since 2000.