Understanding Performance Issues in Oracle Spatial Data Structures

Introduction

As a developer, you strive to provide high-performance applications that meet user expectations. When working with Oracle Spatial data structures, such as MDSYS.SDO_GEOMETRY, it’s essential to understand the underlying performance issues and how to optimize them. In this article, we’ll delve into the details of performance issues related to fetching data from views in an Oracle Cadastral application.

Background

Oracle Spatial is a feature that enables spatial data processing and analysis. It provides advanced capabilities for working with geospatial data, including geometry storage and manipulation. MDSYS.SDO_GEOMETRY is one of the data types used in Oracle Spatial, which represents geometric shapes as complex structures composed of multiple attributes.

In our case study, we’ll focus on a Cadastral application that joins several tables to a view called MATRIKELSKEL_SAG. This view simplifies client-side interactions by providing a single source for various spatial data elements. However, the application experiences high response times when fetching data from this view, which is attributed to a large number of roundtrips between the Oracle client and server.

The Problem

Our question is straightforward: Why do we see a significant increase in roundtrips when fetching data from the MATRIKELSKEL_SAG view, despite similar numbers of rows being returned? To understand this issue, let’s examine the provided measurements (Measure_A and Measure_B) and break down their key statistics.

Measurements

The measurements reveal that both measures return approximately the same number of rows. However, Measure_A experiences a significant increase in roundtrips (175,039 vs. 59) and response time (1 minute 7 seconds vs. 3 seconds).

Analysis

To understand these observations, let’s analyze the data structure and query executed in the provided code snippet:

SELECT 
  sagsid,
  count(*), 
  sum(points), min(points), avg(points), median(points), max(points), 
  sum(bytes), min(bytes), avg(bytes), median(bytes), max(bytes), avg(bytes), median(bytes)
FROM (
  SELECT sagsid, sdo_util.getnumvertices(geom) as points, vsize(geom) as bytes 
  FROM matrikelskel_sag 
  WHERE sagsid IN (100143041, 100149899)
)
GROUP BY sagsid;

This query calculates various statistics about the geometry sizes and points for each SAGSID. By running this query, we can gain insight into the size and complexity of geometries returned by the view.

Explanation

The significant increase in roundtrips between Measure_A and Measure_B is likely due to the size (complexity) of the geometries being transferred. As mentioned earlier, MDSYS.SDO_GEOMETRY represents geometric shapes as complex structures composed of multiple attributes.

When we examine the statistics calculated by the query above, we notice that:

• The number of points varies significantly between rows.
• The geometry sizes in bytes also exhibit considerable variation.

These differences suggest that some geometries are more complex than others. When transferring these complex geometries to the client, Oracle Spatial generates additional roundtrips to ensure accurate representation and processing.

Moreover, when it comes to response time, both physical reads and consistent gets have similar values, but the actual execution of queries differs significantly due to differences in geometry sizes.

Conclusion

To optimize performance issues related to fetching data from views like MATRIKELSKEL_SAG, we must focus on simplifying geometries and reducing their size. This can be achieved by:

• Simplifying shapes: Depending on the nature of your application, you may need to simplify the shapes used in your dataset.
• Reducing decimal places: If the geometry data is automatically digitized or transformed from another coordinate system, consider reducing the number of decimal places.

By addressing these factors, you can reduce roundtrips and improve response times for fetching data from spatial views in Oracle Spatial applications.

Last modified on 2023-08-05