DIAGNOSTIC SET PROFILE - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

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.

where:

 

Syntax element …

Specifies …

profile_name

Name of a cost profile to be activated.

To use the standard default cost profile, specify the profile name SysDefault.

NOT

Deactivate the cost profile at the specified scope level.

scope_level

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 field. See Utilities.

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

    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 DBS Control field.

    See “Enabling DIAGNOSTIC SET PROFILE” on page 611.

    You can control the availability of DIAGNOSTIC SET PROFILE requests with the EnableSetCostProfile DBS Control field. For details, see Utilities and SQL Request and Transaction Processing. This field 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 field 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.

    The profiles referred to by DIAGNOSTIC SET PROFILE are Optimizer cost profiles, not database logon 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.

  • TD15
  • For more information, see SQL Request and Transaction Processing.

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

    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.

    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;

    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;

    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;

    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.
  • This request deactivates all cost profiles at the specified scope level:

         DIAGNOSTIC SET PROFILE OFF FOR SESSION;

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

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