I'm "in" :), so let's check what was the real issue, some more items here...
Jamadagni, Rajendra wrote:
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 ...
I think, the profile *does not* show that. Moreover I'm not quite sure that the cause of the delays was SUBSTR(), but I would like to clarify some points here.
Could you guess what's the difference between these two lines of code?
l_n := ASCII(SUBSTR(l_s, j, 1));
l_n := ASCII(SUBSTR(l_s, j, 1));
That's ok if you could not. Nobody could. Because nobody knows that are the datatypes of l_n and l_s. And there is *significant* difference between datatypes in PL/SQL. Am I right assuming that msg_text could be CLOB and l_n could be NUMBER? Could it be like that? I think so. Could you please tell me what those datatypes are/were?
BTW, why do you think it *was* OK to use SUBSTR() but not SUBSTRB() -- sure, you know the requirements better -- do you tranfer only US ASCII data?
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.
As I mentioned -- do it in 'bulk' if it's acceptable from "security" point.
thanks again for your insight and sample code ... I never knew nor noticed other utl_raw subprograms like utl_raw.copies ...
I would suggest to increase the length of the key at least up to 128 bytes.
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:)
What's the point to pipeline it?
Appreciate your feedback. -- 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).
