Re: ORA-30009

"connect by" is not good idea for a such big generators: 1. https://blogs.oracle.com/sql/entry/row_generators_part_2 2. https://blogs.oracle.com/sql/entry/followup_to_row_generators_part

I'd recommend these variants:

select count(*) from xmltable('1 to 100000000' columns i int path '.');
select count(*) from xmltable('1 to 100000000' columns i int path '0');
select count(*) from xmltable('1 to 100000000' columns i for ordinality);

On Mon, May 16, 2016 at 6:41 PM, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:

> Hi,
>
> I am running a simple statement to generate some test data as shown below:
>
>
>
> *select rownum, mod(rownum-1,3), trunc((rownum-1)/3), trunc(sysdate)+
> trunc((rownum-1)/1)/(60*60*24) from dual connect by level <= 1e8 ;*
>
>
>
> The statement is failing with the following error:
>
> *ERROR:*
>
> *ORA-30009: Not enough memory for CONNECT BY operation*
>
> *18277560 rows selected.*
>
>
>
> The database version is 12.1.0.2 and the OS version is RHEL 6.5.
>
>
>
> The PGA settings are listed below:
>
> *pga_aggregate_limit big integer 16G*
>
> *pga_aggregate_target big integer 8G*
>
>
>
>
>
> When I look at the v$sessstat for the session running the statement, I see
> the PGA usage as shown below:
>
> *session pga memory 951,636,280*
>
> *session pga memory max 951,636,280*
>
> This is a pretty much dormant database and therefore, no other processes
> are consuming PGA. The V$PGASTAT is showing the following at the time of
> error:
>
> *NAME
> VALUE UNIT CON_ID*
>
> *----------------------------------------------------------------
> ---------------- ------------ ----------*
>
> *aggregate PGA target parameter
> 8,589,934,592 bytes 0*
>
> *aggregate PGA auto target
> 6,744,784,896 bytes 0*
>
> *global memory bound
> 858,992,640 bytes 0*
>
> *total PGA inuse
> 1,095,720,960
> bytes 0*
>
> *total PGA allocated
> 1,164,684,288 bytes 0*
>
> *maximum PGA allocated
> 1,169,138,688 bytes 0*
>
> *total freeable PGA memory
> 24,248,320 bytes 0*
>
> *process
> count
> 120 0*
>
> *max processes
> count
> 133 0*
>
> *PGA memory freed back to OS
> 9,637,527,552 bytes 0*
>
> *total PGA used for auto
> workareas 0
> bytes 0*
>
> *maximum PGA used for auto workareas
> 79,927,296 bytes 0*
>
> *total PGA used for manual workareas
> 0 bytes 0*
>
> *maximum PGA used for manual workareas
> 2,117,632 bytes 0*
>
> *over allocation
> count 0
> 0*
>
> *bytes processed
> 29,102,346,240 bytes 0*
>
> *extra bytes
> read/written 0
> bytes 0*
>
> *cache hit percentage
> 100
> percent 0*
>
> *recompute count
> (total)
> 43,645 0*
>
>
>
> Any idea why the statement is failing if there is enough PGA available in
> the instance.
>
>
>
> Thank you,
>
> Amir
>
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l