If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Bufferpool size

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 04-20-04, 01:58
Dipanjan Dipanjan is offline
Registered User
 
Join Date: Sep 2003
Posts: 84
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.
Reply With Quote
  #2 (permalink)  
Old 04-20-04, 02:14
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,198
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
Reply With Quote
  #3 (permalink)  
Old 04-20-04, 03:42
Dipanjan Dipanjan is offline
Registered User
 
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 ?
Reply With Quote
  #4 (permalink)  
Old 04-20-04, 03:51
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,198
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
Reply With Quote
  #5 (permalink)  
Old 04-20-04, 07:03
cdhebar cdhebar is offline
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On