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

7. Re-create the STRESS procedure:

CREATE OR REPLACE PROCEDURE STRESS(ANUM NUMBER)
IS
AVAL NUMBER;
BEGIN
FOR J IN 1..ANUM LOOP
AVAL := C_N_K (50,10);
END LOOP;
END;
/

8. Execute the STRESS procedure measuring the time needed:

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

9. Reset the parameter and clean the database:

ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED;
DROP FUNCTION C_N_K;
DROP PROCEDURE STRESS;

How it works...

In step 2, we create a function C_N_K to calculate the number of k-combinations in a set
of n elements—the binomial coefficient n choose k . We use the iterative form to calculate
the factorial values required.

In step 3, we create a procedure STRESS , which calculates the C_N_K function for 50
choose 10, the number of times equal to its parameter ANUM .