DBA Hub

📋Steps in this guide1/5

Dynamic Statistics for PL/SQL Functions in Oracle Database 23ai/26ai

Oracle database 23ai/26ai (23.8) gives us multiple layers of control over dynamic statistics in relation to PL/SQL functions. This article demonstrates how that control works.

oracle 23configurationintermediate
by OracleDba
17 views
1

Setup

We create a test user. We need a suitable function as an example. In this case we create a standalone pipelined table function, a packaged table function and a non-table function. We connect to a privileged user again.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

drop user if exists testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant db_developer_role to testuser1;

conn testuser1/testuser1@//localhost:1521/freepdb1

-- Create the types to support the table function.
drop function if exists get_tab_ptf;
drop type if exists t_tf_tab;
drop type if exists t_tf_row;

create type t_tf_row as object (
  id           number,
  description  varchar2(50)
);
/

create type t_tf_tab is table of t_tf_row;
/

-- Create the standalone pipelined table function.
create or replace function get_tab_ptf (p_rows in number) return t_tf_tab pipelined as
begin
  for i in 1 .. p_rows loop
    pipe row (t_tf_row(i, 'Description for ' || i));
  end loop;

  return;
end;
/


-- Create the packaged pipelined table function.
create or replace package ptf_package as

function get_tab_ptf (p_rows in number) return t_tf_tab pipelined;

end ptf_package;
/


create or replace package body ptf_package as

function get_tab_ptf (p_rows in number) return t_tf_tab pipelined as
begin
  for i in 1 .. p_rows loop
    pipe row (t_tf_row(i, 'Description for ' || i));
  end loop;

  return;
end get_tab_ptf;

end ptf_package;
/


-- Create a regular non-table function.
create or replace function dummy_f return number as
begin
  return 1;
end dummy_f;
/

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
2

Check Dynamic Statistics Preferences for PL/SQL Functions

The function in the package allows us to see the default preference setting. Using additional parameter values allows us to see the preference setting for a specific function.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
select dbms_stats.get_plsql_prefs('dynamic_stats') as pref_val;

PREF_VAL
--------------------------------------------------------------------------------
CHOOSE

SQL>

select dbms_stats.get_plsql_prefs(pname         => 'dynamic_stats',
                                  ownname       => 'testuser1',
                                  package_name  => null,
                                  function_name => 'get_tab_ptf') as pref_val;

PREF_VAL
--------------------------------------------------------------------------------
CHOOSE

SQL>


select dbms_stats.get_plsql_prefs(pname         => 'dynamic_stats',
                                  ownname       => 'testuser1',
                                  package_name  => 'ptf_package',
                                  function_name => 'get_tab_ptf') as pref_val;

PREF_VAL
--------------------------------------------------------------------------------
CHOOSE

SQL>


select dbms_stats.get_plsql_prefs(pname         => 'dynamic_stats',
                                  ownname       => 'testuser1',
                                  package_name  => null,
                                  function_name => 'dummy_f') as pref_val;

PREF_VAL
--------------------------------------------------------------------------------
CHOOSE

SQL>
3

Set Global Dynamic Statistics Preferences for PL/SQL Functions

The procedure in the package allows us to set the default preference for the database. The allowable values are as follows. - - Allow dynamic statistics. - - Disable dynamic statistics. - - Let the optimimzer decide if dynamic statistics are required. This is the default. - - Defaults to CHOOSE. We set the global preference to 'ON'. We reset it to the default value.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
exec dbms_stats.set_global_plsql_prefs('dynamic_stats', 'ON');


select dbms_stats.get_plsql_prefs('dynamic_stats') as pref_val;

PREF_VAL
--------------------------------------------------------------------------------
ON

SQL>

exec dbms_stats.set_global_plsql_prefs('dynamic_stats', null);


select dbms_stats.get_plsql_prefs('dynamic_stats') as pref_val;

PREF_VAL
--------------------------------------------------------------------------------
CHOOSE

SQL>
4

Set Object-Specific Dynamic Statistics Preferences for PL/SQL Functions

The procedure in the package allows us to set the preference for a specific object, which takes precedence over the global preference. The allowable values are as follows. - - Allow dynamic statistics. - - Disable dynamic statistics. - - Let the optimizer decide if dynamic statistics are required. This is the default. - - The preference for this object is removed, so the global preference is used. We set the preference to 'OFF' for the standalone pipelined table function. We set the preference to 'ON' for the packaged pipelined table function. We set the preference to 'ON' for the non-table function. We remove the preferences, so the default preference is used.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
begin
  dbms_stats.set_plsql_prefs(
    ownname       => 'testuser1',
    package_name  => null,
    function_name => 'get_tab_ptf',
    pname         => 'dynamic_stats',
    pvalue        => 'OFF');
end;
/

select dbms_stats.get_plsql_prefs(pname         => 'dynamic_stats',
                                  ownname       => 'testuser1',
                                  package_name  => null,
                                  function_name => 'get_tab_ptf') as pref_val;

PREF_VAL
--------------------------------------------------------------------------------
OFF

SQL>

begin
  dbms_stats.set_plsql_prefs(
    ownname       => 'testuser1',
    package_name  => 'ptf_package',
    function_name => 'get_tab_ptf',
    pname         => 'dynamic_stats',
    pvalue        => 'ON');
end;
/

select dbms_stats.get_plsql_prefs(pname         => 'dynamic_stats',
                                  ownname       => 'testuser1',
                                  package_name  => 'ptf_package',
                                  function_name => 'get_tab_ptf') as pref_val;

PREF_VAL
--------------------------------------------------------------------------------
ON

SQL>

begin
  dbms_stats.set_plsql_prefs(
    ownname       => 'testuser1',
    package_name  => null,
    function_name => 'dummy_f',
    pname         => 'dynamic_stats',
    pvalue        => 'ON');
end;
/

select dbms_stats.get_plsql_prefs(pname         => 'dynamic_stats',
                                  ownname       => 'testuser1',
                                  package_name  => null,
                                  function_name => 'dummy_f') as pref_val;

PREF_VAL
--------------------------------------------------------------------------------
ON

SQL>

begin
  dbms_stats.set_plsql_prefs(
    ownname       => 'testuser1',
    package_name  => null,
    function_name => 'get_tab_ptf',
    pname         => 'dynamic_stats',
    pvalue        => null);
end;
/


select dbms_stats.get_plsql_prefs(pname         => 'dynamic_stats',
                                  ownname       => 'testuser1',
                                  package_name  => null,
                                  function_name => 'get_tab_ptf') as pref_val;

PREF_VAL
--------------------------------------------------------------------------------
CHOOSE

SQL>


begin
  dbms_stats.set_plsql_prefs(
    ownname       => 'testuser1',
    package_name  => 'ptf_package',
    function_name => 'get_tab_ptf',
    pname         => 'dynamic_stats',
    pvalue        => null);
end;
/


select dbms_stats.get_plsql_prefs(pname         => 'dynamic_stats',
                                  ownname       => 'testuser1',
                                  package_name  => 'ptf_package',
                                  function_name => 'get_tab_ptf') as pref_val;

PREF_VAL
--------------------------------------------------------------------------------
CHOOSE

SQL>


begin
  dbms_stats.set_plsql_prefs(
    ownname       => 'testuser1',
    package_name  => null,
    function_name => 'dummy_f',
    pname         => 'dynamic_stats',
    pvalue        => null);
end;
/


select dbms_stats.get_plsql_prefs(pname         => 'dynamic_stats',
                                  ownname       => 'testuser1',
                                  package_name  => null,
                                  function_name => 'dummy_f') as pref_val;

PREF_VAL
--------------------------------------------------------------------------------
CHOOSE

SQL>
5

Session-Level Control of Dynamic Statistics Preferences for PL/SQL Functions

The parameter allows us to control how dynamic statistics are used for PL/SQL functions at the session level. The allowable values are as follows. - - Let the optimimzer choose. By default it will only gather dynamic statistics for non-table functions. - - Allow dynamic statistics for all PL/SQL functions. - - Disable dynamic statistics for all PL/SQL functions. - (Default) - Use the object or global preferences. We set each session-level preference in turn below. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
alter session set plsql_function_dynamic_stats = 'CHOOSE';
alter session set plsql_function_dynamic_stats = 'ON';
alter session set plsql_function_dynamic_stats = 'OFF';
alter session set plsql_function_dynamic_stats = 'PREFERENCE';

Comments (0)

Please to add comments

No comments yet. Be the first to comment!