RE: ouch

From: Kenneth Naim <kennethnaim_at_gmail.com>
Date: Wed, 31 Aug 2011 17:19:47 -0400
Message-ID: <02ad01cc6823$b719d7d0$254d8770$_at_gmail.com>

You can use asktom's (asktom.oracle.com) runstats to show not just performance timings but locks, latches and several other interesting stats. The process is very friendly and shows a nice comparison of the two processes you pass in.  

Ken  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Wednesday, August 31, 2011 5:01 PM To: SUzzell_at_MICROS.COM; Oracle L
Subject: Re: ouch  

Stephan,

If you want to demonstrate the utter wrongness of this, try...

SQL> set timing on
SQL> declare
  2 v_str varchar2(1000);
  3 begin

  4       for i in 1..100000 loop
  5              v_str := substring('This stinks',5,1);
  6       end loop;

  7 end;
  8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.34
SQL>
SQL> declare
  2 v_str varchar2(1000);
  3 begin

  4       for i in 1..100000 loop
  5              v_str := substr('This stinks',5,1);
  6       end loop;

  7 end;
  8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

Gosh, after seeing that, I had to go rinse my brain out... blech!

Hope this helps...

-Tim

-----Original Message-----

From: Uzzell, Stephan [mailto:SUzzell_at_MICROS.COM] Sent: Wednesday, August 31, 2011 02:31 PM To: 'Oracle L'
Subject: ouch

Found a gem in one of my databases today:  

FUNCTION                        "SUBSTRING"

(INSTRING IN varchar2,

STARTPOS IN number,

LENGTH IN number)

RETURN varchar2

IS

  RESULT varchar2(8000);

BEGIN select substr(INSTRING, STARTPOS, LENGTH)

into RESULT

from DUAL;

RETURN(RESULT); END;   I?m offended by the sheer aesthetics of this. And I want to go yell at the developers that this is wrong. But I?d need a better argument than that it offends me. Is there some way to quantify the impact (if any) of wrapping a built-in function like this?  

Stephan Uzzell    

--

http://www.freelists.org/webpage/oracle-l Received on Wed Aug 31 2011 - 16:19:47 CDT