Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    86

    Unanswered: performance issue

    Hi All,
    I work with db2v7.2 fixpack5 aix 4.3. recently I have performace issue in our production box, I got several snapshot as follow
    Locks held currently = 75
    Lock waits = 8896
    Time database waited on locks (ms) = 801644
    Lock list memory in use (Bytes) = 72216
    Deadlocks detected = 0
    Lock escalations = 0
    Exclusive lock escalations = 0
    Agents currently waiting on locks = 0
    Lock Timeouts = 0

    Total sort heap allocated = 20
    Total sorts = 7816867
    Total sort time (ms) = 99465
    Sort overflows = 583575
    Active sorts = 7

    Buffer pool data logical reads = 149946504
    Buffer pool data physical reads = 1551274
    Asynchronous pool data page reads = 206717
    Buffer pool data writes = 297233
    Asynchronous pool data page writes = 271632
    Buffer pool index logical reads = 96675179
    Buffer pool index physical reads = 509169
    Asynchronous pool index page reads = 47812
    Buffer pool index writes = 131866
    Asynchronous pool index page writes = 115533
    Total buffer pool read time (ms) = 8061377
    Total buffer pool write time (ms) = 678858
    Total elapsed asynchronous read time = 198245
    Total elapsed asynchronous write time = 624718
    Asynchronous read requests = 11999
    LSN Gap cleaner triggers = 20513
    Dirty page steal cleaner triggers = 213
    Dirty page threshold cleaner triggers = 0
    Time waited for prefetch (ms) = 33808
    ******************************************

    (DBHEAP) = 5500
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 512
    Log buffer size (4KB) (LOGBUFSZ) = 512
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 10000
    Buffer pool size (pages) (BUFFPAGE) = 100000
    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) = 700

    Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128

    Sort list heap (4KB) (SORTHEAP) = 2048
    SQL statement heap (4KB) (STMTHEAP) = 2048
    Default application heap (4KB) (APPLHEAPSZ) = 1024
    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) = 50
    Lock timeout (sec) (LOCKTIMEOUT) = 90

    Changed pages threshold (CHNGPGS_THRESH) = 60
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = 7
    Number of I/O servers (NUM_IOSERVERS) = 14
    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 = 4
    Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

    Max number of active applications (MAXAPPLS) = 40
    Average number of active applications (AVG_APPLS) = 1
    Max DB files open per application (MAXFILOP) = 100
    Direct reads = 106309
    Direct writes = 547162
    Direct read requests = 11800
    Direct write requests = 14738
    Direct reads elapsed time (ms) = 2047
    Direct write elapsed time (ms) = 69228
    Database files closed = 2957
    Data pages copied to extended storage = 0
    Index pages copied to extended storage = 0
    Data pages copied from extended storage = 0
    Index pages copied from extended storage = 0
    Host execution elapsed time = 0.283761

    as you see I have 583575 sort overflows WHY? I increased sortheap from 256 to 2046 , I have 100000 bufferpool . could you please let me what should I do and why I have 583575 sort overflows.
    Thanks

  2. #2
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    David,

    give a look at
    http://www.db2mag.com/db_area/archiv...q1/hayes.shtml

    item 05. SORT MEMORY.

    You have an appl. problem, not a DB2 one.

    HTH
    Rodney Krick

  3. #3
    Join Date
    Feb 2002
    Posts
    96

    Question

    If the application is OLAP & Datawarehouse environment, really we do not know about users' ad-hoc queries. Because that SQL may contain lot of group by/order by, etc.
    In this situation how to overcome this sort overflow??

  4. #4
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    I don't thing there is an answer to this question. If you give the users the possibility to create their own queries, you should teach them how to do it. The most users (and application developers) know nothing about access path and performance. A good workshop can make wonders! If you teach them the basics of explain and show how to check the costs of the queries, they could optimize their work (and have more fun, of course).
    You can also "audit" your system, look for bottlenecks, find the users that are killing the maschine, and, and, and, ... but this is the daily work from a DBA, isn't it???

    Just my 2 cents.
    Rodney Krick

  5. #5
    Join Date
    Jan 2003
    Posts
    2

    Re: performance issue

    Originally posted by David2002
    Hi All,
    ........
    as you see I have 583575 sort overflows WHY? I increased sortheap from 256 to 2046 , I have 100000 bufferpool . could you please let me what should I do and why I have 583575 sort overflows.
    Thanks
    I'd not start with ad hock solution - increasing the size of sortheap. It could cause opposite effect.
    I will work on indexes.
    I'd perform query analyzing technique.
    I will monitor statements, find long performing ones, and than using DB2 different tools and try to see if indexes would help.

    Hope it makes a sense.

  6. #6
    Join Date
    Jan 2003
    Posts
    2

    Talking

    Originally posted by RKrick
    David,

    give a look at
    http://www.db2mag.com/db_area/archiv...q1/hayes.shtml

    item 05. SORT MEMORY.

    You have an appl. problem, not a DB2 one.

    HTH
    Well, RKrick, I tend to understand you, but there is a reality.
    We can name the problem as an appl. problem, manager will let DBA to fix it - it is on my (DBA) table.
    Just like in this case, guess who is - at the end of long story - fixing bad performances of very expensive CRM product (which is dynamically generating SQL statements like crazy).
    That is me, poor DBA, who understands all sides (being appl developer myself), but what matters.
    Fix it! Using DB2 DBA technique! (that's where I make my point - in many situations we can not change SQL queries since they are part of black box. I say, oh well at least, we can catch them)
    So, I still have to do performance tune-ups even if situation looks impossible. Even if you see you could do it better, but....
    ....at the end.... you always find the way; another index, extended storage, more memory, bigger box, 64-bit horse, ..... and after that - all over again

    that's the life.......

Posting Permissions

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