SQL Source Cache
Database Administrators (DBAs) use the SQL Source Cache to reduce MUF resource consumption and lower response times.
Database Administrators (DBAs) use the SQL Source Cache to reduce MUF resource consumption and lower response times. The SQL Source Cache is a collection of previously executed dynamic queries from
CA Datacom® Serverand the batch utility DBSQLPR. Additionally, queries executed under the DDOL Interactive SQL Service Facility are also enabled for SQL Source Cache participation. Queries that are stored in the SQL Source Cache and match new queries reduce the preparation (bind) cost. Statistics that are accumulated for these queries allow inefficient queries to be identified.
Do not confuse the SQL Source Cache with the Least Recently Used (LRU) Statement Cache. The LRU Statement Cache reduces the overhead of executing "static" SQL statements by avoiding access to the DDD table. The SQL Source Cache, however, is a collection of "dynamic" statements. The two separate caches do not share statements. Referring to a cache as simply "the Cache" refers to the SQL Source Cache.
Access to the Cache is through using a group of Dynamic System Tables (DSTs). All the Cache DSTs have an authorization ID of SYSADM and begin with the prefix SQLSC (SQL Source Cache).
To be as effective as possible, the Cache replaces literals in the SQL source string that is used to match queries in the Cache, with a unique symbol (@) to allow a match regardless of the literal value. For example, if this query:
select * from cars where color = ‘RED’ and make = ‘FORD’
is stored in the Cache, this new query would match:
select * from cars where color = ‘PINK’ and make = ‘FORD’
Because the two queries are considered matches, the bind process is avoided by using a copy of the same plan that the first query generated. Also, statistics are accumulated to only one query. Both queries were probably generated by the same application that substituted the different input selection colors.
This reuse of the same plan does not prevent selection of the most efficient index, because index selection is still performed by the Compound Boolean Selection Facility (CBS) when different literal values could cause a different index to be most efficient. In this example, CBS would select the COLOR index over the MAKE index for the second query, even if the first query used the MAKE index.
To use the SQL Cache feature, either add the MUF startup options SQL_SOURCE_CACHE_SIZE and SQL_SOURCE_CACHE_STMTS to your MUF SYSIN or accept the defaults.
The values of the SQL Source Cache MUF startup options can be dynamically updated while MUF is running by using the SQL_CONSOLE (SQX) dynamic system table. The change takes effect when the Cache is next searched.
The following example shows the use of the SQL_CONSOLE table:
insert into sql_console values ('mufname', 'SQL_SOURCE_CACHE_STMTS nnnnnn'); insert into sql_console values ('mufname', 'SQL_SOURCE_CACHE_SIZE nnnnnn')
mufnamevariable is the internal MUF name. The valid values for the
nnnnnnvariables are 0 through 100M for SQL_SOURCE_CACHE_SIZE and 0 through 100 K for SQL_SOURCE_CACHE_STMTS.
Although you can dynamically update the
nnnnnnspecification, a dynamic update, in this case, only changes the Master List, not the SQL structures that use the dynamically updated limit. The SQL structures are changed when an INSERT or SELECT statement is processed in
CA Datacom® Serveror the batch utility DBSQLPR. Therefore, while a console command posts the change, the change does not take effect until the next INSERT or SELECT is processed in
CA Datacom® Serveror the batch utility DBSQLPR. The SQLSC_FACILITY (SCF) Dynamic System Table column SCF_MEMORY_SIZED_TS shows the time when the change takes place, not the time the console command was issued.
When the Cache is full and a new query is inserted, the least recently used (LRU) query or queries are purged from the Cache. The SQLSC_FACILITY (SCF) table (DST) can be used to compute the hit ratio. The hit ratio is the ratio of the number of times the Cache is searched, compared to the number of times a match was found.
Not all queries qualify for caching. The Cache is used in cursor-related SQL statements, that is, statements declaring, opening, fetching, and closing cursors. However, when some features are used in a query, the literal replacement process cannot be used. As a result, that query is never added to the Cache and is always bound.
The Source Cache, which skips the bind process, is not used when the SQL Optimization Report is requested.
The Cache is also used in the INSERT VALUES statement.
Queries not cached
Queries with the following syntax are not cached:
- Special Registers SPLREG_DCM_TSN and SPLREG_DCM_MUF_NAME
- Labeled Durations
- predicates comparing between UNSIGNED NUMERIC and CHAR data types
- Expressions containing only literals. Examples:
- column1 = 3 * 4
- coalesce(column1, '09') ←Each operand is an expression, so '09' is a literal-only expression
- column1 LIKE 'XXX%' ←converted to column1 = 'XXX'
- column1_length_3 = '1234' ←special processing used when the literal is longer than the column
For more information see: