2.316 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 the DBMS_STATS package 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: