DIAGNOSTIC HELP PROFILE - Teradata Database

SQL Data Manipulation Language
Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Purpose

Reports cost parameter information for the specified scope level.

For information about setting cost profiles, see “DIAGNOSTIC SET PROFILE”.

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

Required Privileges

None.

Syntax



Syntax Elements

scope_level
Scope level.
Returns the active cost parameter values at the specified scope level.
  • IFP restricts the information reported to the current parsing engine.
  • REQUEST restricts the information reported to the current SQL request.
  • SESSION restricts the information reported to the current session.
  • SYSTEM restricts the information reported to the current system, which means that no restriction is enforced on the data to be reported.
report_option
Set of parameters and fields.
You can set the following options:
  • ALLPARAMS reports all of the values specified by all of the following options. Note that the PARAMID option is excluded.
    • COST
    • INIT
    • INTERNAL
    • NONOPTINIT
    • NONOPTCOST
    • OPTIONS
  • COST reports only optimizer cost parameters.
  • INIT reports only optimizer initialization parameters.
  • INTERNAL reports only internal cost profile parameters.

    Note that this option does not report the data dictionary counterparts to the internal cost profile parameters.

  • NONOPTINIT reports only initialization parameters for workload management predicted elapsed times.
  • NONOPTCOST reports only cost parameters for workload management predicted elapsed times.
  • OPTIONS reports only the cost profile option and field values.
  • PARAMID reports any parameters that have a ConstId value between lower_boundary and upper_boundary.
The report option, including lower and upper boundaries, if specified, must be enclosed between LEFT PARENTHESIS and RIGHT PARENTHESIS characters. See Example: DIAGNOSTIC HELP PROFILE With INIT Option and Example: DIAGNOSTIC HELP PROFILE With INIT Option and Lower and Upper Boundaries on Cost Profile Parameter ID Values.
lower_boundary
Option report with cost profile parameter ID values at or above this value.
The value of lower_boundary must be an integer, from 0 through 99,999.
If you specify lower_boundary, but do not specify upper_boundary, then the report includes information only for lower_boundary.
upper_boundary
Option report with cost profile parameter ID values up to this value.
The value of upper_boundary must be an integer with an upper bound of 99,999.
The upper_boundary must be larger than lower_boundary.

ANSI Compliance

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

Optimizer Cost Profiles, Not Logon Profiles

The profiles referred to by DIAGNOSTIC HELP PROFILE are Optimizer cost profiles, not database logon profiles.

Example: DIAGNOSTIC HELP PROFILE Without Options

DIAGNOSTIC HELP PROFILE scope_level lists the cost parameter values for the cost profile associated with the specified scope level. For example, if you perform this request:

     DIAGNOSTIC SET PROFILE gonzo ON FOR SESSION;

Then you execute a DIAGNOSTIC HELP COSTS request, the system produces this report:

     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

Then, if you execute a DIAGNOSTIC HELP PROFILE SESSION request, the system produces the following report:

     DIAGNOSTIC HELP PROFILE SESSION;

     Cost Profile Parameters
     --------------------------------------------------
     SESSION: gonzo [Id = 10003, Type = 2] TLE IS IFP
     Initialization Parameters:
     OptProperties         = false [0]
     OptIndexBlockSize     = 49152
      . . .
     IndexFillFactor = 7.500e-01
     JoinCardSkewAdj = 1
     Cost Prediction Parameters:
     OptTableReadRate      =      328.75 IOs/Sec.
     OptSpoolReadRate      =      162.50 IOs/Sec.
      . . .
     NumAmpsPerCPU         = 1
     AmpsInSystem          = 2
     Option Field/Flag Values:
     UseActualBlockSize = true  [1]
     ApplySpoolLimit = false [0]
      . . .
     BlockFillFactor[{Table, Spool, Index}] = {7.50000e-01, 1.00000e+00
     ,7.50000e-01}
     JoinCardSkewAdj = 1

Example: DIAGNOSTIC HELP PROFILE With INIT Option

This example shows what the same scenario used to produce Example: DIAGNOSTIC HELP PROFILE Without Options reports when you add the INIT option to the request:

     DIAGNOSTIC HELP PROFILE SESSION (INIT);

      *** Help information returned. 246 rows.
      *** Total elapsed time was 1 second.
     Cost Profile Parameters
     --------------------------------------------------
     SESSION: gonzo [Id = 10003, Type = 2] TLE IS IFP
     Initialization Parameters:
     OptProperties         = false [0]
     OptIndexBlockSize     = 49152
      . . .
     IndexFillFactor = 7.500e-01
     JoinCardSkewAdj = 1

Example: DIAGNOSTIC HELP PROFILE With INIT Option and Lower and Upper Boundaries on Cost Profile Parameter ID Values

This example shows what the same scenario used to produce Example: DIAGNOSTIC HELP PROFILE Without Options reports when you add the INIT option with boundaries on the range of cost profile parameter IDs to the request:

     DIAGNOSTIC HELP PROFILE SESSION (INIT 10008, 10019);

      *** Help information returned. 17 rows.
      *** Total elapsed time was 1 second.
     Cost Profile Parameters
     -------------------------------------------------------
     SESSION: gonzo [Id = 10003, Type = 2] TLE IS IFP
     Initialization Parameters:
     ArrayRead4k           = 1.120e+03
     ArrayRead8k           = 1.020e+03
     ArrayRead16k          = 9.200e+02
     ArrayRead32k          = 7.750e+02
     ArrayRead64k          = 5.400e+02
     ArrayRead128k         = 3.250e+02
     ArrayWrite4k          = 7.400e+02
     ArrayWrite8k          = 6.500e+02
     ArrayWrite16k         = 5.600e+02
     ArrayWrite32k         = 4.450e+02
     ArrayWrite64k         = 2.900e+02
     ArrayWrite128k        = 1.700e+02