DIAGNOSTIC SET COSTS - Teradata Database

Purpose

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.

For more information about environmental costs, see:

“DIAGNOSTIC DUMP COSTS”
SQL Request and Transaction Processing

For more information about DBS Control fields, see Utilities .

Required Privileges

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.

Syntax - Full Form

Syntax - Restricted Form

Syntax Elements

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 and Utilities .

For more information about cost profiles, parameters, and values, see SQL Request and Transaction Processing , Data Dictionary , and SystemFE Macros .

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.

ANSI Compliance

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

Target Level Emulation and DIAGNOSTIC SET COSTS

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.

Session Mode Restrictions

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

Enabling and Disabling Full Syntax

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.

For additional information about the use of the EnableCostProfileTLE DBS Control field, see Utilities and SQL Request and Transaction Processing .

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 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

DIAGNOSTIC SET COSTS Performance Precedence in Multistatement Requests

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.

Setting Costs for a Cost Profile

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
     . . .

Example: Set Costs to Not On

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