ArcSDE ST_Geometry functions slow on AIX server in compare to windows server

[MatjazHabic]

I made simple test to measure execution speed of ArcSDE ST_xxx functions on different platforms. The winner in this test is small noname windows server which is twice faster than AIX server. Procedure execution time on Windows is 2 seconds on AIX 4 seconds. Windows environment is twice faster.

Is there any explanation for this behavior ?

Test script

set timing on;
DECLARE
    counter INTEGER := 2;
    pov number;
BEGIN
    FOR counter IN 1..1500 LOOP
       SELECT sde.st_area(sde.st_geometry ('POLYGON  (( 411008.71460000 4882616.50360000, 411005.20060000 4882619.67950000, 411003.73240000
            4882621.00760000, 411000.85260000 4882625.33210000, 410995.91110000 4882618.18640000, 410989.01930000 4882611.21530000, 410977.44560000
            4882595.75050000, 410962.63320000 4882575.95560000, 410970.59060000 4882567.36640000, 411005.47230000 4882604.73780000, 411010.73030000
            4882608.94600000, 411013.15940000 4882612.48560000, 411008.71460000 4882616.50360000))',5)) into pov from  dual;
    END LOOP;
END;
/


Here are configurations of both environments:



Development environment

windows 2003 server 32  bit (intel core 2 QUAD @[email protected])
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
ArcSDE Version                    9.2
ArcSDE Server Build                for Oracle10g Build 1206

Listener.ora

SID_LIST_LISTENER =
   (SID_LIST =
  (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
    (PROGRAM = extproc)(ENVS="EXTPROC_DLLS=ONLY:C:\arcgis\ArcSDE\ora10gexe\bin\st_shapelib.dll")


Test environment

IBM Power 6, 4 CPU 4,2 GHz 64 bit , aix 5.3.9
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 64 bit
ArcSDE Version                    9.2
ArcSDE Server Build                for Oracle10g Build 1271

Listener.ora
SID_LIST_LISTENER =
   (SID_LIST =
  (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /opt/oracle/app/oracle/product/10g/Db_1)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=/opt/oracle/app/oracle/product/10g/Db_1/lib/libst_shapelib_64.so")
  )
   )

[VinceAngelo]

It's usually best to only have one independent variable when doing statistical evaluations.
You've got closer to five (host architecture, operating system, wordsize, Oracle release,
and ArcSDE release). 

Evaluation of 1500 *different* shapes would be less of a caching exercise -- do the two
machines have the same amount of RAM and identical Oracle memory tuning? 

Are the character sets identical? If one was using 16-bit characters and the other 8-bit,
that could easily account for the 2x performance difference.

Did you run each query locally, or from a 3rd host to control for remote client use?

How are you capturing the benchmark duration?  Unix has the 'time' utility, but then
you're also measuring the time to start the sqlplus utility.  The 'sdesql' utitlity of
se_toolkit has a subsecond resolution timer (millisecond resolution on Windows,
microseconds on Unix), but it doesn't support PL/SQL directly.

It would probably be more interesting to see 150,000 inserts, indexing, and 50 random
spatial queries on 64-bit/RISC/Unix, 32-bit/RISC/Unix, 64-bit/Intel/Windows, and 32-bit/
Intel/Windows, all with the same RAM and system load and using Oracle 10.2.0.3 (same
CPU) and ArcSDE 9.2 (same service pack & patch).  If you go one step further, and run
Linux on the Windows host, you'll come the closest to an apple-to-apples comparison.

- V

[MatjazHabic]

Hi Vince
thank You for Your quick response. Here are some answers.

Character sets are identical:  AL32UTF8

I run query locally on each server.

Benchmark duration is measured by oracle (set timing on;)
and result is show as bellow:

on Windows:
PL/SQL procedure successfully completed.

Elapsed: 00:00:02.84
SQL> /

on AIX:
PL/SQL procedure successfully completed.

Elapsed: 00:00:05.01
SQL> /

I think this is not memory related problem. I observe high numbers in "HS message to agent" wait event in Oracle and I think it is related with calling external procedures (st_shapelib.dll and  libst_shapelib_64.so).

regards
Matjaz

[VinceAngelo]

I ran your SQL on 32-bit and 64-bit Linux hosts (virtual and physical, respectively) running
Oracle 11.1.0.7 with ArcSDE 9.3.1sp2 (32-bit) and ArcSDE 10.0sp0 (64-bit) and saw
execution times of 7.5 and 1.6 seconds.

Whatever the cause for the difference, it certainly doesn't look like anything that Esri can
control.

- V