ref cursors and prefetching
A reader, February 22, 2006 - 11:37 pm UTC
Hi Tom,
A ref cursor returned from a PL/SQL procedure to an OCI C program doesn't support prefetching. For typical select statements, a prefetch count of 50 will result in 50 rows retrieved per fetch. But if a ref cursor has a prefetch count of 50, it still only fetches one row at a time.
February 23, 2006 - 7:45 am UTC
That is not true. Pro*c is a layer on OCI, I know pro*c much better than OCI, but if OCI cannot do it, neither can Pro*C.
static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR ename_data[100][31];
int empno_data[100];
SQL_CURSOR dyn_cursor;
EXEC SQL END DECLARE SECTION;
EXEC SQL ALLOCATE :dyn_cursor;
exec sql alter session set sql_trace=true;
EXEC SQL EXECUTE
BEGIN
proc( p_cursor=>:dyn_cursor );
END;
END-EXEC;
EXEC SQL WHENEVER NOT FOUND DO BREAK ;
while (SQLCODE == 0)
{
EXEC SQL FETCH :dyn_cursor INTO :ename_data, :empno_data;
printf("Ename = %s Empno = %d\n", ename_data, empno_data) ;
}
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL CLOSE :dyn_cursor;
}
SELECT ENAME, EMPNO
FROM
EMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 4 0 14
One fetch, all 14 rows. A ref cursor.
PRO*C built on top of OCI?
Richard, February 23, 2006 - 3:55 pm UTC
Hello Tom.
You say that PRO*C is built on top of the OCI? I had always thought that PRO*C used its own set of low-level libraries, completely separate from the OCI. I assume I must have read that or been told that a long time ago. Has PRO*C always been a layer on top of the OCI?
February 23, 2006 - 7:47 pm UTC
That changed way back in version 8.
Pro*C used to be built on a thing called "sqllib", but when OCI was rewritten after version 7.3 - Pro*C started using it.
PL/SQL code for proc
A reader, February 23, 2006 - 7:15 pm UTC
Tom,
Could you please show the PL/SQL code for proc that you used in your test.
February 23, 2006 - 8:18 pm UTC
create or replace procedure proc( p_cursor in out sys_refcursor )
as
begin
open p_cursor for select ename, empno from emp;
end;
/
9i or 10g
A reader, February 23, 2006 - 8:41 pm UTC
Hi Tom,
Did you perform your test in 9i or 10g? My OCI test, where the ref cursor didn't prefetch, was in 9iR2.
February 24, 2006 - 7:46 am UTC
there you go - ok, that could be the case.
closing of cursor
Joachim Mayer, February 24, 2006 - 1:27 am UTC
Hi Tom,
I use the same ref-cursors to allow applications (most written in C#) access to my database. I saw you made a CLOSE on the ref-cursor! Once i had the problem somebody did forget it and we run out of Cursors. Is there a way to protect myselft against. Can I do somthing to force the cursor closing or do it in the PL/SQL Code when the Applications fails to do so?
thx
Joachim
February 24, 2006 - 8:17 am UTC
Is there a way to protect yourself from forgetting to close a file?
Sort of writing correct code, no - not really. Only the application that opened the cursor can close the cursor. If you have an application that LEAKS a resource, you have a bug in the application - regardless of what the resource is.
fortunately, it only affects that application instance and not the server as a whole, so you can fix the buggy application.
To be more specific
Dieter, February 27, 2006 - 1:15 pm UTC
Hi Tom,
You are absolutely right: i should have been more specific.
What I'm really interested in, are functions returning "synthesized" data!
A ref cursor would not help here but there are still quite a few options that could be used:
1) returning the total amount of data in one vector of rows
2) repeating to call a procedure returning one row
3) mixing 1) and 2) and returning chunks of rows in a vector
4) using 1-3, then casting to a table and fetch data
5) using a pipelined function
6) procedure writes data to a (physical) temporary table and fetch data from the table
Regards,
Dieter
February 27, 2006 - 2:41 pm UTC
if the data is synthesized - a REF CURSOR opened against a pipelined function could make sense - don't rule out ref cursor just because.
ref cursors and prefetching
Biswa, September 21, 2007 - 6:52 am UTC
This is in reference to the Pro*C example that you use to demonstrate prefetching for REF CURSORs:
I think when most users talk of prefetch, they mean the case where Oracle does the hard work and manages the caching data structures behind-the-scenes. This is done
in Pro*C by using "PREFETCH=n" on the Pro*C commandline during pre-compilation
or
in OCI by using OCIAttrSet(...OCI_ATTR_PREFETCH_ROWS...)
The Pro*C example that you use to demonstrate prefetching however is one where you explicitly declare an array to hold 100 elements - and that is why you fetch the 14 rows in one DB roundtrip. This is probably not what most people use (or like to). I expect if you used the "prefetch" functionality managed by Oracle, you would get a Db roundtrip for each row (plus one).
I have tested this using Oracle 10g and prefetch still does not seem to work for REF CURSORs. I hope the Prefetch functionality supplied by Oracle will be made to work consistently across REF CURSORs in the near future.
September 26, 2007 - 7:47 am UTC
why would you think prefetch is "behind the scenes"...
call it what you will....
if Oracle does the prefetching for you - you would NOT get a roundtrip for each row - you would get one roundtrip for each prefetch of N records.
ref cursors and prefetching
Biswa, October 02, 2007 - 7:23 pm UTC
You said: why would you think prefetch is "behind the scenes"...
Because using prefetch, a user does not write code to manage underlying arrays/memory used to cache rows - while using "array fetch" a user has to allocate / free memory explicitly in code.
You said: call it what you will....
Please take a look at metalink "Bug 3140718" and "Doc Id 293030.1" - it distinguishes between prefetch and "array fetch". It is unfortunate that an enhancement request has been pending since 2003 and there is no solution to date.
You said: if Oracle does the prefetching for you - you would NOT get a roundtrip for each row - you would get one roundtrip for each prefetch of N records.
That was what I would expect from Oracle; however I suggest you take some of your own advice and test out prefetch (not array-fetch) for REF CURSORs using OCI or Pro*C. I have tested it and it does not work.