Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008

    Unanswered: What Causes High CPU !!

    Hello Everybody,

    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
    The second database on same instance is restored monthly. When it is slow, both DBs are slow.

    Still there are some times when this mirror works like super fast.

    Thanks for help


  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Quote Originally Posted by DBFinder
    How can I find if AUTOMATIC MAINTENANCE is causing it.
    It is quite unlikely that automatic maintenance causes the problems, because it is switched off, as evidenced by the database configuration.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts