A plan cannot be rebound when in use. If (for a CICS application) R is specified for the SQL Preprocessor's PLNCLOSE= option, the plan remains in use until the SQL User Requirements Table is closed.
To determine which plans are being used, you can use CA Datacom/DB Utility's (DBUTLTY) COMM ALTER option as follows:
Using COMM ALTER to Determine Plans Being Used
►►─ COMM OPTION=ALTER,TRACE=TRACEGLOBAL ──────────────────────────────────────►◄
This writes a report of all open plans (plus various other information about the state of the SQL subsystem) to the CA Datacom/DB Statistics and Diagnostics Area (PXX) when the SQL User Requirements Table is closed. Print the report using CA Datacom/DB Utility's (DBUTLTY) REPORT AREA=PXX option with FULL or TRACE specified for the DUMPS= keyword.
To turn off this option, use CA Datacom/DB Utility's (DBUTLTY) COMM ALTER option:
Using COMM ALTER to Turn Option Off
►►─ COMM OPTION=ALTER,TRACE=NONE ─────────────────────────────────────────────►◄
Note: For more information about using the CA Datacom/DB Utility (DBUTLTY), see the CA Datacom/DB DBUTLTY Reference Guide.
LRU Statement Cache
The LRU (Least Recently Used) statement cache option enhances caching of statements by using a user-specified amount of memory more efficiently. When the LRU option is not used, statements are left in memory until the plan is closed, which can cause the Multi-User Facility to run out of memory by holding on to statements that are rarely re-executed. With the LRU option, however, the amount of memory used is specified by the user and contains only the most recently used statements.
You can set the SQL PLNCLOSE= plan option to close at transaction end or run unit end without concern for caching statements because PLNCLOSE= has no effect on caching when the LRU option is in effect. For example, users who used PLNCLOSE=T for CICS applications have reported a significant decrease in response time by using the LRU option because of reductions in read I/Os to the Data Definition Directory (DDD) database.
Use the console-like command SQL_LRU_STATEMENT_CACHE n to activate the LRU statement cache. The value you specify for the n parameter controls the size of the cache. The range supported for the n parameter is a number from 512000—1073741824.
Use the console-like command SQL_LRU_STATEMENT_CACHE to close at transaction end or run unit end.
Statement objects are usually between 5k and 30k, so the 1M default holds approximately 34 to 204 statements.
This option can be changed at any time while the Multi-User Facility is up. If the LRU cache has been on and is then turned off, the virtual storage used by the LRU cache is released. If the LRU cache has been off and it is turned on, the statements cached up to that point in time are released as their plans are closed. But those cached statements will not be used, since only those statements in the LRU cache will be used from this point forward.
To tune the LRU cache by seeng the current size used by the statement cache (either with or without the LRU statement cache option), use the following query:
SELECT PLAN_POOL_SIZE FROM SYSADM.SQL_STATUS;
To determine how often a statement is found in the LRU statement cache, turn on the following trace:
COMM OPTION=ALTER,TRACE=TRACEGLOBAL,JOBNAME=xxx
where xxx is the job name of the DBUTLTY job. This writes general SQL system status information when the DBULTY job ends.
Turn the trace off with the following:
COMM OPTION=ALTER,TRACE=NONE
The line reporting statement cache performance is as follows:
STMT CACHE REQS: n, FOUND n, PERCENT FOUND n%
There is no facility to report how often a statement is found in the non-LRU statement cache. However, most requests to the DDD database are usually due to reading statements, so you may use activity to the DDD database as a guide to the effectiveness of statement caching. Be aware that it normally takes from 2 to 9 requests to read each statement from the DDD database when the DDD block size is 4K.
|
Copyright © 2014 CA.
All rights reserved.
|
|