16.10 - Cost Profile Capture Procedure Using SQL DIAGNOSTIC Statement Requests - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

The procedure defined here uses the second method.

  1. Determine the name of the session profile.
    DIAGNOSTIC HELP PROFILE;

    Assume the request returns the name prof-name for the session profile.

  2. Capture the cost segment and SQL to generate the emulation cost profile.
    DIAGNOSTIC DUMP COSTS Wellington;
    
    
    .EXPORT FILE=ProfileSQL
    
    EXEC DBC.ShowCostProfileAs ( 'prof-name'
    , 'Wellington_Profile'
    , 'Active cost profile for Wellington')
    
    .EXPORT reset
  3. Load the file named ProfileSQL.

    To do this, execute the following BTEQ installation command.

    .RUN FILE=ProfileSQL

    This installs as a Wellington_Profile variable Cost Profile.

  4. Insert the cost segment row for Wellington into SystemFE.Opt_Cost_Table and his DBS Control and internal parameter rows into SystemFE.Opt_DBSCtl_Table using DIAGNOSTIC DUMP COSTS (see About Capturing Target System Optimizer Cost Values and Dynamic AMP Statistical Samples).
  5. Insert the internal parameter row for Wellington into SystemFE.Opt_RAS_Table using DIAGNOSTIC DUMP SAMPLES.
  6. Run the appropriate SQL DIAGNOSTIC requests to install the Target Level Emulation data.
    DIAGNOSTIC SET COSTS Wellington
    , PROFILE Wellington_Profile
    ON FOR scope_level;

    where scope_level indicates one of the following:

    This scope level... Applies to the current...
    REQUEST SQL request.
    IFP parsing engine.
    SESSION session.
    SYSTEM system, meaning there is no restriction enforced.

    For details, see “SET DIAGNOSTIC COSTS” in SQL Data Manipulation Language.

    DIAGNOSTIC SET SAMPLES Wellington
    ON FOR scope_level;

    where scope_level indicates one of the following:

    This scope level... Applies to the current...
    SESSION session.
    SYSTEM system, meaning there is no restriction enforced.

    For details, see “DIAGNOSTIC SET SAMPLES” in SQL Data Manipulation Language.

  7. This step is optional.

    Remove the profile.

    EXEC DBC.DeleteCostProfile ('Wellington_Profile');