UDR Cache

The first time that a user executes an SPL routine, the database server stores the executable format and any query plans in the UDR cache in the virtual portion of shared memory. When another user executes an SPL routine, the database server first checks the UDR cache. SPL execution performance improves when the database server can execute the SPL routine from the UDR cache. The UDR cache also stores UDRs, user-defined aggregates, and extended data types definitions.

Changing the UDR Cache

The default number of SPL routines, UDRs, and other user-defined definitions in the UDR cache is 127. You can change the number of entries with the PC_POOLSIZE configuration parameter.

The database server uses a hashing algorithm to store and locate SPL routines in the UDR cache. You can modify the number of buckets in the UDR cache with the PC_HASHSIZE configuration parameter. For example, if PC_POOLSIZE is 100 and PC_HASHSIZE is 10, each bucket can have up to 10 SPL routines and UDRs.

Too many buckets cause the database server to move out cached SPL routines when the bucket fills. Too few buckets increase the number of SPL routines in a bucket, and the database server must search though the SPL routines in a bucket to determine if the SPL routine that it needs is there.

Once the number of entries in a bucket reaches 75 percent, the database server removes the least recently used SPL routines from the bucket (and hence from the UDR cache) until the number of SPL routines in the bucket is 50 percent of the maximum SPL routines in the bucket.

Monitoring the UDR Cache

Execute onstat -g prc to monitor the UDR cache. You can also execute onstat -g cac to list the contents of other memory caches (such as the aggregate cache) as well as the UDR cache.

Figure 60 shows sample output for onstat -g prc.

UDR Cache:
    Number of lists             : 31
    PC_POOLSIZE                 : 127
UDR Cache Entries:
list#  id  ref_cnt   dropped?   heap_ptr        udr name
--------------------------------------------------------------
0       138     0       0       a4ba820         sales_rep@london:.destroy
3       50      0       0       a4b2020         sales_rep@london:.assign
6       25      0       0       a4b8420         sales_rep@london:.rowoutput
7       29      0       0       a214860         sales_rep@london:.assign 
...

The onstat -g prc output has the following fields.

Field

Description

Number of Lists

Number of buckets that PC_HASHSIZE specifies

PC_POOLSIZE

Number of UDRs and SPL routines allowed in the UDR cache

List #

Bucket number

ID

Unique ID of the UDR or SPL routines

Ref count

Number of times that users have executed the UDR or SPL routine from the cache

Dropped

Designation if the SPL routine has been marked to be dropped

Heap ptr

Heap pointer

UDR name

Name of the UDR or SPL routine