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 examnple, 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.
Activate the LRU statement cache option by using the Multi-User Facility parameters shown in the following:
DIAGOPTION 3,64,ON DIAGOPTION A,n-K,M-
The first line turns the LRU cache management on, and the second line specifies the size of the cache as n-K,M- where size may be specified as a number of n bytes, in kilobytes as nK, or in megabytes as nM.
If n is zero or the A is not specified, the default is 1 megabyte (1M). If less than 500000 is specified, 500000 is used. If greater than 1024M is specified, 1024M is used. Be aware, however, that no error message is issued to indicate one of these automatic reductions has occurred.
Statement objects are usually between 5k and 30k, so the 1M default holds approximately 34 to 204 statements.
These options may 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 will be 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.
|
|