Last updated on FEBRUARY 08, 2017

Applies to:

Symptoms

When executing sdo_geor.getCellValue having bad performance

set timing on
set serveroutput on
declare
i int;
r sdo_georaster;
  x number;
  y number;
  v number;

begin
  SELECT raster into r
  from TST_LAY_GRD_88
  where id = 1;

  for i in 0..999
  loop
  x := 150+i;
  y := 150+i;
  v := sdo_geor.getCellValue(r, 0, x, y, 0);
  end loop;
end;
/

The trace indicates the bad performance is in:

SELECT count(*)
FROM
all_object_tables WHERE owner=:1 AND table_name=:2 AND table_type=:3


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.41 0.40 0 0 0 0
Fetch 1000 25.38 25.84 0 28000 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2001 25.79 26.24 0 28000 0 1000


Is there a way to avoid that recursive statement or how to improve its performance?

Cause