Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008

    Unanswered: package cache hit ratio is so less

    Hi ,
    i am facing problem in package cache hit ratio its between 37-45%
    which is very less.

    here are my database configuration snapshot

    Database Configuration for Database tamlogin

    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 = 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
    Database heap (4KB) (DBHEAP) = 1200
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
    Log buffer size (4KB) (LOGBUFSZ) = 128
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
    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) = 8096

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

    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
    Sort list heap (4KB) (SORTHEAP) = 256
    SQL statement heap (4KB) (STMTHEAP) = 2048
    Default application heap (4KB) (APPLHEAPSZ) = 256
    Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
    Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

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

    Changed pages threshold (CHNGPGS_THRESH) = 60
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
    Number of I/O servers (NUM_IOSERVERS) = 3
    Index sort flag (INDEXSORT) = YES
    Sequential detect flag (SEQDETECT) = YES
    Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC

    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) = 1000
    Number of primary log files (LOGPRIMARY) = 3
    Number of secondary log files (LOGSECOND) = 2
    Changed path to log files (NEWLOGPATH) =
    Path to log files = /san/db/smsdb/smsdb/NODE0000/SQL00001/SQLOGDIR/
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file = S0000858.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) = 100
    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 (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) =

    I update parameter PCKCACHESZ by 2000 but hit ratio same then by 4096 and again then by 8096 but the hit ration is still same...please look the snapshot and suggest what to do for increasing package hit ratio....

    waiting for the reply...
    Pawan Kumar

  2. #2
    Join Date
    Jul 2008
    I think MAX_APPLS is 1000 by default thus making your PCKCACHESZ = 8000 only.
    ==> You can raise this setting up to where you don't have any more pb with the hit ratio...
    To geek or not to geek !

  3. #3
    Join Date
    Jan 2003
    Provided Answers: 5
    This is one of those situations where throwing more memory at it is probably not going to work. You also need to look at what uses the package cache. This is where all the SQL goes to see if it has been seen before. If so, DB2 already has an access plan and can cut some CPU usage. Unfortunately, DB2 must match the SQL exactly to gain this benefit. So, similar SQL statements are treated as being different and a new access plan must be generated and you hit ratio goes down. For example "select * from mytable where code = 1" and "select * from mytable where code = 2" are different in DB2's eyes. If the queries were coded and prepared like "select * from mytable where code = ?" then the statements would be the same and you hit ratio would go up and you CPU usage would go down.


  4. #4
    Join Date
    Mar 2008
    PCKCACHESZ parameter depend on RAM memory directly or what?
    if i increase till problem short out will not affect on other parameters of database???
    Pawan Kumar

  5. #5
    Join Date
    Jan 2003
    Provided Answers: 5
    Package cache does come from RAM. But Like I tried to state earlier, increasing package cache might not fix your problem. You can try, and see what happens. And yes, increasing it will keep memory from other resources.


  6. #6
    Join Date
    May 2003
    Two things to remember:

    1. If your database has recently been activated (by first conneciton or explicitly with activate command) the packages have to be loaded the first time in the package cache.

    2. If SQL statements are not parameterized and they each have different syntax with regard to literals in the WHERE clause, then they will rarely be in the package cache when exectued.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jul 2006
    Pune , India
    Quote Originally Posted by Marcus_A
    Two things to remember:

    2. If SQL statements are not parameterized and they each have different syntax with regard to literals in the WHERE clause, then they will rarely be in the package cache when exectued.
    point 2 has helped us increase PC hit ratio grom 40 to 80%
    also if on v9 then its automatic i guess
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

Posting Permissions

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