PLSQL_FUNCTION_DYNAMIC_STATS
PLSQL_FUNCTION_DYNAMIC_STATS specifies, at the session level, whether dynamic statistics are allowed to be generated for PL/SQL functions.
| Property | Description |
|---|---|
| Parameter type | String |
| Syntax | PLSQL_FUNCTION_DYNAMIC_STATS = { PREFERENCE | ON | OFF | CHOOSE } |
| Default value | PREFERENCE |
| Modifiable | ALTER SESSION |
| Modifiable in a PDB | No |
| Basic | No |
Dynamic statistics is an optimization technique in which the database uses recursive SQL to scan a small random sample of the blocks in a table. In earlier releases, the optimizer made the decision to allow dynamic statistics for some PL/SQL functions, and disallow dynamic statistics for others.
Starting with Oracle AI Database 26ai, Release Update
23.8, Oracle provides users with more control over dynamic statistics for PL/SQL functions. The DBMS_STATS package offers procedures that allow you to set and view global-level and function-level preferences for generating dynamic statistics for PL/SQL functions.
Additionally, the PLSQL_FUNCTION_DYNAMIC_STATS parameter allows you to override these preferences for a particular session. You can specify the following values:
-
PREFERENCE- Do not override. Use the global-level and function-level preferences that were set with theDBMS_STATSpackage for generating dynamic statistics for PL/SQL functions. This is the default. -
ON- Enable dynamic statistics in the session for all PL/SQL functions. -
OFF- Disable dynamic statistics in the session for all PL/SQL functions. -
CHOOSE- Allow the optimizer to choose whether dynamic statistics are generated in the session for each PL/SQL function.
Note: This parameter is available starting with Oracle AI Database 26ai, Release Update 23.8.
See Also:
-
Oracle AI Database PL/SQL Packages and Types Reference for more information about the
DBMS_STATSpackage -
Oracle AI Database SQL Tuning Guide for more information about this parameter