NIM010953 for No Product Found

Bug ID Number	NIM010953
Submitted	August 14, 2007
Last Modified	April 2, 2025
Applies to	No Product Found
Version found	9.2
Version Fixed	9.3
Status	Fixed

Description

Executing sdeversion -o compress or the compress command from ArcCatalog results in a Network I/O error when running on ArcSDE for Oracle.

Cause

The compress operation can encounter a Network I/O error when the process is unable to open an Oracle cursor for executing SQL SELECT or DML (INSERT, UPDATE, DELETE) statements.

ArcSDE for Oracle is highly optimized for performance and scalability by maintaining a set of open cursors for performing frequently executed SELECT and DML statements. If Oracle cursors are not preserved and closed after every execution, this prevents the Oracle instance from scaling to support a large number of concurrent users.

Workaround

If the compress operation encounters a Network I/O error, the first step is to ensure that the value for the Oracle init.ora parameter open_cursors is set to a relatively large value. The value one needs to set for the parameter is dependent upon the number of registered tables in the ArcSDE instance.

Oracle's default value of 300 is typically too low. Setting the open_cursors parameter to a value of 2000 or 3000 should be sufficient. There is no harm setting the value too large. A larger value does not consume additional resources. System resources are not consumed until a cursor is explicitly opened.

To check the value for the open_cursors parameter, one can connect in SQL*Plus as the SYS or SYSTEM user and execute the following command:

Code:
SQL> SHOW PARAMETERS open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
open_cursors                         integer     300

SQL>

To alter the parameter to increase the value, use the ALTER SYSTEM command:

Code:
SQL> ALTER SYSTEM SET open_cursors = 2000;

System altered.

SQL>

If the Oracle instance is using a spfile.ora for managing instance parameters, make sure to include the 'scope = both' when executing the ALTER SYSTEM command.

Code:
SQL> ALTER SYSTEM SET open_cursors = 2000 scope = both;

System altered.

SQL>

Once the parameter has been set, execute the compress command. If the compress operation continues to encounter a Network I/O error, contact ESRI Support Services.