Re: Cursor performance in Oracle

I seem to remember someone telling me that explicit cursors would release there memory as well, wheras implicit's would not until the session was over. Does this make any sense? When you say the client side will have multiple cursors open for implicit cursors based on the same SQL code, where do you lose resources, less memory on your PC?

• Dc.

Sybrand Bakker wrote:

> Hi Paul,
> I would guess not. Assuming we are talking about a pl/sql context, there are
> two different types of cursors
> implicit and explicit.
> You will get an implicit cursor for statements like
> select ename from emp where empno = 10
> ie the statement can retrieve only one single row
> Explicit cursors are coded by the developer
> cursor sel_emp is
> select ename from emp where empno > 10
> in the declaration
> and called by
> open sel_emp
> fetch sel_emp into something
> etc.
> For implicit cursors the first danger is to forget to trap the no data found
> situation. Many inexperienced developers think this would never happen (the
> records we know we have, are always there, and operators don't make
> mistakes). However, to get this construct robust you need to code
> begin
> select ename from emp
> where empno = 10;
> exception
> when no_data_found then
> <something>;
> when others then
> <etc>
> It is even more dangerous because constructs like this are usually sprinkled
> liberally through PL/SQL code, even if the statement is exactly the same.
> Oracle will detect this is the same statement in the library cache, yet at
> the client side, it will still use multiple cursors. So the guideline is:
> try to avoid implicit cursors as much as possible and use explicit cursors
> everywhere. In Oracle a cursor is basically a handle, and it is used always,
> there is no difference in performance, yet the performance of your package
> can decrease if you are using implicit cursors everywhere.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
> NB: If you want to read a real good book about PL/SQL programming, get the
> books of Steve Feuerstein published by O'Reilly
> Paul Davies wrote in message <375d20ad_at_newsread3.dircon.co.uk>...
> >I'm migrating a system from Sybase to Oracle. In Sybase, one avoided
> cursors
> >when possible as they were many, many times slower than writing a straight
> >query. Is this also true in Oracle?
> >
> >
Received on Wed Jun 09 1999 - 17:19:11 CDT