Spilled sorts

[rdudejr]

Hi all,

Outside of changing the SQL of the query or creating indexes, what can
one do in order to prevent sort spills. Im thinking along the lines of
the db cfg and dbm cfg files. Ill post the current setup we have of
one database which is having problems with these:

db2 get dbm cfg:

Database manager configuration release level = 0x0a00

CPU speed (millisec/instruction) (CPUSPEED) = 4.723442e-07
Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02

Max number of concurrently active databases (NUMDB) = 8
Data Links support (DATALINKS) = NO
Federated Database System Support (FEDERATED) = NO
Transaction processor monitor name (TP_MON_NAME) =

Default charge-back account (DFT_ACCOUNT_STR) =

Java Development Kit installation path (JDK_PATH) =
/usr/java14_64

Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) =
/apps/db2diag/db2inst2

Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = ON
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = ON
Statement (DFT_MON_STMT) = ON
Table (DFT_MON_TABLE) = OFF
Timestamp (DFT_MON_TIMESTAMP) = ON
Unit of work (DFT_MON_UOW) = ON
Monitor health of instance and databases (HEALTH_MON) = ON

SYSADM group name (SYSADM_GROUP) = DB2GRP1
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =

Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
Client Kerberos Plugin (CLNT_KRB_PLUGIN) =
Group Plugin (GROUP_PLUGIN) =
GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =
Server Connection Authentication (SRVCON_AUTH) =
NOT_SPECIFIED
Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Bypass federated authentication (FED_NOAUTH) = NO

Default database path (DFTDBPATH) =
/home/db2inst2

Database monitor heap size (4KB) (MON_HEAP_SZ) = 520
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC
Backup buffer default size (4KB) (BACKBUFSZ) = 1024
Restore buffer default size (4KB) (RESTBUFSZ) = 1024

Sort heap threshold (4KB) (SHEAPTHRES) = 400000

Directory cache support (DIR_CACHE) = YES

Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
Query heap size (4KB) (QUERY_HEAP_SZ) = 1000

Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

Priority of agents (AGENTPRI) = SYSTEM
Max number of existing agents (MAXAGENTS) = 400
Agent pool size (NUM_POOLAGENTS) = 160
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = (MAXAGENTS -
NUM_INITAGENTS)
Max no. of concurrent coordinating agents (MAXCAGENTS) =
MAX_COORDAGENTS
Max number of client connections (MAX_CONNECTIONS) =
MAX_COORDAGENTS

Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) =
MAX_COORDAGENTS
Initial number of fenced processes (NUM_INITFENCED) = 0

Index re-creation time and redo index build (INDEXREC) = RESTART

Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180

SPM name (SPM_NAME) = hdmcjun1
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =

TCP/IP Service name (SVCENAME) = DB2_db2inst2
Discovery mode (DISCOVER) = SEARCH
Discover server instance (DISCOVER_INST) = ENABLE

Maximum query degree of parallelism (MAX_QUERYDEGREE) = 4
Enable intra-partition parallelism (INTRA_PARALLEL) = YES

No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 4220
Number of FCM request blocks (FCM_NUM_RQB) = AUTOMATIC
Number of FCM connection entries (FCM_NUM_CONNECT) = AUTOMATIC
Number of FCM message anchors (FCM_NUM_ANCHORS) = AUTOMATIC

Node connection elapse time (sec) (CONN_ELAPSE) = 10
Max number of node connection retries (MAX_CONNRETRIES) = 5
Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

db2start/db2stop timeout (min) (START_STOP_TIME) = 10
db2 get db cfg

Database Configuration for Database

Database configuration release level = 0x0a00
Database release level = 0x0a00

Database territory = US
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Database collating sequence = BINARY
Alternate collating sequence (ALT_COLLATE) =
Database page size = 4096

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Discovery support for this database (DISCOVER_DB) = ENABLE

Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20

Backup pending = NO

Database is consistent = NO
Rollforward pending = NO
Restore pending = NO

Multi-page file allocation enabled = YES

Log retain for recovery status = NO
User exit for logging status = YES

Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0

Database heap (4KB) (DBHEAP) = 10000
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 717
Log buffer size (4KB) (LOGBUFSZ) = 512
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 290779
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 4096

Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 50000
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 1024

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 25600
Sort list heap (4KB) (SORTHEAP) = 25600
SQL statement heap (4KB) (STMTHEAP) = 4096
Default application heap (4KB) (APPLHEAPSZ) = 1024
Package cache size (4KB) (PCKCACHESZ) = 1600
Statistics heap size (4KB) (STAT_HEAP_SZ) = 20000

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 58
Lock timeout (sec) (LOCKTIMEOUT) = -1

Changed pages threshold (CHNGPGS_THRESH) = 45
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 8
Number of I/O servers (NUM_IOSERVERS) = 20
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32

Track modified pages (TRACKMOD) = OFF

Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

Max number of active applications (MAXAPPLS) = AUTOMATIC
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64

Log file size (4KB) (LOGFILSIZ) = 10000
Number of primary log files (LOGPRIMARY) = 100
Number of secondary log files (LOGSECOND) = 150
Changed path to log files (NEWLOGPATH) =
Path to log files =
/appsdata/db2logs/db2inst2/warehous/NODE0000/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0015869.LOG
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active log space by transaction(MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 120
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF

HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC

First log archive method (LOGARCHMETH1) = TSM:DB2LOGS
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
/appsdata/db2backups/failarch/
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
Vendor options (VENDOROPT) =

Auto restart enabled (AUTORESTART) = ON
Index re-creation time and redo index build (INDEXREC) = SYSTEM
(RESTART)
Log pages during index build (LOGINDEXBUILD) = OFF
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366

TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =

Automatic maintenance (AUTO_MAINT) = OFF
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = OFF
Automatic runstats (AUTO_RUNSTATS) = OFF
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF

[Phil Sherman]

What makes you think you have a problem?

You cannot prevent sort spills. Sort spills occur when there is more
data to sort than will fit into the allocated workspace. Your allocation
of 25k+ pages will provide a 100mb sort work space. The sort heap
threshold will allow a few less than 40 concurrent sorts before
restricting the storage allocated to the next (concurrent) sort. Don't
forget that there are overall memory limits on how much real memory an
instance can use.

I'm assuming you're not running into the "hard" limit that
SHEAPTHRES_SHR causes because you stated that the sorts are spilling and
not failing. Do a search on the various sortheap controlling parameters
in the online rinformation center to get additional information about them.

You should also post additional information about your operating system,
db2 service level, and memory available on the system.

You've sspecified a large sort work area but are using a miniscule
default for the database buffer pools. The information you provided does
not indicate the size of the buffer pools, only the default size if you
haven't modified them.

You need to get a databbase snapshot to determine the percentage of
sorts that are overflowing. Better is to get two of them with a time
interval between them and use both to determine what happened over the
interval.

Phil Sherman
rdudejr wrote:

Hi all,

Outside of changing the SQL of the query or creating indexes, what can
one do in order to prevent sort spills. Im thinking along the lines of
the db cfg and dbm cfg files. Ill post the current setup we have of
one database which is having problems with these:

db2 get dbm cfg:

Database manager configuration release level = 0x0a00

Sort heap threshold (4KB) (SHEAPTHRES) = 400000

db2 get db cfg

Database Configuration for Database

>
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 25600
Sort list heap (4KB) (SORTHEAP) = 25600

[Ian]

rdudejr wrote:

Hi all,

Outside of changing the SQL of the query or creating indexes, what can
one do in order to prevent sort spills. Im thinking along the lines of
the db cfg and dbm cfg files. Ill post the current setup we have of
one database which is having problems with these:

db2 get dbm cfg:

Enable intra-partition parallelism (INTRA_PARALLEL) = YES
Sort heap threshold (4KB) (SHEAPTHRES) = 400000
db2 get db cfg

Database Configuration for Database

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 25600
Sort list heap (4KB) (SORTHEAP) = 25600

This is potentially a serious problem.

1) SHEAPTHRES_SHR is very small compared to SHEAPTHRES. Meaning,
unless you have a lot of databases, this database will only be
able to allocate a small amount of the total available sort
memory. (i.e. you might consider setting SHEAPTHRES_SHR to
the default (= SHEAPTHRES)

2) Setting SHEAPTHRES_SHR = SORTHEAP means that one sort can
allocate potentially allocate ALL sort memory available to the
database. Usually the goal is to have

SHEAPTHRES_SHR = SORTHEAP * <max concurrent sorts>

[rdudejr]

The db2 level is v8 fp 10, on AIX 5.3, 16GB memory, 5 Processors, and
im not sure as to the number of disks as we use lpar's on an IBM san so
the number varies. We have 4 containers for all tablespaces.

Also, we only have 2 databases on this instance. So the resources
should be allocated so that they are dedicated to the one database.
The other is very small, and supports the first.