Global PL/SQL Cache Parameters

enable_global_plsqlcache

Parameter description: Specifies whether to globally cache compilation products of packages, stored procedures, and functions, and cache execution products at the session level. Enabling this function can reduce the memory usage of database nodes in high concurrency scenarios.

This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Default value: on

max_execute_functions

Parameter description: Specifies the number of execution products of stored procedures and functions in a session. This parameter must be set when enable_global_plsqlcache is set to on. Otherwise, the setting is invalid.

If the number of execution products is greater than the value of max_execute_functions, only the most recently invoked execution products (the number is specified by max_execute_functions) are retained and others are cleared.

This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Value range: a value ranging from 1 to 2147483647.

Default value: 1000

max_compile_packages

Parameter description: Specifies the maximum number of package compilation results stored in the server.

Parameter type: integer

Unit: none

Value range: 0 to 2147483647. The value 0 indicates that this function is disabled and the number of package compilation products is not controlled. The default value of this parameter can be adjusted based on different specifications. The recommended value can be obtained by rounding down the value of (max_process_memory x 2%)/4.4, in MB.

The value 4.4 MB is the average value obtained based on the simulation statistics in the lab. You need to observe the value in the actual scenario. If the value does not comply with the actual scenario, you need to adjust the value to adjust the memory usage of the stored procedure.

Default value:

Default values for different specifications are calculated by rounding down (max_process_memory x 2%)/4.4, in MB.

Setting method: This is a SUSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Storing too many package compilation results may occupy a large amount of memory. Setting this parameter to a proper value helps reduce memory usage and improve system performance.