Results 1 to 5 of 5

Thread: Cpu usage full

  1. #1
    Join Date
    Aug 2009

    Unanswered: Cpu usage full

    Hi all,
    I am facing severe performance problem. We are using DB2 9.1 for windows.
    We got most of the time 95-100% CPU usage where db2syscs utilized 85-90%.We are using Misys's Trade Innovation application.

    My Database backup size is more than 6GB.
    Hardware is IBM xSeries_346, Intel(R) Xeon(TM) CPU 3.20GHz 3.20 GHz, 6 GB RAM.
    I am explaining configuration parameter.
    Database Manager Configuration

    Node type = Database Server with local and remote clients
    Database manager configuration release level = 0x0b00
    Maximum total of files open (MAXTOTFILOP) = 16000
    CPU speed (millisec/instruction) (CPUSPEED) = 2.361721e-007
    Max number of concurrently active databases (NUMDB) = 8
    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) = C:\Program Files\IBM\SQLLIB\java\jdk
    Diagnostic error capture level (DIAGLEVEL) = 3
    Notify Level (NOTIFYLEVEL) = 3
    Diagnostic data directory path (DIAGPATH) =
    Default database monitor switches
    Buffer pool (DFT_MON_BUFPOOL) = OFF
    Lock (DFT_MON_LOCK) = OFF
    Sort (DFT_MON_SORT) = OFF
    Statement (DFT_MON_STMT) = OFF
    Table (DFT_MON_TABLE) = OFF
    Timestamp (DFT_MON_TIMESTAMP) = ON
    Unit of work (DFT_MON_UOW) = OFF
    Monitor health of instance and databases (HEALTH_MON) = ON
    SYSADM group name (SYSADM_GROUP) =
    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) = IBMkrb5
    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) = C:
    Database monitor heap size (4KB) (MON_HEAP_SZ) = 256
    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
    Agent stack size (AGENT_STACK_SZ) = 16
    Minimum committed private memory (4KB) (MIN_PRIV_MEM) = 32
    Private memory threshold (4KB) (PRIV_MEM_THRESH) = 20000
    Sort heap threshold (4KB) (SHEAPTHRES) = 0
    Directory cache support (DIR_CACHE) = YES
    Application support layer heap size (4KB) (ASLHEAPSZ) = 15
    Max requester I/O block size (bytes) (RQRIOBLK) = 32767
    DOS requester I/O block size (bytes) (DOS_RQRIOBLK) = 4096
    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) = 200
    Agent pool size (NUM_POOLAGENTS) = 100(calculated)
    Initial number of agents in pool (NUM_INITAGENTS) = 0
    Max number of coordinating agents (MAX_COORDAGENTS) = MAXAGENTS
    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 log size (SPM_LOG_FILE_SZ) = 256
    SPM resync agent limit (SPM_MAX_RESYNC) = 20
    SPM log path (SPM_LOG_PATH) =
    NetBIOS Workstation name (NNAME) =
    TCP/IP Service name (SVCENAME) = db2inst1c
    Discovery mode (DISCOVER) = SEARCH
    Discover server instance (DISCOVER_INST) = ENABLE
    Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
    Enable intra-partition parallelism (INTRA_PARALLEL) = NO
    No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC
    No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC
    db2start/db2stop timeout (min) (START_STOP_TIME) = 10

    Also attached the DB2 configuration parameters.

    Is there any configuration tunning for getting better performance?

    Keenly awaiting for experts advice..............

    With Regards
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2007
    take a snapshot for dynamic sql and check for total exec time which is high , check for queires which is doing tablescan using explain, high cpu utilization is coz bad sql statements

  3. #3
    Join Date
    Aug 2009

    Thanks Paul

    Hi Paul,

    Thanks for your reply. Yesterday i got some db2 script for Index creation from my application vendor. Now it is working fine.
    Anyways i may face the problem in future. Can you please tell me how to take snapshot for dynamic sql and check for total exec time ?

    Waiting for your response...

    Best Regards

  4. #4
    Join Date
    Oct 2007
    on the monitors switches statement uow on
    db2 get snapshot for dynamic sql on dbname
    u will get all dynamic sql statements details

  5. #5
    Join Date
    Nov 2009
    Provided Answers: 1

    CPU usage


    Adding to Paul's suggestions I would like to give another option too. You can try the db2mon tool. Its a freeware and it works awesome. This tool helps you monitor the dynamic sql statements and it shows the CPU usage in time for each SQL. You can give a try !!!



Posting Permissions

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