Performance Programming in PL/SQL using Oracle 10G

CREATE OR REPLACE PROCEDURE preformance_compare(iterations PLS_INTEGER)
IS

/* =======================================================================================================================
PROGRAM ID : Procedure preformance_compare.
PROGRAM DESC : This Program is written to measure the Performance of the Different Control structure/Construct available
in Oracle 10G i.e FOR LOOP, FOR ALL,LOOP,WHILE LOOP and THE /*+APPEND */ /*Hint, Using of BULK Collect etc.
-----------------------------------------------------------------------------------------------------------------------
VERSION DATE CATCH STRING MODIFIED BY REASON
1.0 19.02.2013 Muhammad Asif To measure the Performance of different Oracle Control Structure For While Loop and Hints etc*/
--=================================================================================================--


TYPE NumTab IS TABLE OF t1.pnum%TYPE INDEX BY PLS_INTEGER;
TYPE NameTab IS TABLE OF t1.pname%TYPE INDEX BY PLS_INTEGER;

pnums NumTab;
Pnames NameTab;

CURSOR CR1(p_iterations IN NUMBER) IS
SELECT *
FROM t2
WHERE ROWNUM <= p_iterations ;

TYPE MyType IS TABLE OF t1%ROWTYPE INDEX BY BINARY_INTEGER;
COLLType MyType;

a INTEGER;
b INTEGER;
c INTEGER;
d INTEGER;
e INTEGER;
f INTEGER;
g INTEGER;
h INTEGER;
i BINARY_INTEGER := 1;
ln_pnum NUMBER;
lv_pname VARCHAR2(1000);
BEGIN
a:= dbms_utility.get_time;

FOR j IN 1.. iterations LOOP --Load index by table
pnums(j) := j;
pnames(j) := 'Part No. '|| TO_CHAR(j);
END LOOP;

a:= dbms_utility.get_time;
FOR i IN 1..iterations Loop --use FOR loop
INSERT INTO t1 VALUES (pnums(i),pnames(i));
END LOOP;

b := dbms_utility.get_time;

FORALL i IN 1.. iterations -- user FORALL statment
INSERT INTO t2 VALUES (pnums(i), pnames(i));

COMMIT;
c:= dbms_utility.get_time;


OPEN CR1(iterations);
FETCH CR1 BULK COLLECT INTO COLLType;
CLOSE CR1;
FOR CR1 IN 1..CollType.Count LOOP
INSERT INTO t3 VALUES (COLLType(CR1).pnum, COLLType(CR1).pname);
END LOOP;

d:= dbms_utility.get_time;

OPEN CR1(iterations);
LOOP
FETCH CR1 INTO ln_pnum,lv_pname;
EXIT WHEN CR1%NOTFOUND;
INSERT INTO t4 VALUES (ln_pnum,lv_pname);
END LOOP;
e:= dbms_utility.get_time;
BEGIN
WHILE pnums(i) IS NOT NULL LOOP
INSERT INTO t5 VALUES (ln_pnum,lv_pname);
i := i + 1;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
f:= dbms_utility.get_time;
FORALL i IN 1.. iterations -- user FORALL statment
INSERT /*+APPEND*/ INTO t2 VALUES (pnums(i), pnames(i));

COMMIT;
g:= dbms_utility.get_time;

dbms_output.put_line('This Program is written to measure the Performance of the Different Control structure/Construct available
in Oracle 10G i.e FOR LOOP, FOR ALL,LOOP,WHILE LOOP and THE /*+APPEND */ /*Hint, Using of BULK Collect etc.' );

dbms_output.put_line('Total No of Records Inserted : '||iterations);
dbms_output.put_line('Execution Time (secs)');
dbms_output.put_line('FOR ALL loop with /*+APPEND*/ Hint in Insert : '|| TO_CHAR((g - f) / 100));
dbms_output.put_line('FOR ALL loop: '|| TO_CHAR((c - b) / 100));
dbms_output.put_line('WHILE LOOP: '|| TO_CHAR((f - e) / 100));
dbms_output.put_line('FOR loop: '|| TO_CHAR((b - a) / 100));
dbms_output.put_line('FOR BULK Collect loop: '|| TO_CHAR((d - c) / 100));
dbms_output.put_line('LOOP: '|| TO_CHAR((e - d) / 100));

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' ERROR !!!!! ' || '\' || SQLERRM);

END preformance_compare;

--Script

drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
CREATE TABLE t1 (pnum INTEGER, pname VARCHAR2(4000));
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;
CREATE TABLE t4 AS SELECT * FROM t1;
CREATE TABLE t5 AS SELECT * FROM t1;
set serveroutput on

exec preformance_compare(150000);

SELECT COUNT(1) FROM t1;
SELECT COUNT(1) FROM t2;
SELECT COUNT(1) FROM t3;
SELECT COUNT(1) FROM t4;
SELECT COUNT(1) FROM t5;
SELECT count(1) FROM t2
where rownum <= 50000 ;


--OutPut


This Program is written to measure the Performance of the Different Control structure/Construct available
in Oracle 10G i.e FOR LOOP, FOR ALL,LOOP,WHILE LOOP and THE /*+APPEND */ /*Hint, Using of BULK Collect etc.
Total No of Records Inserted : 150000
Execution Time (secs)
FOR ALL loop with /*+APPEND*/ Hint in Insert : .24
FOR ALL loop: .31
WHILE LOOP: 6.26
FOR loop: 6.17
FOR BULK Collect loop: 6.61
LOOP: 9.25