DIAGNOSTIC SET COSTS - Teradata Database

Instructs the Optimizer on the test system to use the optimizer cost parameters and relevant DBS Control information from the target (production) system for its emulations for a period defined by the scope level.

where:

Syntax element …	Specifies …
target_system_name	Name of the target (production) system to be emulated. For information on object naming, see SQL Fundamentals. target_system_name is not case sensitive and can be up to 63 characters in length. target_system_name must be defined in SystemFE.Opt_Cost_Table for this request to be valid.
TPA NOT	Use the default system environmental cost parameters defined by the current Trusted Parallel Application environment. These values are calculated at system startup time for the current system. TPA and NOT have the same function.
PROFILE profile_name	Optional profile name for which costs are to be set. To be able to specify this option, the value for the DBS Control field EnableCostProfileTLE must be set to TRUE. See “Enabling and Disabling Full Syntax” on page 602 and Utilities. See SQL Request and Transaction Processing, Data Dictionary, and SystemFE Macros for more information about cost profiles, parameters, and values.
REQUEST	Cost values for the target system are in effect only for the current multistatement request.
SESSION	Cost values for the target system are in effect for the duration of the current session until one of the following events occurs. You log off the session. You restart the database. You change the costs in effect by performing a DIAGNOSTIC SET COSTS PROFILE NOT ON FOR SESSION request. You reset costs to the same values as those for the IFP scope level by performing a DIAGNOSTIC SET COSTS NOT ON FOR SESSION request.
IFP	Cost values for the target system are in effect on the current parsing engine for all its sessions until one of the following events occurs. The next database restart. The costs are changed to a different target for the IFP scope level. You reset costs to the same values as those for the SYSTEM scope level by performing a DIAGNOSTIC SET COSTS NOT ON FOR IFP request.
SYSTEM	Cost values for the target system are in effect for all sessions across all restarts and reboots until one of the following events occurs. The costs are changed to a different target. The costs are set to the TPA scope level by performing a DIAGNOSTIC SET COSTS TPA PROFILE SysDefault ON FOR SYSTEM request, which resets costs to the TPA scope level.

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

Access to SystemFE.Opt_Cost_Table and SystemFE.Opt_DBSCtl_Table is restricted to users with the appropriate database and table-level privileges.

The following scope levels can only be set by user DBC or user SystemFE:

IFP

The scope level for the IFP designation is the current parser.

SYSTEM

All scope levels require SELECT privileges on SystemFE.Opt_Cost_Table and SystemFE.Opt_DBSCtl_Table.

Note that SystemFE.Opt_Cost_Table and SystemFE.Opt_DBSCtl_Table must exist for any of these scope levels to be defined.

DIAGNOSTIC SET COSTS requires that Target Level Emulation is enabled to set scope level cost estimation data.

See SQL Request and Transaction Processing for more information.

You cannot perform DIAGNOSTIC SET COSTS in ANSI session mode or within explicit transaction boundaries in Teradata session mode.

The DBS Control field EnableCostProfileTLE controls whether your site supports the full or the restricted syntax for DIAGNOSTIC SET COSTS, as indicated by the following table:

IF the value for EnableCostProfileTLE is set to …	THEN the syntax available for users is …
FALSE	the restricted version.
TRUE	the full version.

If EnableSetCostProfile is set to 0, then EnableCostProfileTLE is set to FALSE.

See Utilities and SQL Request and Transaction Processing for additional information about the use of the EnableCostProfileTLE DBS Control field.

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 describes precedence for each scope definition:

For this scope definition …	The scope for the costs 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

Each DIAGNOSTIC SET COSTS request in a multistatement request is performed in the order of its appearance, but prior to the other requests in the same multistatement request. All DIAGNOSTIC SET COSTS requests in a multistatement are in effect before the other requests are processed.

If you submit the request DIAGNOSTIC SET PROFILE gonzo ON FOR SESSION, subsequent queries are optimized using the profile named gonzo. The profile named gonzo becomes the session‑standard profile and is used for the current session until you do one of the following:

Specify a different profile.

Submit a DIAGNOSTIC SET PROFILE NOT ON FOR SESSION request, which restores the IFP scope level profile for the current session. An IFP‑level scope applies only to the current parsing engine.

In the event that a profile for identifier in a DIAGNOSTIC SET … PROFILE identifier ON … cannot be found, the system logs a warning message. Only the first five instances of identical messages are logged to avoid excessive logging.

DIAGNOSTIC HELP PROFILE scope_level lists the cost parameter values for the cost profile associated with scope_level.

For example, assume the following request has been submitted:

     DIAGNOSTIC SET PROFILE gonzo ON FOR SESSION; 

The following report is produced:

     DIAGNOSTIC HELP COSTS;

     *** Help information returned. 4 rows.

      *** Total elapsed time was 1 second.

     Status of Cost Profile and TLE Cost Parameters

     --------------------------------------------------------

     REQUEST: gonzo [Id = 10003, Type = 2]

     SESSION: gonzo [Id = 10003, Type = 2] TLE IS IFP

     IFP    : T2_Linux64 [Id =    21, Type = 2] TLE IS SYSTEM

     SYSTEM : T2_Linux64 [Id =    21, Type = 2] TLE IS TPA

Additionally,

     DIAGNOSTIC HELP PROFILE SESSION;

returns:

     Cost Profile Parameters

     --------------------------------------------------

     SESSION: gonzo [Id = 10003, Type = 2] TLE IS IFP

     Initialization Parameters:

     OptProperties         = false [0]

     OptIndexBlockSize     = 49152

     . . .

Suppose you have set costs ON for all scope levels on target system production_1. You then set costs NOT ON at the SESSION level:

     DIAGNOSTIC SET COSTS NOT ON FOR SESSION;

You then run DIAGNOSTIC SET COSTS. The result is similar to the following:

     DIAGNOSTIC HELP COSTS;

      *** Help information returned. 4 rows. 

      *** Total elapsed time was 1 second.

Status of Cost Profile and TLE Cost Parameters

------------------------------------------------------------------------

REQUEST: SysDefault                   [Id =    0, Type = 1 Legacy]

SESSION: SysDefault                   [Id =    0, Type = 1 Legacy] TLE I

IFP    : SysDefault                   [Id =    0, Type = 1 Legacy] TLE P

SYSTEM : SysDefault                   [Id =    0, Type = 1 Legacy] TLE P