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.

 

Monday, November 28, 2016 by Team search · 1

Saturday, November 19, 2016

Example for DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in 11g















Often, it’s a head ache for me to debug the unhandled exception in my programs. Now, I have a pill for it. It’s DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Let’s see how it’s useful for PLSQL developers. 

Test Package:
CREATE OR replace PACKAGE test_oracle
IS
  PROCEDURE main;
END test_oracle;

/
CREATE OR replace PACKAGE BODY test_oracle
IS
  PROCEDURE Fun5
  IS
    ln_dummy_number NUMBER;
  BEGIN
      ln_dummy_number := 1 / 0; -- Kept to raise an exception
  END;
  PROCEDURE Fun4
  IS
  BEGIN
      fun5;
  END;
  PROCEDURE Fun3
  IS
  BEGIN
      fun4;
  END;
  PROCEDURE Fun2
  IS
  BEGIN
      fun3;
  END;
  PROCEDURE Main
  IS
  BEGIN
      fun2;
  EXCEPTION
    WHEN OTHERS THEN
               dbms_output.Put_line('Format error stack:'
                                    ||dbms_utility.Format_error_stack());

               dbms_output.Put_line('Print error stack:'
                                    ||Chr(10)
                                    ||dbms_utility.Format_error_backtrace());
  END;
END test_oracle;

/
show errors;
Testing:


Now, I know the exact line which caused the error. It mentioned standard oracle utilities gaves the complete back trace. It’s a nice utility!!

Hopefully it helps!!

Saturday, November 19, 2016 by Team search · 1

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.