Re: Solved - RE: UTL_RAW and slowness

Raj

Jamadagni, Rajendra wrote:

> Why do you think the profsum output is not right?

I did not say that profsum output is not right/correct. I said that it does not show what's needed to be seen.

Just imagine to run SUBSTR() you call 1 internal function, to run utl_raw PL/SQL engine has to do a lot more. And now we see that due to some "unknown" reason utl_raw is faster than simple SUBSTR(). To get you some more clue -- enable SQL tracing (waits) for you PL/SQL block. You would see that most of the time your code is waiting for some temp space operations, your next guess will be correct ... it's because of CLOB datatype. So, SUBSTR() (overloaded one) does operate with data which is very expensive.

> at least it tells me that 83% of my time is spent on the line that does
> substr() ... right?

Right, but it leaded you to the wrong direction of fixing the issue and to the wrong conclusion as well.

> msg_text is a clob, l_n is number you are right ....

msg_text has to be changed to VARCHAR2(32767) [32767, or something more suitable for your needs] and l_n has to be changed to BINARY_INTEGER.

> substr() was used because we transfer only US ASCII data.

Ok.

> 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.

If it works for you then Ok.

> Got better ideas?

Sure, have a look at the note 61737.1 or into the documentation, there you find an example of 'CLOB piece wise fetch' -- that's what you need.

> 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.

Fine, but it has to be tested carefully as well :)

> Thanks for your feedback, I appreciate it.

You're welcome.

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