Spatial Queries Led Latch: Cache Buffers Chains In Oracle Locator

(Doc ID 1305575.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 04-Mar-2013***

Symptoms

Slow performance running SQL with spatial operator

SELECT... FROM ... WHERE ... AND SDO_INSIDE(p.geom, area.geom) = 'TRUE';

or any other SDO function.

Symptoms:

1. Number of executions of recursive query:

SELECT DIMINFO FROM ALL_SDO_GEOM_METADATA
WHERE OWNER = :own AND TABLE_NAME = :tab AND COLUMN_NAME = :col"

is very large and the CPU consumption is also relatively high, considering the user query is only executed once. Due to this behaviour while the user query is running several warnings including latch: cache buffers chains are received and the CPU goes to 100%

This recursive statement is called once per row if an SDO operator is executed without a spatial index. So you will see a lot of such queries, if a spatial index is not chosen. But this is changed in new releases (12 and higher) putting DIMINFO into KGL cache. We won't see such sql queries in the next Oracle release. This will not be backported to 11.2 release.

2. The Execution Plan shows that the Spatial index is not used even with INDEX hint in the query the spatial index is not used. The optimizer chooses a Full Table Scan instead and use the SDO operation as a filter not as a predicate.

3. When the spatial index is used and the user query runs fast the recursive statement is slightly different:

SELECT diminfo, nvl(srid,-1) FROM ALL_SDO_GEOM_METADATA
WHERE OWNER = :own AND TABLE_NAME = NLS_UPPER(:tab) AND '"'||COLUMN_NAME||'"' = :col"

Similarly, we will not see it in later releases because this is put in KGL cache.

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