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.
- report_option
- Set of parameters and fields.
- lower_boundary
- Option report with cost profile parameter ID values at or above this value.
- upper_boundary
- Option report with cost profile parameter ID values up to this value.
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