Home » Server Options » Spatial » declare geom SDO_GEOMETRY causing oracle mem usage increase
declare geom SDO_GEOMETRY causing oracle mem usage increase [message #562137] Sun, 29 July 2012 09:49 Go to next message
cy163
Messages: 1
Registered: July 2012
Junior Member
Hi, ALL

I've got milions of line objects to be inserted into a Oracle sptial table.

I use the following code to do the work. I am using Oracle 10g


declare geom  SDO_GEOMETRY;
begin geom:=SDO_GEOMETRY(2002, 8307, MDSYS_ELEM_INFO_ARRAY(1,2,1), MDSYS_SDO_ORDINATE_ARRAY(%s);
EXECUTE IMMIDIATE 'insert in %s values(%s, null, null,%d, :1, null)'USING geom;
END;

It works fine for hours. Then, the program got exception error. I noticed from the Windows Task Management that the amount of memory consumed by the oracle process increased as the program running.

I do not know why. I suspect that 'declare geom SDO_GEOMETRY' which actually is in a for loop cause too many varaible geom are declared consuming too many memory. But, I am not sure.

To avoid placing 'declare geom SDO_GEOMETRY' in the for loop, I also tried with declaring a global varible outside the for loop body using
create or replace PACKAGE MM IS

   geom ADO_GEOMETRY

END;


begin MM.geom:=SDO_GEOMETRY(2002, 8307, MDSYS_ELEM_INFO_ARRAY(1,2,1), MDSYS_SDO_ORDINATE_ARRAY(%s);
EXECUTE IMMIDIATE 'insert in %s values(%s, null, null,%d, :1, null)'USING MM.geom;
END;


but no luck too.

Or you may tell me the best way to insert a large amount of LINE objects into sptial table.
Re: declare geom SDO_GEOMETRY causing oracle mem usage increase [message #562168 is a reply to message #562137] Mon, 30 July 2012 02:32 Go to previous message
_jum
Messages: 490
Registered: February 2008
Senior Member
We are using BULK COLLECT with LIMIT clause and FORALL INSERT in this case.
Why you have to use dynamic SQL with EXECUTE IMMIDIATE ?
Previous Topic: SDO_RELATE
Next Topic: Oracle Spatial Query peformance
Goto Forum:
  


Current Time: Wed Nov 26 09:45:33 CST 2014

Total time taken to generate the page: 0.06993 seconds