The SQLSC_FACILITY (SCF) table consists of one row. The SCF table describes the overall state of the SQL Cache since the MUF was started. The SCF table has the following characteristics:
Note: When the SCF_QUERY_ALLOWED or SCF_MEMORY_ALLOWED column is changed, the SCF_MEMORY_SIZED_TS column is set the next time the Cache is searched, and the SCF_SEARCH_CNT and SCF_MATCH_CNT columns are reset so that the hit ratio can then be computed using the new limits.
|
Column Name |
SQL Data Type |
Nullable |
Description |
|---|---|---|---|
|
SCF_ID |
INTEGER |
No |
Primary Key (always 1) |
|
SCF_MUF_NAME |
CHAR(8) |
No |
Name of the MUF |
|
SCF_CURRENT_TS |
TIMESTAMP |
No |
Current time |
|
SCF_MEMORY_SIZED_TS |
TIMESTAMP |
No |
When the Cache size was last set |
|
SCF_QUERY_ALLOWED |
INTEGER |
No |
Maximum queries the Cache can hold (default is 1000) |
|
SCF_MEMORY_ALLOWED |
INTEGER |
No |
Maximum memory the Cache can use (default is 10M) |
|
SCF_MEMORY_CURRENT |
INTEGER |
No |
Current memory used |
|
SCF_QUERY_CNT |
INTEGER |
No |
Current number of queries in the Cache (see the note aove the table) |
|
SCF_SEARCH_CNT |
INTEGER |
No |
Number of times the Cache was searched--SCF_SEARCH_CNT and SCF_MATCH_CNT are set to zero when SCF_SEARCH_CNT overflows the maximum fullword value (see the note aove the table) |
|
SCF_MATCH_CNT |
INTEGER |
No |
Number of times a match was found |
Note: Two additional columns in the SCF table are planned for future releases, SCF_OPTIMIZED_QUERIES and SCF_TERMINATED_CNT. When implemented in a future release, it is planned that optimized statements would never be removed from the Cache.
Cache Size
The size of the Cache can be limited by both the memory used (SCF_MEMORY_ALLOWED) and the number of statements in the Cache (SCF_QUERY_ALLOWED). Because both limits are in effect, the statement count limit can cause the memory limit to never be reached. Likewise, the memory limit can cause the statement count limit to never be reached.
When a new statement is inserted into the Cache, the least-recently-used (LRU) statement is removed from the Cache. Currently-executing statements cannot be removed from the Cache, because statistics are recorded in the cached copy of the statement at end of execution. SCF_QUERY_CNT and SCF_MEMORY_CURRENT can therefore be larger than the respective limits.
The size of the Cache can be tuned by using a “hit ratio” of SCF_MATCH_CNT divided by SCF_SEARCH_CNT. When the Cache size is too small, this hit ratio decreases.
|
Copyright © 2014 CA.
All rights reserved.
|
|