| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: multiple cursor defs, vs NDS
In article <2edf8ed9.0201071210.2f477010_at_posting.google.com>,
jobmiller_at_yahoo.com says...
>
>Multiple queries, all with the same very long select statement, just
>different columns in the where clause, or no where clause at all.
>
>There is a finite number of possibilities though.
>
>my lazy approach was to define the cursor returning all the records,
>so that I could define a record based on that cursor type and a ref
>cursor variable. I would use the real cursor if I needed the whole
>set.
>
>I also defined a function that returned the same SQL statement with a
>different where clause to be used with Native Dynamic SQL to open the
>cursor using the ref cursor variable defined earlier.
>
>This means in total I define a cursor and a function. But I don't
>know how much my application is actually suffering from the use of NDS
>as opposed to 15 explicitly defined cursors with paramaters. NDS
>would still be necessary in at least one of the cases though.
>
>are there any more practical design ideas to handling multiple
>different where clauses all of the form (column=xxx) where column can
>be any column but with the flexibility to return all rows as well?
>
>all those cursor definitions would be quite a drag.
>any thoughts or advice would be appreciated.
>
>Job
when considering between static ref cursors and NDS, performance doesn't come into play really -- as long as you use bind variables, they will be about the same (most of the work is done with the cursor, not the opening of it)
Below shows one method of seeing that they do pretty much the same thing:
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table run_stats ( runid varchar2(15), name varchar2(80), value int );
Table created.
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace view stats 2 as select 'STAT...' || a.name name, b.value 3 from v$statname a, v$mystat b 4 where a.statistic# = b.statistic# 5 union all 6 select 'LATCH.' || name, gets 7 from v$latch;
View created.
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> column name format a40 ops$tkyte_at_ORA817DEV.US.ORACLE.COM> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package demo_pkg 2 as 3 type rc is ref cursor; 4 5 procedure open_static_ref_cursor( p_cursor in out rc ); 6 7 procedure open_dynamic_ref_cursor( p_cursor in out rc );8 end;
Package created.
ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package body demo_pkg
2 as
3 procedure open_static_ref_cursor( p_cursor in out rc )
4 as
5 begin
6 open p_cursor for select * from dual;
7 end;
8
9 procedure open_dynamic_ref_cursor( p_cursor in out rc )
10 as
11 begin
12 open p_cursor for 'select * from dual';
13 end;
14
15 end;
16 /
Package body created.
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> declare 2 l_start number; 3 l_cursor demo_pkg.rc; 4 l_dummy dual.dummy%type; 5 begin 6 insert into run_stats select 'before', stats.* from stats; 7 8 l_start := dbms_utility.get_time; 9 for i in 1 .. 1000 10 loop 11 demo_pkg.open_static_ref_cursor( l_cursor ); 12 loop 13 fetch l_cursor into l_dummy; 14 exit when l_cursor%notfound; 15 end loop; 16 close l_cursor; 17 end loop; 18 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' ); 19 20 insert into run_stats select 'after 1', stats.* from stats; 21 22 l_start := dbms_utility.get_time; 23 for i in 1 .. 1000 24 loop 25 demo_pkg.open_dynamic_ref_cursor( l_cursor ); 26 loop 27 fetch l_cursor into l_dummy; 28 exit when l_cursor%notfound; 29 end loop; 30 close l_cursor; 31 end loop; 32 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' ); 33 34 insert into run_stats select 'after 2', stats.* from stats;35 end;
PL/SQL procedure successfully completed.
ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1,
c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name 5 and b.name = c.name 6 and a.runid = 'before' 7 and b.runid = 'after 1' 8 and c.runid = 'after 2' 9 and (c.value-a.value) > 0 10 and (c.value-b.value) <> (b.value-a.value)11 order by abs( (c.value-b.value)-(b.value-a.value)) 12 /
NAME RUN1 RUN2 DIFF ---------------------------------------- ---------- ---------- ---------- LATCH.redo writing 1 0 -1 LATCH.undo global data 1 0 -1 STAT...consistent gets 1012 1011 -1 STAT...parse time cpu 1 2 1 STAT...enqueue releases 3 2 -1 STAT...calls to get snapshot scn: kcmgss 1006 1005 -1 LATCH.direct msg latch 2 0 -2 LATCH.done queue latch 2 0 -2 LATCH.session queue latch 2 0 -2 STAT...enqueue requests 4 2 -2 LATCH.cache buffers lru chain 3 6 3 LATCH.checkpoint queue latch 3 6 3 STAT...redo entries 16 13 -3 STAT...parse time elapsed 2 5 3 LATCH.messages 3 0 -3 LATCH.library cache 2272 2269 -3 LATCH.ksfv messages 4 0 -4 STAT...db block changes 27 23 -4 STAT...db block gets 4024 4019 -5 STAT...recursive cpu usage 35 30 -5 STAT...session logical reads 5036 5030 -6 STAT...redo size 21472 21480 8 LATCH.redo allocation 14 23 9 LATCH.shared pool 1128 1110 -18 LATCH.cache buffers chains 10099 10132 33
25 rows selected.
With no measurable difference in run time, latching, or other stats...
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Mon Jan 07 2002 - 18:39:32 CST
 |
 |