Thanks Vladimir ... your input has made me look at my code again ...
Here is relevant portion of profsum.sql output ...
<profsum>
====================
Lines taking more than 1% of the total time, each run separate
RUNID HSECS PCT OWNER UNIT_NAME LINE# TEXT
----- ------- ------ ----------- -------------- ------ ---------------------
3 809.03 86.3 ST_DVDB2 STWRITER_PKG_RAJ 246 ntcpchar := ASCII(SUBSTR
(msg_text, i,1));
3 69.29 7.4 ST_DVDB2 STWRITER_PKG_RAJ 256 COMMIT;
3 13.62 1.5 ST_DVDB2 STWRITER_PKG_RAJ 248 nenctcpchar :=
TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),'xxxx');
3 10.13 1.1 ST_DVDB2 STWRITER_PKG_RAJ 247 r_chr :=
utl_raw.cast_to_raw(CHR(ntcpchar));
=
=
====================
Most popular lines (more than 1%), summarize across all runs
HSECS PCT UNIT_OWNER UNIT_NAME LINE# TEXT
------- ------ ----------- ---------------- ------ ---------------------
809.03 86.3 ST_DVDB2 STWRITER_PKG_RAJ 246 ntcpchar := ASCII(SUBSTR (msg_text,
i,1));
69.29 7.4 ST_DVDB2 STWRITER_PKG_RAJ 256 COMMIT;
13.62 1.5 ST_DVDB2 STWRITER_PKG_RAJ 248 nenctcpchar :=
TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),'xxxx');
10.13 1.1 ST_DVDB2 STWRITER_PKG_RAJ 247 r_chr =
utl_raw.cast_to_raw(CHR(ntcpchar));
</profsum>
This shows that substr must have been the culprit ...
BTW I benchmarked your code, extended the strings to 2000 characters and ran each
conversion in a loop of 2000 and using utl_raw method turned out to be the fastest.
thanks again for your insight and sample code ... I never knew nor noticed other
utl_raw subprograms like utl_raw.copies ...
Now due to pipelining my code is very fast and to accomodate a 122 baud feed, I have
insert artificial delays in my code. 8:)
Raj
--------------------------------------------------------------------------------
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-----Original Message-----
Sent: Monday, October 27, 2003 6:59 PM
To: Multiple recipients of list ORACLE-L
Raj,
Jamadagni, Rajendra wrote:
> Read if you are interested ...
>
> Finally I got some time and luckily the largest message to use with dbms_profiler.
> And the results shocked me .... dbms_profiler showed me that instead of utl_raw,
> substr() was the culprit. Remember my operation is character by character.
Could you please show dbms_profiler output data? I'd also suggest to remove
everything related to TCP/IP out from the code -- to get the clear picture.
Some questions/suggestions, if you do not mind
. I do not think that you need utl_raw to do byte by byte xor operation -- you
could do it using BITAND -- it should be faster.
. What's the point to do it char by char in general? Do you modify encryption
key making it dependent on each given char in the string? If not why not to
use something like the code below (see r1), hope I did not make any mistake:
VAR r1 VARCHAR2(256);
VAR r2 VARCHAR2(256);
VAR r3 VARCHAR2(256);
DECLARE
r_key RAW(1) := '41'; -- hex
r_key_n BINARY_INTEGER := 65; -- dec
l_n BINARY_INTEGER; -- ASCII of current char
-- string to be "encrypted"
l_string VARCHAR2(128) := 'AZBYCXDWEVFUGT';
-- its length
l_string_len BINARY_INTEGER := NVL(LENGTH(l_string), 0);
BEGIN
-- string
-- one can define utl_raw.copies(r_key, 128) as a constant, if it's possible.
:r1 :=
utl_raw.substr(utl_raw.bit_xor(utl_raw.cast_to_raw(l_string),
utl_raw.copies(r_key, 128)), 1, l_string_len);
-- char by char
:r2 := '';
FOR i IN 1..l_string_len
LOOP
:r2 := :r2 || utl_raw.bit_xor(utl_raw.cast_to_raw(SUBSTR(l_string, i, 1)), r_key);
END LOOP;
-- bitand
:r3 := '';
FOR i IN 1..l_string_len
LOOP
l_n := ASCII(SUBSTR(l_string, i, 1));
:r3 := :r3 || TO_CHAR(BITAND(-BITAND(-l_n - 1, -r_key_n - 1) - 1, -BITAND(l_n,
r_key_n) - 1), 'FM0X');
END LOOP;
END;
/
PRINT r1
PRINT r2
PRINT r3
BTW, you have double conversion to ASCII then back to CHR (lines 6 and 7) --
it's not dramatic but it can be eliminated.
HTH.
1 msglen := LENGTH (msg_text);
2 nCharsSent := 0;
3 p('Encrypting data...');
4 FOR i IN 1 .. msglen
5 LOOP
6 ntcpchar := ASCII (SUBSTR (msg_text, i, 1));
7 r_chr := utl_raw.cast_to_raw(CHR(ntcpchar));
8 nenctcpchar := TO_NUMBER(utl_raw.bit_xor(r_chr,r_key),'xxxx');
9 tcpmsglen := UTL_TCP.write_text (gv_tcp_conn, CHR(nenctcpchar), NULL);
10 nCharsSent := nCharssent + 1;
11 IF MOD(ncharssent,128) = 0 THEN
12 p('Before Flush ...');
13 UTL_TCP.FLUSH (gv_tcp_conn);
14 p('Connection Flushed at ' || ncharssent);
15 END IF;
16 --
17 END LOOP; -- FOR i IN 1 .. msglen
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
**************************************************************************************
This e-mail message is confidential, intended only for the named recipient(s) above
and may contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank you.
**************************************************************************************4
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jamadagni, Rajendra
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
