Skip to content

Oracle PL/SQL optimization – implicit type casting

7 May 2013

Lately I’ve been asked to review some PL/SQL code seaming to take longer than expected time to execute.

The Usual Suspects are variables not bound and large looping operations without using native collections causing multiple context switches.

For my surprise that was not the case. Instead I found implicit type casting. It turns out that these are not considered as bad practice.

This provoked me to fire these test listed below so as to demonstrate how much time has been lost in internal type casing.

Test 1

DECLARE
 l_str varchar2(8);
 l_start number;
begin
 DBMS_OUTPUT.enable(null);
 l_start := DBMS_UTILITY.get_time;
 for i in 1..10000000 loop
 l_str := i; -- internal cast of variable length number assignment
 end loop;
 dbms_output.put_line( 'Cast of variable number assignment: ' || to_char(DBMS_UTILITY.get_time - l_start) );
end;
/
Cast of variable number assignment: 474

Test 2

DECLARE
 l_num number;
 l_start number;
begin
 DBMS_OUTPUT.enable(null);
 l_start := DBMS_UTILITY.get_time;
 for i in 1..10000000 loop
 l_num := i; -- variable number assignment
 end loop;
 dbms_output.put_line( 'No cast; variable number assignment: ' || to_char(DBMS_UTILITY.get_time - l_start) );
end;
/
No cast; variable number assignment: 172

Test 3

DECLARE
 l_str varchar2(8);
 l_start number;
begin
 DBMS_OUTPUT.enable(null);
 l_start := DBMS_UTILITY.get_time;
 for i in 1..10000000 loop
 l_str := 1; -- internal cast of fixed value number assignment
 end loop;
 dbms_output.put_line( 'Cast of fixed number assignment: ' || to_char(DBMS_UTILITY.get_time - l_start) );
end;
/
Cast of fixed number assignment: 288

Test 4

DECLARE
 l_str varchar2(8);
 l_start number;
begin
 DBMS_OUTPUT.enable(null);
 l_start := DBMS_UTILITY.get_time;
 for i in 1..10000000 loop
 l_str := 'a'; -- internal cast of fixed value number assignment
 end loop;
 dbms_output.put_line( 'Cast of fixed STRING assignment: ' || to_char(DBMS_UTILITY.get_time - l_start) );
end;
/
Cast of fixed STRING assignment: 74

Test 5

DECLARE
 l_num number;
 l_start number;
begin
 DBMS_OUTPUT.enable(null);
 l_start := DBMS_UTILITY.get_time;
 for i in 1..10000000 loop
 l_num := 1; -- fixed number assignment
 end loop;
 dbms_output.put_line( 'No cast; fixed number assignment: ' || to_char(DBMS_UTILITY.get_time - l_start) );
end;
/
No cast; fixed number assignment: 86

Test 6

DECLARE
 l_num BINARY_INTEGER;
 l_start number;
begin
 DBMS_OUTPUT.enable(null);
 l_start := DBMS_UTILITY.get_time;
 for i in 1..10000000 loop
 l_num := 1; -- fixed number assignment
 end loop;
 dbms_output.put_line( 'No cast; fixed number assignment: ' || to_char(DBMS_UTILITY.get_time - l_start) );
end;
/
No cast; fixed number assignment: 29

Result is self-explanatory. Variable length assignment casting number to varchar2 (Test 1) is more than twice slower than direct (no cast) assignment.

Fixed length number to varchar2 assignment is about four times slower than assignment without type casting.

As results of test 4 and 5 show, number assignment is slower than varchar2 assignment. But there is room for additional improvement. Pre 10g version, as Oracle documentation states, NUMBER data type is better to be replaced with some of the PL/SQL equivalents – PLS_INTEGER, BINARY_FLOAT or BINARY_DOUBLE. PLS_INTEGER is recommended as most optimized. With version 10g BINARY_INTEGER is identical to PLS_INTEGER and it doens’t matter which one will be used.

The comparison of Test 5 & 6 results shows that PL/SQL numbers are about 3 times faster than NUMBER.

Comparing Test 1 with Test 6 it is obvious the impact internal type casting could have on the performance – about 17 times!

Now you know!

 

Advertisements

From → Oracle, PLSQL

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: