Thread: What Causes High CPU !!
05-14-09, 12:53 #1Registered User
- Join Date
- Sep 2008
Unanswered: What Causes High CPU !!
On our Mirror database, we restore it every night. People who draw reports usually complain of SLOW or even some times takes too long for simple queries. How can I find if AUTOMATIC MAINTENANCE is causing it.
Here is DB CFG
Database Configuration for Database POS 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 = IDENTITY 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 = RECOVERY User exit for logging status = NO 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 Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC Max storage for lock list (4KB) (LOCKLIST) = 50000 Percent. of lock lists per application (MAXLOCKS) = 52 Package cache size (4KB) (PCKCACHESZ) = 10000 Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES Sort list heap (4KB) (SORTHEAP) = 20000 Database heap (4KB) (DBHEAP) = 15000 Catalog cache size (4KB) (CATALOGCACHE_SZ) = 5000 Log buffer size (4KB) (LOGBUFSZ) = 4096 Utilities heap size (4KB) (UTIL_HEAP_SZ) = 10000 Buffer pool size (pages) (BUFFPAGE) = 305000 Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 15707 Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70 Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 2048 SQL statement heap (4KB) (STMTHEAP) = 8704 Default application heap (4KB) (APPLHEAPSZ) = 2048 Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384 Interval for checking deadlock (ms) (DLCHKTIME) = 10000 Lock timeout (sec) (LOCKTIMEOUT) = 20 Changed pages threshold (CHNGPGS_THRESH) = 10 Number of asynchronous page cleaners (NUM_IOCLEANERS) = 16 Number of I/O servers (NUM_IOSERVERS) = 105 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) = 500 Average number of active applications (AVG_APPLS) = 1 Max DB files open per application (MAXFILOP) = 2000 Log file size (4KB) (LOGFILSIZ) = 20000 Number of primary log files (LOGPRIMARY) = 30 Number of secondary log files (LOGSECOND) = 90 Changed path to log files (NEWLOGPATH) = Path to log files = E:\DB2\NODE Overflow log path (OVERFLOWLOGPATH) = Mirror log path (MIRRORLOGPATH) = First active log file = S0116080.LO Block log on disk full (BLK_LOG_DSK_FUL) = NO Percent max primary 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) = 420 Log retain for recovery enabled (LOGRETAIN) = RECOVERY 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) = LOGRETAIN Options for logarchmeth1 (LOGARCHOPT1) = Second log archive method (LOGARCHMETH2) = OFF Options for logarchmeth2 (LOGARCHOPT2) = Failover log archive path (FAILARCHPATH) = 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 (RES 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 statement statistics (AUTO_STMT_STATS) = OFF Automatic statistics profiling (AUTO_STATS_PROF) = OFF Automatic profile updates (AUTO_PROF_UPD) = OFF Automatic reorganization (AUTO_REORG) = OFF
Still there are some times when this mirror works like super fast.
Thanks for help
05-14-09, 14:01 #2:-)
Provided Answers: 1Originally Posted by DBFinder
- Join Date
- Jun 2003
- Toronto, Canada
I would start by looking at the database and dynamic SQL snapshots to determine if there are memory shortages, I/O issues or inefficient queries.---
"It does not work" is not a valid problem statement.