1

There is something not very clear in my mind and that I wanted to know when performing queries that retrieve large result sets:

Is a result set entirely loaded into memory and send to the client or only a part of it is loaded, send and then the memory is reused to load the next part until all data has been processed to fulfill the query ?

I suppose that for some queries that require a sort operation or use an analytical function it has to be fully loaded but what for basic queries that would only involve one table ? Like for instance:

select * from mytable where id between 1 and 1000000;

Will the one million records be loaded entirely into the memory allocated for my query or only a part of it ?

Thanks for your help,

2 Answers 2

1

The client requests some number of rows. It could be all of them. Oracle will return the rows as they are requested. If there is a blocking operation then the entire result set could end up in memory or temp. If there's not then it's unlikely that the entire result set will end up in server memory. Think of the query as a streaming operation that moves rows from the server as it finishes with them to your client's memory.

Let's go through a simple example. The code below creates a table and populates it with integers from 1 - 1000000:

CREATE TABLE X_TBL (DUMMY_DATA INTEGER NOT NULL);

-- insert 1M rows
INSERT INTO X_TBL select level 
from dual connect by level <= 1000000; 

COMMIT;

In SQL Developer I run the following query. By default I get the first 50 rows of the result set:

select /*+ MONITOR */ * 
from X_TBL 
where DUMMY_DATA between 1 and 500000;

The first 50 rows come back very quickly. Using SQL Monitoring we can see that Oracle got the first 50 rows from the table and is waiting to return more rows. No memory was used. The green arrows means those operations are still active.

50 rows

As I request more rows Oracle returns more rows:

400 rows

After returning all rows to the client the query is finally marked as DONE on the server. No memory was used:

all rows

Now let's add an ORDER BY clause to the example query:

select /*+ MONITOR */ * 
from X_TBL 
where DUMMY_DATA between 1 and 500000
ORDER BY DUMMY_DATA;

There are no indexes on the table so Oracle must process all of the rows from the table before returning a single row. We can see this in the SQL Monitor report as well. Even though I asked for just the first 50 rows on the client Oracle needed to find all 500k rows in the table. The query uses 10 MB of memory just to return the first 50 rows.

[]sort 50[4]

After I return all of the rows the memory is released by the server. Note that the maximum memory used did not increase:

sort 500k

1
  • Caveat as usual: SQL Monitoring is Enterprise Edition and Tuning Pack Option only
    – jmk
    Mar 8, 2017 at 8:01
1

Usually it's in the PGA (Program Global Area). But it will depend on a number of things. For more info, take a look at: https://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm#CNCPT007

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.