Swap Space Usage High For Spatial Query (Doc ID 1612633.1)

Last updated on MARCH 01, 2019

Applies to:

Symptoms

The following query runs for 5 minutes and generates an ORA-4030 error

select
 MDSYS.SDO_GEOM.SDO_BUFFER(
  MDSYS.SDO_GEOM.SDO_INTERSECTION(
  -- Circular Buffer
  MDSYS.SDO_GEOM.SDO_BUFFER(
  MDSYS.SDO_CS.TRANSFORM(MDSYS.SDO_GEOMETRY(2001, 8265,
MDSYS.SDO_POINT_TYPE(-79.484371,43.837539, NULL), NULL, NULL), 54004),
  69, -- circle radius (segment length)
  0.5 -- circle tolerance
  ),
  -- Road PIN
  
MDSYS.SDO_GEOMETRY(2003,54004,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.S
DO_ORDINATE_ARRAY(-8848166.06435338,5410845.27275742,-8848189.45898198,
5410838.38464044,-8848189.42702961,5410838.1971014,-8848187.36703236,5410826.1
7763883,-8848185.64778766,5410816.15065753,-8848183.92906599,5410806.12427315,

-8848182.21041478,5410796.09679622,-8848180.49228661,5410786.0699162,-8848178.
77357729,5410776.04356676,-8848177.05435316,5410766.01664371,-8848175.33568524
,
5410755.98976547,-8848173.61698826,5410745.96345097,-8848171.89832857,5410735.
93659603,-8848170.17970616,5410725.90920065,-8848168.46150728,5410715.88405845
,
-8848166.74237418,5410705.85610111,-8848165.02421669,5410695.8304301,-8848163.
30509181,5410685.80249607,-8848161.58639047,5410675.7768152,-8848159.86727383,

5410665.74890446,-8848158.14916597,5410655.72272796,-8848156.43057647,5410645.
69542578,-8848154.71189158,5410635.66979149,-8848152.99275823,5410625.64247944
,
-8848151.27466682,5410615.61634953,-8848149.55557487,5410605.5885087,-8848147.
83742535,5410595.56350625,-8848146.11834163,5410585.53568872,-8848144.39974776
,
5410575.50902016,-8848142.68112484,5410565.48291533,-8848142.12369895,5410562.
22806129,-8848140.72037208,5410555.49380288,-8848138.26376966,5410546.99804745
,
-8848136.87886369,5410543.00976489,-8848134.54308995,5410536.86498296,-8848132
.07434332,5410530.36980529,-8848131.86824221,5410529.82551711,-8848135.8774739
4,
5410520.89879893,-8848100.30029126,5410534.42578144,-8848109.22645521,5410538.
43501632,-8848109.43258824,5410538.97875355,-8848113.0840074,5410548.58573697,

-8848114.2370779,5410551.61873515,-8848116.16302262,5410557.33609771,-8848117.
33807664,5410561.79700032,-8848118.25069867,5410566.31889682,-8848118.26133752
,
5410566.38159193,-8848121.02277922,5410582.49256056,-8848122.86986422,5410593.
26665024,-8848124.4361237,5410602.40709597,-8848126.00297184,5410611.54703247,

-8848127.56979024,5410620.68753073,-8848129.13605997,5410629.82800551,-8848130
.70288521,5410638.96852313,-8848132.54982926,5410649.74544373,-8848134.2689430
8,
5410659.77278883,-8848135.98754211,5410669.79956033,-8848137.70611208,5410679.
82689556,-8848139.42423359,5410689.852553,-8848141.14336388,5410699.87994471,
-8848142.86201253,5410709.90621071,-8848144.58115105,5410719.93362571,-8848146
.29925584,5410729.95988184,-8848148.01840259,5410739.98732014,-8848149.7365156
1,
5410750.01359957,-8848151.45567058,5410760.04106116,-8848153.173825,5410770.06
68118,-8848155.02134579,5410780.84448136,-8848156.58822751,5410789.98515893,
-8848158.15456056,5410799.125813,-8848159.72144913,5410808.26650993,-8848161.2
8837428,5410817.40666444,-8848162.85471759,5410826.54734756,-8848164.42161641,

5410835.68807353,-8848166.06435338,5410845.27275742)),
  0.5 -- road to buffer intersect tolerance
  ), 10, 1.0 -- buffer around the road segment and tolerance
  ) from dual;

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.