Posted by In the previous post, I mentioned that for a programming environment fetching rows from the database, then the method of
- open a ref cursor
- issue a fetch call
- close the ref cursor
might not be appropriate for those situations where the result set is known be a single row (eg primary key lookup).
A better option might be to call a procedure and get those outputs as parameters.
And I broke a cardinal rule… I effectively said “Here’s something that I know to be true…so you should just trust me“.
Dear oh dear oh dear. That’s not good. So without further ado, it’s time to put together a test case.
We’re need to come up with a way to test this across a “fast” network, the aim being that even with a best network latency we can obtain, there will still be a demonstratable benefit to using a PL/SQL procedure over the ref cursor method.
One way of doing this is, is to have two databases running on the same box. One database will be the “real” database server, ie, it will hold our data. The other database will actually be a client to this database, and we will access the data via a database link. So we never go out across the true physical network – we are simply getting the smallest latency we can by going (in my case) localhost to localhost.
So firstly on our “real” database, we create a table with a primary key. This database is called “NP12” on my machine
SQL> drop table T purge;
Table dropped.
SQL> create table T (
2 x int primary key,
3 y int ,
4 z char(100));
Table created.
SQL> insert into T
2 select rownum, rownum, rownum
3 from dual
4 connect by level <= 1000;
1000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','T');
PL/SQL procedure successfully completed.
Now I’ve created a second database, so we’ll connect to that and that create some routines to access the table T on NP12
SQL> create database link np12 using 'np12';
Database link created.
These are our two procedure we will use for the test. One is our REF CURSOR procedure, which opens a cursor and passes it back to the client. The second does the whole job in the procedure but issuing the query and passing the fetched value back as a parameter.
SQL> create or replace
2 procedure ref_cursor_open(p_i int, rc in out sys_refcursor) is
3 begin
4 open rc for select y from t@np12 where x = p_i;
5 end;
6 /
Procedure created.
SQL> create or replace
2 procedure single_row_fetch(p_i int, p_y out int) is
3 begin
4 select y into p_y from t@np12 where x = p_i;
5 end;
6 /
Procedure created.
And now its time to test them for speed using a simple looping script. I’m using the INLINE pragma to reduce the overhead of PL/SQL calls so that we have a better accuracy with respect to the cost of the extra calls when using REF CURSOR.
SQL> set timing on
SQL> declare
2 rc sys_refcursor;
3 res int;
4 begin
5 for j in 1 .. 10 loop
6 for i in 1 .. 1000 loop
7 pragma inline(ref_cursor_open,'YES');
8 ref_cursor_open(i,rc);
9 fetch rc into res;
10 close rc;
11 end loop;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.55
SQL> declare
2 rc sys_refcursor;
3 res int;
4 begin
5 for j in 1 .. 10 loop
6 for i in 1 .. 1000 loop
7 pragma inline(single_row_fetch,'YES');
8 single_row_fetch(i,res);
9 end loop;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.80
SQL>
And there you have it. Almost twice as fast. If you just trusted what I said from the first post…then that’s great. But in general, if you read something in someone’s blog, either challenge them to prove their assertion or just whip up a script to prove it for yourself.
Learning is not a spectator sport 
Simple test case but point proved.
Cheers,
Vijay Sehgal
Fake. “Elapsed” does not display in red on my SQL*Plus… 🙂
Cheers
Tim…
I did my demo using command line mode of the Oracle Games Console version 13x
If you use that, you’ll see the colours 🙂
What you really need is a video demonstration… 🙂
What you really need is to call from a typical client environment. Calling one database from another using a server-side programming language is not typical.
As I mentioned in a comment on your previous post, client-side drivers such as JDBC have worked to reduce round trips over the years.
Here is my test case:
import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;
public class RoundTrips {
public static void main(String[] args) throws Exception{
OracleDataSource ods = null;
Connection conn = null;
PreparedStatement ps = null;
CallableStatement cs = null;
ResultSet rset = null;
long startTime;
// Create DataSource and connect to the local database
ods = new OracleDataSource();
ods.setURL(“jdbc:oracle:thin:@//127.0.0.1:1522/orcl.localdomain”);
ods.setUser(“stew”);
ods.setPassword(“stew”);
conn = ods.getConnection();
conn.setAutoCommit(false);
try {
// Do once to load appropriate Java classes
ps = conn.prepareStatement(“select y from t where x = ?”);
ps.setInt(1, 1);
rset = ps.executeQuery();
rset.next();
rset.close();
ps.close();
cs = conn.prepareCall(“begin ? := single_row_fetch(?); end;”);
cs.registerOutParameter(1, Types.INTEGER);
cs.setInt(2, 1);
cs.execute();
cs.close();
// Real test
ps = conn.prepareStatement(“select y from t where x = ?”);
startTime = System.currentTimeMillis();
for (int i = 1; i <= 1000; i++) {
ps.setInt(1, i);
rset = ps.executeQuery();
rset.next();
}
System.out.println("Select time: " + (System.currentTimeMillis() – startTime) + " ms");
cs = conn.prepareCall("begin ? := single_row_fetch(?); end;");
cs.registerOutParameter(1, Types.INTEGER);
startTime = System.currentTimeMillis();
for (int i = 1; i <= 1000; i++) {
cs.setInt(2, i);
cs.execute();
}
System.out.println("Call time: " + (System.currentTimeMillis() – startTime) + " ms");
} catch (Exception e) {
e.printStackTrace();
}
finally{
if(rset!=null) rset.close();
if(ps!=null) ps.close();
if(cs!=null) cs.close();
if(conn!=null) conn.close();
}
}
}
Select time: 301 ms
Call time: 331 ms
The pragma inline is fantastic, in my test code performance is also twice faster! Good explanation is also here:
http://stevenfeuersteinonplsql.blogspot.com/2015/07/inline-your-subprograms-but-only-within.html
Well, let’s learn every day something new about Oracle.
If only I knew about this on my previous project, where every millisecond counted!
Dusan