Results 1 to 5 of 5

Thread: Bufferpool size

  1. #1
    Join Date
    Sep 2003
    Posts
    84

    Unanswered: Bufferpool size

    I ran a query like SELECT * from order where orderid = 4 ( orderid PK on the table) and the visual explain ( run from command center was showing an Index I/O of 2).
    I trebled the bufferpool size and ran the query agin but the I/O remained at 2.
    Why is this ? I would have thought that the I/O would be reduced to 1.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The Explain is not influenced by the buffer pool or whether a page (and the needed row) is already in the buffer pool or must be fetched from disk and placed in the buffer pool. It only shows logical reads, regardless of whether a physical read is necessary or not. If you actually run the query, you can monitor the number of logical reads vs physical reads by using a snapshot.
    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
    Sep 2003
    Posts
    84
    sorry to ask you this - feel like a dumb but I do not know and need to know - what is the difference between logical read and physical read ?
    And the i/o that the explain shows - how does it get the figure ?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A logical read is when the data is already in the buffer pool, and a physical read is when the data must be obtained from disk and put in the buffer pool. The explain does not track whether data is already in the buffer pool. Maybe you should look at the Administration Guide which has a good explanation of how buffer pools work.

    I assume (but do not know for sure) that it takes 1 I/O to read the non-leaf index page, and 1 I/O to read the index leaf page (where DB2 can find the index value and the RID).
    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
    Jul 2003
    Posts
    6

    Re: Bufferpool size

    EXPLAIN tells you about the "ACCESS PATH" of the SQL statement, for example what index, what type of table joins, prefetch etc. If you have a bigger bufferpool the GETPAGES will be reduced and you may see better runtime performance, depending upon the nature of the SQL query and activity from other concurrent users of the pools. To measure the impact of Bufferpool size, you'll need to analyze the DB2 Performance/Accounting Reports.

Posting Permissions

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