By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 862 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

Bufferpool access on insert

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"Paul Shaw" <pa*******@eds.com> wrote in message
news:ee**************************@posting.google.c om...
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.

Is there a clustering index on the table? Is DB2 trying to insert the data
into the correct data page (and looking for a place to insert it) or is it
always inserting at the end of the table (append mode or no clustering
index).

Is there enough freespace for indexes inserted (all index rows are put in
the correct sequence).

Is there any lock contention issues where DB2 keeps checking for the lock to
be released?

One thing I would try is to define percent free for the table and indexes
and then reorg both. Then execute runstats with full statistics and run the
test again. Also check to make sure that the indexes are be used properly
and there are no tablespace scans or full index scans (not using the
b-tree).
Nov 12 '05 #2

P: n/a
If DB2 believes any of your parent tables to be near-empty, it could
scan them rather than use an index. Make sure RUNSTATS has been run on
the insert table plus all parent tables.
DG
Nov 12 '05 #3

P: n/a
Thanks for the insight. Your comments about clustering indexes and
free space were right on the mark.

I've altered and reorg'ed the table. Tests run since then have
produced remarkably better results.

Follow up question ...

Is there any way to determine the average free space per page ... or
some similar metric that would identify a need to reorg a table in
this situation?


"Mark A" <ma@switchboard.net> wrote in message news:<QI*****************@news.uswest.net>...

....

Is there a clustering index on the table? Is DB2 trying to insert the data
into the correct data page (and looking for a place to insert it) or is it
always inserting at the end of the table (append mode or no clustering
index).

....
Nov 12 '05 #4

P: n/a
> "Mark A" <ma@switchboard.net> wrote in message
news:<QI*****************@news.uswest.net>...
Is there a clustering index on the table? Is DB2 trying to insert the data into the correct data page (and looking for a place to insert it) or is it always inserting at the end of the table (append mode or no clustering
index).

"Paul Shaw" <pa*******@eds.com> wrote in message
news:ee**************************@posting.google.c om... Thanks for the insight. Your comments about clustering indexes and
free space were right on the mark.

I've altered and reorg'ed the table. Tests run since then have
produced remarkably better results.

Follow up question ...

Is there any way to determine the average free space per page ... or
some similar metric that would identify a need to reorg a table in
this situation?

You can run the reorgchk command. Check the Command Reference for the
meaning of the output report.

Another way to approach the issue is to figure out what percent of the table
is inserted within a given time period (say a week, for example). If the
table grows by 10% per week, then having a percent free of 15% (5% extra
margin of safety) and doing a reorg once per week (to recreate the percent
free) would probably work well. The more frequently you reorg, the less
percent free is required.

However, you do need to take into consideration whether the distribution of
inserts is truly random (according to the clustering index), and also take
into consideration the number of deletes (which free up space on the page)
in the same time period. Updates to varchar columns can also have an adverse
effect because the rows may not fit back in the old space on the page.

As mentioned previously (IIRC), setting the table to append might ensure
better insert performance since rows will be inserted at the end of the
table. However, this will not help updates of varchar columns, and could
create a hotspot if a large number of users are trying to insert at the same
time. Using append could also hurt select performance if the table is not
clustered in an optimal manner.
Nov 12 '05 #5

P: n/a

"Mark A" <ma@switchboard.net> wrote in message
news:o2****************@news.uswest.net...
"Mark A" <ma@switchboard.net> wrote in message news:<QI*****************@news.uswest.net>...
Is there a clustering index on the table? Is DB2 trying to insert the data into the correct data page (and looking for a place to insert it) or
is it always inserting at the end of the table (append mode or no clustering
index).

"Paul Shaw" <pa*******@eds.com> wrote in message
news:ee**************************@posting.google.c om...
Thanks for the insight. Your comments about clustering indexes and
free space were right on the mark.

I've altered and reorg'ed the table. Tests run since then have
produced remarkably better results.

Follow up question ...

Is there any way to determine the average free space per page ... or
some similar metric that would identify a need to reorg a table in
this situation?

You can run the reorgchk command. Check the Command Reference for the
meaning of the output report.

Another way to approach the issue is to figure out what percent of the

table is inserted within a given time period (say a week, for example). If the
table grows by 10% per week, then having a percent free of 15% (5% extra
margin of safety) and doing a reorg once per week (to recreate the percent
free) would probably work well. The more frequently you reorg, the less
percent free is required.

However, you do need to take into consideration whether the distribution of inserts is truly random (according to the clustering index), and also take
into consideration the number of deletes (which free up space on the page)
in the same time period. Updates to varchar columns can also have an adverse effect because the rows may not fit back in the old space on the page.

As mentioned previously (IIRC), setting the table to append might ensure
better insert performance since rows will be inserted at the end of the
table. However, this will not help updates of varchar columns, and could
create a hotspot if a large number of users are trying to insert at the same time. Using append could also hurt select performance if the table is not
clustered in an optimal manner.

I forgot to add that you should also check the percent free on indexes the
same way. Index are always stored in the exact order of the index key and
percent free is very important for indexes when there is insert activity.
Also make sure you don't have any low cardinality indexes such as sex (m/f),
or division (where there are only a few divisions). Low cardinality indexes
require a lot of work for DB2 to add new rows, and these indexes will not be
used by DB2 to increase performance.

Note, this does not apply to situations where a column such a "division" is
part of a multi-column index. In the case of multi-column indexes, the
cardinality of the entire index key is important, and not of just any one
column in the index.
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.