Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: Predicting Database Slowness

    OLTP system running DB2 9.5 on AIX system works fine currently however there is no archiving setup hence I'm curious about the following
    1. Will it take longer response time in future to fetch data from the table because of index tree growth ?

    2. Any method available to 'forecast' database slowness before it happens ?
    Like if index read efficiency is coming down from 5 to 20 or index level has grown from 5 levels to 6 levels ?

    3. Is there is any ideal or desired database size expected to support an OLTP db supporting around 3000 users

    Thank You
    Tony

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Why don't you put your archive data into a separate table? That way, you don't have any performance impact on the operational data.

    Regarding the ideal database size, it really depends on the hardware you have. Running 3000 users on a thinkpad is a stupid idea, of course, if those users are more active. If each of the users runs one transaction per day, such a thinkpad may be mostly idle.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Tony Tsang
    OLTP system running DB2 9.5 on AIX system works fine currently however there is no archiving setup hence I'm curious about the following
    1. Will it take longer response time in future to fetch data from the table because of index tree growth ?

    2. Any method available to 'forecast' database slowness before it happens ?
    Like if index read efficiency is coming down from 5 to 20 or index level has grown from 5 levels to 6 levels ?

    3. Is there is any ideal or desired database size expected to support an OLTP db supporting around 3000 users

    Thank You
    Tony
    Probably the biggest factor is going to be buffer pool hit ratio for OLTP systems. Do some research on how to calculate this, and how to raise the bufferpool hit ratio for the data and indexes, but never use more memory than actually exists on the server (real memory, not virtual).

    Through in a few reorgs when appropriate (in-place if you don't have a maintenance window) and you should be in decent shape.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Sep 2009
    Posts
    3
    Stolze: Unfortunately we do not have a archiving mechanism yet.

    Marcus: Do you mean periodic calculation of BP Hit ratio would provide more insight to forecast db slowness rather than index read ratio(rows read/rows selected) and synchronous read percentage as suggested by Scott Hayes in DBI Blogs ?

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Tony Tsang
    Stolze: Unfortunately we do not have a archiving mechanism yet.

    Marcus: Do you mean periodic calculation of BP Hit ratio would provide more insight to forecast db slowness rather than index read ratio(rows read/rows selected) and synchronous read percentage as suggested by Scott Hayes in DBI Blogs ?
    I am not necessarily saying that. But I don't really track the index read ratio either at the database level, becasue I prefer to analyze every SQL statement for elapsed time using a snapshot for dynamic SQL, or an explain for static SQL. If I take care of each statement and make sure it is optimized, then the database totals will take care of themselves.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Sep 2009
    Posts
    3
    Thanks.

    From db cache I can get the list of Dynamic SQLs. Any method available to get static SQL cache in DB2 9.1 ?

    Thanks
    Tony

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Tony Tsang
    Any method available to get static SQL cache in DB2 9.1 ?
    You can use db2pd -static to obtain the list of packages in the cache and db2expln to analyze these packages.
    ---
    "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
  •