topic Re: Oracle Direct Connect - Slow Performance in ArcGIS Enterprise Questions

Oracle Direct Connect - Slow Performance

AndrewHaigh — Wed, 29 Feb 2012 13:19:36 GMT

In the have the following scenario:

- Machine 1: Windows 2008 R2 Service Pack 1; Oracle 32-bit Client 11.2.0.2; ArcGIS Server Enterprise Advanced 10 Service Pack 3
- Machine 2: Red Hat Enterprise Linux 5.5; Oracle 64-bit Database Server 11.2.0.2; ArcSDE 10 Service Pack 3

Machine 1 connects to Machine 2 using ArcSDE Direct Connect.

When Machine 1 and Machine 2 are in the same VLAN they work fine, when they are in different VLAN's (with all IP TCP / UDP traffic allowed) it takes about five minutes for Machine 1 to connect to Machine 2.

I have run traces using SDE Intercept and the delay seems to be around the following query:

======================================== [W 30:18.053] Command:      QueryWithInfo [W 30:18.053] Long:         1 [W 30:18.053] Query Info:   Num Columns:   1  Columns:       "*"  SQL_Construct: [1]  Tables:        "SDE.GDB_Items"  WhereClause:   "Name = 'Workspace'"  Query Type:    4  ByClause:      <null>  Num Hints:     0  Num Parameter markers: 0  Logfile:       <null> [R 33:52.580] Long:         0 [W 33:52.596] Col_Defines:  [17]  Name                                 Type    Width nDec  NULL?   RowID  -------------------------------- ----------- ----- ---- -------- -----  OBJECTID                         SE_INT32       10   0  NOT NULL  SDE   UUID                             SE_UUID        38   0  NOT NULL        TYPE                             SE_UUID        38   0  NOT NULL        NAME                             SE_NSTRING    226   0      NULL        PHYSICALNAME                     SE_NSTRING    226   0      NULL        PATH                             SE_NSTRING    512   0      NULL        URL                              SE_NSTRING    255   0      NULL        PROPERTIES                       SE_INT32       10   0      NULL        DEFAULTS                         SE_BLOB         0   0      NULL        DATASETSUBTYPE1                  SE_INT32       10   0      NULL        DATASETSUBTYPE2                  SE_INT32       10   0      NULL        DATASETINFO1                     SE_NSTRING    255   0      NULL        DATASETINFO2                     SE_NSTRING    255   0      NULL        DEFINITION                       SE_XML          0   0      NULL        DOCUMENTATION                    SE_XML          0   0      NULL        ITEMINFO                         SE_XML          0   0      NULL        SHAPE                            SE_SHAPE        0   0      NULL        -------------------------------- ----------- ----- ---- -------- -----

However, when I use SQL Plus to connect it connects without delay and the query returns within a second.

Any suggestions as to what may be causing the issue?

Thanks

Andrew

Re: Oracle Direct Connect - Slow Performance

RichNezelek — Mon, 22 Oct 2012 19:15:49 GMT

Aloha - Any resolution ever found for this problem? I'm having the exact same issue/error connecting ArcGIS 10.1 to a 9.3 Oracle database.

Thanks- Rich
Pacific Disaster Center

Re: Oracle Direct Connect - Slow Performance

AndrewHaigh — Tue, 23 Oct 2012 04:53:34 GMT

Hi,

It turned out that our issue was down to a known bug in the version of the Cisco Firewall Service Module, we used which sat between the ArcGIS Server and the Database Server.

Upgrading the Cisco Firewall Service Module version, resolved the problem.

Not sure if this will be helpful or not.

Andrew

Re: Oracle Direct Connect - Slow Performance

RichNezelek — Fri, 26 Oct 2012 19:44:00 GMT

Thanks, Andrew.

Doesn't appear to be any firewall or network issue here. I installed Arc 10.1 on the same machine as the Oracle 9.3 SDE database and the time to establish a connection was still terrible.

Re: Oracle Direct Connect - Slow Performance

AndrewHaigh — Sat, 27 Oct 2012 06:32:15 GMT

I've also had similar issues, if there are a large number of feature classes >100 due to the pre caching Arc does and also if the client is connecting to the database over a WAN link which has a high level of latency.

It might be work using SDE intercept on the client, as we saw an obvious pause in the log when we were connecting.

Re: Oracle Direct Connect - Slow Performance

AndrewHaigh — Sat, 27 Oct 2012 06:35:51 GMT

Ok so you've run SDE intercept, have you tried to run the query in SQL plus from the same machine - does that perform ok? If you can run wireshark or something on the client, as we only spotted the network issue by doing this, packets were getting very delayed.