Re: Solved - RE: UTL_RAW and slowness

Raj

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: Vladimir.Begun_at_oracle.com

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: ListGuru_at_fatcity.com (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).