Monday, November 28, 2016
- 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
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
Subscribe to:
Posts (Atom)
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.