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.

 

1 Responses to “Tuning PLSQL Oracle 10g – Using the PLS_INTEGER data type for arithmetic operations”

Saritha said...
December 2, 2016 at 11:13 AM

This is a good simple trick for performance tuning. Thanks!


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.