Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: how to use SYSIBMADM.BP_HITRATIO admin. view?

    DB2 v9.5.1 ESE AIX v6.1

    I’d like to use SYSIBMADM.BP_HITRATIO admin. view to tune the bufferpools, but could not find anywhere if data in it is cumulative or instant, or for some time period.
    Does it require to turn on the bufferpool snapshot switch? I know I need to do it for db2pd –bufferpools
    How often information is getting updated in the view?
    What would be your suggestion - if I am running a performance test which take 6 hrs, how often does it make sense to select from this view to get info?

    Thanks in advance
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MarkhamDBA View Post
    I’d like to use SYSIBMADM.BP_HITRATIO admin. view to tune the bufferpools, but could not find anywhere if data in it is cumulative or instant, or for some time period. Does it require to turn on the bufferpool snapshot switch?
    Hit ratios are cumulative by definition. Information is collected from the moment the database starts, provided that the DFT_MON_BUFPOOL parameter is set.

    Quote Originally Posted by MarkhamDBA View Post
    How often information is getting updated in the view?
    You may remember that a view (any view) does not store information. The monitoring views call the corresponding table functions behind the scenes, which in turn retrieve snapshot monitor element values at that time.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by n_i View Post
    Hit ratios are cumulative by definition. Information is collected from the moment the database starts, provided that the DFT_MON_BUFPOOL parameter is set.

    You may remember that a view (any view) does not store information. The monitoring views call the corresponding table functions behind the scenes, which in turn retrieve snapshot monitor element values at that time.
    - OR from the moment when dft_mon_bufpool set to ON if db is already active? Pls correct me if I am wrong

    - which means that data is collected at the time of SELECT from admin. view? Again pls correct me if I am wrong
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    restarted the database and selected data with bp monitor off: Buffer pool (DFT_MON_BUFPOOL) = OFF
    and it is still showing data:

    BP_NAME TOTAL_LOGICAL_READS TOTAL_PHYSICAL_READS TOTAL_HIT_RATIO_PERCENT DATA_HIT_RATIO_PERCENT INDEX_HIT_RATIO_PERCENT XDA_HIT_RATIO_PERCENT
    -------------- -------------------- -------------------- ----------------------- ---------------------- ----------------------- ---------------------
    BP_16K_1 150 348 -132.00 -357.14 2.12 -
    BP_SYSCATSPC 545 274 49.72 73.21 39.25 -
    BP_TBS_PROD 0 0 - - -


    why am I still getting numbers? and why are they negative? and what are we supposed to do based on this "intel"?

    Thanks in advance
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MarkhamDBA View Post
    - OR from the moment when dft_mon_bufpool set to ON if db is already active?
    I believe so.

    Quote Originally Posted by MarkhamDBA View Post
    - which means that data is collected at the time of SELECT from admin. view? wrong
    Yes, that is what I said.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    and why even with all monitors OFF, db2pd -db <db_name> -bufferpools' showing data as well:

    Bufferpool Statistics for all bufferpools (when BUFFERPOOL monitor switch is ON):

    BPID DatLRds DatPRds HitRatio TmpDatLRds TmpDatPRds HitRatio IdxLRds IdxPRds HitRatio TmpIdxLRds TmpIdxPRds HitRatio
    1 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
    2 336 90 73.21% 0 0 00.00% 752 458 39.10% 0 0 00.00%
    3 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
    4 58 257 -343.10% 0 0 00.00% 188 184 02.13% 0 0 00.00%
    4096 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
    4097 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
    4098 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
    4099 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  7. #7
    Join Date
    Nov 2009
    Posts
    21
    Provided Answers: 1

    Sysibmadm.bp_hitratio

    Let me share some of my experiences with the snapshot administrative views.
    We can use the snapshot administrative views to capture the current state of the database. In particular about the SYSIBMADM.BP_HITRATIO
    view we get the information about the bufferpool data hit ratio,bufferpool index hit ratio and bufferpool hit ratio.
    Regarding your next question about db2pd -bufferpools yes that is another best option to see the bufferpool hit ratio. And this SYSIBMADM view is based on snapshot timestamp and it does require monitor swtiches on. Let me make it clear I am not talking about the dbm cfg parameters DFT_MON_........ You can also use this view for your session by turning on the monitor switches for your session only.
    db2 get monitor switches and then turn them on . Once you disconnect from the database it goes back to its original state.
    In this way you can avoid overhead and in your case if you are running a performance test for 6 hours you can automate with a script. I dont know shell scripting so I am just giving you a small example.


    USAGE : BP_HITRATIO.ksh <Database name>
    db2 connect to $1;
    db2 update monitor switches using BUFFERPOOL ON SORT ON TABLE ON UOW ON LOCK ON TIMESTAMP ON STATEMENT ON;
    db2 "SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(BP_NAME,1,14) AS BP_NAME,
    TOTAL_HIT_RATIO_PERCENT, DATA_HIT_RATIO_PERCENT,
    INDEX_HIT_RATIO_PERCENT, XDA_HIT_RATIO_PERCENT, DBPARTITIONNUM
    FROM SYSIBMADM.BP_HITRATIO ORDER BY DBPARTITIONNUM" ;
    db2 terminate;


    And the output looks something like this
    DB_NAME BP_NAME TOTAL_HIT_RATIO_PERCENT DATA_HIT_RATIO_PERCENT INDEX_HIT_RATIO_PERCENT XDA_HIT_RATIO_PERCENT DBPARTITI
    ONNUM
    -------- -------------- ----------------------- ---------------------- ----------------------- --------------------- ---------
    -----
    ODD003T IBMDEFAULTBP 63.52 73.07 50.73 -
    0
    ODD003T BP6 0.00 0.00 - -
    0
    ODD003T BP7 0.00 0.00 - -
    0
    ODD003T BP8 0.00 0.00 - -
    0
    ODD003T BP9 0.00 0.00 - -
    0
    ODD003T IBMSYSTEMBP4K - - - -
    0
    ODD003T IBMSYSTEMBP8K - - - -
    0
    ODD003T IBMSYSTEMBP16K - - - -
    0
    ODD003T IBMSYSTEMBP32K - - - -
    0

    9 record(s) selected.

    Then if you want you can further export this to a excel file and make you script better.

    These views are really helpful to anlayze the bufferpool hit ratio easily rather than going through the whole bufferpool snapshot.

    Hope this would have given an insight ... LET ME KNOW IF YOU GET MORE INFORMATION ABOUT THIS I AM JUST A BEGINNER TOO I AM READY TO LEARN !!!!!!!!!!!!!

    Thanks

    Surgeon

  8. #8
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    so I run this query to get bp_hitratio:
    db2 "select substr(BP_NAME,1,14) bp_name ,TOTAL_LOGICAL_READS TOT_LOG_READS ,TOTAL_PHYSICAL_READS tot_phys_reads ,TOTAL_HIT_RATIO_PERCENT tothitratio ,DATA_HIT_RATIO_PERCENT datahitratio ,INDEX_HIT_RATIO_PERCENT idxhitratio ,substr(to_char(SNAPSHOT_TIMESTAMP,’YYYY-MM-DD-HH24:MMS’),1,19) snapshot_ts from SYSIBMADM.BP_HITRATIO"

    and it works well from command line (as long as I am connected to the database). However when I run it in the script (every 60 secs) data does not get updated:
    BP_NAME TOT_LOG_READS TOT_PHYS_READS TOTHITRATIO DATAHITRATIO IDXHITRATIO SNAPSHOT_TIMESTAMP
    -------------- -------------------- -------------------- ----------- ------------ ----------- -------------------
    IBMDEFAULTBP 426 0 100.00 100.00 - 2010-01-08-15:01:45
    BP_SYSCATSPC 0 0 - - - 2010-01-08-15:01:45
    BP_TBS_PROD 0 0 - - - 2010-01-08-15:01:45
    BP_TBS16K 0 0 - - - 2010-01-08-15:01:45
    IBMSYSTEMBP4K 0 0 - - - 2010-01-08-15:01:45
    IBMSYSTEMBP8K 0 0 - - - 2010-01-08-15:01:45
    IBMSYSTEMBP16K 0 0 - - - 2010-01-08-15:01:45
    IBMSYSTEMBP32K 0 0 - - - 2010-01-08-15:01:45


    Timestamp remains the same. What and where is the problem?
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  9. #9
    Join Date
    Nov 2009
    Posts
    21
    Provided Answers: 1
    Can you post the script you are trying to run.... so that I can help you from my side....

    Thanks

    Surgeon

  10. #10
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by surgeon View Post
    Can you post the script you are trying to run.... so that I can help you from my side....

    Thanks

    Surgeon

    how do i post a large script here?
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by MarkhamDBA View Post
    how do i post a large script here?
    Can you attach the script.

    When replying click Go Advance and then Manage Attachments

    DBFinder

  12. #12
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by MarkhamDBA View Post
    so I run this query to get bp_hitratio:
    db2 "select substr(BP_NAME,1,14) bp_name ,TOTAL_LOGICAL_READS TOT_LOG_READS ,TOTAL_PHYSICAL_READS tot_phys_reads ,TOTAL_HIT_RATIO_PERCENT tothitratio ,DATA_HIT_RATIO_PERCENT datahitratio ,INDEX_HIT_RATIO_PERCENT idxhitratio ,substr(to_char(SNAPSHOT_TIMESTAMP,’YYYY-MM-DD-HH24:MMS’),1,19) snapshot_ts from SYSIBMADM.BP_HITRATIO"

    and it works well from command line (as long as I am connected to the database). However when I run it in the script (every 60 secs) data does not get updated:
    BP_NAME TOT_LOG_READS TOT_PHYS_READS TOTHITRATIO DATAHITRATIO IDXHITRATIO SNAPSHOT_TIMESTAMP
    -------------- -------------------- -------------------- ----------- ------------ ----------- -------------------
    IBMDEFAULTBP 426 0 100.00 100.00 - 2010-01-08-15:01:45
    BP_SYSCATSPC 0 0 - - - 2010-01-08-15:01:45
    BP_TBS_PROD 0 0 - - - 2010-01-08-15:01:45
    BP_TBS16K 0 0 - - - 2010-01-08-15:01:45
    IBMSYSTEMBP4K 0 0 - - - 2010-01-08-15:01:45
    IBMSYSTEMBP8K 0 0 - - - 2010-01-08-15:01:45
    IBMSYSTEMBP16K 0 0 - - - 2010-01-08-15:01:45
    IBMSYSTEMBP32K 0 0 - - - 2010-01-08-15:01:45


    Timestamp remains the same. What and where is the problem?
    found an error:
    i used
    substr(to_char(SNAPSHOT_TIMESTAMP,'YYYY-MM-DD-HH24:MMS') instead
    substr(to_char(SNAPSHOT_TIMESTAMP,'YYYY-MM-DD-HH24:MI:SS')
    so it was showing month instead of minuted.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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