DIAGNOSTIC HELP PROFILE - Teradata Database

Reports cost parameter information for the specified scope level.

where:

Syntax element …	Specifies that a report of cost profile information is returned for the…
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 2: DIAGNOSTIC HELP PROFILE With INIT Option” on page 608 and “Example 3: DIAGNOSTIC HELP PROFILE With INIT Option and Lower and Upper Boundaries on Cost Profile Parameter ID Values” on page 608).
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.

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

None.

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

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

This example shows what the same scenario used to produce “Example 1: DIAGNOSTIC HELP PROFILE Without Options” on page 607 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

This example shows what the same scenario used to produce “Example 1: DIAGNOSTIC HELP PROFILE Without Options” on page 607 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

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

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