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

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');