15.10 - Cost Optimization - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

When processing requests, the Optimizer considers all of the following factors and attempts to choose the least costly access method and, when appropriate, the least costly join path.

  • Row selection criteria
  • Index references
  • Available statistics on indexes and columns
  • Note that the unit for all Optimizer cost measures is time in milliseconds. The longer a given request takes to complete, the more costly it is. The column and index demographics gathered by the COLLECT STATISTICS (Optimizer Form) statement (see SQL Data Definition Language Syntax and Examples and SQL Data Definition Language Detailed Topics for syntax and usage information) and the statistics it computes permit the Optimizer to estimate the cardinalities of relations for single‑table and join access and to better identify skew in tables, so it has more finely-tuned knowledge for generating plans using its cost estimator functions.

    Consider the following table fragment.

     

    Assume the following query results in a full‑table scan.

         SELECT * 
         FROM table_x
         WHERE state IN (28, 51)
         AND   serial_num IN (12345, 23456);

    Now modify the request as follows.

         SELECT * 
         FROM table_x
         WHERE (state=28 AND serial_num=12345)
         OR (state=51 AND serial_num=23456)
         OR (state=28 AND serial_num=23456)
         OR (state=51 AND serial_num=12345);

    The semantically identical modified query resulted in four primary index accesses, which is a marked performance enhancement over the full table scan used to solve the first query.

    Expressions involving both AND and OR operators can be expressed in either of the following logically equivalent forms.

  • (A OR B) AND (C OR D)
  • (A AND C) OR (A AND D) OR (B AND C) OR (B AND D)
  • The first form is known as conjunctive normal form, or CNF. In this form, operand pairs are ORed within parentheses and ANDed between parenthetical groups. The advantage of CNF is that as soon as any individual condition in the expression evaluates to FALSE, the entire expression evaluates to FALSE and can be eliminated from further consideration by the Optimizer.

    The second is called Disjunctive Normal Form, or DNF. Different access paths might be selected based on these 2 forms; depending on the expression, one form may be better than the other.

    If A, B, C and D refers to columns of a single table or single column partition, the Optimizer generates the access path based on the form specified in the query; there is no attempt to convert from one form to another to find the best path. On the other hand, if A, B, C or D specifies a join condition, the second form is converted to the first.

    Consider the following expression.

        (NUSI=A OR NUSI=B) AND (X=3 OR X=4)

    In this case, CNF is more high-performing because the access path consists of 2 NUSI SELECT requests with values of A and B. The condition (X=3 OR X=4) is then applied as a residual condition. If DNF had been used, then four NUSI SELECT requests would be required.

    In the following expression the collection of (NUSI_A, NUSI_B) comprise a NUSI.

        (NUSI_A=1 OR NUSI_A=2) AND (NUSI_B=3 OR NUSI_B=4)

    In this case, DNF is better because the access path consists of four NUSI SELECTs, whereas the access path using CNF would require a full table scan.

    Consider an expression that involves a single column comparison using IN, such as the following.

        column IN (value_1, value_2, …)

    Internally, that expression is converted to CNF.

        column=value_1 OR column=value_2 OR …

    Therefore, the same access path is generated for either form.

    Assume an expression involves a multiple‑column comparison using an IN list, such as in the following example.

        column_1 IN (value_1, value_2, …)
        AND column_2 IN (value_3, …)

    The Optimizer converts this syntax internally to conjunctive normal form.

        (column_1=value_1 OR column_1=value_2 OR …)
         AND (column_2=value_3 OR …)

    Note how the converted form, conjunctive normal form (or CNF) differs from the second form, which is formulated in disjunctive normal form (DNF) as shown in the following example.

          (column­_1=value_1 AND column_2=value_3)
       OR (column_1=value_2 AND column_2=value_4)
       OR …

    The point is that semantically equivalent queries, when formulated with different syntax, often produce different access plans.

    The purpose of cost prediction is to provide the Optimizer with accurate estimates of the costs to perform various operations in various ways so the Optimizer can make cost‑based decisions.

    The cost of an operation is the service time required by subsystems to undertake an operation on an otherwise unloaded system. The Optimizer compares alternative ways of performing an operation based on estimates of CPU, I/O, and BYNET component service times and then chooses the alternative that has the lowest overall cost. There are practical situations where a particular approach should be excluded, or its likelihood greatly reduced, by increasing its predicted cost value, so a heuristic cost component is also included with the collection of various subsystem cost components.

    Overall cost, which is a linear combination of the subsystem costs, is the value used by the Optimizer to compare the various alternatives available to it. The cost prediction formulas and methods maintain individual subsystem level values in order to make it possible to determine how much of a given overall cost is accounted for by each of its CPU, I/O, BYNET, and heuristic component values. This information makes it possible to distinguish between
    I/O‑intensive and CPU‑intensive approaches, enabling an analyst to have a clear view of error sources when a situation occurs in which a predicted cost value is grossly inaccurate.

    You can capture subsystem cost values for individual steps in a query plan using either an SQL INSERT EXPLAIN request or the Teradata Index Wizard to funnel the appropriate information into the query capture database table QuerySteps, in the following column set.

  • EstCPUCost
  • EstIOCost
  • EstNetworkCost
  • EstHRCost
  • See Chapter 7: “Query Capture Facility” and “INSERT EXPLAIN” in SQL Data Manipulation Language for additional information.

    If query logging is enabled, the system captures the same information in DBQLStepTbl in the following column set.

  • EstCPUCost
  • EstIOCost
  • EstNetCost
  • EstHRCost
  • See Database Administration and “BEGIN QUERY LOGGING” in SQL Data Definition Language for additional information.

    The corresponding overall cost values are captured in the Cost column of the QCD QuerySteps table and in the EstProcTime column of the DBQL DBQLStepTbl tables, respectively.

    When predicting costs, there are 2 input data categories. First are values that characterize the runtime environment, and second are values which characterize the database entities involved in the request being optimized. The runtime environment includes hardware plat-form, operating system, I/O subsystem and database algorithms, essentially all the product set elements of a Teradata database system. I/O throughput rates, CPU speed, BYNET capacity, and code path lengths all are examples of runtime environment performance factors. Both CPU and I/O costing vary depending on the sizes of the data blocks containing the data accessed by a request that is being optimized.

    During query planning, the Optimizer estimates database entity characteristics using various kinds of metadata, including statistics, dynamic AMP samples, and a variety of estimates derived from these base data sources.

    Cost profiles provide an alternate method to DBS Control fields for specifying system defaults. If there is a conflict between a cost profile constant setting and its equivalent DBS Control field setting, the cost profile constant takes precedence and Teradata Database does not use the DBS Control field setting.

    You can use cost profiles to control Optimizer semantics for some operations. You can set cost profiles to be applicable system-wide or only to specific users using a CREATE USER or CREATE PROFILE request. For more information about setting a cost profile for a specific user or profile, see “CREATE USER” or “CREATE PROFILE” in SQL Data Definition Language.

    The following table lists the user‑accessible Optimizer cost profiles.

     

                      Cost Profile Constant

                                                                        Description

    Autocompression Cost Profile Constants

    AutoCompressDefault

    Controls whether the default for a column partitioning level for a table or a join index is AUTO COMPRESS or NO AUTO COMPRESS.

    This constant applies to ALTER TABLE, CREATE JOIN INDEX, CREATE TABLE, SHOW JOIN INDEX, SHOW TABLE, and SHOW requests with respect to the COLUMN option of the PARTITION BY clause.

    Teradata recommends that if you specify a value of 2 in some V cost profiles, you should only use cost profiles with values of 1 and 2 for AutoCompressDefault.

    The following are the valid values for AutoCompressDefault.

  • 0   The default is COLUMN AUTO COMPRESS if you specify COLUMN without specifying an AUTO COMPRESS or NO AUTO COMPRESS option for a column partitioning level when a table or join index is created or the partitioning of a table is modified by an ALTER TABLE … MODIFY request.
  • SHOW TABLE, SHOW JOIN INDEX, and SHOW requests report COLUMN (without AUTO COMPRESS or NO AUTO COMPRESS) for a column partitioning in their output.

    0 is for downward compatibility.

    AutoCompressDefault (continue)

  • 1   The default is COLUMN AUTO COMPRESS if you specify COLUMN without specifying an AUTO COMPRESS or NO AUTO COMPRESS option for a column partitioning level when a table or join index is created or the partitioning of a table is modified by an ALTER TABLE … MODIFY request.
  • SHOW TABLE, SHOW JOIN INDEX, and SHOW requests report either a COLUMN AUTO COMPRESS or COLUMN NO AUTO COMPRESS option for a column partitioning in their output.

    1 is the installed system default.

  • 2   The default is COLUMN NO AUTO COMPRESS if you specify COLUMN without specifying an AUTO COMPRESS or NO AUTO COMPRESS option for a column partitioning level when a table or join index is created or the partitioning of a table is modified by an ALTER TABLE … MODIFY request.
  • SHOW TABLE, SHOW JOIN INDEX, and SHOW requests report either a COLUMN AUTO COMPRESS or COLUMN NO AUTO COMPRESS option for a column partitioning in their output.

    COLLECT STATISTICS Cost Profile Constant

    StatsDefaultTimeThreshold

    Defines a default time threshold value in days for statistics recollection. Teradata Database only recollects statistics if this many days or more have passed since statistics were last collected.

    This setting is applicable only if a COLLECT STATISTICS request does not specify USING THRESHOLD DAYS.

    The valid range is -1 to 9999.

    The following are the valid coded values for StatsDefaultTimeThreshold.

  • -1   Disable time threshold checking.
  •  0    Use the DBS Control field DefaultTimeThreshold to define the default.
  • The default value is 0.

    StatsDefaultUserChangeThreshold

    Defines a user‑determined default data change percentage threshold value for statistics recollection. Teradata Database only recollects statistics if the data demographics have changed by this percentage since statistics were last collected.

    This setting is applicable only if a COLLECT STATISTICS request does not specify USING THRESHOLD PERCENT.

    The valid range is -1 to 9999.

    The following are the valid coded values for StatsDefaultUserChangeThreshold

  • -1   Disable user change threshold checking.
  •  0    Use the DBS Control field DefaultUserChangeThreshold to define the default.
  • The default value is 0.

    StatsSysChangeThresholdOption

    Defines a system‑determined default change threshold option for statistics recollection. Teradata Database only recollects statistics if the data demographics have changed by this percentage since statistics were last collected

    This setting is only applicable if DefaultUserChangeThreshold is disabled and a COLLECT STATISTICS request does not specify USING THRESHOLD PERCENT.

    The following are the valid values for StatsSysChangeThresholdOption.

  • 0  Use the DBS Control field SysChangeThresholdOption to define the default.
  • 1  Enable system‑determined change threshold checking for collecting statistics only when DBQL ObjectUsage (UDI counts) is enabled.
  • 2  Enable system‑determined change threshold checking for collecting statistics regardless of the ObjectUsage setting in DBQL logging options
  • 3  Disable system-determined change threshold checking.
  • The default value is 0.

    StatsSysSampleOption

    Defines a system‑determined default sampling option for statistics recollection.

    This setting is only applicable if a COLLECT STATISTICS request does not specify the USING SAMPLE option.

    The following are the valid values for StatsSysSampleOption.

  • 0  Use the DBS Control field SysSampleOption to define the default.
  • 1  Enable system‑determined sampling percentages up to 100%.
  • 2  Set the default sampling percentage to 100%, which means not to use sampling, but always collect full statistics.
  • The default value is 0.

    Partitioning Cost Profile Constants

    PartitioningConstraintForm

    Determines the form of the partitioning constraint text used. Existing usage rules and considerations continue to apply. See Database Design or SQL Data Definition Language for more information about partitioning constraint text forms.

    Only a user in a V cost profile can set the value for this cost profile constant.

    There is no default for this cost profile constant; however, you should set the value to 1 to make the additional information in the constraint form available for all partitioned tables.

    The following are the valid values for PartitioningConstraintForm.

  • 0   Use one of the 3 older forms of partitioning constraint text.
  • The partitioning constraint text for partitioning that does not use the partitioning features added in Teradata Database 14.0 uses one of the older forms of partitioning constraint text whether PartitioningConstraintForm is set to 0 or not.

  • 1   Use the newer form of partitioning constraint text introduced in 14.0 when creating a new partitioned table or when altering the partitioning of a table.
  • This form is required for any table that uses the partitioning features introduced by Teradata Database 14.0.

    PPICacheThrP

    Specifies the maximum amount of memory to be used for disk read operations that involve multiple index partitions.

    Only a user in a V cost profile can set the value for this cost profile constant.

    There is no default for this cost profile constant.

    This cost profile constant has the same function as the DBS Control performance group field PPICacheThrP except that the cost profile constant uses units of 1% instead of 0.1% and enables you to specify a fractional percentage. Existing usage rules and considerations apply to this cost profile constant.

    If you specify a value for this constant in a currently applicable cost profile for a session, the cost profile setting overrides the DBS Control field setting.

    If you do not set the PPICacheThrP cost profile, or if you set it to 0, the DBS Control field PPICacheThrP overrides the cost profile setting.

    The following table lists the cost profile algorithmic factors defined for parameterized value peeking.

     

      Cost Profile Algorithmic

                    Factor

                                                       Definition

    TacticalResp1

    Runtime CPU cost per AMP that determines whether a query is treated as a tactical or decision support request.

    The value is derived from the CPU time used for query execution in the AMPs.

    By definition, a request whose per-AMP CPU time  1 second is considered to be tactical and a request whose per-AMP CPU time > 1 second is considered to be a decision support request.

    TacticalResp2

    Runtime CPU cost per AMP that determines whether a query is treated as a tactical or decision support query when it is submitted as a HiPriority request.

    The value is derived from the CPU time used for query execution in the AMPs.

    HighParsingPTThreshold

    Threshold percentage of the parsing cost compared to the runtime CPU cost on which a determination is made on whether the request has a high parsing cost.

    HighParsingRTThreshold

    Threshold multiplication factor for the determination of runtime benefits for a query that has a high parsing cost.

    If the request has a high parsing cost, then the runtime CPU times of specific and generic plans should differ by at least this value multiplied times the specific CPU parsing time.

    HighParsingPTThreshold

    Threshold multiplication factor for the determination of runtime benefits for a query that has a low parsing cost.

    If the request has a low parsing cost, then the runtime CPU times of specific and generic plans should differ by at least this value multiplied times the specific CPU parsing time.

    UseHiPriority

    A flag that enables or disables the HiPriority-based decisions in the caching algorithm.

    ElapsedTimeThreshold

    Threshold multiplication factor by which the elapsed time of a specific plan execution (the sum of its parsing and run times) should exceed the elapsed time of the equivalent generic plan.

    EstimateCostFilter

    Threshold factor by which the estimated cost of a specific plan should be better than the estimated cost of the equivalent generic plan.

    Used for comparison of estimated costs.

    CompareEstimates

    A Cost Profile flag that enables or disables the estimate-based comparisons for deciding between using the generic and specific plan for a request.

    This flag is disabled by default.

    Use this flag to tune those cases where the system executes a bad generic plan the second time a cached query is encountered.

    The Optimizer has a set of internal functions that return various cost factors used to compute optimal relation access and join plans. The cost factors returned to the Optimizer by these functions include the calculated number of blocks in a relation, the cost of various disk operations required to access a relation or its indexes or both, the number of AMPs used for an operation, the number of AMPs configured per node, the cost of sorting a spool, the cost of duplicating rows across AMPs, hashing costs, and so on.

    The following properties apply to Optimizer cost estimates when statistics are available.

  • The Optimizer evaluates access selectivity based on demographic information.
  • If statistics have recently been collected on the primary index for a table, then it is likely, but not certain, that an accurate cardinality estimate is available to the Optimizer. If statistics are stale, then the derived statistics framework should minimize any problems with them.

  • The Optimizer uses primary index statistics to estimate the cardinality of a table.
  • Therefore, you should keep those statistics current to ensure optimum access and join planning. Collect new statistics on primary indexes when more than 10% of the total number of rows are added or deleted.

    When no statistics are available, or when the table has no primary index, the Optimizer estimates the total number of rows in a table based on information sampled from a dynamically selected AMP (see “Dynamic AMP Sampling” on page 181).

    For the following reasons, this might not be an accurate estimate of the cardinality of the table.

  • The table is relatively small and distributed unevenly across the AMPs.
  • The NUPI selected for a table causes its rows to be distributed unevenly across the AMPs.
  • When either of these properties is true, then there might be AMPs on which the number of rows is highly unrepresentative of the average population of rows in the other AMPs.

    If such an unrepresentative AMP is selected for the row estimate, the Optimizer might generate a bad estimate, and thus might not choose a plan (join, access path, and so on) that would execute the request most efficiently.

    Stale statistics often cause the Optimizer to produce a worse join plan than one based on estimated information from a dynamically sampled AMP.

    The only difference is that a plan based on outdated statistics is consistently bad, while a plan based on a dynamic AMP sample has a likelihood of providing a reasonably accurate statistical estimate.

    To guarantee that an index is used in processing a request, specify a constraint in the WHERE clause of a query on a value from an indexed column.

    If multiple NUSIs apply to such a WHERE constraint, and if the subject table is very large, then bit mapping provides the most efficient retrieval. For smaller tables, the Optimizer selects the index estimated to have the highest selectivity (fewest rows per index value).

    If statistics are not available for a NUSI column, then the Optimizer assumes that indexed values are distributed evenly. The Optimizer estimates the number of rows per indexed value by selecting an AMP dynamically based on its table ID and dividing the total number of rows from the subject table on that AMP by the number of distinct indexed values on the AMP. When distribution of index values is uneven, such an estimate can be unrealistic and result in poor access performance.

    Always use the EXPLAIN request modifier or the client Visual Explain utility to determine the plan the Optimizer will generate to perform a query.

    The Optimizer follows these guidelines for indexed access to table columns.

  • For NoPI tables, column access is done using secondary or join indexes. If no secondary or join indexes are defined for the column in question, then access is always done using a full‑table scan.
  • UPIs are used for fastest access to table data.
  • USIs are used only to process requests that employ equality constraints.
  • The best performance in joins is achieved when the following is possible.
  • Matching UPI values in one table with unique index values, either UPI or USI, in another table.
  • Using only a primary index when satisfying an equality or IN condition for a join.
  • NUPIs are used for single-AMP row selection or join processing to avoid sorting and redistribution of rows.
  • When statistics are not available for a table, the estimated cost of using an index is based on information from a single AMP. This estimate assumes an even distribution of index values. An uneven distribution impacts performance negatively.
  • Composite indexes are used only to process requests that employ equality constraints for all columns that comprise the index.
  • Note that you can define an index on a single column that is also part of a multicolumn index on the same table.

  • Bit mapping is used only when equality or range constraints involving multiple nonunique secondary indexes are applied to very large tables.
  • Use either a nonuniquely indexed column or a USI column in a nested join to avoid redistributing and sorting a large table.
  • For example, consider the following condition.

        table_1.column_1 = 1 AND table_1.column_2 = table_2.nusi

    Without using a Nested Join, where table_1 is duplicated and joined with table_1.nusi, both table_1 and table_2 might have to be sorted and redistributed before a Merge Join. This join is not high-performing when table_2 is large.