| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Why no implicit cursors ? (was Re: Problem with PL/SQL / nextval)
A copy of this was sent to "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
(if that email address didn't require changing)
On Fri, 20 Aug 1999 10:31:12 +0100, you wrote:
>
>This is one of those good ideas which has been surrounded
>by ignorance and taken on more significance than it really deserves.
>
hear hear.
>It is generally a good idea to be in control of what you are doing,
>and it is possible to run into unexpected problems if you use lots
>of implicit cursors, and there is (in some cases) a performance
>overhead.
>
>But my opinion is that the issue is only a problem if you are using
>PL/SQL in an unsuitable way anyway.
>
>
>To address the issues:
>a) Performance:
> Historically an implicit cursor did TWO fetches from the database,
> so that it could report 'too many rows' if the second fetch returns
> a row. This required a second round-trip across the network.
>
> But this was only a 'performance issue' when compared to an explicit
> cursor that does NOT do a second fetch - and thus fails to trap a
> possible error.
>
right -- the people who say "use an explicit cursor and open/fetch/close it instead of select into..." have CHANGED the behaviour of the code! Select Into says -- there will be one row, not zero rows not 2 rows -- there will be EXACTLY one row. open/fetch/close allows for 0, 1 or more rows. It is 100% different.
> However, various changes in how SQL is executed in later versions
> of Oracle were introduced to handle such network costs, and if you
> trace the action of an implicit cursor (under 8.0.4 at least) it
>manages
> to return 'too many rows' whilst executing a single FETCH at the
> database end.
>
> Scratch one old wives' tale about performance on implicit cursors.
>
>
>b) Cursors closing;
[snip]
>
> When the cursor loop opens, the cursor 'select 1 from dual'
> should have been closed - but it isn't. The theory is that if
> you rewrote this as an explicit cursor it would: unfortunately
> on my copy of 8.0.4 of you change
well, the PLSQL cursor is closed. the cursor however remains cached in the server in the likely event that you run that query again in that session. this avoids the softparse on the subsequent execs.
In 8.0 and before, you can set close_cached_opened_cursors = true (or alter session ....) and when you commit, all of these will disappear. In 8.1 and above, this parameter is obsoleted and a more sophisiticated caching algorithm is used... the cursors will be managed and freed for you based on session cached cursors and open cursors.
If you really really don't want the cursor cached (see the paper on fine grained access control at the url in my signature for a reason you might not want it cached) you can use ref cursors, for example:
SQL> create or replace procedure p
2 as
3 cursor c is select 1 from emp; 4 x number; 5 begin 6 open c; 7 fetch c into x; 8 close c;
SQL> exec p
PL/SQL procedure successfully completed.
SQL> select sql_text from v$open_cursor where user_name = user;
SQL_TEXT
SQL> create or replace procedure p2
2 as
3 type refcur is ref cursor; 4 rc refcur; 5 x number; 6 begin 7 open rc for select 1 from emp; 8 fetch rc into x; 9 close rc;
SQL> exec p2
PL/SQL procedure successfully completed.
SQL> select sql_text from v$open_cursor where user_name = user;
SQL_TEXT
PL/SQL does not cache REF CURSORS or any cursor opened using dbms_sql (or the new dynamic sql in Oracle8i, release 8.1)
> select 1 into m_junk from dual;
> into
> declare
> cursor c1 is select1 from dual;
> begin
> open c1;
> fetch c1 into m_junk;
> close c1;
> end;
>
> You still see the same result !!
> Scratch another theory of implicit vs. explicit cursors. (Again,
> this may have been true in the past, and may become true in
> the future).
>
> The main benefit of explicit cursors is that they can
> have parameters, so you can have just one open, but
> use it for lots of different calls.
but implicit cursors have the same exact benefit!
select empno into x from emp where ename = p_some_variable;
for x in ( select * from t where c1 = p_some_variable and c2 =
p_some_other_variable ) loop
...
plsql variables can be used in implicit cursors just as easily (more so i think) then explicit cursors...
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Aug 20 1999 - 07:01:35 CDT
 |
 |