Oracle视图MATRIKELSKEL_SAG数据获取往返次数过高性能问题咨询

背景 #

在地籍应用中，多个表被关联至视图MATRIKELSKEL_SAG以简化客户端调用，部分表使用Oracle Spatial数据类型MDSYS.SDO_GEOMETRY。调用该视图返回行数相近时，通过SQLPlus的autotrace测量发现SQLNet往返次数存在数量级差异，且高往返次数对应高响应时间。

环境信息 #

Oracle客户端版本：19.3.0.0.0，运行于Windows Server 2016
Oracle服务器版本：19.15.0.0.0，运行于RHEL 7.9

使用的SQL*Plus Autotrace脚本 #

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

其中sagsID取值为100143041或100149899。

测量数据 #

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

异常观测 #

两组查询返回行数仅相差约25%（25118对比30021），预期相关指标差异应在25%左右，但实际观测到：

• measure_A向SQL*Plus客户端发送65MB数据，measure_B仅发送6MB，数据量相差10倍；
• measure_B的SQLNet往返次数为59次，measure_A则达175039次，相差2966倍；结合设置的arraysize=1000*，measure_A预期往返次数约为55次，实际值远高于预期；
• 尽管物理读和一致性读数据相近，measure_A响应时间为1分7秒，measure_B仅为3秒，相差22倍。

问题 #

1. 为何measure_A的SQL*Net往返次数较measure_B高出2966倍，而返回数据量仅高出10倍？
2. 为何measure_A的响应时间较measure_B高出22倍，而返回数据量仅高出10倍？

注：可提供视图定义。

内容的提问来源于stack exchange，提问作者Erik