Are ref cursors a good "best practice"?
Matt, February 16, 2005 - 9:07 am UTC
I have been thinking a little about this approach. Basically, I have been trying to come up with a best practice for system development. I had imagined that I might be able to design a system with a data access layer. This layer would be a set of wrappers to SQL and would return result sets. These would be called from clients (java, or whatever), but also re-used as necessary by the PL/SQL (say if there was a number crunching component). Problems taht I see are:
* By using these interfaces within PL/SQL, I am off setting code re-use and maintainability against reduced performance
* A ref cursor gets parsed on each call (although correctly setting session_cached_cursors should offset this)
Is this a fair best practice, given my understanding of the trade-offs?
Best Regards,
February 16, 2005 - 9:27 am UTC
I have a theory.
The best database programs on the planet do not contain the words "select", "insert", "update", "delete", and "merge" in them.
they contain "call" or "begin/end"
and performance is massively improved in such a system -- sure, maybe they soft parse more than they should be at least they are NOT hard parsing like mad (assuming we can get the coders to bind the calls to the stored procedures -- and even if they don't the procedures they call will do 5-10 sql's perhaps that are nicely bound so instead of hard parsing 5-10 sql's for each transaction, they only do 1 -- that is a start)
it is like instrumentation of code. People say 'but that is a performance hit, we don't want that in production'
Me -- I think the code has to be heavily instrumented before going into production, the best code in production has every other line being "debug, trace, instrumentation". And that is the only thing that lets the code run fast. You need proof for that? Ok, imagine Oracle in production with:
a) no sql_trace=true ability
b) no v$ tables -- NONE, no v$waitstat, no statspack, no v$sql, no v$sql_plan, nothing
c) no events for tracking down that spurious error that doesn't reproduce in test, nothing
You would be blind, you would have NO CHANCE of fixing or even figuring out what the problem is on such a system. Oracle is heavily instrumented -- excessively instruemented. Would the binaries run faster without this instrumentation? I say NO, resoundingly NO! Why? Because we would have no abilities to find out "what is wrong"
So, same thing applies here. I would take most definitely the lesser evil of some soft parsing that I could avoid simply to have the code in the database so I can actually
a) make sure it uses binds
b) is implemented transactionally correct (GUI programmers do not make the best OLTP programmers, they do really good GUI's -- but they are not database, transactional programmers -- many of them think "autocommit" in jdbc/odbc is a "good thing" (it is HORRIBLE)
c) when it "goes slow", we can actually find out why really fast and then fix it. How many systems have I seen where I point to a query in v$sql and say "who is running that query, we need to fix it now" and the answer is "gee, I don't know, not mine, nope -- i didn't write that, not me, me neither..." and you cannot even grep the code for it since the query is a string lovingly put together a bit at a time or even worse, constructed by some "database independent layer whose intention is to hide this complex thing called a database from the coders" (funny, j2ee isn't deemed horribly complex but SELECT is, oh well)
d) it maximizes code reuse far beyond any other technology I've ever seen. For you see, if something can connect to the database and run "select", they can connect and run "stored procedure" meaning all of a sudden, anything on the planet can run my well formed, tested, performant tranactions -- incredible code reuse.
so don't even call it a trade-off, call is "the safest thing to do"
Pipelined Table Function is faster than Store Procedure return Ref Cursor, and use less Latches
Charlie Zhu, May 17, 2006 - 7:18 pm UTC
I just wonder why I got 50% more logical reads when call the PL/SQL Store Procedure to return Ref Cursor.
Run1 is Store Procedure, Run2 is Table Function.
Run1 ran in 341 hsecs
Run2 ran in 172 hsecs
run 1 ran in 198.26% of the run2 time
STAT...consistent gets 90,010 60,012 -29,998
STAT...consistent gets from ca 90,010 60,012 -29,998
STAT...no work - consistent re 90,000 60,000 -30,000
STAT...cluster key scan block 90,000 60,000 -30,000
STAT...buffer is not pinned co 90,000 60,000 -30,000
STAT...session logical reads 90,072 60,065 -30,007
STAT...recursive calls 64,003 4,004 -59,999
LATCH.cache buffers chains 180,277 120,266 -60,011
STAT...session pga memory max 196,608 65,536 -131,072
STAT...session uga memory max 196,500 61,996 -134,504
Run1 latches total versus Run2 -- difference and pct
Run1 Run2 Diff Pct
202,749 146,812 -55,937 138.10%
PL/SQL procedure successfully completed.
Here is the objects creation SQL scripts:
== == ===
CREATE CLUSTER abelisting.book_clus(listingsid NUMBER(20,0))
SIZE 23 SINGLE TABLE
HASH IS listingsid
pctfree 5
HASHKEYS 5000
tablespace data_auto
--HASHKEYS 20000000
--tablespace abe_data_8m;
CREATE TABLE abelisting.book_del_check (
listingsid NUMBER(20,0), --PRIMARY KEY,
upd_dd NUMBER(2,0)
)
CLUSTER abelisting.book_clus (listingsid);
CREATE OR REPLACE PACKAGE ABELISTING.sp_sql_query AS
/******************************************************************************
NAME: sp_sql_query
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ----------- --------------- ------------------------------------
1.0 11/May/2006 Charlie Zhu1 1. Create the package.
******************************************************************************/
TYPE nt_tab IS TABLE OF NUMBER;
TYPE ListIDRecTyp IS RECORD (listingid number(20));
TYPE listidcurtyp IS REF CURSOR RETURN ListIDRecTyp;
--SYS_REFCURSOR
--TYPE listidcurtyp IS REF CURSOR RETURN abelisting.book_del_check%ROWTYPE;
PROCEDURE list_df(p_cursor in out listidcurtyp, p_string VARCHAR2);
FUNCTION list_df_tabf(p_string VARCHAR2) return nt_type PIPELINED DETERMINISTIC;
END;
/
CREATE OR REPLACE PACKAGE BODY ABELISTING.sp_sql_query
AS
/* Java programmer utilities
*/
PROCEDURE list_df(p_cursor in out listidcurtyp, p_string VARCHAR2)
as
i pls_integer;
begin
open p_cursor for
WITH sq
as
(
SELECT substr(x, instr(x,',',1,level)+1, instr(x,',',1,level+1) - instr(x,',',1,level) -1) token
FROM (select ','||l_str||',' x,l_str from (select p_string l_str from dual))
CONNECT BY level <= length(l_str)-length(replace(l_str,',',''))+1
)
select a.listingsid
from abelisting.book_del_check a, sq
where a.listingsid = To_Number(sq.token);
END;
Function list_df_tabf(p_string VARCHAR2)
return nt_type
PIPELINED DETERMINISTIC
as
l_rec number;
begin
for c in (WITH sq
as
(
SELECT substr(x, instr(x,',',1,level)+1, instr(x,',',1,level+1) - instr(x,',',1,level) -1) token
FROM (select ','||l_str||',' x,l_str from (select p_string l_str from dual))
CONNECT BY level <= length(l_str)-length(replace(l_str,',',''))+1
)
select a.listingsid listingsid
from abelisting.book_del_check a, sq
where a.listingsid = To_Number(sq.token))
loop
pipe row( c.listingsid);
end loop;
return;
End;
END;
/
Declare
l_string varchar2(4000);
--c_sp ABELISTING.sp_sql_query.listidcurtyp;
c_sp SYS_REFCURSOR;
l_i number;
Begin
--l_string := '6536119,6857511,410567,410698,696183,12740420,3742216,1198278,513323,151440,10214888,8387745,3212870,808131,798430,6273692,409569,1395308,3988346,4737101,3377464,95642';
l_string := '700000173,680000143,680000221,700000135,180000118,680000265,720000215,700000112,700000151,680000251,600000120,700000142,720000158,700000262,660000202,600000115,600000123,600000126,600000139,600000141,600000145,600000148,600000152,600000159,600000188,600000244,600000253,660000246,680000200,700000136';
runStats_pkg.rs_start;
For i in 1 .. 2000 Loop
ABELISTING.sp_sql_query.list_df(c_sp,l_string );
Loop
fetch c_sp into l_i;
exit when c_sp%notfound;
End loop;
End Loop;
runStats_pkg.rs_middle;
For i in 1 .. 2000 Loop
For c in(select column_value listingsid from table(cast(abelisting.sp_sql_query.list_df_tabf(l_string) as nt_type)))
Loop
Null;
End loop;
End Loop;
runStats_pkg.rs_stop;
End;
/