Skip to Main Content
  • Questions
  • Where does the Cursor stores the rows ?

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, sri.

Asked: April 11, 2007 - 3:28 am UTC

Last updated: February 18, 2008 - 11:38 pm UTC

Version: 9.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi,

Following is a piece of cursor code

declare

cursor cur is select * from profiles ; -- will return 1 milion rows
c1rec cur%rowtype;
begin
open cur;
LOOP
FETCH cur INTO c1rec;
;;;;;;;;;
end;


These are my queries

1. When we open the cursor the select query will be executed. So where will the resultset be stored ? Will be result set be stored in memory ?? how can it accomodate 1 million rows ... Will it be fetched in batches or arrays?
What exactly happens when we open the cursor.


2.From where the Fetch gets the row details ..will it maintain a pointer in the resultset to get the rows one by one....

3. Will the fetch operation query the database again to get the rows?


Please help me to understand ..



Thanks








and Tom said...

no where, it is not stored anywhere. For a simple single table query like that - without any aggregation - we would not "store" the data anywhere.

We read it from the table as we need it.


Now, some queries - like:

select c, count(*) from t group by c;


MIGHT (stress MIGHT, maybe, MIGHT - not will, but rather MIGHT) have to be materialized (the last row generated before the first row can be returned). In this case, they'll be stored in an in memory workarea (sort/hash area) OR if they do not fit there entirely, in your temporary tablespace.


If you are interested in the end to end processing of DML and DDL - I have written an entire chapter on this in "Effective Oracle by Design"


But, as a way of demonstrating that your simple "select * from" query would not "store the data anywhere", consider this example:

ops$tkyte%ORA10GR2> alter session set sql_trace=true;

Session altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          for x in ( select rownum r, t1.* from big_table.big_table t1 )
  3          loop
  4                  exit;
  5          end loop;
  6          for x in ( select rownum r, t2.* from big_table.big_table t2 )
  7          loop
  8                  exit when x.r > 1000;
  9          end loop;
 10          for x in ( select rownum r, t3.* from big_table.big_table t3 )
 11          loop
 12                  exit when x.r > 10000;
 13          end loop;
 14          for x in ( select rownum r, t4.* from big_table.big_table t4 )
 15          loop
 16                  exit when x.r > 100000;
 17          end loop;
 18  end;
 19  /

PL/SQL procedure successfully completed.



Now, big_table has 1,000,000 rows in it - when we look at tkprof:

SELECT ROWNUM R, T1.* FROM BIG_TABLE.BIG_TABLE T1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          5          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          5          0         100

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 120  (OPS$TKYTE)   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  COUNT  (cr=5 pr=0 pw=0 time=647 us)
    100   TABLE ACCESS FULL BIG_TABLE (cr=5 pr=0 pw=0 time=339 us)
--------------------------------------------------------------------------------
SELECT ROWNUM R, T2.* FROM BIG_TABLE.BIG_TABLE T2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       11      0.00       0.00          0         28          0        1100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.01       0.01          0         28          0        1100

Rows     Row Source Operation
-------  ---------------------------------------------------
   1100  COUNT  (cr=28 pr=0 pw=0 time=4474 us)
   1100   TABLE ACCESS FULL BIG_TABLE (cr=28 pr=0 pw=0 time=1170 us)
--------------------------------------------------------------------------------
SELECT ROWNUM R, T3.* FROM BIG_TABLE.BIG_TABLE T3

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      101      0.07       0.06          0        237          0       10100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.07       0.06          0        237          0       10100

Rows     Row Source Operation
-------  ---------------------------------------------------
  10100  COUNT  (cr=237 pr=0 pw=0 time=50615 us)
  10100   TABLE ACCESS FULL BIG_TABLE (cr=237 pr=0 pw=0 time=20300 us)
--------------------------------------------------------------------------------
SELECT ROWNUM R, T4.* FROM BIG_TABLE.BIG_TABLE T4

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1001      0.55       0.54          0       2441          0      100100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1003      0.55       0.54          0       2441          0      100100

Rows     Row Source Operation
-------  ---------------------------------------------------
 100100  COUNT  (cr=2441 pr=0 pw=0 time=400595 us)
 100100   TABLE ACCESS FULL BIG_TABLE (cr=2441 pr=0 pw=0 time=100223 us)


You can see the work done (IO's and CPU) goes up as the amount of work goes up. The more you fetch, the more we did.

Fetch nothing - no work
Fetch lots - lots of work

we do not copy the data anywhere in most cases - and if we need to - we use memory and then temp.

Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

sri, April 12, 2007 - 1:09 am UTC

Sorry , i am little confused here.

When we open the cursor , the Select query will be executed .So it will read the rows from the table and try to build a read consistent image . ( so the resultset will not be stored in memory in most cases for a plain select )

But what happens when we fetch data from the cursor.

Everytime during a fetch a row will be read again from the table block ? Also it should fetch the read consistent image when the cursor was opened? using SCN ?


Where the fetch pointer is maintained ?
How will it identify the particular row during some nth fetch ?

Thanks















Tom Kyte
April 13, 2007 - 11:43 am UTC

...
When we open the cursor , the Select query will be executed ...

no, it won't. Why would it be? when you open a cursor, a "cursor area" is set up, and points to a plan that as you fetch (execute the query) the data will be assembled.

The data is RETRIEVED AS NEEDED, it is not retrieved all up front in general.

If you want the lengthly details, the book "Effective Oracle by Design" has a complete chapter on how statements are processed from end to end.

sri, April 18, 2007 - 8:15 am UTC



This is what i found in the oracle doc , "opening a cursor" part ,

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/06_ora.htm#36656


"Opening the cursor executes the query and identifies the result set, which consists of all rows that meet the query search criteria. For cursors declared using the FOR UPDATE clause, the OPEN statement also locks those rows."


If you say that a "cursor area" is set up and points to a plan and only during the fetch data is assembled

Then to open a cursor which selects say 100K rows why does it take such a long time. ? ( time equivalent to a same select ) why does just setup of cursor area will take a long time ?

Thanks









Tom Kyte
April 18, 2007 - 12:13 pm UTC

big_table%ORA10GR2> select count(*) from big_table;

  COUNT(*)
----------
   1000000

big_table%ORA10GR2>
big_table%ORA10GR2> set timing on
big_table%ORA10GR2> declare
  2     cursor c is select count(*) from big_table, big_table, big_table;
  3  begin
  4          open c;
  5          close c;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01


Ok, do you think that counted 1,000,000 * 1,000,000 * 1,000,000 rows

or not.


If you want the entire story as I would write it, I have to say "effective oracle by design" - it is quite a few pages.

Noticed something in your first fetch, wanted to confirm

Scot Martin, April 18, 2007 - 1:33 pm UTC


Hey Tom, love the site. I noticed in your first fetch, which was in the first for loop that did an unconditional exit:

2 for x in ( select rownum r, t1.* from big_table.big_table t1 )
3 loop
4 exit;
5 end loop;

In looking at the TKPROF output for that query, it shows the number of rows being fetched as 100. Does that prove / demonstrate the bulk collecting optimization that Oracle added in 10g, where it implicitly and automatically does a bulk collect of limit 100 behind the scenes?

This came up at a discussion at my site very recently, and I think I can just point them to your example here as a demo rather than creating my own. I assume that if you ran the same thing in 9iR2, then that first fetch of rows in TKPROF would only show 1?

Tom Kyte
April 18, 2007 - 1:37 pm UTC

yes, that demonstrates the implicit array fetch of 100 rows...

in 9i, it would show 1 row fetched.

execute? does it fetch? I am thinking no...

Kevin, April 18, 2007 - 11:07 pm UTC

maybe some confusion comes from terminology here. Its been a while since I did PRO*Cobol and OCI work but let me try this anyway.

When we did PRO*Cobol work, we always took a look at the precompiler output to see what it did. To handle a cursor in OCI I seem to recall the following steps:

1) prepare a statement
2) define select list items if you got any
3) bind input variables if you got any
4) execute the statement
5) fetch rows till done
6) close somehow

Knowing what I do (which is little to be sure), I seem to recall that the "EXECUTE" step does not fetch rows. I presume that under normal Oracle concurrency/consistency rules, the execute step is when oracle grabs a "time stamp" for the statement.

When we say OPEN CURSOR in some "high" level language like PLSQL, for sure this translates into multiple OCI steps. I would think prepare, define, bind, execute steps to be more precise. Thus OPEN CURSOR indeed does execute the statement but the meaning of execute in the manual is likely being used in a specific technical context, and not as a general term.

Sri, hope this clarifies for you.

Tom, if I got any of it wrong please correct me.

Kevin
Tom Kyte
April 19, 2007 - 7:16 am UTC


But - it goes deeper.

consider just this:


open c for select * from one_billion_row_table;

fetch c into l_rec;
fetch c into l_rec;


that first fetch - it touches, ONE ROW, it does not 'fetch all one billion rows' somewhere. Data is retrieved AS NEEDED, it is not 'pre-answered' in general (sure, sometimes it can be, but it need NOT be)



Read consistency

A reader, April 19, 2007 - 3:15 am UTC

Hi Tom,
Many thanks to your invaluable service to oracle community.
In your previous answers, you demonstrated that every fetch it would read the some 'n' number of rows instead of reading all the records at one time. Suppose after the first fetch started in one session ,I am inserting some records in another session to the same table.
Will the recently inserted records would be processed by that cursor. Consider the table is big(i.e some 5 million records). Please clarify my doubt.

Tom Kyte
April 19, 2007 - 7:19 am UTC

that is what read consistency is about.

when you OPEN the cursor, before the first bit of data is even touched, before we do any IO at all - the result set is already "known". Oracle saves the SCN (system change number) and ensures that every block you read while processing that query is rolled back to that point in time.

Since the inserts were not committed when you OPENED the query, the newly inserted data will not be SEEN by your query. They did not exist when you started, they will not exist when you get to those blocks.

sqlplus Test

Pasko, April 19, 2007 - 11:08 am UTC

Hi,

Like Tom said,
the 'Open Cursor' step executes really quick.
a simple sqlplus Test will show this:


SQL> alter session set nls_language='American';

Session altered.

Elapsed: 00:00:00.01
SQL> create or replace function open_cursor
2 return sys_refcursor
3 as
4 cur sys_refcursor;
5 begin
6 open cur
7 for
8 select count (*)
9 from all_objects;
10
11 return cur;
12 end;
13 /

Function created.

Elapsed: 00:00:00.01
SQL> --
SQL> --define a cursor variable
SQL> var my_cursor refcursor
SQL> --
SQL> set timing on
SQL> --
SQL> --open-cursor comes back really quick!
SQL> --
SQL> exec :my_cursor := open_cursor;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> --
SQL> --the bulk of the work is performed during the fetch
SQL> --
SQL> print my_cursor;

COUNT(*)
----------
39245

Elapsed: 00:00:02.12
SQL>

Qualifying the answer

Alex Voinea, April 25, 2007 - 10:30 am UTC

Hi Tom,

We could point out that Oracle is doing the minimum amount of work required when opening a cursor:

for a 'select only' cursor it probably does nothing more that getting the SCN of the moment the cursor is OPENed (in addition to parsing the cursor query, setting up memory areas).

for a 'select for update' cursor on the other hand, it needs to update the data blocks headers to lock the rows: it takes (on my PC) roughly 1min 44sec for the OPEN of a cursor with FOR UPDATE:

d:ninja@XE> declare
  2  cursor c is select  * from t_all_objects;
  3  begin
  4  open c;
  5  close c;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01 --> As expected, very little to do here.
d:ninja@XE> ed
Wrote file afiedt.buf

  1  declare
  2  cursor c is select  * from t_all_objects for update;
  3  begin
  4  open c;
  5  close c;
  6* end;
d:ninja@XE> /

PL/SQL procedure successfully completed.

Elapsed: 00:01:44.32 --> much longer that the previous execution
d:ninja@XE> select count(*) from t_all_objects;

  COUNT(*)
----------
   2875230

Elapsed: 00:00:06.67

Execution Plan
----------------------------------------------------------
Plan hash value: 3675235668

----------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |    27   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |               |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T_ALL_OBJECTS |  6987 |    27   (0)| 00:00:01 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      35482  consistent gets
      31164  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

d:ninja@XE> rollback;

Rollback complete.

Elapsed: 00:02:18.94 
d:ninja@XE>
d:ninja@XE>
d:ninja@XE>





Tom Kyte
April 25, 2007 - 10:42 am UTC

but, that is what I said.

In general, the data is not stored anywhere, if we can return the first row before we touch the last (and your for update is just one of dozens of examples where we cannot do that), we will.

Cursor and 1555

A reader, April 25, 2007 - 4:54 pm UTC

Tom,
If I open a cursor and do not fetch from it for a long time, am I increasing the probability of getting 1555?

Thanks

Tom Kyte
April 25, 2007 - 5:30 pm UTC

yes - the odds of a 1555 are definitely increasing as time goes on....

when does query execute in cursor?

Sagar, April 26, 2007 - 6:21 am UTC

SQL> select empno from emp;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

     EMPNO
----------
      7900
      7902
      7934

14 rows selected.

SQL> declare
  2  cursor c is select * from emp;
  3  emp_rec emp%rowtype;
  4  begin
  5  open c;
  6  delete from emp;
  7  loop
  8  fetch c into emp_rec;
  9  exit when c%notfound ;
 10  dbms_output.put_line(emp_rec.empno);
 11  end loop;
 12  end;
 13  /
7369
7499
7521
7566
7654
7698
7788
7839
7844
7876
7900
7902
7934
7782

PL/SQL procedure successfully completed.

SQL> select empno from emp ;

no rows selected


It seems opening the cursor executes the query.Can you please explain this ...as Oracle Documentation also says that.
Tom Kyte
April 26, 2007 - 1:02 pm UTC

sigh, that is called read consistency.

When you open a cursor, the results are "pre-ordained as of that point in time"

regardless of what happens to the data - by you, by other sessions.

if you have access to any of my books - I write about this a lot (read consistency), the concepts guide writes a lot about this.

the query in question above is fetching data as it needs it. As you retrieve data. when you did the a) open b) delete c) fetch - you would find that Oracle used the UNDO information to put the blocks back the way they were....


the data was not "copied" when you opened that query. rather as you fetched the data - oracle looked at each block and said "hey, this was modified since you opened your query, let's roll it BACK and put it the way it was when you opened it"


When does query execute...

Sagar, April 26, 2007 - 8:48 am UTC

Thanks Tom,
Ok...got it... SCN at the time of Open statement is used to execute the query and reconstruct blocks from rollback if required.

I guess oracle docs says "open statement executes query" for sake of simplicity from point of view of oracle developer.Is it?




Tom Kyte
April 26, 2007 - 1:06 pm UTC

sure

Thanks Tom

Sagar, April 26, 2007 - 1:58 pm UTC

Thank you very much Tom ... ;)

Can you please to describe the bulk collect of limit 100 in detail?

Annie, February 03, 2008 - 8:50 pm UTC

Hey Tom, love the site. I noticed in your first fetch, which was in the first for loop that did an unconditional exit:

2 for x in ( select rownum r, t1.* from big_table.big_table t1 )
3 loop
4 exit;
5 end loop;

In looking at the TKPROF output for that query, it shows the number of rows being fetched as 100. Does that prove / demonstrate the bulk collecting optimization that Oracle added in 10g, where it implicitly and automatically does a bulk collect of limit 100 behind the scenes?

This came up at a discussion at my site very recently, and I think I can just point them to your example here as a demo rather than creating my own. I assume that if you ran the same thing in 9iR2, then that first fetch of rows in TKPROF would only show 1?



Followup April 18, 2007 - 1pm US/Eastern:

yes, that demonstrates the implicit array fetch of 100 rows...

in 9i, it would show 1 row fetched.

--------------
In the above, Can you please to describe the bulk collect of limit 100 in detail?
I find that if the row numbers in big_table is more than 100, then here 100 is the bulk collect of limit. But if the rows is less than 100, such as 30, then here 30 would be fetched.
Thanks very much!
Tom Kyte
February 04, 2008 - 3:56 pm UTC

of course if there is only 30 rows to prefetch - it can only prefetch 30 rows.

When you ask for row 1, it asks the server for rows 1 through 100.

If there isn't a row 31 - it will stop of course, it won't make up rows.

why no any info in v$sql_cursor for DML statement?

Annie, February 18, 2008 - 10:31 pm UTC

Tom, Thanks very much!
I have another questions as following:
1. The first step of executing an UPDATE statement is to open a cursor for it. So I think we can find the info about the UPDATE statement from v$open_cursor, but no anything, why? If the dynamic view is not used to show DML cursor, where can I find the implicit cursor info?
2. The cursor is a private sql area and the result of cursor would not be saved anywhere, does it mean all result rows would be returned to user process directly and not via UGA, right? If yes, I'm confused with BULK COLLECT, how does it work? very confused!

Hope your prompt in detail, and thanks as always.

Tom Kyte
February 18, 2008 - 11:38 pm UTC

1) why no example of what you tried? if I said something was not possible, I would have shown you what I tried.. why won't you?

2) eh? no idea what you mean. "a cursor is a private sql area", " results would not be saved anywhere" - what do these have to do with each other?

and bulk collect just is short hand for "get me rows X through Y please", instead of saying "get me row X, X+1...., Y" in order. so, not really sure why you think it is "special"

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library