Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Location
    India
    Posts
    246

    Unanswered: BP not showing hit ratio

    Dear all,

    version: db2 10.1 wse on rhel 6

    i have two BPs in my DB

    1. IBMDEFAULTBP


    2. CREATE BUFFERPOOL DWHPOOL
    IMMEDIATE
    ALL DBPARTITIONNUMS
    SIZE 1000
    NUMBLOCKPAGES 0
    PAGESIZE 32K;

    while checking bp hit ratio DWHPOOL is not showing any hit ratio

    Query to check hit ratio is (without making MONITOR switches ON) :

    select current timestamp as Time,
    member,
    substr(bp_name,1,20) as BP_name,
    case when POOL_DATA_L_READS < 1000 then null else cast (100*(float(POOL_DATA_L_READS - POOL_DATA_P_READS)) / POOL_DATA_L_READS as decimal(4,1)) end as Data_Hit_R,
    cast( 100 * case when pool_data_p_reads+pool_temp_data_p_reads + pool_index_p_reads+pool_temp_index_p_reads < 1000 then null else 1.0 - ( float(pool_data_p_reads+pool_temp_data_p_reads +pool_index_p_reads+pool_temp_index_p_reads) -float(pool_async_data_reads+pool_async_index_reads )) /float(pool_data_p_reads+pool_temp_data_p_reads + pool_index_p_reads+pool_temp_index_p_reads) end as decimal(4,1)) as Prefetch_hit_ratio,
    cast( 100 * case when pool_async_index_reads+pool_async_data_reads < 1000 then null else unread_prefetch_pages /float(pool_async_index_reads+pool_async_data_reads ) end as decimal(4,1)) as Pct_P_F_unread
    from table(mon_get_bufferpool(null,null)) as t
    where t.bp_name not like 'IBMSYSTEMBP%'



    OUTPUT:

    TIME MEMBER BP_NAME DATA_HIT_R PREFETCH_HIT_RATIO PCT_P_F_UNREAD
    10/10/2014 3:40:53 PM 0 IBMDEFAULTBP 99.6 16.8
    10/10/2014 3:40:53 PM 0 DWHPOOL

    Can any body tell me why it is so even if I am querying the tables residing in tbsp linked with bp DWHPOOL ?

    thanks in advance
    ssumit

  2. #2
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    hi, can you check if the bufferpool is activated.
    - Ananth
    DB2 DBA LUW
    "coming second, just means you are the first loser"

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    this is probably due to expressions in the SELECT list like this:
    case when POOL_DATA_L_READS < 1000 then null else ... end
    Seems that the numbers of physical / logical reads are too small for such a select statement...
    Regards,
    Mark.

Posting Permissions

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