Using a Statistical View to Improve Query Performance

You may also like...

5 Responses

  1. raf mathijs says:

    In DB2 9.1 we had created statistical views to help the database optimize queries on our union all views, however when we tried to drop the tables contained in the views, we ran into issues since dropping the statistical views left still a packed descriptor.
    we had to use db2cat to identify the packed descriptor, recreate the statistical view,
    alter … disable query optimization, and then drop it again.

    https://www-304.ibm.com/support/docview.wss?uid=swg1IZ39997
    https://www-304.ibm.com/support/docview.wss?uid=swg1IZ40001

    I think the feature could help us, since we are in 9.7 now, however I’m still reluctant to risk it again.

    • Ember Crooks says:

      9.1 was the first release where it was fully implemented. I’d give it a try again if I were you – often there are issues like that when a feature is first implemented, and then they go away in later releases. I have not, however, tried dropping one lately, so I couldn’t say for sure.

  2. Nitesh says:

    Thanks for explaining the things with detail example.

  3. Suvradeep Sensarma says:

    Hi Ember,
    I have some doubt related to monitoring elements DIRECT_READ_TIME and POOL_READ_TIME fetched from the table MON_GET_PKG_CACHE_STMT. Which one indicates the time for which DB2 waits for data to be fetched from disk to Bufferpool?

    Suvradeep Sensarma

    • Ember Crooks says:

      It depends on which part of db2 you’re talking about “waiting”. If you’re talking about agents directly performing work for applications, then DIRECT_READ_TIME is the time spent reading data from disk, by the agents, into the bufferpool. This is usually the one you might be more worried about. I think that POOL_READ_TIME would be the time spent prefetching data into the buffer pools, which is usually a bit less of a concern. However, I am pretty sure that POOL_READ_TIME includes DIRECT_READ_TIME since all pages read go through the buffer pool unless they are LOBS. Usually the I/O details are calculated in terms of time per page. See http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.mon.doc/doc/r0001241.html?cp=SSEPGG_10.5.0 for more details.

Leave a Reply

Your email address will not be published. Required fields are marked *