Oracle-L: RE: Solved - RE: UTL

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 28 Oct 2003 14:09:25 -0800
Message-ID: <F001.005D4D61.20031028140925@fatcity.com>

Why do you think the profsum output is not right? at least it tells me that 83% of my time is spent on the line that does substr() ... right?

msg_text is a clob, l_n is number you are right .... substr() was used because we transfer only US ASCII data.

The point of pipelining was because in my previous version of code, I was experiencing delays due to (or may be due to) substr() operation ... There is no other easy way to split the CLOB and process it. I am comfortable with pipelining, I know it will work in this scenario so I used it, it worked.

Got better ideas? BTW I on a second (9600 baud) feed I was feeding plain_text using utl_tcp.write_text and my colleagues were experiencing slight delays on the monitor even when I was sending 32k characters. So, I tested with sending 8k characters, convert to raw and use utl_tcp.write_raw, my colleagues are happy, they don't want to change it now.

Thanks for your feedback, I appreciate it. 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: Tuesday, October 28, 2003 4:15 PM
To: Multiple recipients of list ORACLE-L

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));

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.


**************************************************************************************
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.
**************************************************************************************5
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.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).

Received on Tue Oct 28 2003 - 16:09:25 CST

RE: Solved - RE: UTL_RAW and slowness