The procedure for Oracle is in principle similar to Informix and MaxDB. There are two SAP cursor caches:
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.
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.
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.
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.
Executing a Dynamic SQL Statement
With dynamic SQL statements, no statement ID is created.
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.