English

How To: Improve the performance of ArcSDE with Oracle

Summary

Instructions provided describe how to troubleshoot and improve the performance of ArcGIS 9.x when working with ArcSDE 9.x and Oracle 9i/10g.

Procedure

  • Check the free RAM available on the client and server machines. The computer with ArcGIS needs to have at least 512 MB free and the computer with ArcSDE server needs to have at least 1 GB free. Check the available memory by using Windows Task Manager. Free up RAM on the computer by closing non-related applications on the client or by adjusting memory allocated on the server for Oracle (SGA and PGA values) in Enterprise Manager. Test if there are any performance improvements.
  • Increase the frequency of compressing the database. See the link in the Related Information section below for more information on how to compress a versioned database to state. After doing a compress, test the field calculation and see if performance improves. For datasets with frequent editing, a weekly compress of the database often improves performance.
  • Improve ArcSDE performance by rebuilding indexes on tables. See the link in the Related Information section for more information. After indexes are rebuilt, see if there is any change in performance.
  • Increase the frequency of updating statistics on feature classes with the 'Analyze' function in ArcCatalog: right-click on a feature class, select Analyze and select all tables.
  • Check the network traffic between the ArcSDE server and the client applications. Does performance depend on the number of users connected to the server? High network traffic or slow connections may be impacting performance at certain times of the day.
  • Test using a direct connect to the database in ArcCatalog with the following parameters and test if performance is different:

    For Oracle9i and Oracle10g client software on the local machine with ArcSDE prior to 9.2 use:

    Code:
    Server : <blank>
    Service : sde:oracle9i
    Database : <blank>
    Username : sde
    Password : <SDE_User_Password>@<NetServiceName>

    For Oracle10g client software on the local machine with ArcSDE 9.2 use:

    Code:
    Server : <blank>
    Service : sde:oracle10g
    Database : <blank>
    Username : sde
    Password : <SDE_User_Password>@<NetServiceName>

  • Increase performance displaying selected sets in ArcGIS sub-selected features from ArcSDE feature classes. See the link in the Related Information section for more information.
  • Test changing the Oracle parameter 'optimizer_mode' from CHOOSE to FIRST_ROWS or ALL_ROWS.
  • In giomgr.defs, change MINBUFSIZE & MAXBUFSIZE to the new values below and import in the new values using ArcSDE command:

    Code:
    MINBUFSIZE 409600 # minimum buffer size > 4096
    MAXBUFSIZE 819200 # maximum buffer size > MINBUFSIZE

    Use the sdeconfig command to import in the new values above. For example:

    Code:
    sdeconfig -o import -f C:\arcgis\ArcSDE\ora9iexe\etc\giomgr.defs -i 5151 -D DBOG -u sde -p sde

  • Check that the latest Service Packs for ArcSDE 9.x and ArcGIS 9.x are applied. See the links in the Related Information section for more information.
  • Perform an Oracle Session Trace and run the TKPROF utility to check which SQL statements are taking the most time.

Related Information