Top area of the window
The top area of the Optimization Parameters window identifies the database
and states when the SQL statement was explained.
The columns in the window show configuration parameters and bind options
that were used at the time of the explain, and their current values.
Rows in the Optimization Parameters window
The rows of the Optimization Parameters window represent the following
values used by the SQL optimizer at the time of an explain invocation:
Database Configuration Parameters
To change these values, select the database in the Control Center, click on
mouse button 2 and select Configure from the pop-up menu.
Then select the notebook tabs referenced below. For more information,
use the help from this notebook, or see the Administration
Guide.
Configuration Parameters
- AVG_APPLS
- The average number of active applications assumed to be connected to the
database at the time of the access plan's execution. To change
this parameter, use the Applications tab.
- SORTHEAP
- The amount of private memory to be used for each sort (in 4KB
pages). To change this parameter, use the Performance tab.
- LOCKLIST
- The amount of storage allocated to the lock list (in 4KB pages). To
change this parameter, use the Performance tab.
- MAXLOCKS
- The percentage of the lock list assumed to be available for each
user. To change this parameter, use the Applications tab.
- NUM_FREQVALUES
- The number of "most frequent values" that are collected when the WITH
DISTRIBUTION option is used with the runstats command.
To change this parameter, use the Status tab.
- NUM_QUANTILES
- The number of quantiles that are collected when the WITH DISTRIBUTION
option is used with the runstats command. To change this
parameter, use the Status tab.
- DBHEAP
- The amount of shared memory available for use by the database (in 4KB
pages) used or sorts in intra-partition
parallelism or full parallelism (intra-partition and inter-partition). To change this
parameter, use the Performance tab.
- BUFFPAGE
- The size of the buffer pool. To change this parameter, use the
Performance tab.
| Note: | This configuration parameter will only be used if a -1 is specified in the
size field of any buffer pool in the buffer pool system catalog.
|
Calculated Values
- LOCKS_AVAIL
- The estimated number of locks available for each user. This value
is calculated by the optimizer using the values of LOCKLIST and
MAXLOCKS.
Database Manager Configuration Parameters
- CPUSPEED
- The average time (in milliseconds) to execute a CPU instruction.
This value is calculated when DB2 is installed, and is used for estimating CPU
costs. To change this parameter, select the instance in the Control
Center, and select Configure from the pop-up menu. This
parameter is in the Environment tab.
| Note: | Use caution if you decide to modify this parameter, and change it only if
doing "what if" analysis for alternative machine types. When you are
finished, always reset it to -1 so that its value will be set back
automatically to the CPU speed of the current machine.
|
-
COMM_BANDWIDTH
- The bandwidth, in MB/SU (megabytes/second), available between nodes for
data transfer.
(This item is displayed only for inter-partition parallelism or full
parallelism ( intra-partition and
inter-partition)).
Bind Options
- DEGREE
- The degree of parallelism. This parameter is not displayed if the
type of parallelism is None.
- QUERYOPT
- The query optimization class.
- ISOLATION
- The isolation level. Possible values
are cursor stability, repeatable read, read stability, and uncommitted
read.
- BLOCK
- The type of cursor blocking. Possible
types are no cursor blocking, blocking all cursors, or blocking only
unambiguous cursors.