15.00 - CostProfileId - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

CostProfileId

Purpose  

Specifies the cost profile that the Teradata Database Optimizer uses by default to choose the most efficient query execution path.

Note: This setting should be changed only under the direction of Teradata Support Center personnel.

Field Group

General

Valid Range

0 through 32760

Default

 

IF CostProfileID is…

THEN…

0

the system uses profile T2_Linux64, the Teradata Database default cost profile for 64-bit Linux platforms, which has cost profile id = 21.

This will not cause any running session to change the cost profile it is currently using.

greater than 0

the standard default cost profile is defined by DBC.CostProfiles.CostProfileId = N, where N is the CostProfileId value.

The non-zero value N for CostProfileId implies that the system will use the cost profile values in DBC.ConstantValues where DBC.ConstantValues.ProfileId = N.

Note: The set of constant values in DBC.ConstantValues for a particular cost profile consists of the values to be used instead of what the system uses by default. In nearly all cases, this set represents only a part of the full set of cost profile constant definitions.

Changes Take Effect

For new sessions after the DBS Control Record has been written.

Example  

To return the list of cost profiles defined for the system, use the following SQL query:

SELECT * FROM DBC.CostProfiles_v;

The following appears:

Type Name Profile Name      ProfileId Cat Description
--------- ----------------- --------- --- ------------------------------------------------------------------------------
Legacy    lsi6840_56               15 F   Disk array cost values for LSI 6840 array with 56 drives. Partial profile, disk 
Legacy    V2R4                      1 F   V2R4 DBS cost values only. Partial profile, no disk array or network values. 
Legacy     emc                      9 F   Disk array cost values for EMC. Partial profile, disk array values only.
Legacy    lsi6288_40               11 F   Disk array cost values for LSI 6288 array with 40 drives. Partial profile, disk 
Legacy    lsi6288_52               12 F   Disk array cost values for LSI 6288 array with 52 drives. Partial profile, disk 
Legacy    lsi6840_40               14 F   Disk array cost values for LSI 6840 array with 40 drives. Partial profile, disk 
Legacy    SysDefault                0 F   SysDefault DBS cost values only. Partial profile, no disk array or network 
Legacy    lsi6285_40               17 F   Disk array cost values for LSI 6285 array with 40 drives. Partial profile, disk 
Legacy    lsi6840_28               13 F   Disk array cost values for LSI 6840 array with 28 drives. Partial profile, disk 
Legacy    V2R4_Array               18 F   Disk array cost values for V2R4 Default. Partial profile, disk array values 
Legacy    V2R5_Bynet_V1             5 F   Bynet V1 cost values for V2R5. Partial profile, bynet values only.
Legacy    symbios_half              7 F   Disk array cost values for Half Populated Symbios 6299. Partial profile, disk 
Legacy    V2R5                      2 F   V2R5 DBS cost values only. Partial profile, no disk array or network values. 
Legacy    lsi6283                  10 F   Disk array cost values for LSI 6283 array. Partial profile, disk array values 
Legacy    V2R4_Bynet                4 F   Bynet cost values for V2R4. Partial profile, bynet values only.
Legacy    V2R5_Solaris              3 F   V2R5 DBS cost values only. Specific to Solaris platforms. Partial profile, no 
Legacy    V2R6                     35 F   Generic standard cost profile for V2R6 systems.
Legacy    symbios_full              8 F   Disk array cost values for Fully Populated Symbios 6299. Partial profile, disk 
Legacy    lsi6285_20               16 F   Disk array cost values for LSI 6285 array with 20 drives. Partial profile, disk 
Legacy    V2R5_Bynet_V2             6 F   Bynet V2 cost values for V2R5. Partial profile, bynet values only.
Legacy    V2R5_Array               19 F   Disk array cost values for V2R5 Default. Partial profile, disk array values 
Type2     T2_lsi6288_40            28 F   LSI 6288 array with 40 drives cost values for Type 2 cost profiles.
Type2     T2_Win64                 37 F   Default Type 2 formula parameter initialization values for 64 bit Window 
Type2     T2_symbios_half          24 F   Half Populated Symbios 6299 cost values for Type 2 cost profiles.
Type2     T2_lsi6840_28            30 F   LSI 6840 array with 28 drives cost values for Type 2 cost profiles.
Type2     T2_lsi6840_56            32 F   LSI 6840 array with 56 drives cost values for Type 2 cost profiles.
Type2     T2_lsi6285_40            34 F   LSI 6285 array with 40 drives cost values for Type 2 cost profiles.
Type2     T2_Bynet_V3              39 F   Bynet V3 cost values for Type 2 cost profiles.
Type2     Teradata12               36 F   Standard cost profile for Teradata12 systems (== T2_32Bit).
Type2     T2_32Bit                 20 F   Default Type 2 formula parameter initialization values for 32 bit platforms.
Type2     T2_Linux64               21 F   Default Type 2 formula parameter initialization values for 64 bit Linux 
Type2     T2_lsi6283               27 F   LSI 6283 array cost values for Type 2 cost profiles.
Type2     T2_emc                   26 F   EMC cost values for Type 2 cost profiles.
Type2     T2_lsi6288_52            29 F   LSI 6288 array with 52 drives cost values for Type 2 cost profiles.
Type2     T2_Bynet_V2              23 F   Bynet V2 cost values for Type 2 cost profiles.
Type2     T2_lsi6840_40            31 F   LSI 6840 array with 40 drives cost values for Type 2 cost profiles.
Type2     T2_Bynet_V1              22 F   Bynet V1 cost values for Type 2 cost profiles.
Type2     T2_lsi6285_20            33 F   LSI 6285 array with 20 drives cost values for Type 2 cost profiles.
Type2     T2_symbios_full          25 F   Fully Populated Symbios 6299 cost values for Type 2 cost profiles.
Type2     T2_TD13_0_LINUX64        38 F   Default Type 2 formula parameter initialization values for TD13 on 64 bit Linux 

Example  

To return the list of constant values defined for a specific profile, for example ProfileId = 21, use the following SQL query:

SELECT * FROM DBC.CostProfileValues_v WHERE ProfileId = 21;

The following is a portion of the output:

Profile Name P-Id Constant Name        C-Id  Cat       Value Description
------------ ---- -------------------- ----- --- ----------- -----------------------------------------------------------
T2_Linux64     21 OptIndexBlockSize    10002 I   49,152.0000 Assumed block size for index subtable IOs. Value is in 
T2_Linux64     21 OptMaxBldKeySize     10003 I    1,000.0000 Defines the maximum size of a key that can be built for a 
T2_Linux64     21 OptMaxRowIdSIndex    10004 I    8,030.0000 Defines the average number of row ids in a secondary index 
T2_Linux64     21 OptRowidSize         10005 I       12.0000 Defines the size of each entry in a RowID list. Value is in 
T2_Linux64     21 OptSpoolBlockSize    10006 I  131,072.0000 Assumed block size for spool file IOs. Value is in bytes.
T2_Linux64     21 OptTableBlockSize    10007 I   49,152.0000 Assumed block size for table IOs. Value is in bytes.
T2_Linux64     21 OptBitInst           10060 I       33.0000 Defines the path length to check (or set) bit map given a 
T2_Linux64     21 OptBMAndRowInst      10061 I   98,500.0000 Defines the path length to AND two bit maps, each the size 
T2_Linux64     21 OptCharFieldInst     10062 I      261.0000 Defines the path length to build a one character field. 
T2_Linux64     21 OptNumFieldInst      10063 I       37.0000 Defines the path length to build one numeric field. Value in 
T2_Linux64     21 OptOutputRowInst     10064 I    1,641.0000 Defines the path length of the overhead to build an output 
T2_Linux64     21 OptOvhdOfRowCompInst 10065 I      242.0000 Defines the path length of the row comparison overhead durin
T2_Linux64     21 OptRedistributeInst  10066 I   73,389.0000 Defines the path length for redistributing a row. Value in 
T2_Linux64     21 OptRowAccessInst     10067 I      409.0000 Defines the path length for accessing a row in memory. Value
T2_Linux64     21 OptRowIdInst         10068 I   27,139.0000 Defines the path length for accessing a row thru a RowID 
T2_Linux64     21 OptSynonymInst       10069 I   27,139.0000 Defines the path length for accessing a row by using an ind
T2_Linux64     21 InitBlockProcessOv   10100 I         0.0000 Unscaled value of BlockProcessOv
T2_Linux64     21 InitBlockProcessCf   10101 I        0.0000 Unscaled value of BlockProcessCf
T2_Linux64     21 InitAccessRowOv1     10102 I        0.0002 Unscaled value of AccessRowOv1
T2_Linux64     21 InitAccessRowCf1     10103 I        0.0000 Unscaled value of AccessRowCf1
T2_Linux64     21 InitAccessRowBd1     10104 I      200.0000 Unscaled value of AccessRowBd1
T2_Linux64     21 InitAccessRowOv2     10105 I        0.0003 Unscaled value of AccessRowOv2
T2_Linux64     21 InitAccessRowCf2     10106 I        0.0000 Unscaled value of AccessRowCf2
T2_Linux64     21 InitRowDupOv1        10107 I        0.00040 UUnscaled value of RowDupOv1
T2_Linux64     21 InitRowDupCf1        10108 I        0.0000 Unscaled value of RowDupCf1
T2_Linux64     21 InitRowDupBd1        10109 I      140.0000 Unscaled value of RowDupBd1
T2_Linux64     21 InitRowDupOv2        10110 I        0.0000 Unscaled value of RowDupOv2
T2_Linux64     21 InitRowDupCf2        10111 I        0.0000 Unscaled value of RowDupCf2
T2_Linux64     21 InitRowRedistOv      10112 I        0.0006 Unscaled value of RowRedistOv
T2_Linux64     21 InitRowRedistCf      10113 I        0.0000 Unscaled value of RowRedistCf