Show TOC

 SAP Cursor Cache for Oracle, Informix, MaxDB and DB2 for AIXLocate this document in the navigation structure

Purpose

The procedure for Oracle is in principle similar to Informix and MaxDB. There are two SAP cursor caches:

  • Statement ID cache
  • Statement cache

All the statement IDs are stored in the statement ID cache. Each statement ID in the statement ID cache is mapped to an entry in the statement cache. The total number of entries in the statement ID cache is five times that of the statement cache. Both the statement ID cache and statement cache adopt the LRU (Least Recently Used) displacement.

The statement cache is an array of SQL statement structures or strings.

Note

SELECT * FROM T100 WHERE MSGNR = :A001

In this example, table 'T100' and field 'MSGNR' are defined. The host variable 'A001' uses Oracle syntax.

Input variables such as '?' are used for Informix and DB2 for AIX. The DBSL (Database SQL Library) assigns the actual values to the variables. The output values are given descriptors and are allocated to the output buffers by the DBSL.

The number of entries in the statement cache is determined by the instance parameter (this is not the case with DB2 for AIX), where dbs/stmt_cache_size = 250 (default value). You should not change this value.

Note

DB2 for AIX: The number of statement cache entries is defined at 290.

The memory space of each entry in the statement cache is allocated dynamically. The maximum size of each entry is 64KB.

Each statement cache entry is mapped to the corresponding cursor, which points to a context area in the shadow process. This context area contains the representation of the parsed SQL statement.

The statement ID cache, statement cache, and cursor cache are in each SAP work process. The context areas are in the shadow process. Each SAP work process owns a shadow process.

The SAP cursor cache is an additional layer before using the database cursors.

Process

Executing a NormalSQL Statement:

A normal SQL statement is a statement with statement ID (may or may not be normalized by the Statement Analyzer):

DBSL uses the statement ID and searches the statement ID cache for a match.

  1. If the ID is found, the corresponding cursor is executed. The process is now finished.
  2. If the ID cannot be found, it is inserted into the statement ID cache.
  3. Using string comparison, the DBSL search the statement cache for an identical SQL statement structure.
  4. If it is found, map the statement ID in the cache to this SQL statement structure, which is already mapped to an existing cursor. Execute the cursor. You have now completed the procedure.
  5. If it is not found, parse the SQL statement and execute it. The corresponding SQL statement structure will also be created and inserted into the statement cache. It is mapped to the corresponding cursor. The statement ID in the statement ID cache is mapped to this SQL statement structure.

Executing a Dynamic SQL Statement

With dynamic SQL statements, no statement ID is created.

  1. DBSL search the statement cache (using string comparison) to find an identical SQL statement structure or string.
  2. If it is found, the corresponding cursor is executed. The process is now finished.
  3. If an identical structure cannot be found, the SQL statement is parsed and executed. The SQL statement structure is created and inserted into the statement cache. This SQL statement structure is then mapped to the cursor that was created.
    Note

    DB2 for AIX: A DB2 agent process is assigned to every work process. The agent processes have access to the DB2 Cursor Cache, in which the access information for each 'prepared' statement is stored.