DIAGNOSTIC SET PROFILE - Teradata Database

DIAGNOSTIC SET PROFILE

Purpose  

Instructs the Optimizer on the test system to activate or deactivate a cost profile at the specified level. The system propagates activated cost profiles to successor levels.

Syntax  

where:

Syntax element …	Specifies …
profile_name	the name of a cost profile to be activated. To use the standard default cost profile, specify the profile name SysDefault.
NOT	to deactivate the cost profile at the specified scope level.
scope_level	the scope level for which the profile is to be set. IFP restricts the cost profile setting to the current parsing engine. REQUEST restricts the cost profile setting to the current SQL request. SESSION restricts the cost profile setting to the current session. SYSTEM restricts the cost profile setting to the current system, which means that no restriction is enforced on the data to be reported. If you specify a scope level of SYSTEM, then the specified cost profile becomes the standard default cost profile and the system saves its identifier in the CostProfileId DBS Control flag (see Utilities).

ANSI Compliance

DIAGNOSTIC SET PROFILE is a Teradata extension to the ANSI SQL:2011 standard.

Required Privileges

You must have SELECT privileges on SystemFE.Opt_Cost_Table, be user DBC, or be user SystemFE to set profiles.

The ability to perform DIAGNOSTIC SET PROFILE is controlled by the setting of the EnableSetCostProfile flag in the DBS Control record.

See “Enabling DIAGNOSTIC SET PROFILE” on page 630.

Enabling DIAGNOSTIC SET PROFILE

You can control the availability of DIAGNOSTIC SET PROFILE requests with the EnableSetCostProfile flag of the DBS Control record. For details, see Utilities and SQL Request and Transaction Processing. This flag controls usage of DIAGNOSTIC SET PROFILE statements to dynamically change cost profiles used for query planning.

The following table describes each of the settings:

Value	Definition
0	The system default cost profile cannot be changed by a DIAGNOSTIC SET PROFILE request. The default EnableSetCostProfile value. The EnableCostProfileTLE DBS Control flag is set FALSE. The DIAGNOSTIC SET PROFILE statement is disabled. The system aborts attempts to submit DIAGNOSTIC SET PROFILE requests and returns an error.
1	A nondefault cost profile can be activated only at the SESSION and REQUEST levels.
2	A nondefault cost profile can be activated at all levels.

Optimizer Cost Profiles and Logon Profiles

The profiles referred to by DIAGNOSTIC SET PROFILE are Optimizer cost profiles, not database logon profiles.

About Optimizer Cost Profiles

An optimizer cost profile is a list of named values. You can add, delete, or change values without redefining Data Dictionary structures. A cost profile definition consists of the following general attributes:

A name

A type

A collection of named constant values (the collection varies by profile type)

A profile is identified by name and is an instance of its profile type. Instance definitions in the dictionary can supply alternative constant values.

There are several valid cost profiles defined in the Data Dictionary for special purposes, but the following profile is the candidate system profile that supplies the standard set of calibrated CPU cost parameter values.

T2_Linux64

For more information, see SQL Request and Transaction Processing.

System Response When profile_name Does Not Exist

The system returns an error if the specified cost profile is not found.

If the CostProfileId in the DBS Control record is not a valid cost profile ID, the standard default profile for the system remains unchanged.

Scope Level Precedence

Scope levels have a sequential precedence. The following table lists the precedence ratings for the various scope levels:

Precedence	Option
1	REQUEST
2	SESSION
3	IFP An IFP‑level scope applies only to the current parsing engine.
4	SYSTEM

The following table indicates how precedences cascade:

For this scope definition …	The scope for the profile definition defaults to this level …
NOT ON FOR REQUEST	SESSION
NOT ON FOR SESSION	IFP An IFP‑level scope applies only to the current parsing engine.
NOT ON FOR IFP	SYSTEM

Effect of Setting a Cost Profile ON

The syntax DIAGNOSTIC SET PROFILE profile_name ON FOR scope_level activates the specified cost profile at the indicated scope level as described in this table:

Scope Level	How the Profile is Activated and Used
SYSTEM IFP	applied to the current session and all new sessions started by the system or the specific parsing engine. An IFP‑level scope applies only to the current parsing engine.
SESSION	used for subsequent requests in the session.
REQUEST	applied to all statements of a multistep request. The request DIAGNOSTIC SET PROFILE profile_name NOT ON FOR REQUEST by itself is effectively a no-op because the profile reverts to the SESSION scope level as soon as the diagnostic statement completes.

For IFP (an IFP‑level scope applies only to the current parsing engine) and SYSTEM level settings, propagation to session‑local settings occurs only in the session that is handling the DIAGNOSTIC request. Other sessions continue to use their local SESSION‑level or REQUEST‑level profile values until they execute a DIAGNOSTIC SET PROFILE profile_name NOT ON FOR SESSION request, which forces the active IFP value to propagate to the SESSION and REQUEST levels.

DIAGNOSTIC SET PROFILE Precedence in Multistatement Requests

In multistatement requests:

Each DIAGNOSTIC SET PROFILE request is processed based on its placement relative to other DIAGNOSTIC SET PROFILE requests.

All DIAGNOSTIC SET PROFILE requests are processed before requests of another type.

For example, in the following multistatement request, the three DIAGNOSTIC SET PROFILE statements are processed in the order they are specified relative to each other, and before the SELECT statement. As a result, the SELECT statement is optimized using profiles g3 and g2 activated at the SESSION level.

     DIAGNOSTIC SET PROFILE g1 ON FOR SESSION

     ;SELECT emp_num FROM employee_phone WHERE employee_phone=5553229

     ;DIAGNOSTIC SET PROFILE g2 ON FOR SESSION

     ;DIAGNOSTIC SET PROFILE g3 ON FOR REQUEST;

Example  

This request sets the active SYSTEM‑level cost profile using constant values from the profile named T2_special:

     DIAGNOSTIC SET PROFILE T2_special ON FOR SYSTEM;

Example  

This request sets the active SESSION‑level or REQUEST‑level cost profiles using constant values from the profile named T2_workaround1265:

     DIAGNOSTIC SET PROFILE T2_workaround1265 ON FOR SESSION;

     DIAGNOSTIC SET PROFILE T2_workaround1265 ON FOR REQUEST;

Example  

This request sets the active IFP‑level cost profile (an IFP‑level scope applies to the current parsing engine only) using constant values from the profile named T2_workaround23:

     DIAGNOSTIC SET PROFILE T2_workaround23 ON FOR IFP;

After the system processes this request, Teradata Database uses this IFP‑level profile:

For new sessions.

As the prior cost profile for a subsequent DIAGNOSTIC SET PROFILE NOT ON FOR SESSION request.

Example  

This request deactivates all cost profiles at the specified scope level:

     DIAGNOSTIC SET PROFILE OFF FOR SESSION;

For More Information

For more information about cost profile attributes, see “DIAGNOSTIC HELP PROFILE” on page 624.

For more information about cost profiles, see SQL Request and Transaction Processing.