Type: WGN_STATS

Database Statistics Reference Guide › Package Customization › Types › Type: WGN_STATS_PARAM

Type: WGN_STATS_PARAM

This is a user-defined type that defines the significant statistics attributes that may need to be customized on a per object basis. Defined as:

CREATE OR REPLACE TYPE WGN_STATS_PARAM AS OBJECT (
			l_process		number,
			l_object_name	varchar2(30),
			l_partition_level	number(1),
			l_estimate_percent	number,
			l_block_sample	number(1),
			l_method_opt	varchar2(2000),
			l_degree		number)

Each user-defined type has the following attributes:

Attribute	Description
l_process	A user-defined number to allow different process to be defined for example it may be required to have different attribute values for daily versus weekly process for a specified object.
l_object_name	Specifies the name of the object (a table or index).
l_partition_level	Allows different attributes to be specified at a global or partition level for the specified object. 0 Global level 1 Partition level
l_estimate_percent	Specifies the percentage sampling rate. In addition, a value of zero or ‑1 has a special meaning: 0 Do not gather statistics on the specified object -1 Use the default sampling rate
l_block_sample	Specifies whether or not to use random block sampling: 0 Do not use block sampling 1 Use block sampling
l_method_opt	Specifies the method_opt parameter passed to dbms_stats for the specified object. This corresponds to the parameter in the GATHER_TABLE_STATS method. It controls whether and how histograms are constructed for the statistics.
l_degree	Allows the default DOP to be overridden for the specified object. A value of -1 means 'adopt the default'.

Returning objects of this type to wgn_stats is the mechanism that allows statistics to be customized down to the level of individual objects, at either a global or partition level. In addition, using a process identifier allows this to be varied across different processes (for example, daily or weekly).

Note: If specific values (other than -1) are set for l_estimate_percent or l_degree, then these values are used in preference to any global defaults that may exist.

Example

The pseudo code below defines an object of this type to represent a customized statistic for the 'WGN3USER' table for process 2, at a global level, sampled at 50%, using block sampling, with a method_opt to make Oracle automatically compute relevant histograms, and with a degree of parallel of 32:

BEGIN
   My_stats_rec WGN_STATS_PARAM := WGN_STATS_PARAM
(2,
'WGN3USER',
0,
50,
1,
'for all columns size auto',
32);
END;