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

Prefetchers

P: n/a
OK, there are lots of recommendations out there for number of
prefetchers to configure. But, there isn't any guidance on how to tell
if I have too many or not enough? What metrics can I use to figure
this out....Thx, Flick
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
If you have too few prefetchers (NUM_IOSERVERS is too low) you will
see relatively high values for "Time waited for prefetch (ms)" and
slow performance for queries that scan lots of rows. Prefetchers don't
do much when they're not being used, so there is little risk
associated with specifying too many prefetchers.

In addition to defining enough prefetchers, it is important to
identify the larger tables that are likely to be scanned and make sure
they are placed in separate tablespaces. When multiple tablespaces are
involved in a biq query that scans, there is a good chance that
multiple prefetchers will be used to pull in all the extents needed by
the query.

Good luck,

Fred
Nov 12 '05 #2

P: n/a
If i may add,
you'll also see high Time waited for prefetch (ms)" when you're too
agressively prefetching.
e.g. prefetch queue(s) are full, you're asking too many io's per sec (there
the wait).
Also if you prefetch more than the bp size you use, you compete for bp
space.
You should see something like stolen pages or forced writes, cleaners
triggered and waits, etc.

If you want to investigate more, i suggest you read a bit on DB2BPVARS.
http://publib.boulder.ibm.com/infoce...n/r0005665.htm

A good and simple way to measure the io performance is the old ms/io.
You start low and increase and see what it does on a tablescan or your
workload.
There would be a relation with the number of io's and the size you're
asking. (containers setup, extent and prefetch values, etc.)

In the end, every environment is different. (io capacity of adapters used,
etc.)

A general rule of thumb is NUM_IOSERVERS = nb cpu + a few extra.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.