English

Bug: Executing a compress when running on ArcSDE for Oracle results in a Network I/O error

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.

    Related Information