Re: ORA-30009

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Mon, 16 May 2016 17:05:26 +0100
Message-ID: <CABe10sYZPLBSoA9E4w_Qy6hV1dz3G=+xqx78Cr_Bhq-3MSG7iA_at_mail.gmail.com>

Given that your links list the xmltable variants as pretty much the slowest way to do this and the followup lists out several ways to improve the efficiency of connect by queries (the cartesian join trick being likely what's required here) why do you prefer the xmltable version? I'm assuming I'm missing some other drawback.

On Mon, May 16, 2016 at 4:50 PM, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Amir,
>
> "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
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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

Received on Mon May 16 2016 - 18:05:26 CEST