CODEWITHSUNDEEP
sqlvieworacle19coracle-spatialresponse-time
Erik
Erik

Reputation: 11

Performance issue: High number of roundtrips when fetching data from view

In a Cadastral application several tables are joined to a view, MATRIKELSKEL_SAG, to ease client calling. Some of the tables use Oracle Spatial data structures, MDSYS.SDO_GEOMETRY.

When calling the view returning comparable number of rows we see order of magnitude change in number of roundtrips measured with auto trace in SQL Plus. In all our measurements high number of roundtrips between Oracle client and Oracle server are reflected in high response times as documented below.

The Oracle client is version 19.3.0.0.0 running on Windows Server 2016.
The Oracle server is version 19.15.0.0.0 running on RHEL 7.9

The SQL Plus autotrace script used is defined by:

set autotrace traceonly statistics
set serveroutput off
set echo off
set line 200
set array 1000
set verify off
timing start

SELECT * FROM MATRIKELSKEL_SAG WHERE SAGSID=<sagsID>;

timing stop

where sagsID is either 100143041 or 100149899

Measurements

Here are our measurements, call them measure_A and measure_B.

Measure_A: sagsId = 100143041

25118 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     792108  consistent gets
       2149  physical reads
        528  redo size
   65322624  bytes sent via SQL*Net to client
   14001426  bytes received via SQL*Net from client
     175039  SQL*Net roundtrips to/from client
      23098  sorts (memory)
          0  sorts (disk)
      25118  rows processed

Elapsed: 00:01:07.54

Measure_B: sagsId = 100149899

30021 rows selected.

Statistics
----------------------------------------------------------
        180  recursive calls
          0  db block gets
     324173  consistent gets
       2904  physical reads
        396  redo size
    6000615  bytes sent via SQL*Net to client
       2681  bytes received via SQL*Net from client
         59  SQL*Net roundtrips to/from client
      27988  sorts (memory)
          0  sorts (disk)
      30021  rows processed

Elapsed: 00:00:03.16

Since the number of rows only differ by ~25% (25118 compared to 30021) we would expect some metrics to only differ in range of ~25%.

Observation 1

While 65Mb is sent to SQL Plus client in measure_A, only 6Mb is sent to client in measure_B. This may be an indication of an issue.

Observation 2

While measure_B has 59 roundtrips, measure_A has 175039 roundtrips, up by factor 2966. Since arraysize is set to 1000 we would expect 30021/1000 + handshake + overhead for measure_B. We see 59 roundtrips which is ok. For measure_A we would expect 25118/1000 + handshake + overhead = ~55. But we see 175039 roundtrips. This is definitely a puzzle.

Observation 3

Despite ~comparable physical reads and consistent gets response time is 1m7s in measure_A compared to 3s in measure_B.

Our questions

  1. Why do we see a factor 2966 up in roundtrips in measure_A compared to measure_B, when returned bytes is only up a factor of 10?
  2. Why do we see a factor 22 up in response time in measure_A compared to measure_A, when returned bytes is only up a factor of 10?

We can provide definition of view if needed.

Upvotes: 1

Views: 433

Answers (2)

Erik
Erik

Reputation: 11

The issue has been reported to Oracle Support as well as demoed to Oracle Support. After demo Oracle Support are able to reproduce issue in internal Oracle environments and an Oracle service request and bug is raised:

  1. SR 3-34190423731: Performance degrades when fetching Spatial data.
  2. Bug 36132342

Upvotes: 0

Albert Godfrind
Albert Godfrind

Reputation: 2078

This is probably because the size (= complexity, i.e. the number of vertices). The more vertices, the more data to send to the server.

You can get a feeling of that by running this query:

select 
  sagsid,
  count(*), 
  sum(points), min(points), avg(points), median(points), max(points), avg(points), median(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 will return numbers about the number of points and the size of geometries in bytes for each SAGSID.

Its should help you understand what is happening and explain your observations.

As for optimizing the process, there are settings you can use for the SQLNET layer. See https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/optimizing-performance.html

Other things to check:

  • The complexity of the shapes. Depending on the nature of the shapes you are using, you may be able to simplify them, i.e. reduce the number of vertices. If the data is automatically digitized from photos or imagery, they maybe over complex wrt the needs of your application.

  • The number of decimals in the geometries you use. If they are automatically digitized or if they are transformed from another coordinate system. It is possible to reduce those and so make the geometries more compact with less data to transfer to the client.

Upvotes: 0

Related Questions