Several configuration parameters affect the access plan chosen by the SQL compiler. Many of these are appropriate to a single-partition database and some are only appropriate to a partitioned database. When working with configuration parameters in a partitioned database, it is recommended that the values used for each parameter be the same on all partitions.
When working in a federated system, if the majority of your queries access nicknames then consider the type of query you are sending before changing your environment. For example, the buffer pool does not cache pages from data sources; as such, increasing the buffpage parameter value does not guarantee that the optimizer will consider additional alternatives when creating an access plan for queries containing nicknames. (Data sources are DBMSs and data within the federated system.) Also, the optimizer may decide that local materialization of data source tables is the least cost route or a necessary step for a sort operation. In that case, increasing the resources available to DB2 Universal Database may speed performance. For additional information, see Server Options Affecting Federated Database Queries and Database Shared Memory.
Following is a list of configuration parameters that affect the access plan chosen by the SQL compiler:
When selecting the access plan, the optimizer considers the I/O cost of fetching pages from disk to the buffer pool. In its calculations, the optimizer will estimate the number of I/Os required to satisfy a query. This estimate includes a prediction of buffer pool usage, since additional physical I/Os are not required to read rows in a page that is already in the buffer pool. The optimizer considers the value of the npages column in the BUFFERPOOLS system catalog tables in estimating whether a page will be found in the buffer pool.
The I/O costs of reading the tables can have an impact on :
You can have more than one buffer pool in a database. You can also have more than one buffer pool in a partitioned database. The new buffer pool can be selectively added to each of the partitions in the database or across all partitions. The npages column in the BUFFERPOOLS and BUFFERPOOLSNODE system catalog tables are used for estimating in a partitioned database.
The dft_degree configuration parameter specifies the default value for the CURRENT DEGREE special register and the DEGREE bind option. A value of one (1) means no intra-partition parallelism. A value of minus one (-1) means the optimizer determines the degree of intra-partition parallelism based on the number of processors and the type of query.
When compiling SQL queries, you can use the query optimization class to direct the optimizer to use different degrees of optimization. For more information on selecting a suitable query optimization class, see Adjusting the Optimization Class.
The avg_appls parameter is used by the SQL optimizer to help estimate how much of the buffer pool will be available at run-time for the access plan chosen. Higher values for this parameter can influence the optimizer to choose an access plan for queries that will be more conservative in its buffer pool usage. A value of 1 for this parameter will cause the optimizer to treat the entire buffer pool as being available to the application.
A sort is considered to be "piped" if it does not require a temporary table to store the final, sorted list of data. That is, the results of the sort can be read in a single, sequential access. Piped sorts result in better performance than non-piped sorts and will be used if possible. (See Influence of Sorting on the Optimizer for a definition of non-piped sorts compared to piped sorts.)
When choosing an access plan, the optimizer estimates the cost of the sort operations, including evaluating whether a sort can be piped, by:
When the isolation level (see Concurrency) being used is repeatable read (RR), the SQL optimizer will consider the values of the locklist and maxlocks parameters to determine whether it is likely that row level locks will be escalated to a table level lock. If the optimizer predicts that lock escalation will occur for a table access, then it will choose a table level lock for the access plan, rather than incurring the overhead of lock escalation during the execution of the query.
The CPU speed is used by the SQL optimizer to estimate the cost of performing certain operations. The optimizer uses these CPU cost estimations along with various I/O cost estimations to select the best access plan for a query.
The CPU speed of a machine can have a significant influence on the access plan chosen. This configuration parameter is automatically set to an appropriate value when the database is installed or migrated. You should only adjust this parameter if you are modelling a production environment on a test system, or to assess the impact of a hardware change. Using this parameter to model a different hardware environment allows you to observe the access plan that will be chosen for that environment.
The size of the statement heap does not influence the optimizer in choosing different access paths; however, it can affect the amount of optimization that will be performed for complex SQL statements.
If the stmtheap parameter is not set large enough, you may receive an SQL warning indicating that there is not enough memory available to process the statement. For example, SQLCODE +437 (SQLSTATE 01602) can indicate that the amount of optimization that has been used to compile a statement is less than the amount that you requested when you specified the query optimization class. (See Adjusting the Optimization Class for more information.)
When this parameter has a value of "ANY", then the optimizer chooses the degree of parallelism to be used. If other than "ANY" is present, then the user-specified value is used to determine the degree of parallelism for the application.
Communications bandwidth is used by the optimizer to determine access paths. The optimizer uses the value in this parameter to estimate the cost of performing certain operations between the database partition servers of a partitioned database.
For additional information, see Tuning Configuration Parameters.