Home » SQL & PL/SQL » SQL & PL/SQL » Reach the highest PLSQL performances....
Reach the highest PLSQL performances.... [message #219855] Fri, 16 February 2007 06:33 Go to next message
Messages: 1
Registered: February 2007
Location: Roma, Italia
Junior Member

This is my first posting here, hope not to bore you too much...

I'm working for a customer who has a lot of data (teras) stored in different databases (different versions also: from 9.2 to 10.2) that need to be "changed" into something different using an algorithm written in PL/SQL.

Being the time of execution the most important point in all the project, what we need to have is the certainty that our work is the faster that can be achieved by anyone...

His tables are organized in partitions, and we're thinking of something that copies into a new table the partition, modifying the data where needed, and then replaces the copied table partition with the new table.

To simplify things, this is the usual statement we use to do this (as an EXECUTE IMMEDIATE in a stored procedure):

SELECT /*+ parallel (t,4) */ field01, field02, [B]changeIt(field03)[/B] field03, field04
FROM   originalTable PARTITION (p001) t

being changeIt() a stored PL/SQL function that transforms (masks) the old data into the new one.

What we'd like to know is if our way of writing the stored function is the best possible.

The masking rule is based on a matrix composed by 26 rows (one for each alphabet letter and 127 columns, one for each ASCII standard code).

We stored this matrix using user-defined types, so composed:

CREATE OR REPLACE TYPE charRow IS varray(128) OF char(1);


CREATE OR REPLACE TYPE charRowMatrix IS varray(26) of charRow;

The matrix, for each row has a complete alphabet that is used to transpose the original field value into a masked one (that's because Italian security laws)

Following is the function code, the assignment to "charToCrypt" and "columnId" are merged into the "concat" instruction in the "real" function, I just "split" them here in several commands for sake of readability:

   cryptedString  VARCHAR2(10) := '';
	 charToCrypt    PLS_INTEGER;
	 columnId       PLS_INTEGER;
   for i in 1..length(stringToCrypt) loop
	    charToCrypt   := ascii(substr(stringToCrypt,i,1));
	    columnId      := substr(headerRow,(charToCrypt*3)+1,3))+1;
      cryptedString := concat(cryptedString,myMatrix(keyRow(i))(columnId);
   end loop;
   return cryptedString;

On a 4-processors Linux machine it takes something like 38 seconds to process the above "create table" command on a 3 million records table. If the same function "changeIt" is applied on 2 fields the time needed for its execution grows to 61 secs, on 3 fields needs 79 secs and so on...

Is there something, at the bwest of your knowledge, that can be done to improve performances?
Maybe the varray usage isn't the fastest choice? Any init.ora parameter that can be tuned?

Any help (including hints about where/what to read and/or to ask) would be greatly appreciated!


- Paolo Castagno
Re: Reach the highest PLSQL performances.... [message #220461 is a reply to message #219855] Tue, 20 February 2007 14:50 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try on asktom.oracle.com

Also - just because it's a 4cpu machine - does not mean that parallel degree of 4 is optimal. I suspect it could be more like 8 - you need to experiment. On a machine with a large number of sessions, the number would be lower, but you seem to be on a data warehouse type environment...

The quickest way to transpose the data will be using "translate".

select translate('ABCabc', 
chr(44)||chr(45)||chr(46)||'qwertyPLOX') from dual;


Use chr() to run through all the characters you can't type. Be sure to test all characters including chr(0). Use dump(my_column, 1010) or dump(my_column, 1016) to examine the character values of the result. Watch out for multi-byte characters e.g. Euro (check your database charactersets).
Previous Topic: Converting Dates
Next Topic: outer join problem
Goto Forum:

Current Time: Mon Aug 21 20:52:46 CDT 2017

Total time taken to generate the page: 0.06617 seconds