Question on CURSOR re-use [message #345797] |
Thu, 04 September 2008 18:47  |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
As DBA's at my firm disabled viewing of any v$ system view useful for tuning, I was wondering if anyone knew off hand if
the following change to a cursor would negate's it from
being re-used (ie, Oracle will require a hard parse on every execution).
Right now we have a process which takes advantage of cursor variables. For a quick example to reference, lets say:
...
CURSOR C1(p1 varchar2, p2 varchar2) IS
SELECT COUNT(*) FROM T_SOME_TABLE T
WHERE T.A_FIELD = P1 OR
T.A_FIELD2 = P2;
...
The cursor resides in a stored procedure within a package.
I now need to add a function into the cursor SQL which resides
in the same package.
The function will perform sql and return a value. Let's use
this as example:
FUNCTION fn_lookup_data(p_key varchar2) RETURN integer IS
lvRet Integer := 0;
BEGIN
SELECT COUNT(*) INTO lvRet
FROM T_SOME_XREF_TBL T
WHERE T.A_FIELD = p_key;
RETURN lvRet;
END;
Now the original cursor will be modified to use the function as:
CURSOR C1(p1 varchar2, p2 varchar2) IS
SELECT COUNT(*) FROM T_SOME_TABLE T
WHERE T.A_FIELD = P1 OR
T.A_FIELD2 = P2 AND
DECODE(fn_lookup_data(T.A_FIELD3),1,1,0) = 1;
Does using a user function in the sql cause the cursor
to be hard for every execution or is there no affect on cursor sharing?
I provided the examples to outline these factors if they
are involved:
(1) The function performs SQL
(2) The function is non-deterministic
(3) The use of the function will be inside a DECODE and
add literals into the cursor
(4) The function exists in the same package as the procedure
which processes the cursor that implements it
I do not know off-hand if we have CURSOR_SHARING set to
EXACT or SIMILAR.
Regards,
Harry
|
|
|
|
|