Query SDO_RELATE Spatial Function Is Slow At 11.2.0.3 (Doc ID 1982891.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

Query SDO_RELATE Spatial Function Is Slow at 11.2.0.3

Upgrade to Oracle 11gR2 results in queries spatial running much slower in compared to Oracle 10g.

Queries with sod_relate function are very slow in production environment taking more than 15 minutes.
There are around 1.5 million records.

Index quadtree takes 2 seconds at 10g and 15 minutees at 11gr2.

Example :
CREATE INDEX T1E12_01_SI ON T1E12_CONTR_POLGN
(DG_GEOMT)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('tablespace=ARP_EDI_S07_U16M initial=16m next=16m pctincrease=0 minextents=1 maxextents=UNLIMITED sdo_commit_interval=10000 sdo_level=6 sdo_fanout=60 sdo_max_memory=25000000 sdo_rtr_pctfree=0 ')
PARALLEL 20;

SELECT t1e12.no_contr_polgn
 FROM t1e12_contr_polgn t1e12
 WHERE t1e12.co_echel = 20
  AND sdo_relate (t1e12.dg_geomt,
  mdsys.sdo_geometry (
  2003,
  1000001,
  NULL,
  mdsys.sdo_elem_info_array (1, 1003, 3),
  mdsys.sdo_ordinate_array (45920.48173103843,
  983430.3131500725,
  46056.955974961565,
  983501.2658899275)),
  'mask=anyinteract querytype=window') = 'TRUE';

The platform was also changed from Linux to SPARC

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms