Introduction

Oracle Autonomous Database 23ai and Oracle Database RU23.8 introduces more control over the use of PL/SQL functions with dynamic statistics.

Consider the following queries that incorporate PL/SQL functions:

select count(*)
from   table(my_package.my_table_function(1000));

select count(*)
from   t1
where  a = my_package.my_function(50)
or a = 10;

In order to estimate the cardinality of these queries, we need to know something about the values these PL/SQL functions return. How can we do that are parse time?

The solution is to allow the PL/SQL functions to be called while gathering dynamic statistics. It means that table column values and PL/SQL function values are sampled at parse time (by executing recursive sampling queries) to improve cardiality estimates.

Doesn’t Oracle use PL/SQL with dynamic statistics already?

Before this new feature, you might be aware that we already called PL/SQL functions under some circumstances when gathering dynamic statistics. However, the FROM clause case was turned off by default. It could be enabled using a fix control (rather than a documented preference) or implicitly enabled with a DYNAMIC_SAMPLING hint. In addition, before this enhancement, there were no fine-grained controls to specify which functions could be used when gathering dynamic statistics.

The upside of calling PL/SQL functions at parse time

If PL/SQL is used in a dynamic sampling query, it becomes possible to significantly improve the accuracy of cardinality estimates, yielding better SQL execution plans.

The downside of calling PL/SQL at parse time

PL/SQL functions may be complex and take time to execute, so if you have any long-running PL/SQL functions they may significantly affect the parse time of a SQL statement if they are used in a dynamic sampling query. Oracle has, therefore, added fine-grained control so that you can decide which PL/SQL functions can be used when gathering dynamic statistics.

Do I need to worry about a behavior change when upgrading to RU23.8 from an earlier RU?

No. The default behavior in RU23.8 matches the default behavior prior to this RU. The main change this feature adds is the ability to control which PL/SQL functions are used when gathering dynamic statistics. In addition, you can allow table functions to be used with dynamic statistics using a documented DBMS_STATS preference rather than an undocumented fix control. 

If you previously enabled PL/SQL to be sampled in FROM clauses (using the undocumented fix control), you can enable the same behavior using the following command:

exec dbms_stats.set_global_plsql_prefs('dynamic_stats','ON')

Examples

You can find an example script in my Github repository (it’s runnable in Oracle Autonomous Database). There is limited scope to control dynamic statistics levels in Oracle ADB, but the examples provided still demonstrate the point.

Documentation

You can find documentation here.