multiple cursor defs, vs NDS

multiple cursor defs, vs NDS

Post by Job Mill » Wed, 09 Jan 2002 05:10:36



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

 
 
 

multiple cursor defs, vs NDS

Post by Thomas Kyt » Wed, 09 Jan 2002 09:39:32


In article <2edf8ed9.0201071210.2f477...@posting.google.com>,
jobmil...@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$tk...@ORA817DEV.US.ORACLE.COM> create table run_stats ( runid varchar2(15),
name varchar2(80), value int );

Table created.

ops$tk...@ORA817DEV.US.ORACLE.COM>
ops$tk...@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$tk...@ORA817DEV.US.ORACLE.COM>
ops$tk...@ORA817DEV.US.ORACLE.COM>
ops$tk...@ORA817DEV.US.ORACLE.COM> column name format a40
ops$tk...@ORA817DEV.US.ORACLE.COM>
ops$tk...@ORA817DEV.US.ORACLE.COM>
ops$tk...@ORA817DEV.US.ORACLE.COM>
ops$tk...@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$tk...@ORA817DEV.US.ORACLE.COM>
ops$tk...@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$tk...@ORA817DEV.US.ORACLE.COM>
ops$tk...@ORA817DEV.US.ORACLE.COM>
ops$tk...@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$tk...@ORA817DEV.US.ORACLE.COM>
ops$tk...@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 (tk...@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

 
 
 

1. Remote view field defs WILL NOT match table field defs

I use remote views defined from a datasource that is a visual foxpro
DBC.  At one point i had a table in this remote DBC where some fields
were defined as currency fields.  I then created a remote view for this
table. Later i changed the table's field def from currency to numeric
(16,2).

I then deleted the remote view out of my project and recreated it. Now,
when i modify the view definition and under the FIELDS TAB of the view
designer, highlight the field and click on PROPERTIES, the width of the
field is 16 and the decimal percision is 1 and NOT 2!  The view designer
will let me change the decimal percision to 2 and save it (appears to),
but when you look at the properties again, its still 1. It never saves
it.

What is going on?


2. Path not found error

3. Multiple cursors - second cursor hangs when opened

4. PS Printing from 4GL

5. client side cursor vs. server side cursor

6. Universe Retrieve Not Equal

7. global cursors vs. local cursors

8. SF, CA :Programmer Analyst with System Builder experience

9. SQL Server Cursors vs. Oracle Cursors

10. Client Side Cursors vs Server Side Cursors

11. Please explain server side cursor vs client side cursor and CacheSize=1

12. Connection cursor location vs. Recordset cursor location