Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: February 24, 2024 - 11:30 pm UTC

Last updated: March 07, 2024 - 6:24 am UTC

Version: 19

Viewed 1000+ times

You Asked

I run in sqlplus a query with huge result set. It has to return million records
Set arraysize to 5000
Monitoring v$session, v$process, v$sqlstats while query is running I can see after some minutes executions changes from 0 to 1.
Then fetch count begin to be incremented at each stats read cycle and so is doing rows processed count (row counter is incremented by multiple of 5000)
At this point I expected fetched rows to be unloaded, step by step, from process pga but I'm surprised to see that process pga grows continuously.
So why process is still keeping in memory rows that are already been fetched ?
No way to free pga step by step after each fetch ?

and Connor said...

I need to see your test case - here's mine, showing I didnt grow without bounds


SQL>
SQL> create table t as
  2  select d.* from dba_objects d,
  3    ( select 1 from dual connect by level <= 20 );

Table created.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
   1769340

SQL>
SQL> set serverout on
SQL> declare
  2
  3    type rowlist is table of t%rowtype
  4      index by pls_integer;
  5    r rowlist;
  6    cursor c is select * from t;
  7
  8    procedure print_pga is
  9    begin
 10      for mem in (
 11      select s.name, st.value
 12      from v$statname s, v$sesstat st
 13      where st.statistic# = s.statistic#
 14      and st.sid = to_number(sys_context('USERENV','SID'))
 15      and s.name like '%pga%'
 16      )
 17      loop
 18        dbms_output.put_line(rpad(mem.name,28)||' '||ceil(mem.value/1024)||'kb');
 19      end loop;
 20      dbms_output.put_line('---------------');
 21    end;
 22
 23  begin
 24    open c;
 25    loop
 26      fetch c bulk collect into r limit 5000;
 27      print_pga;
 28      exit when c%notfound;
 29    end loop;
 30    close c;
 31  end;
 32  /
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb
---------------
session pga memory           14244kb
session pga memory max       14244kb

Rating

  (1 rating)

Comments

Fetch and pga

miky, March 01, 2024 - 3:10 pm UTC

Thanks a lot for your answer, very explicative to me.
So infact my pga size is growing more and more not due of the amount of data, but due of the complexity in the query including joins and pivot function
After starting to fetch rows other computing still occurr on resultset and pga consuming grows up.
Connor McDonald
March 07, 2024 - 6:24 am UTC

as per first answer

I need to see your test case
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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