Oracle Direct Connect - Slow Performance

[ahaigh]

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

[richnezelek]

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

[ahaigh]

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

[richnezelek]

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.

[ahaigh]

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.

[ahaigh]

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.