Jared,
Point taken. I should do some testing instead of publish an opinion. I
still do not like the constraction, but that's a matter of
taste.
I have done some testing as well, because I think you were somehow
comparing apples and oranges: function a uses an implicit cursor, whereas
function b has an explicit cursor. So I ran a, b and b2 through Tom
Kyte's runstats harness, but found no significant differences:
Functions:
create or replace function a return
varchar2
is
begin
for srec in (select dummy from ctest)
loop
return srec.dummy;
end loop;
return null;
end;
/
create or replace function b return varchar2
is
cursor c1
is
select dummy
from ctest;
v_dummy varchar2(1) := null;
begin
open c1;
fetch c1 into v_dummy;
close c1;
return v_dummy;
end;
/
create or replace function b2 return varchar2
is
v_dummy varchar2(1) := null;
begin
select dummy
into v_dummy
from ctest;
return v_dummy;
exception
when no_data_found
then return null;
end;
/
Testrun:
set serveroutput on size 20000
exec runstats_pkg.rs_start;
declare
l_loop number := 0;
l_dummy varchar2(1);
begin
for l_loop in 1..1000
loop
l_dummy := a;
end loop;
end;
/
exec runstats_pkg.rs_middle;
declare
l_loop number := 0;
l_dummy varchar2(1);
begin
for l_loop in 1..1000
loop
l_dummy := b;
end loop;
end;
/
exec runstats_pkg.rs_stop(1);
For the test of b2 b was simply replaced by b2)
The results:
a vs b:
Run1 ran in 18 hsecs
Run2 ran in 18 hsecs
run 1 ran in 100% of the time
Name
Run1 Run2
Diff
LATCH.enqueue hash
chains
28
26 -2
LATCH.enqueues
28
26 -2
LATCH.library
cache
2,067
2,069 2
LATCH.redo
allocation
33
31 -2
LATCH.library cache
pin
2,046
2,048 2
STAT...enqueue
requests
16
14 -2
STAT...enqueue
releases
16
14 -2
STAT...calls to get snapshot s
4,011
4,009 -2
STAT...active txn count during
16
8 -8
STAT...consistent gets - exami
16
8 -8
STAT...calls to
kcmgcs
16
8 -8
STAT...cleanout - number of kt
16
8 -8
STAT...CPU used by this sessio
33
23 -10
STAT...consistent
gets
3,026 3,016
-10
STAT...CPU used when call star
33
23 -10
STAT...redo
entries
46
34 -12
LATCH.cache buffers
chains
6,226 6,212
-14
STAT...db block
changes
63
49 -14
STAT...db block
gets
100
68 -32
STAT...session logical reads
3,126 3,084
-42
STAT...redo
size
30,224 29,720 -504
STAT...recursive
calls
1,001 2,001
1,000
Run1 latches total versus runs -- difference and pct
Run1 Run2
Diff Pct
11,543 11,525 -18
100.16%
a vs b2:
Run1 ran in 17 hsecs
Run2 ran in 23 hsecs
run 1 ran in 73.91% of the time
Name
Run1 Run2
Diff
LATCH.enqueue hash
chains
28
26 -2
LATCH.enqueues
28
26 -2
LATCH.library
cache
2,067
2,069 2
STAT...bytes received via SQL*
984
986 2
LATCH.library cache
pin
2,046
2,048 2
LATCH.redo
allocation
34
31 -3
STAT...CPU used by this sessio
32
29 -3
STAT...enqueue
releases
17
14 -3
STAT...enqueue
requests
17
14 -3
STAT...CPU used when call star
32
29 -3
STAT...calls to get snapshot s
4,013
4,009 -4
STAT...active txn count during
17
8 -9
STAT...cleanout - number of kt
17
8 -9
STAT...calls to
kcmgcs
17
8 -9
STAT...consistent gets - exami
17
8 -9
STAT...consistent
gets
3,029 3,016
-13
STAT...recursive cpu
usage
12
25 13
STAT...redo
entries
49
34 -15
STAT...db block
changes
69
48 -21
LATCH.cache buffers
chains
6,235 6,207
-28
STAT...db block
gets
111
66 -45
STAT...session logical reads
3,140 3,082
-58
STAT...redo
size
30,648 29,660 -988
STAT...recursive
calls
1,001 2,001
1,000
Run1 latches total versus runs -- difference and pct
Run1 Run2
Diff Pct
11,557 11,519 -38
100.33%
(9.2.0.2/SuSE 8.1)
I find it quite strange that results vary from run to run. E.g. in
a vs b active txn count was 16 for a, whilst it was 17 for a vs. b2. Why
this difference?
B variants are consequently cheaper in redo size, session logical reads.
Recursive calls is conseqently 1000 higher for b/b2.
What counts is runtime, and a and b have no differences, b2, is appr. 25%
slower. b/b2 consume slightly less latches, but the diff is < 1
%.
So, IMHO what remains is the question of taste. I started 22 years ago as
software engineer, worked in R&D departments (of commercial software
firms) and built a 4GL, including a compiler and a universal interpreter
for the code produced. Maybe bearing the burdens of my career as a
stone-age 'C'-developer influences my 'taste' of beautiness of code (or
the lack off it).
Unless I've put some flaws in my testruns (which I'm glad to hear of), I
rest my case.
Regards, Carel-Jan
===
If you think education is expensive, try ignorance. (Derek Bok)
===
At 17:44 28-12-03 -0800, you wrote:
Carel,
It might seem that the loop construct would be more expensive, but
it didn't appear that way on my test system. ( 9.2.0.4, RH 8.0
)
function a:
create or replace function a return varchar2
is
begin
for srec in (select dummy from ctest)
loop
return srec.dummy;
end loop;
return null;
end;
/
function b:
create or replace function b return varchar2
is
cursor c1
is
select dummy
from ctest;
v_dummy varchar2(1) := null;
begin
open c1;
fetch c1 into v_dummy;
close c1;
return v_dummy;
end;
/
The resource consumption for a 1000 iterations of each: ( a is the
first
column )
17:38:42 poirot.jks.com - jkstill@ts01 SQL> @run_stats
NAME
RUN1
RUN2 DIFF
---------------------------------------- ---------- ----------
----------
LATCH.Consistent
RBA
0
1 1
LATCH.cache buffers lru
chain
1
0 -1
LATCH.lgwr LWN
SCN
0
1 1
LATCH.mostly latch-free
SCN
0
1 1
LATCH.session idle
bit
0
1 1
STAT...calls to get snapshot scn:
kcmgss
3012
3013 1
STAT...calls to
kcmgcs
7
6 -1
STAT...cleanout - number of ktugct
calls
0
1 1
STAT...consistent gets -
examination
0
1 1
STAT...session cursor cache
hits
1
2 1
STAT...parse count
(total)
1
2 1
STAT...opened cursors
current
1
2 1
STAT...opened cursors
cumulative
1
2 1
STAT...messages
sent
0
1 1
STAT...free buffer
requested
1
0 -1
STAT...execute
count
1003
1004 1
STAT...deferred (CURRENT) block
cleanout
4
3 -1
applications
STAT...calls to
kcmgas
0
1 1
STAT...user
commits
0
1 1
STAT...active txn count during
cleanout
0
1 1
LATCH.enqueues
0
1 1
LATCH.dml lock
allocation
0
2 2
LATCH.session
allocation
0
2 2
STAT...db block
changes
25
27 2
STAT...enqueue
releases
0
2 2
STAT...consistent
gets
3010
3012 2
LATCH.cache buffers
chains
6130
6133 3
STAT...redo
entries
17
20 3
STAT...recursive cpu
usage
4
7 3
STAT...db block
gets
30
33 3
LATCH.redo
writing
0
3 3
LATCH.undo global
data
1
4 3
LATCH.library
cache
7
4 -3
LATCH.enqueue hash
chains
0
4 4
LATCH.redo
allocation
18
22 4
LATCH.library cache
pin
7
3 -4
LATCH.messages
0
5 5
STAT...session logical
reads
3040
3045 5
STAT...commit
cleanouts
0
7 7
STAT...commit cleanouts successfully
com
0
7 7
pleted
STAT...redo
size
27184
27820 636
STAT...recursive
calls
2004
3007 1003
42 rows selected.
The for loop actually appears to be somewhat less expensive in terms
of database resources.
Jared
On Sun, 2003-12-28 at 11:39, Carel-Jan Engel wrote:
> What I don't understand is the loop construction:
>
> Actually only one (row) is read form the cursor, and then the
function is
> left with a return. Because it's an unconditional return, the code
within
> the loop will either execute once, or never. When no data is
found NULL is
> returned. When an error occurs NULL is returned as well. So, why a
loop?
>
> Wouldn't it be better to have something like:
>
> create or replace function XYZ(gid in number) return varchar2
is
> l_c1 tab1.C1%TYPE;
/* local variable to store C1 */
> begin
> select c1
> into l_c1
> from tab1
> where id = gid;
>
> return l_c1;
>
> exception
> when no_data_found
> then return
<some_error_code>; /* let the caller know that no data is
> found */
> when others
> then return 'ERROR:
'||TO_CHAR(SQLCODE); /* return the error-code,
> preceded by the text ERROR for identification */
> end;
>
> Sure, a loop prevents an ORA-1422, but I don't think a loop
construction
> should be abused for this. Just think about all loop controlling
code that
> needs to be set up by the interpreter. tab1.ID should be unique, so
a 1422
> normally cannot occur. Robust programming however asks us to prevent
any
> error. I would prefer to think about how a 1422 should be handled,
and
> write some code accordingly.
>
> Regards, Carel-Jan
>
> ===
> If you think education is expensive, try ignorance. (Derek
Bok)
> ===
>
>
> At 09:39 28-12-03 -0800, you wrote:
> >Hi
> >
> >if we assume it is implements this way (see below)
there will only be
> >one cursor since c_gid
> >is a bind variable and there for the cursor will be sharded
from call to
> >call of the function.
> >
> >create or replace function XYZ (gid in number) return varchar2
is
> > cursor cur1(c_gid number) is select C1 from tab1
where id = c_gid;
> >begin
> > for x in cur1(gid) loop
> > return x.c1;
> > end loop;
> > return null;
> >exception
> > when others then return null;
> >end;
> >
> >It will only be one coursor
> >
> >Guang Mei wrote:
> >>
> >>I have a function like below (psudo code). If cursor cur1
have multiple
> >>rows, would the code leave the cursor open when this
function is called?
> >>So if this function is called 1000 times, I would have 1000
open cursors?
> >>
> >>function XYZ(gid in number) return varchar2 is
> >> cursor cur1 is select C1 from tab1 where ID =
gid;
> >>begin
> >> for x in cur1 loop
> >> return x.c1;
> >> end loop;
> >> return null;
> >>exception
> >> when others then return null;
> >>end;
> >>
> >>
> >
> >--
> >
> >Best regards/Venlig hilsen
> >
>
><mailto:peter.gram@miracleas.dk>Peter
Gram
> >
>
><http://www.miracleas.dk/>Miracle<http://www.miracleas.dk/>
A/S
> >Kratvej 2
> >DK - 2760 Måløv
> >Cell: (+45) 2527 7107
> >Phone: (+45) 4466 8855
> >Fax: (+45) 4466 8856
> >Home: (+45) 3874 5696
> >Email:
<mailto:peter.gram@miracleas.dk>peter.gram@miracleas.dk
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Jared Still
INET: jkstill@cybcon.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@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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Carel-Jan Engel
INET: cjpengel.dbalert_at_xs4all.nl
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 Dec 30 2003 - 04:49:27 CST
