Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: multiple cursor defs, vs NDS

Re: multiple cursor defs, vs NDS

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 7 Jan 2002 16:39:32 -0800
Message-ID: <a1df4404nh@drn.newsguy.com>


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;
  9 /

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;
 36 /
41 hsecs
39 hsecs

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 Corp 
Received on Mon Jan 07 2002 - 18:39:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US