Re: ORA-30009

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Mon, 16 May 2016 20:29:11 -0400
Message-ID: <573A65D7.7080002_at_gmail.com>

On 05/16/2016 11:41 AM, Hameed, Amir 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 8*
>
> 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
>

Hi Amir, you should create pipelined function with FOR loop instead of connect by. That would be much cheaper.

-- 
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217


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

Received on Tue May 17 2016 - 02:29:11 CEST