Great response
Manoj, July 05, 2001 - 2:38 pm UTC
Nothing on the earth could have explained it better. Can you help me once more. Do I need to modify all the procedures to use session_cache_cursor or there is any system level parameter for it.
Most useful
Harrison, July 06, 2001 - 12:32 am UTC
Really good, if it is not in the book we can glue
the pages in (I don't know what that other reader
was talking about "Nobody could do it better...",
you might do really well on the next one, and then
start to expect "Nobody..." every time, and our lives
will be hell trying to deal with you. I am in favor
of some moderation on these compliments until we are
satisfied that you have answered all the questions we
can come up with.
REFCURSOR
Phil Williams, April 30, 2002 - 7:39 pm UTC
Is this statement still true for 9i or is there a new methodology availble now ?
April 30, 2002 - 8:26 pm UTC
Ref Cursors HAVE to parse each time.
The reason we cannot cache them (like plsql cursors) is due to the fact that you could have a procedure:
procedure p( x in out refcursor_type )
is
begin
open x for select * from dual;
end;
Now, if there was JUST one cached cursor and a client went:
p( cursor1 );
p( cursor2 );
we would be in a heap of trouble! They would both be the "same" cursor. It gets even more muddied when p looks like:
procedure p( x in out refcursor_type )
is
begin
if some_condition then
open x for select * from t;
else
open x for select * from t2;
end if;
end;
Here, the meaning of "x" changes from call to call.
to lessen the impact, one should use session_cached_cursors.
Not quite getting it....sorry
A reader, December 08, 2002 - 9:05 pm UTC
Tom,
Just got confused on these two sentences here.
"Ref cursors will cause AT LEAST a soft parse each time, yes."
"Ref Cursors HAVE to parse each time."
Soft parse indicates that we do not parse the query again. So, ref cursors cause hard parse each time. Do i make sense?
My second question is in Oracle reports application, would all the ref cursor queries be parsed every time it runs?
Thanks for your time
December 09, 2002 - 7:25 am UTC
read
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2588723819082 <code>
a soft parse does NOT mean we do not parse again -- if it did, it wouldn't have the word parse in it!!
A soft parse is a parse. A hard parse is a parse. They are both parsed, just different kinds and hopefully that writeup I point you to will clear that up.
when you use a REF CURSOR, by definition -- a parse will take place on each and every open. It might be a soft parse, it might be a hard parse -- but it'll be a parse.
Arun Gupta, December 09, 2002 - 10:05 am UTC
If I set session_cached_cursors to say 100 then are there any other implications that I need to aware of, like increase in memory requirements etc.?
Thanks
December 09, 2002 - 10:19 am UTC
marginal increase in UGA memory in the dedicated server or SGA depending on where your UGA is coming from.
Parsed cursor cache
Christophe Parmentier, December 09, 2002 - 11:58 am UTC
Hi, Tom
Reading this article I reminded something that bothered me some time ago...
In our DataWarehouse, we use dynamic tables, with dynamic names, we can't bind a table name, does that mean the cursor will always be hard parsed ?
Example:
'Select ... from ' || DYN_TABLE_NAME || ' WHERE Field = :1'
Is it useful to bind a such request even if it will be parsed each time, at least for the Table_name ?
I thought binding a such request was useless, was I right ?
Thanks for the help ;) I'll probably sleep better!
December 09, 2002 - 12:56 pm UTC
if dyn_table_name is different each time, you might as well not bind.
Soft/Hard Parse
A reader, December 12, 2002 - 1:46 pm UTC
This is on my earlier question of ref cursor parsing. In that sense how is ref cursor different from regular cursors. THis is with reference only to the context of parsing.
Can you please illustrate with examples.
regards
December 12, 2002 - 2:17 pm UTC
Ok, in order to show that ref cursors cannot be cached, all I need to do is open a "single" ref cursor twice. If we cached - there could only be ONE instance of it right? Well, the semantics of ref cursors do not prevent them from being opened as many times as the caller wants. So, consider:
ops$tkyte@ORA920> create or replace package demo_pkg
2 as
3 type rc is ref cursor;
4
5 procedure get_rc( p_cursor out rc );
6
7 procedure open_regular_cursor;
8 end;
9 /
Package created.
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace package body demo_pkg
2 as
3
4 cursor C is select * from DUAL;
5
6 procedure get_rc( p_cursor out rc )
7 is
8 begin
9 open p_cursor for select * from dual;
10 end;
11
12 procedure open_regular_cursor
13 is
14 begin
15 open C;
16 end;
17
18
19 end;
20 /
Package body created.
ops$tkyte@ORA920> show errors
No errors.
ops$tkyte@ORA920> pause
ops$tkyte@ORA920>
ops$tkyte@ORA920> variable x refcursor
ops$tkyte@ORA920> variable y refcursor
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec demo_pkg.get_rc( :x );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> exec demo_pkg.get_rc( :y );
PL/SQL procedure successfully completed.
<b>that "single" ref cursor is opened twice now -- but it isn't really that the ref cursor is opened, there are TWO separate cursor variables that have open result sets associated with them. PLSQL doesn't have a cursor to cache -- the client OWNS this cursor so plsql cannot cache them. We had to have 2 parses in the above -- one each for :x and :y</b>
ops$tkyte@ORA920>
ops$tkyte@ORA920> print x
D
-
X
ops$tkyte@ORA920> print y
D
-
X
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec demo_pkg.open_regular_cursor
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> exec demo_pkg.open_regular_cursor
BEGIN demo_pkg.open_regular_cursor; END;
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "OPS$TKYTE.DEMO_PKG", line 4
ORA-06512: at "OPS$TKYTE.DEMO_PKG", line 15
ORA-06512: at line 1
<b>that just shows why PLSQL can "cache" a cursor -- it owns them, there can only be one instance of it -- it is "well behaved"
Maybe this is even a better example:</b>
ps$tkyte@ORA920> create or replace package body demo_pkg
2 as
3
4 cursor C is select * from DUAL;
5
6 cnt number := 1;
7
8 procedure get_rc( p_cursor out rc )
9 is
10 begin
11 if ( cnt = 1 )
12 then
13 open p_cursor for select 'Hello', 55 from dual;
14 cnt := 2;
15 else
16 open p_cursor for select 'World', sysdate from dual;
17 end if;
18 end;
19
20 procedure open_regular_cursor
21 is
22 begin
23 open C;
24 end;
25
26
27 end;
28 /
Package body created.
ops$tkyte@ORA920> show errors
No errors.
ops$tkyte@ORA920> pause
ops$tkyte@ORA920>
ops$tkyte@ORA920> variable x refcursor
ops$tkyte@ORA920> variable y refcursor
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec demo_pkg.get_rc( :x );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> exec demo_pkg.get_rc( :y );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> print x
'HELL 55
----- ----------
Hello 55
ops$tkyte@ORA920> print y
'WORL SYSDATE
----- ---------
World 12-DEC-02
ops$tkyte@ORA920>
<b>how could PLSQL cache that? it doesn't even know what the cursor will be until someone askes for it and the code runs...</b>
Thanks
A reader, December 12, 2002 - 3:15 pm UTC
That was a very clear explanation.
Summing it up regular cursors would be cached but Ref Cursors cannot be cached because its value would not be known until runtime.
But, we can get around this by setting session_cached_cursors. Correct?
My other question and the most important question to me is specific to my application - Oracle Reports. Would the same issue of caching be there on ref cursor queries on reports also? Or will a regular query and ref cursor query have the same performance when taken in Oracle Reports.
Really appreciate your time and effort...
December 12, 2002 - 4:02 pm UTC
You can "help it" by using session cached cursors which is a softer "soft parse".
In reports - if you enable sql_trace and look at the parse/execute counts on your queries, you would be able to tell if you would benefit from session cached cursors. If parse > 1, you would most likely benefit from it.
session_cursor_max and open_cursor
A reader, May 17, 2003 - 9:58 pm UTC
sorry for my confusion.
If I have value set as 500 for open_cursor, is not that the cursor will persist for the session. Then why should we set session_cursor_max again?
Another silly question...
If procedure open the ref cursor for select * from emp;, will there be 2 cursor, one for ref_cur and other for 'select * from emp'
May 18, 2003 - 10:23 am UTC
what is "session cursor max"?
open cursor is the maximum number of concurrent open cursors you have. we allocate them 64 at a time (so no, it is not the number the "persist" for a session, it is a threshold, a maximum boundary value).
a ref cursor is a cursor. if you open a ref cursor for select * from emp there is ONE open cursor -- the ref cursor. the select * from emp, that is just text.
Sorry, I meant session_cached_cursor
A reader, May 18, 2003 - 12:08 pm UTC
May 18, 2003 - 1:08 pm UTC
that caches cursors you closed, for performance.
so, open cursors = max cursors you can have.
session cached cursors = how many we try to keep open when you prematurely closed them, for performance.
they are apples and oranges when comparing.
Is not parsing happens for tetxt
A reader, May 18, 2003 - 12:10 pm UTC
Tom
Is it not true that parsing happens for SQL text. So how ref cursor will be parsed ?
May 18, 2003 - 1:09 pm UTC
exactly like a "non ref cursor" would be.
I wish I could transport your inteliigence........
A reader, May 18, 2003 - 3:24 pm UTC
Tom, you are expert and I am not. I know everything is clear in your mind, but not mine.
Let me rephrase it.
There is a statement called ( p_ref is predifned ref cursor)
open p_ref for select * from emp;
so here we have a cursor (p_ref) and a SQL statement.
Now which one of the 2 is getting parsed.
My understanding (confusion) is that
a) SQL statement is parsed ( syntax/semantics checking, optmization, parse tree etc).
b) Cursor is handle for statement execution and does not need parsing.
I know if I get answer from anyone, it's you.
I am sorry for killing your valuable time, but hope that I am not only person who will be benefited by the answer.
May 18, 2003 - 6:26 pm UTC
the cursor is getting opened.
the statement that will be parsed is represented by the SQL you have "select * from emp"
the SQL gets parsed, the cursor points to the parsed representation.
Query
Peter, August 14, 2003 - 9:23 am UTC
so...
CREATE OR REPLACE PROCEDURE T1
(
vCursor OUT XX_RECORDSET.OUT_CURSOR
)
BEGIN
OPEN vCursor FOR
SELECT * FROM EMP;
END;
/
-------------------
CREATE OR REPLACE PROCEDURE T2
(
vCursor OUT XX_RECORDSET.OUT_CURSOR
)AS
vStmt varchar2(2000);
BEGIN
vStmt :="Select * from EMP";
OPEN vCursor FOR
vStmt;
END;
/
Both the procedure are same??.
Because I am facing some problem here.
I have a procedure like this
CREATE OR REPLACE PROCEDURE Test1
(
vCursor OUT XX_RECORDSET.OUT_CURSOR
)
vStmt varchar2(2000);
BEGIN
vStmt :="Select * from EMP";
IF Some_Condition THEN
vStmt := vStmt ||"Where EMPID =v_id"
END IF;
IF Some_Condition THEN
vStmt := vStmt ||"AND EMP_NAME =v_Name"
END IF;
IF Some_Condition THEN
vStmt := vStmt ||"AND MGR=v_Mgr"
END IF;
OPEN vCursor FOR
vStmt;
END;
This is working fine.
But I don't like to use dynamic SQL.
So I have rewritten this procedure like this
CREATE OR REPLACE PROCEDURE Test2
(
vCursor ........
)
BEGIN
IF Some_Condition THEN
OPEN vCursor FOR
Select * from EMP Where EMPID =v_id;
ELSIF Some_Condition THEN
OPEN vCursor FOR
Select * from EMP Where EMPID =v_id
AND EMP_NAME =v_Name;
ELSIF Some_Condition THEN
OPEN vCursor FOR
Select * from EMP Where EMPID =v_id
AND EMP_NAME =v_Nam
AND MGR=v_Mgr;
END IF;
END;
logically both are giving same result.No problem. But I would like to know about the efficienty point of view.
Actually my question is in the procedure Test1, I have only one "Open vCursor For Selec..." statement. But
in the Test2 procedure I have 3 "Open vCursor FOR Select ..." statement. According to your previous
explanation, soft/hard parse will be done. So in the procedure Test2 it has to parse 3 sql statement.
But in the Test1 procedure the parsing will be done for one SQL statement. So which way will be usefull?. Here
I have just given these procedures for example. But in my real application I have used this same logic.
So I end up with 12 SQL statements.
So please suggest me, which approach I should follow?
Thanks in advance
Peter
August 14, 2003 - 10:12 am UTC
static sql is always preferred for
o performance
o maintanence
o realiability
in plsql. procedure test2 will only parse THE single statement you request.
Dynamic no impact?
Jon, August 28, 2003 - 12:44 am UTC
Since ref cursors are parsed each time, is there actually any difference between using static sql and dynamic sql when associate with a ref cursor (assuming no binding required). My testing seems to indiciate there is no significant difference:
declare
type refcursor is ref cursor;
rc refcursor;
r_client client%rowtype;
begin
execute immediate 'truncate table run_stats';
commit;
insert into run_stats select 'before', stats.* from stats;
for x in 1..100 loop
open rc for 'select * from client where client_id = 711345';
loop
fetch rc into r_client;
exit when rc%notfound;
end loop;
close rc;
end loop;
insert into run_stats select 'after 1', stats.* from stats;
for x in 1..100 loop
open rc for select * from client where client_id = 711345;
loop
fetch rc into r_client;
exit when rc%notfound;
end loop;
close rc;
end loop;
insert into run_stats select 'after 2', stats.* from stats;
commit;
end;
/
select * from report_stats;
Run 1 Run 2 Diff
------------------------------------------------------------
STAT redo size 20900 20984 84
LATCH cache buffers chains 562 586 24
LATCH shared pool 275 266 9
LATCH redo allocation 13 21 8
STAT db block gets 26 19 7
STAT session logical reads 437 430 7
LATCH library cache 458 464 6
STAT db block changes 27 22 5
LATCH checkpoint queue latch 2 6 4
STAT redo entries 16 13 3
STAT enqueue requests 5 3 2
STAT calls to get snapshot scn: kcmgss 106 105 1
STAT parse time cpu 3 2 1
STAT recursive cpu usage 16 17 1
STAT free buffer requested 4 3 1
STAT enqueue releases 4 3 1
August 28, 2003 - 7:46 am UTC
you lose the dependencies....
you lose the assurance that your SQL is correct....
if you can do it statically, do it.
session_cached_cursors
Charlie, May 09, 2004 - 12:08 pm UTC
If I set a value for this parameter, does oracle actually keep the cursors asssociated with my session SQL/PL SQL statements in the UGA not in the Shared pool? Could you explain the difference between setting this parameter to a value and zero? Thanks.
May 10, 2004 - 7:49 am UTC
it sets up for a softer soft parse. It keeps a soft link to the parsed stuff in the sga. it needs to still confirm that all is OK with the stuff in the sga (hence it is still a parse, still latches). It consumes some uga memory in order to accomplish this and manage the 'cache cursors' it has.
if you set this to 100, we'll attempt to keep a soft link to your 100 most frequently re-executed statements.
set to 0 and we won't.
it is a softer soft parse -- but remember, the best parse is NO parse at all.
ref cursor
A reader, June 23, 2004 - 5:25 pm UTC
Tom,
I have a questions about ref cursor. I have to write a procedure that will accept ref cursor as parameter and will create csv files with column names in it. This procedure can be exceuted from sevral different programs(means different ref cursors can be passed).
Could you Pl show me an example of how to do it?
Thanks Tom for your help and this great site for us.
June 24, 2004 - 8:30 am UTC
you cannot do it -- you have to use DBMS_SQL.
with dbms_sql you can "describe" a query and figure out the number of columns, their datatypes, their lengths and their names.
with a ref cursor you have to know in plsql at compile time how many columns, their datatypes, their lengths and so on.
</code>
http://asktom.oracle.com/~tkyte/flat/index.html <code>
has a link to the plsql code to do this already. the caller just sends you a query in a string or you could change the API to take a dbms_sql cursor instead.
Ref Cursor With Bulk Collect
ARC, August 11, 2004 - 12:30 pm UTC
Hi,
Need your help in solving my problem.
I am using Oracle version
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production.
I am writing procedure with a table name as a parameter.
In procedure I am bulding query string. Using this query string I am opening a cursor which is defined as ref cursor type.
Cursor is opening fine. I mean statement is parsing fine. The problem is at time of fetching data. I am using bulk collect to fectch data. It is giving below error.
GEFDWDEV>EXEC SP_COM401_ASSIGN_BUS_TIERS('TB_SDW023_STG_SALES',:PS,:PR)
BEGIN SP_COM401_ASSIGN_BUS_TIERS('TB_SDW023_STG_SALES',:PS,:PR); END;
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "DWADMIN.SP_COM401_ASSIGN_BUS_TIERS", line 55
ORA-06512: at line 1
But the same thing is worked fine with single record. Also I used the same above in another procedure working fine. The diff. is in the procedure which is working fine the statement is not building dynamically.
Also I teird with EXECUTE IMMEDIATE <QUERY STRING> BULK COLLECT INTO < >;
gETTING below error.
Warning: Procedure created with compilation errors.
Elapsed: 00:00:00.12
GEFDWDEV>SHO ERR
Errors for PROCEDURE SP_COM401_ASSIGN_BUS_TIERS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
39/27 PLS-00103: Encountered the symbol "BULK" when expecting one of
the following:
. ( * @ % & = - + ; < / > at in mod not rem return returning
<an exponent (**)> <> or != or ~= >= <= <> and or like
between into using is null is not || is dangling.
Here I am giving my code.
CREATE OR REPLACE PROCEDURE SP_COM401_ASSIGN_BUS_TIERS (P_TABLE_NAME_I IN VARCHAR2, P_STATUS_O OUT NUMBER, P_RESULT_O OUT VARCHAR2)
AS
TYPE TYP_REFCURSOR IS REF CURSOR;
L_CURSOR1 TYP_REFCURSOR ;
/*******************************************************************************
* Declaration of object types for each field in the table to be updated
********************************************************************************/
TYPE TYP_BUS_TIER0 IS TABLE OF TB_SDW023_STG_SALES.BUS_TEAM_TIER0_DSC%TYPE INDEX BY BINARY_INTEGER;
TYPE TYP_BUS_TIER1 IS TABLE OF TB_SDW023_STG_SALES.BUS_TEAM_TIER1_DSC%TYPE INDEX BY BINARY_INTEGER;
TYPE TYP_BUS_TIER2 IS TABLE OF TB_SDW023_STG_SALES.BUS_TEAM_TIER2_DSC%TYPE INDEX BY BINARY_INTEGER;
TYPE TYP_BUS_TIER3 IS TABLE OF TB_SDW023_STG_SALES.BUS_TEAM_TIER3_DSC%TYPE INDEX BY BINARY_INTEGER;
TYPE TYP_BUS_DIM_KEY IS TABLE OF TB_SDW012_DIM_BUS_TIERS.BUS_DIM_KEY%TYPE INDEX BY BINARY_INTEGER;
TYPE TYP_PROD_PS_FLG IS TABLE OF TB_SDW012_DIM_BUS_TIERS.PRODUCT_SERVICE_PS_FLG%TYPE INDEX BY BINARY_INTEGER;
TYPE TYP_ROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
TYPE TYP_LOG IS TABLE OF TB_SDW023_STG_SALES.PROCESS_LOG_DSC%TYPE INDEX BY BINARY_INTEGER;
/*******************************************************************************
* Declaration of binary integer tables for each field in the table to be updated
********************************************************************************/
FLD_BUS_TIER0 TYP_BUS_TIER0;
FLD_BUS_TIER1 TYP_BUS_TIER1;
FLD_BUS_TIER2 TYP_BUS_TIER2;
FLD_BUS_TIER3 TYP_BUS_TIER2;
FLD_BUS_DIM_KEY TYP_BUS_DIM_KEY;
FLD_PROD_PS_FLG TYP_PROD_PS_FLG;
FLD_ROWID TYP_ROWID;
FLD_LOG TYP_LOG;
V_STMT VARCHAR2(2000);
BEGIN
DBMS_OUTPUT.ENABLE(100000);
V_STMT := 'SELECT A.BUS_DIM_KEY BUS_DIM_KEY, A.BUS_TEAM_TIER0_DSC BUS_TEAM_TIER0_DSC, '||CHR(10)||'A.BUS_TEAM_TIER1_DSC BUS_TEAM_TIER1_DSC, A.BUS_TEAM_TIER2_DSC BUS_TEAM_TIER2_DSC,'||CHR(10)||'A.BUS_TEAM_TIER3_DSC BUS_TEAM_TIER3_DSC, A.PRODUCT_SERVICE_PS_FLG PRODUCT_SERVICE_PS_FLG,'||CHR(10)||'C.ROWID ROW_ID'||CHR(10);
V_STMT := V_STMT || 'FROM '|| P_TABLE_NAME_I ||' C, TB_COM003_DIM_BUS_HIER A,'||CHR(10)||'TB_SDW005_MAP_SUBPRDLN_BUSTIER B'||CHR(10);
V_STMT := V_STMT || 'WHERE (C.SUB_PROD_LN_CD||NVL(C.DISTRIBUTION_CHANNEL_CD, '||'''#'''||')||NVL(C.CUST_GRP_CD, '||'''#'''||')) = (B.SUB_PROD_LN_CD||NVL(B.DISTRIBUTION_CHANNEL_CD, '||'''#'''||')||NVL(B.CUST_GRP_CD, '||'''#'''||'))'||CHR(10);
V_STMT := V_STMT || 'AND (B.BUS_TEAM_TIER1_DSC||B.BUS_TEAM_TIER2_DSC||B.BUS_TEAM_TIER3_DSC) = (A.BUS_TEAM_TIER1_DSC||A.BUS_TEAM_TIER2_DSC||A.BUS_TEAM_TIER3_DSC)';
-- DELETE T_DUMMY;
-- INSERT INTO T_DUMMY VALUES(V_STMT);
-- COMMIT;
--DBMS_OUTPUT.PUT_LINE('1');
-- OPEN L_CURSOR1 FOR V_STMT;
--DBMS_OUTPUT.PUT_LINE('2');
/* SELECT A.BUS_DIM_KEY BUS_DIM_KEY, A.BUS_TEAM_TIER0_DSC BUS_TEAM_TIER0_DSC, A.BUS_TEAM_TIER1_DSC BUS_TEAM_TIER1_DSC, A.BUS_TEAM_TIER2_DSC BUS_TEAM_TIER2_DSC, A.BUS_TEAM_TIER3_DSC BUS_TEAM_TIER3_DSC, A.PRODUCT_SERVICE_PS_FLG PRODUCT_SERVICE_PS_FLG, C.ROWID ROW_ID
FROM P_TABLE_NAME_I C,
TB_COM003_DIM_BUS_HIER A,
TB_SDW005_MAP_SUBPRDLN_BUSTIER B
WHERE (C.SUB_PROD_LN_CD||NVL(C.DISTRIBUTION_CHANNEL_CD,'#')||NVL(C.CUST_GRP_CD,'#')) = (B.SUB_PROD_LN_CD||NVL(B.DISTRIBUTION_CHANNEL_CD,'#')||NVL(B.CUST_GRP_CD,'#'))
AND (B.BUS_TEAM_TIER1_DSC||B.BUS_TEAM_TIER2_DSC||B.BUS_TEAM_TIER3_DSC) = (A.BUS_TEAM_TIER1_DSC||A.BUS_TEAM_TIER2_DSC||A.BUS_TEAM_TIER3_DSC);
*/
-- if l_cursor1%isopen then
-- DBMS_OUTPUT.PUT_LINE('3');
-- else
-- DBMS_OUTPUT.PUT_LINE('4');
-- end if;
/* LOOP
FETCH L_CURSOR1 BULK COLLECT INTO
FLD_BUS_DIM_KEY,
FLD_BUS_TIER0,
FLD_BUS_TIER1,
FLD_BUS_TIER2,
FLD_BUS_TIER3,
FLD_PROD_PS_FLG,
FLD_ROWID LIMIT 1000;
exit;
*/
EXECUTE IMMEDIATE V_STMT BULK COLLECT INTO
FLD_BUS_DIM_KEY,
FLD_BUS_TIER0,
FLD_BUS_TIER1,
FLD_BUS_TIER2,
FLD_BUS_TIER3,
FLD_PROD_PS_FLG,
FLD_ROWID LIMIT 1000;
FORALL I IN 1..FLD_ROWID.COUNT
UPDATE TB_SDW023_STG_SALES
SET BUS_DIM_KEY = FLD_BUS_DIM_KEY(I),
BUS_TEAM_TIER0_DSC = FLD_BUS_TIER0(I),
BUS_TEAM_TIER1_DSC = FLD_BUS_TIER1(I),
BUS_TEAM_TIER2_DSC = FLD_BUS_TIER2(I),
BUS_TEAM_TIER3_DSC = FLD_BUS_TIER3(I),
PRODUCT_SERVICE_PS_FLG = FLD_PROD_PS_FLG(I)
WHERE ROWID = FLD_ROWID(I);
COMMIT;
-- EXIT WHEN L_CURSOR1%NOTFOUND;
-- END LOOP;
-- CLOSE L_CURSOR1;
COMMIT;
P_STATUS_O := 0;
P_RESULT_O := 'SUCCESS';
/*EXCEPTION
WHEN OTHERS THEN
HANDLE_ERROR('SP_COM401_ASSIGN_BUS_TIERS'); */
END SP_COM401_ASSIGN_BUS_TIERS;
/
Please help me.
Thanking you in advance.
ARC
August 11, 2004 - 1:51 pm UTC
bulk collect from a dynamically opened ref cursor did not work in 8i, was not supported:
ops$tkyte@ORA817DEV> declare
2 type rc is ref cursor;
3 c rc;
4 type array is table of number index by binary_integer;
5 d array;
6 begin
7 open c for 'select 1 x from dual';
8 fetch c bulk collect into d;
9 end;
10 /
declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 8
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
2 type rc is ref cursor;
3 c rc;
4 type array is table of number index by binary_integer;
5 d array;
6 begin
7 open c for select 1 x from dual;
8 fetch c bulk collect into d;
9 end;
10 /
PL/SQL procedure successfully completed.
in 9i:
ops$tkyte@ORA920> declare
2 type rc is ref cursor;
3 c rc;
4 type array is table of number index by binary_integer;
5 d array;
6 begin
7 open c for 'select 1 x from dual';
8 fetch c bulk collect into d;
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 type rc is ref cursor;
3 c rc;
4 type array is table of number index by binary_integer;
5 d array;
6 begin
7 open c for select 1 x from dual;
8 fetch c bulk collect into d;
9 end;
10 /
PL/SQL procedure successfully completed.
Ref Cursor with Bulk Collect
ARC, August 12, 2004 - 6:58 am UTC
Tom,
Thanks for your feedback.
Could you please suggest me how can I code this.
Really I want to use bulk option in my process.
Thanks
ARC
August 12, 2004 - 9:24 am UTC
are you sure you even need to use procedural code.
when I see:
loop
fetch bulk collect;
forall i update
I'm thinking "should be a single sql statement" -- just update.
Ref Cursor with bulk collect
ARC, August 14, 2004 - 8:28 am UTC
Tom,
Really I did not get you.
Could you please explain me in detail.
Thank You.
ARC
August 14, 2004 - 1:36 pm UTC
update ( join the data here )
set x = y;
don't
open c for select * from t;
loop
fetch c into ....
update t2 set ....
just
update ( select t.x, t2.y
from t, t2
where ... )
set x = y;
Ref Cursor
ARC, August 16, 2004 - 5:45 am UTC
Tom,
Thanks for your feedback.
I am trying update as you mentioned. But I am getting following error.
GEFDWDEV>/
STG_BUS_DIM_KEY = DIM_BUS_DIM_KEY
*
ERROR at line 22:
ORA-01779: cannot modify a column which maps to a non key-preserved table
I below statement TABLE TB_SDW023_STG_SALES is detail table for other two tables in query.
The column TB_SDW023_STG_SALES.BUS_DIM_KEY is foreign key with reference to TB_COM003_DIM_BUS_HIER.BUS_DIM_KEY and also the combination of TB_SDW023_STG_SALES.SUB_PROD_LN_CD, TB_SDW023_STG_SALES.DISTRIBUTION_CHANNEL_CD, TB_SDW023_STG_SALES.CUST_GRP_CD is foreign key with combinational unique key on TB_SDW005_MAP_SUBPRDLN_BUSTIER with columns SUB_PROD_LN_CD, DISTRIBUTION_CHANNEL_CD, CUST_GRP_CD
Update Statement:
UPDATE
(
SELECT A.BUS_DIM_KEY DIM_BUS_DIM_KEY,
A.BUS_TEAM_TIER0_DSC DIM_BUS_TEAM_TIER0_DSC,
A.BUS_TEAM_TIER1_DSC DIM_BUS_TEAM_TIER1_DSC,
A.BUS_TEAM_TIER2_DSC DIM_BUS_TEAM_TIER2_DSC,
A.BUS_TEAM_TIER3_DSC DIM_BUS_TEAM_TIER3_DSC,
A.PRODUCT_SERVICE_PS_FLG DIM_PRODUCT_SERVICE_PS_FLG,
C.BUS_DIM_KEY STG_BUS_DIM_KEY,
C.BUS_TEAM_TIER0_DSC STG_BUS_TEAM_TIER0_DSC,
C.BUS_TEAM_TIER1_DSC STG_BUS_TEAM_TIER1_DSC,
C.BUS_TEAM_TIER2_DSC STG_BUS_TEAM_TIER2_DSC,
C.BUS_TEAM_TIER3_DSC STG_BUS_TEAM_TIER3_DSC,
C.PRODUCT_SERVICE_PS_FLG STG_PRODUCT_SERVICE_PS_FLG
FROM TB_SDW023_STG_SALES C,
TB_COM003_DIM_BUS_HIER A,
TB_SDW005_MAP_SUBPRDLN_BUSTIER B
WHERE (C.SUB_PROD_LN_CD||NVL(C.DISTRIBUTION_CHANNEL_CD,'#')||NVL(C.CUST_GRP_CD,'#')) = (B.SUB_PROD_LN_CD||NVL(B.DISTRIBUTION_CHANNEL_CD,'#')||NVL(B.CUST_GRP_CD,'#'))
AND (B.BUS_TEAM_TIER1_DSC||B.BUS_TEAM_TIER2_DSC||B.BUS_TEAM_TIER3_DSC) = (A.BUS_TEAM_TIER1_DSC||A.BUS_TEAM_TIER2_DSC||A.BUS_TEAM_TIER3_DSC)
)
SET
STG_BUS_DIM_KEY = DIM_BUS_DIM_KEY,
STG_BUS_TEAM_TIER0_DSC = DIM_BUS_TEAM_TIER0_DSC,
STG_BUS_TEAM_TIER1_DSC = DIM_BUS_TEAM_TIER1_DSC,
STG_BUS_TEAM_TIER2_DSC = DIM_BUS_TEAM_TIER2_DSC,
STG_BUS_TEAM_TIER3_DSC = DIM_BUS_TEAM_TIER3_DSC,
STG_PRODUCT_SERVICE_PS_FLG = DIM_PRODUCT_SERVICE_PS_FLG
/
Why I am getting the error as my staging table TB_SDW023_STG_SALES is a key-preserved table ?
Thanks
ARC
August 16, 2004 - 8:31 am UTC
you need primary keys (unique constraints) in place.
if you get this warning -- it is saying "hey, many rows could match here updating STG_BUS_DIM_KEY many times. that would result in ambigous non-reproducible updates.
it is all of your functions in there to join, the database has *no clue* that anything is even remotely "safe" here.
God, i was stupid :)
Toms, January 21, 2005 - 2:37 pm UTC
I was trying for about 1.5 hours to get ref cursor work with bulk collect in 8.1.7
Then i read this question, and when i came to place that "if i see fetch x bulk collect in y; and then forall" - i understood that that exactly was my case.
Not all variables bound
Kanth, May 09, 2005 - 4:23 pm UTC
Tom,
I am using the following code to fetch values from a ref cursor, by building a Dynamic sql (ofcourse binding the values).
But i'm getting Not all variables bound message
-- Here is the code
declare
v_sql varchar2(2000):='Select emp.empno,emp.sal,emp.deptno,dept.loc,dept.dname from emp ,dept ';
v_where varchar2(200):='Where ';
p_empno integer:=1234;
p_ref sys_refcursor;
p_deptno integer:=10;
v_column dbms_sql.varchar2_table;
v_values varchar2(100):='';
begin
v_where:=v_where||'emp.deptno=dept.deptno and emp.empno=:empno and dept.deptno=:dno ';
v_sql:=v_sql||v_where ;
dbms_output.put_line(v_sql);
v_column(1):=p_empno;
v_column(2):=p_deptno;
for i in 1..v_column.count
loop
v_values:=v_values||','||v_column(i);
end loop;
v_values:=ltrim(v_values,',');
open p_ref for v_sql using v_values ;
end;
--End of the code
I'm guessing v_values is the culprit here. Is there any way that i can append values from my array into a value and use it for using clause?
Thanks,
Here are the tables and data
Kanth, May 09, 2005 - 4:56 pm UTC
CREATE TABLE DEPT
(
DEPTNO INTEGER,
DNAME VARCHAR2(20 BYTE),
LOC VARCHAR2(30 BYTE)
)
;
CREATE TABLE EMP
(
EMPNO INTEGER,
SAL INTEGER,
DEPTNO INTEGER
);
INSERT INTO EMP ( EMPNO, SAL, DEPTNO ) VALUES (
1234, 2345, 10);
INSERT INTO EMP ( EMPNO, SAL, DEPTNO ) VALUES (
2345, 4501, 10);
INSERT INTO EMP ( EMPNO, SAL, DEPTNO ) VALUES (
3456, 4501, 20);
INSERT INTO EMP ( EMPNO, SAL, DEPTNO ) VALUES (
4567, 3456, 20);
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
10, 'Accounting', 'Frankfort');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
20, 'Human Resources', 'Frankfort');
COMMIT;
But..V_values is not an array
Kanth, May 10, 2005 - 11:27 am UTC
Tom, Thanks for your reply. V_values, i was using , is a varchar and i'm appending values to it from array.
I could use array directly in "Using array(1),
array(2)" ..but array elements are dynamically built and could change for each call to this proc.
So how do i use them in using clause if i don't know the no. of array elements?
Thanks for all your help
May 10, 2005 - 1:27 pm UTC
did you see the link I gave you? it was exactly in anticipation of this question.
Is there any other way
Kanth, May 11, 2005 - 11:39 am UTC
I have read your link talking about application context, sounds good, but to implement that i've to rewrite my entire logic and i was hoping not to take that route (atleast for now).
Do you think there is any other way to pass array values in using clause ?
when i append all the values to a string (comma seperated) and use that in my query like
open p_ref for vqry using v_using;
where v_using has values like 1,1,20,12
I think it doesn't work because v_values is like one big string rather than values. is my thinking right?
If so , how do i seperate them as values
Thanks
May 11, 2005 - 12:26 pm UTC
why would you have to rewrite the entire application logic? you are asking how to do a piece of code you haven't written yet.
Application Context issue..
Kanth, May 23, 2005 - 11:22 am UTC
Tom,
I have used Application Context approach as you showed earlier in this thread. It works great. I have to repeat this for different procedures . This is a search function, users will be using from front end, and we are expecting there will be atleast 20 or 30 more search functions that needs to be implemented in our application. Will this cause any performance issues if i create 30 contexts and use them seperately (one for each procedure)?
Thanks,
May 23, 2005 - 3:30 pm UTC
well, it'll be up to you whether you want 1 or 30 contexts, I've not observed any sort of scaling issues with a single or multiple namespaces.
A reader, May 24, 2005 - 9:48 am UTC
I am trying to use distinct clause in the select statement; but it is throwing error. Could you please give me the right approach? Here is my code:
CREATE OR REPLACE
TYPE MY_REC AS OBJECT
(
fam_name VARCHAR2(35),
given_name VARCHAR2(15),
status_code VARCHAR2(50)
);
CREATE OR REPLACE
TYPE MY_TAB
AS VARRAY (150) OF MY_REC;
CREATE OR REPLACE CONTEXT C1 USING TEST;
PROCEDURE TEST(I_FNAME IN VARCHAR2,
I_LNAME IN VARCHAR2,
O_REC OUT MY_TAB)
AS
L_QRY VARCHAR2(3000) DEFAULT
-----TRIED 'DISTINCT' BEFORE 'MY_REC' AS WELL AS
---FNAME
'SELECT MY_REC(FNAME,LNAME,STATUS) FROM T1,T2 WHERE ' ||
'T1.COL1=T2.COL2';
BEGIN
DBMS_SESSION.set_context (C1', 'lname', i_lname);
l_qry := l_qry || ' and fname = sys_context( ''C1'', ''lname'' ) ';
END;
Thanks.
May 24, 2005 - 1:06 pm UTC
unless your object is "orderable" -- has a map or order method -- there is no concept of ordering for them and distinct needs that:
*
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method
You need to teach us how to "sort" your object first.
but quick fix would be:
select my_rec(fname,lname,status)
from (select distinct fname,lname,status
from t1,t2
where ........ )
A reader, May 24, 2005 - 1:21 pm UTC
Thanks for your response Tom!
I have tried the quick fix you suggested, but my idea is to append the conditional clauses at the end (when user decides to filter by a particular column)
In the quick fix you suggested, how can i append the conditions dynamically using session context?
Hope I have explained it clearly.
May 24, 2005 - 1:52 pm UTC
not sure why it matters if you put them in the middle or at the end:
'select mytable(a,b,c)
from (select distinct a,b,c
from t ' || whatever || '
)'
why do you need to add to the end? it is just as easy to add your where clause and a closing ")"
A reader, May 24, 2005 - 2:45 pm UTC
Once again thanks for your time.
My concern is:
'select mytable(a,b,c)
from (select distinct a,b,c
from t ' || whatever || '
)'
in the above query we have closed the inner query with ')'.
so the session context will include it within that closing ')' or will it append outside the ')'?
for eg. will it become something like this, iin which case it would be wrong:
'select mytable(a,b,c)
from (select distinct a,b,c
from t ' || whatever || '
)
and lname = ...
and fname = ....'
(as there won't be a 'where' clause in the outer query, this will be wrong right?)
May 24, 2005 - 4:03 pm UTC
i'm saying you glue in your predicate AND THEN close the query with the ")"
you own the query don't you??
without a map or order method, you will not distinct an object type.
40 queries per second system -- SELECT, Ref Cursor or PipelineTable Function
Charlie Zhu, May 12, 2006 - 6:57 pm UTC
We got a 20 million rows single hash cluster table,
frequently queried by IN-List equal look-up.
30 concurrent users, totally run 40 times per second.
What do you recommend?
JDBC PrepareStatement SELECT, Ref Cursor or Pipeline Table Function ?
<Quote>
Followup:
I would use ref cursors and session cached cursors
OR (after benchmarking) letting the java programs:
select * from TABLE( your_plsql_pipelined_function );
<Quote End>
Ref Cursor is a little better than Table Function, 135:154 = 87% run time,
both slower than SELECT, 118 hsec.
== == ===
Here is the bench mark in single user mode
1) REF CURSOR vs. SELECT
Declare
l_string varchar2(4000);
c_sp ABELISTING.sp_sql_query.listidcurtyp;
--c_sp SYS_REFCURSOR;
l_i Number(20);
Begin
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
/* For c in( select column_value listingsid from table(cast(abelisting.tab_f(l_string) as nt_type)))
Loop
l_i := c.listingsid;
End loop;
*/
ABELISTING.sp_sql_query.list_df(c_sp,l_string );
while (True)
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(
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 l_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)
)
Loop
l_i := c.listingsid;
End loop;
End Loop;
runStats_pkg.rs_stop;
End;
/
rollback;
Run1 ran in 135 hsecs
Run2 ran in 116 hsecs
run 1 ran in 116.38% of the run2 time
Name Run1 Run2 Diff
STAT...consistent gets 60,011 60,012 1
STAT...parse time cpu 1 0 -1
STAT...parse time elapsed 1 0 -1
STAT...Elapsed Time 137 118 -19
STAT...recursive cpu usage 133 113 -20
LATCH.cache buffers chains 120,278 120,256 -22
...
STAT...session cursor cache hi 1,999 0 -1,999
STAT...parse count (total) 2,001 2 -1,999
STAT...recursive calls 4,003 2,003 -2,000
LATCH.row cache objects 12,014 12 -12,002
STAT...session uga memory 65,464 0 -65,464
STAT...session pga memory 65,536 0 -65,536
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
142,417 130,485 -11,932 109.14%
PL/SQL procedure successfully completed.
2) PIPELINE TABLE FUNCTION vs. SELECT
Run1 ran in 154 hsecs
Run2 ran in 118 hsecs
run 1 ran in 130.51% of the run2 time
STAT...recursive calls 4,049 2,003 -2,046
LATCH.row cache objects 6,254 12 -6,242
LATCH.library cache pin 12,138 4,012 -8,126
LATCH.shared pool 12,125 2,005 -10,120
LATCH.library cache 14,273 4,018 -10,255
STAT...session pga memory max 196,608 131,072 -65,536
STAT...session uga memory max 196,500 127,460 -69,040
STAT...session uga memory 130,928 0 -130,928
STAT...session pga memory 131,072 0 -131,072
Run1 latches total versus Run2 -- difference and pct
Run1 Run2 Diff Pct
165,633 130,485 -35,148 126.94%
PL/SQL procedure successfully completed.
One more fact -- it's calling SELECT in JDBC
Charlie Zhu, May 12, 2006 - 7:07 pm UTC
import java.sql.*;
import oracle.jdbc.OracleTypes;
//Reture SELECT rowset/ResultSet by Oracle PL/SQL store procedure
//<quote>
//I firmly believe the best java programs are those that have ZERO "selects/inserts/updates/deletes" in them.
//Hence, using ref cursors is the way to go. Lets you tune without bothering those java programmers.
//</quote>
//there are many advantages of doing so apart from having interfaces and access controlled -- something really convincing "those java programmers"?
//Followup:
//tell them "you will not have to write sql" :)
//sorry, but the reason is one of encapsulation, dependency tracking (who uses what), tuning....
public class JDBC_SP_list_df {
private static Connection conn1 = null, conn2 = null;
public static void main (String[] args) throws Exception {
initInet();
//initOracle();
Statement sql_trace1 = conn1.createStatement();
sql_trace1.execute( "begin dbms_monitor.session_trace_enable( WAITS=>TRUE, binds=>true); end;");
JDBC_SP_list_df app = new JDBC_SP_list_df();
//app.sub_SELECT();
int i=0;
while ( i < 200 )
{
app.sub_SELECT();
app.sub_StoreProcedure();
app.sub_table_function();
i = i+1;
}
conn1.close();
//conn2.close();
}
private static void initInet() throws Exception {
Class.forName("com.inet.ora.OraDriver");
conn1 = DriverManager.getConnection("jdbc:inetora:testdb:1521:dev", "scott", "tiger");
}
private static void initOracle() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn1 = DriverManager.getConnection("jdbc:oracle:thin:@testdb:1521:dev", "scott", "tiger");
conn1.setAutoCommit(false);
}
//abe usage
private void sub_SELECT() throws Exception {
String list_ids = "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";
String ls_sql = "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 ? l_str from dual))" +
" CONNECT BY level <= length(l_str)-length(replace(l_str,',',''))+1" +
" )" +
" select a1.listingsid listingsid" +
" from abelisting.book_del_check a1, sq" +
" where a1.listingsid = To_Number(sq.token)";
PreparedStatement pstat = conn1.prepareStatement(ls_sql);
pstat.setString(1, list_ids);
ResultSet rset = pstat.executeQuery();
int li_listingsid=0;
while ( rset.next() ) {
li_listingsid = rset.getInt("listingsid");
}
//System.out.println(li_listingsid + ":select: ");
rset.close();
//pstat.close();
}
//better usage
private void sub_StoreProcedure() throws Exception {
String list_ids = "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";
String query = "begin ABELISTING.sp_sql_query.list_df(?,?); end;";
CallableStatement stmt = conn1.prepareCall(query);
// register the type of the out param - an Oracle specific type
stmt.registerOutParameter(1, OracleTypes.CURSOR);
// set the in param
stmt.setString(2, list_ids);
// execute and retrieve the result set
stmt.execute();
ResultSet rset = (ResultSet)stmt.getObject(1);
int listingsid=0;
while ( rset.next() ) {
listingsid = rset.getInt("listingsid");
}
//System.out.println(listingsid + ":^_^: ");
stmt.close();
}
//Pipeline rows from PL/SQL Table Function
private void sub_table_function() throws Exception {
String list_ids = "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";
PreparedStatement pstat = conn1.prepareStatement("select * from table(cast(abelisting.tab_f(?) as nt_type))");
pstat.setString(1, list_ids);
ResultSet rset = pstat.executeQuery();
int listingsid=0;
while ( rset.next() ) {
listingsid = rset.getInt(1);
//System.out.println(listingsid + ":-:");
}
rset.close();
//pstat.close();
}
}
I got answer on another QA
Charlie Zhu, May 17, 2006 - 7:20 pm UTC
Looking for a diffrent approach..
A reader, September 13, 2006 - 2:50 pm UTC
Tom,
This is an interesting thread. Our environment is an oltp one. I am looking to minimized hits by cutting down on ref cursor. Here is one of them. Is there any way I could re-write this one so I don't have to parse so many times? Please advice.
PROCEDURE process_main( main_REC_in IN mon_rec_TYPE
,student_in IN VARCHAR2
,school_in IN VARCHAR2 DEFAULT NULL
)
IS
TYPE ref_cur IS REF CURSOR;
code_cur ref_cur;
code_REC code_REC_TYPE := NULL;
BEGIN
OPEN code_cur FOR 'SELECT * FROM main_sessions WHERE stud_code = :1'
USING mon_REC_in.main_code;
FETCH code_cur INTO code_REC;
CLOSE code_cur;
IF code_rec.Mon = 'Y'
THEN
write_mon_PROC(mon_REC_in,student_in);
END IF;
IF main_REC.Email = 'Y'
THEN
send_message_proc(main_REC_in,student_in,message_in);
END IF;
EXCEPTION
WHEN OTHERS THEN
IF code_cur%ISOPEN
THEN
CLOSE code_cur;
END IF;
RAISE_APPLICATION_ERROR(-20008,SQLERRM);
END process_main;
September 13, 2006 - 3:22 pm UTC
why are you using a ref cursor at all here?
that should be a select into, nothing else.
ugh, raise_application_error, why not just RAISE??? why hide the actuall error code from clients that might want to know.
Simple code vs. scalability
Boris, November 02, 2006 - 1:10 pm UTC
Hello Tom,
I really enjoy reading threads on asktom.oracle.com. I guess, I'm not the only one. Unfortunately, I did not find answer for one particular question. We have application that gets dozen of millon hits per day to one particular procedure. For the sake of code simplicity we open ref cursor for one or another SELECT based on incoming parameters. We experience great wait time on "Latch: library cache".
Here is a mock-up version of our procedure:
CREATE OR REPLACE PROCEDURE count_people
(is_loc IN dept.loc%TYPE,
is_flag IN VARCHAR2,
on_qnt OUT NUMBER)
IS
TYPE ref_cur IS REF CURSOR;
vc_cur ref_cur;
vn_deptno dept.deptno%TYPE;
vn_n PLS_INTEGER;
BEGIN
IF is_flag = 'ALL' THEN
OPEN vc_cur FOR SELECT deptno FROM dept;
ELSE
OPEN vc_cur FOR SELECT deptno FROM dept WHERE loc = is_loc;
END IF;
on_qnt := 0;
LOOP
FETCH vc_cur INTO vn_deptno;
SELECT COUNT(*) INTO vn_n FROM emp WHERE deptno = vn_deptno;
on_qnt := on_qnt + vn_n;
END LOOP;
CLOSE vc_cur;
END count_people;
Please note that instead of SELECT COUNT(*)... we open another ref cursor based on another parameter to go through details (eg., pick people with salary over some limit / or pick people with same manager) and do some additional checks. In short, our procedure serves 4 different purposes.
If this could help: we use Oracle 10.2.0.2
The actual question is:
1) For sake of performance / scalability should we rewrite this code by using 4 other procedures based on incoming parameters and use explicit cursors only?
2) Is there any good way to substiture REF CURSOR with explicit cursor without:
a) IF flag='ALL' THEN OPEN vc_ref_all; ELSE OPEN vc_ref; END IF; ....and same for FETCH and CLOSE
Your help is greatly appreciated.
Thank you very much,
Boris
November 02, 2006 - 7:31 pm UTC
1) likely would help, plsql would cache the open cursors - ref cursors are not "cachable"
2) you can use the fact that nvl or expansion works well, assuming loc is NOT NULL, then:
select deptno from dept where loc = nvl( is_loc, loc );
will result in a plan like this:
ops$tkyte%ORA10GR2> create table t ( x int primary key, y int );
Table created.
ops$tkyte%ORA10GR2> variable x number
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t where x=nvl(:x,x);
Execution Plan
----------------------------------------------------------
Plan hash value: 3837537679
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | C
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 130 |
| 1 | CONCATENATION | | | |
|* 2 | FILTER | | | |
|* 3 | TABLE ACCESS FULL | T | 4 | 104 |
|* 4 | FILTER | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T | 1 | 26 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0015661 | 1 | |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:X IS NULL)
3 - filter("X" IS NOT NULL)
4 - filter(:X IS NOT NULL)
6 - access("X"=:X)
see, it is two plans in one - one for the full table scan, one for an index access - and only one or the other will be used depending on the bind variable value at runtime (see the additional predicates the optimizer added)
With Oracle you can learn something new evey single day!
Boris, November 03, 2006 - 8:49 am UTC
Hello Tom,
Thanks a lot for prompt followup! This advise is very interesting.
Looking on execution plan you just provided I would never assume that only part of the plan would be executed. I would suggest "the obvious" - Oracle will always do full table scan (step #3) and execute all 6 steps. Therefore I would avoid this statement at all costs (just imagine that this table has few million rows and majority of calls have :x as NOT NULL).
Thanks a lot for the advise,
Boris
Amit, February 23, 2008 - 3:33 am UTC
Hi Tom,
Another question on explain plan. What will oracle do if I write somethig like :
select * from
TableA MAIN_TAB
WHERE (1=some_var or exists(select 1 from chunk_tab B where B.id=MAIN_TAB.id)
If some_var=1 will the chunk_tab will never be touched in runtime.
Also, Is there any better way of implementing this. I want to join an additional table only on certain condition. dynamic code is not an option as the code is already in an unmanagable form.
Many Thanks
Amit
February 24, 2008 - 11:38 am UTC
.... dynamic code is not an option as ...
hahahahahaha, more untrue words could not be said - ever. Those words should never be uttered, unless you want people to laugh out loud. Give me a break "not an option". Of course it is - EVERYTHING is. EVERY-THING.
Your comment "...the code is already in an unmanagable form. ..." would lead me to believe that the query itself must be slightly more complex - in which case commenting on how oracle would do that simple query (it would short circuit) isn't very useful - since in a larger complex query with merging, predicate pushing, rewrites - it would be impossible to say.
In your SIMPLE case, it might not (probably won't) touch the table
create table t1 as select * from all_users;
create table t2 as select * from all_users;
variable x number
exec :x := 2;
select /*+ x=2 */ *
from t1
where (:x = 1 or exists (select null from t2 where t2.user_id = t1.user_id))
/
exec :x := 1;
select /*+ x=1 */ *
from t1
where (:x = 1 or exists (select null from t2 where t2.user_id = t1.user_id))
/
@trace
exec :x := 2;
select /*+ x=2 */ *
from t1
where (:x = 1 or exists (select null from t2 where t2.user_id = t1.user_id))
/
exec :x := 1;
select /*+ x=1 */ *
from t1
where (:x = 1 or exists (select null from t2 where t2.user_id = t1.user_id))
/
select /*+ x=2 */ *
from t1
where (:x = 1 or exists (select null from t2 where t2.user_id = t1.user_id))
Rows Row Source Operation
------- ---------------------------------------------------
58 FILTER (cr=181 pr=0 pw=0 time=112 us)
58 TABLE ACCESS FULL T1 (cr=7 pr=0 pw=0 time=900 us)
58 TABLE ACCESS FULL T2 (cr=174 pr=0 pw=0 time=1890 us)
select /*+ x=1 */ *
from t1
where (:x = 1 or exists (select null from t2 where t2.user_id = t1.user_id))
Rows Row Source Operation
------- ---------------------------------------------------
58 FILTER (cr=7 pr=0 pw=0 time=1132 us)
58 TABLE ACCESS FULL T1 (cr=7 pr=0 pw=0 time=1865 us)
0 TABLE ACCESS FULL T2 (cr=0 pr=0 pw=0 time=0 us)
but in general, you cannot say that will be true, it can move things around and come up with some pretty tricky plans - SQL is non-procedural, it does whatever it feels like to get the answer.
Amit, February 25, 2008 - 2:26 pm UTC
Many Thanks Tom,
and the learning continues... "EVERYTHING is. EVERY-THING"