Solved

Disadvantages

Posted on 2004-08-07
5
4,035 Views
Last Modified: 2010-08-05
What are the disadvantages of cursors ?
What are the disadvantages of packages?
0
Comment
Question by:hiswapna
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
morphman earned 50 total points
ID: 11747582
Disadvantages of Cursors:
1. Row by row processing, so bulk get operations are slow.
2. Inherent risk of memory mismanagement if cursors are not managed correctly. ie. in our compnay we usually forfeit the performance benefits of cursor fetch loops for cursor for loops, so that they will automatically be implicitly closed rather than having the possibility of rather large open cursors floating round the system on an abnormal exit.
3. Pre 10g, cursors are handled by the plsql engine which is not quite as efficient/bug free as the sql engine. I think 10g has combined the 2 engines now.
4. Building a large cursor can have quite an overhead, which is more of a problem especially in an XA environment, when a transactional commit means that cursors must be re-built to continue from the last processed record. (ie checkpointing etc). In an oracle only system this is not a problem, but multi platform/multi database transactional system, we have found that we need to strike the right balance for our batch checkpoint frequency, otherwise the time taken to build a cursor can outweigh the actual work taking place, and the batch slows right down.

I think the advantages far outweigh the disadvantages though.

Disadvantages of Packages.
1. They are stored code, and any malicious/accidental overwriting can result in a disaster. This can be aleviated by putting certain privs on the packages for users, but that is a maintenance overhead.
2. The DESC function could be more descriptive in my opinion. In a package you can desc it to se what procedures and function, and their inputs/outputs, but it would be nice to also include a description. (can you do this by the way?)

Cant think of any more as packages are pretty much wonderful as long as someone keeps an eye on the stored code to make sure some developers testing hasnt been included onto live :-)

0
 
LVL 8

Expert Comment

by:annamalai77
ID: 11750383
hi

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors.
 
 
0
 
LVL 48

Expert Comment

by:schwertner
ID: 11760201
Cursors are the only way to handle multirow select in PL/SQL. They embed the SELECT statement to the logic of 3GL languges like PL/SQL.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 11760234
The main disadvantage is ORA-01000 error.
"Too many open cursors".
This is when in PL/SQL one has no EXCEPTION section which closes the all opened cursors.

But this is also a hidden error in the Java application world.
The drivers (JDBC - thin and fat) implicitely opened many cursors and very often (canceled application)
forget to close them. The result is very unpleasant - ORA-01000. So programmers have to catch the exception
and to close the resulsets by every normal or abnormal end of the programs.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question