Results 1 to 6 of 6

Thread: some doubts

  1. #1
    Join Date
    Jan 2008
    Posts
    88

    Unanswered: some doubts

    Hi All, I have 2 doubts regarding DB2
    1 ) Does dynamic SQL snapshot contains only dynamic SQL or both static and dynamic .
    If only dynamic, when I flush the dynamic package cache why it only deletes some of the data and not all. Also , I can see some of the static SQLs executed by me in the dynamic SQL snapshot.

    2) while going through asynchronous and synchronous reads, I came across two different formulas to calculate it

    for synchronous read ratio :
    SRP = 100 - (((Asynchronous pool data page reads + Asynchronous pool index page reads) x 100) / (Buffer pool data physical reads + Buffer pool index physical reads))

    and for asynchronous read ratio :
    ARP = ((Asynchronous pool data page reads + Asynchronous pool index page reads) / (Buffer pool data logical reads + Buffer pool index logical reads)) * 100%


    My confusion is why while taking synchronous read ratio we are comapring it with physical reads only and while calculating asynchronous read ratio we are comparing it with logical reads. Shouldn't ARR be compared with physical reads only and if we are calculating the number of Asynchronous pages read, shouldn't we subtract unread prefetch pages from the numerator of ARR formula.

    Is I am missing something somewhere. Can anyone explain it to me.I have searched the whole internet but never got any satisfactory reasons.

    Thanks for help

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    1) Only dynamic. The reason you still see statements after a FLUSH is that a flush only marks the cache as invalid, it does not erase the sql. This will happen as subsequent sql is processed.

    2) Why would you want to subtract an asynchronous read that did not get used? You still had to incur the cost of the read so it should count.

    Andy

  3. #3
    Join Date
    Jan 2008
    Posts
    88
    Hi Andy,
    thanks for replying.
    1) why can i see the statements I have executed locally like some select count(*) from tables in dynamic SQL snapshot
    2) why Synchronous read is divided by physical reads and asynchronous reads by logical reads, why asynchronous reads is not divided by physical reads while calcualting ARR

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    1) Because that is still dynamic SQL.
    2) Because a synchronous read is when the DBM has to wait for the data to be retrieved from disk (physical read). An asynchronous read is when the physical reads are done ahead of time and the data is read from the bufferpool. Reading from the bufferpool is a logical read.

    Andy

  5. #5
    Join Date
    Jan 2008
    Posts
    88
    Thanks Andy for clearing my doubts, just one more confusion
    what if I have a tablespace which has both synchronous read ratio and asynchrnous read ratio very low. Now according to what i have learnt about them is when ARR is low that implies SRR is high and vice versa. so what should I conclude from it.
    I think my whole understanding is a bit messed.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You want synchronous ratio low. Synchronous reads are "bad". When the ARR is low using this formula, this is not necessarily bad. It means that you are reading a lot of stuff from the bufferpool and not having to read from disk. This is actually good. I bet your bufferpool hit ratio is high for this tablespace. I am also betting that your DB is OLTP as apposed to DW.

    Andy

Posting Permissions

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