Can anyone explain why an insert might cause multiple logical
bufferpool data reads?
Here's a situation that has me scratching my head.
Table A's data resides in tablespace B
Table A's indexes reside in tablespace C
Table A has no long data
No other data resides in either of these tablespaces.
According to the dynamic SQL snapshot there are four different
statements accounting for access to table A: An insert and three
select statements. The first select statement is qualified by the
primary key and rows read = number of executions. The second and
third statements are qualified by two different sets of indexed
columns and these queries read and average of 3 rows per execution
according to the dynamic cache snapshot.
From a referential integrity point of view: Table A has three parent
tables and no children.
According to the tablespace and table snapshots (see below), I see an
average of over 100 logical bufferpool data reads per row read/write.
Having observed these same statistics during times of read access
only, it appears that the bulk of the bufferpool access occurs as a
result of the inserts. Extrapolating from my read-only observations,
I estimate that nearly 1,000 bufferpool reads are executed for every
row inserted.
Comparing this to the same statistics for other tables in the database
I can find nothing that even remotely approaches these kinds of
ratios.
Running DB2/UDB v 7.2 on AIX.
Any and all insights or theories are welcome.
Table Name = A
Table Type = User
Rows Read = 387568
Rows Written = 51750
Overflows = 0
Page Reorgs = 45
Tablespace name = B
Buffer pool data logical reads = 50468678
Buffer pool data physical reads = 5835113
Asynchronous pool data page reads = 0
Buffer pool data writes = 1862
Asynchronous pool data page writes = 1855
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Asynchronous pool index page reads = 0
Buffer pool index writes = 0
Asynchronous pool index page writes = 0
Total buffer pool read time (ms) = 30975627
Total buffer pool write time (ms) = 1648125
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 1602559
Asynchronous read requests = 0
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Number of files closed = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Tablespace name = C
Buffer pool data logical reads = 1197
Buffer pool data physical reads = 215
Asynchronous pool data page reads = 0
Buffer pool data writes = 3
Asynchronous pool data page writes = 3
Buffer pool index logical reads = 1779535
Buffer pool index physical reads = 321165
Asynchronous pool index page reads = 1170
Buffer pool index writes = 69250
Asynchronous pool index page writes = 69084
Total buffer pool read time (ms) = 1453091
Total buffer pool write time (ms) = 99786
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 99384
Asynchronous read requests = 0
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Number of files closed = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0