RE: ORA-30009
Date: Mon, 16 May 2016 17:04:49 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED200F39EE_at_USA7109MB012.na.xerox.net>
Thank you for your feedback. I was not aware of the other variants, I just ran the statement with the xmltable variant and it worked fine.
From: Sayan Malakshinov [mailto:xt.and.r_at_gmail.com]
Sent: Monday, May 16, 2016 12:21 PM
To: Niall Litchfield <niall.litchfield_at_gmail.com>
Cc: Hameed, Amir <Amir.Hameed_at_xerox.com>; oracle-l_at_freelists.org
Subject: Re: ORA-30009
Niall,
I've posted in the comments on first link that these 3 variants work much faster than "select * from xmltable ('1 to 1000000');" from article:
SQL> select i from xmltable('1 to 1000000' columns i for ordinality); Elapsed: 00:00:03.05
And this variant is much easier to read and write, especially when number of rows is vary:
select i from xmltable(' 1 to xs:integer($N)' passing :N as N columns i for ordinality);
On Mon, May 16, 2016 at 7:05 PM, Niall Litchfield <niall.litchfield_at_gmail.com<mailto:niall.litchfield_at_gmail.com>> wrote: 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<mailto: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<mailto: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
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
--
http://www.freelists.org/webpage/oracle-l
Received on Mon May 16 2016 - 19:04:49 CEST