Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Location
    Bangalore
    Posts
    12

    Angry Unanswered: Bufferpool HIT ratio too low ????

    Hi ,
    One of my bufferpools is showing BP hit ratio low most of times and 0% at times.

    Onle one tablespace with one table is allocated to this BP. The tablespace is of 32 partitions ,each of PRIQTY 22320K & SECQTY 2232K ).

    The platinum subsystem analyser data for that BP at an instant is ..

    VP_ALLOC 20000K
    HP_ALLOC 20000K
    BUFHIT 0.00%
    GETPAGE 169260
    GETPAGE_SQ 0
    SYNCRIO 9421
    SYNCRIO_SQ 0
    PF_REQ 17122
    PF_IO 9799
    PF_PGS 218395
    PF_PIO 22.3
    SPF_REQ 0
    SPF_IO 0
    SPF_PGS 0
    SPF_PIO 0
    LPF_REQ 0
    LPF_IO 0
    LPF_PGS 0
    LPF_PIO 0
    DPF_REQ 17122
    DPF_IO 9799
    DPF_PGS 218395
    DPF_PIO 22.3
    BUF_UPDATE 456
    SYNCWIO 22
    ASYWIO 67
    ASYWIO_PGS 114
    ASYWIO_PIO 1.7
    PAGING_RIO 7
    PAGING_WIO 0

    could some one help me what is the reason for this very low HIT ratio ?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There are two probable explanations:

    1. The first I/O from a data page will never be in the buffer pool. It is only when retrieving the same page again can it possibly be found in the buffer pool. Did you excute the exact same SQL statements more than once?

    2. If the application is doing tablespace scans (or scans of an entire partition) then the likelihood of a buffer pool hit is much lower because the entire tablespace (or even entire partition) cannot fit into the buffer pool. So that means as the tablespace is scanned, the buffer pool starts filling up, and the least recently used pages are kicked out of the buffer pool. So by the end of the scan, only the last part of the tablespace is in the buffer pool. The next query with a tablespace scan starts with the first part of the tablespace.

    In order to analyze this correctly, it helps to see the SQL that go with the statistics you listed. I also noted that there is no sequential prefetch, which is not good if you are doing tablespace scans.

    It is generally a bad idea to have only one table in a buffer pool. Usually a DB2 subsystem should have no more than 3 4K buffer pools. A few number of larger buffer pools work better than a large number of smaller buffer pools.

    Note: Please list your DB2 version and Operating System in future posts. (DB2 for OS/390 version ?).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2004
    Location
    Bangalore
    Posts
    12
    DB2 V6.1 & OS390 V2.8

    Marcus ,
    The highest resource consuming SQL is a fetch cursor that is not using indexes.So it should be doing TS scan.

    But the SQL contains join of 3 tables.only one of the table belongs to the BP in problem. The othe rtwo table are allocated to other BP.
    In such cases which bufferpool does DB2 use ??

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Each table uses the bufferpool assigned for that tablespace. For a join, the result set may be materialized (before you fetch each row) in a system temporary tables (with associated tablespace) which also has a bufferpool assigned to it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Just to clarify the above post, system temporary (or work file) tables (such as needed for building answer sets in a declare cursor that needs to be materialized) reside in database DSNDB07. There is a bufferpool assigned at the database level for any tablespace created by DB2 in that database.
    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
    Jun 2004
    Location
    Bangalore
    Posts
    12
    thanks marcus. That was really helpful

Posting Permissions

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