Monday, November 28, 2016
Tuning PLSQL Oracle 10g – Using the PLS_INTEGER data type for arithmetic operations
- PLS_INTEGER a data type introduced in Oracle 7 especially to speed up the intensive arithmetic operations.
- It’s part of NUMBER family
- It’s an only data type in oracle which uses native machine arithmetic instead of “C” arithmetic library. This in turn makes its faster than “NUMBER” data type
- This 32 bit data type can store values in the range of -2147483648 to 2147483648
Let’s undertake a case study
where we will observe the difference in performance of arithmetic operations
using NUMBER and PLS_INTEGER data type.
Script:
SET SERVEROUTPUT ON;
DECLARE
ln_number NUMBER DEFAULT 0;
ln_pls_integer PLS_INTEGER DEFAULT 0;
ln_start_time NUMBER;
ln_end_time NUMBER;
BEGIN
ln_start_time := DBMS_UTILITY.get_cpu_time;
FOR i in 1 .. 100000000
LOOP
ln_pls_integer := ln_pls_integer+1;
END
LOOP;
ln_end_time := DBMS_UTILITY.get_cpu_time;
DBMS_OUTPUT.put_line('Time Taken (PLS_IMTEGER): '
||ROUND(ln_end_time - ln_start_time,2)
);
ln_start_time := DBMS_UTILITY.get_cpu_time;
FOR i in 1 .. 100000000
LOOP
ln_number := ln_number+1;
END
LOOP;
ln_end_time := DBMS_UTILITY.get_cpu_time;
DBMS_OUTPUT.put_line('Time Taken
(NUMBER) : '
||ROUND(ln_end_time - ln_start_time,2)
);
END;
Test Results
The test results are very much evident that I got around 300% improvement in performance.
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
1 Responses to “Tuning PLSQL Oracle 10g – Using the PLS_INTEGER data type for arithmetic operations”
December 2, 2016 at 11:13 AM
This is a good simple trick for performance tuning. Thanks!
Post a Comment