English

How To: Disable ArcSDE from holding Oracle logfile queries

Summary

To optimize performance and scalability ArcSDE 9 holds open numerous Oracle cursors. When a database cursor is not held open and is closed on each execution, the act of opening and closing the cursor consumes a significant amount of system resources and prevents a highly scalable architecture.

While ArcSDE 9 improves performance by holding various shareable cursors, some cursors that are held open, may not be ideal candidates to be held open if the cursors are not shareable among all users on the system. For example, ArcSDE logfile queries are held open because of the possibility of the cursor being executed over and over, but are not shareable because the queries are unique per user.

It is optimal to hold the cursor open, but holding the cursor open consumes valuable memory in Oracle's shared pool and each user's dedicated process. The memory consumption is minor, but if an organization has hundreds or thousands of users holding each of their individual cursors, the total cumulative memory will be significant. Instructions provided describe how to disable ArcSDE from holding Oracle logfile queries.

The following is an example of just some of the ArcSDE logfile cursors which are held open over the life span of a connection:

SELECT <username>.sde_logfile_lid_gen.NEXTVAL
FROM DUAL

SELECT logfile_name, logfile_id, logfile_data_id, registration_id, flags, session_tag, logfile_data_db, logfile_data_owner, logfile_data_table
FROM <username>.sde_logfiles
WHERE logfile_name = :log_name

INSERT INTO <username>.sde_logfile_data (logfile_data_id,sde_row_id) VALUES (:data_id, :row_id)

DELETE FROM <username>.sde_logfiles
WHERE logfile_name = :b1

DELETE FROM <username>.sde_logfile_data
WHERE logfile_data_id = :b1

Because the <username> is unique for each user, the cursor is not shareable, and it may become advantageous not to hold the cursors open when the Oracle database administrator (DBA) cannot size the shared_pool accordingly.

The DBA can investigate the total amount of memory being consumed by a cursor by viewing the sharable_mem column in the V$SQLAREA view.

Procedure

  1. To disable ArcSDE from holding logfile cursors open, simply set the environment variable SDEDISLOGCUR in the ArcSDE instance's $SDEHOME/etc/dbinit.sde file.

    Example:
    set SDEDISLOGCUR=1
  2. Restart ArcSDE.

    When the environment variable is defined, no ArcSDE logfile cursors will be held open.