RE: ouch
- From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
- To: <tim@xxxxxxxxx>, <SUzzell@xxxxxxxxxx>, "'Oracle L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 31 Aug 2011 17:19:47 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Tim Gorman
Sent: Wednesday, August 31, 2011 5:01 PM
To: SUzzell@xxxxxxxxxx; 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@xxxxxxxxxx]
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
Other related posts:
