Home » SQL & PL/SQL » SQL & PL/SQL » Cursor Memory Reserve
Cursor Memory Reserve [message #222692] Tue, 06 March 2007 00:25 Go to next message
nsurendiran
Messages: 16
Registered: March 2007
Location: India
Junior Member

Hi,
I knew that Cursors will reserve some memory space for storing the result set of the query. I want to know that memory reserve will happen at compile time or run time?

Re: Cursor Memory Reserve [message #222697 is a reply to message #222692] Tue, 06 March 2007 00:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Cursors do NOT store the resultset of a query. Imagine a cursor that would return 1 zillion rows...
Re: Cursor Memory Reserve [message #222701 is a reply to message #222697] Tue, 06 March 2007 01:00 Go to previous messageGo to next message
nsurendiran
Messages: 16
Registered: March 2007
Location: India
Junior Member


Ok. I agree and thanks for changeing my View on the cursors. But I wanted to knwo about the reply I got in another forum. Here it goes..



> Can anybody tell me that cursors reserve memory at compile time or
> execution time??

Both.

At compilation time. The SQL is passed to the SQL Engine. There is it parsed (hard or soft).

If it is hard parsed, it is added to the SQL Shared Pool, together with its execution plan. (a soft parse will find an existing cursor in the shared pool to re-use)

This cursor is also called the parent cursor.

At execution time, a child cursor is created. This is a "copy" of the parent cursor, and also includes the bind values you have assigned the bind variables in the cursor.

The following virtual views can be used to view these (parent and child) cursors in the Shared Pool:

V$SQLAREA Lists statistics on shared SQL area and contains one row per
SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution. (i.e. parent cursors plus child cursor totals)

V$OPEN_CURSOR This view lists cursors that each user session currently has opened and parsed. (i.e. child cursors)

V$SQL_CURSOR This view displays debugging information for each cursor associated with the session querying this view. (it also links the child cursor to the parent cursor)

V$SQL_SHARED_MEMORY Displays information about the cursor shared memory snapshot. Each SQL statement stored in the shared pool has one or more child objects associated with it. Each child object has a number of parts, one of which is the context heap, which holds, among other things, the query plan.

Details on these views are in the OracleŽ Database Reference guide.
Re: Cursor Memory Reserve [message #222949 is a reply to message #222701] Wed, 07 March 2007 00:58 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Your post is not talking about the DATA returned by the cursor, but the SQL itself and its execution plan. So a cursor that returns a zillion rows will take up the same amount of memory as a similar cursor that returns no rows.

Oracle's memory structures are quite complex. It's very difficult to provide a concise answer to your question.

If you want to understand how memory structures work, you should read the manuals, or there is a good For Dummies summary here.

Ross Leishman
Previous Topic: How to DELETE The data FROM a PL/SQL table
Next Topic: having two PK for a table
Goto Forum:
  


Current Time: Fri Dec 09 02:21:52 CST 2016

Total time taken to generate the page: 0.08620 seconds