Optimizing PL/SQL Code - Oracle Database 11gR2 Performance Tuning - page 273

6. Execute the STRESS procedure measuring the time needed:

SET TIMING ON
BEGIN
STRESS(100000);
END;
/

7. Execute the STRESS_CACHE procedure measuring the time needed:

BEGIN
STRESS_CACHE(100000);
END;
/

8. Clean the database:

SET TIMING OFF
DROP FUNCTION C_N_K;
DROP PROCEDURE STRESS;
DROP FUNCTION C_N_K_CACHE;
DROP PROCEDURE STRESS_CACHE;

How it works...

In step 2 and step 3, we create the function C_N_K and the procedure STRESS
as in the Using native compilation recipe in this chapter.

In step 4, we create a function C_N_K_CACHE , equivalent to the function C_N_K
except for the RESULT_CACHE option in its heading:

CREATE OR REPLACE FUNCTION C_N_K_CACHE (N IN NUMBER,
K IN NUMBER) RETURN NUMBER
RESULT_CACHE

In step 5, we create the procedure STRESS_CACHE , which is the same as STRESS
but invokes the C_N_K_CACHE function instead of the original C_N_K function.