Hi all,
Ive got a database approx 350 GB in which Im getting very high Time
waited for prefetch. This is directly out of the snapshot for the db
(these are for the entire database I assume as I pulled it out of get
snapshot for all on {dbname})
Total buffer pool read time (milliseconds) = 45660639
Total buffer pool write time (milliseconds)= 42128058
Total elapsed asynchronous read time = 33856320
Total elapsed asynchronous write time = 21817205
Asynchronous data read requests = 2127045
Asynchronous index read requests = 92769
No victim buffers available = 8785764
LSN Gap cleaner triggers = 691047
Dirty page steal cleaner triggers = 1298
Dirty page threshold cleaner triggers = 6637
Time waited for prefetch (ms) = 2386004
Unread prefetch pages = 945322
Direct reads = 3298851768
Direct writes = 17558698
Direct read requests = 6794130
Direct write requests = 69421
Direct reads elapsed time (ms) = 60671696
Direct write elapsed time (ms) = 3467406
Database files closed = 240580
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
so with those figures in mind, is the 40 minutes I see in time waited
for prefetch too high? Ive read repetedly that If its in the seconds
you should be concerned...but Ive not read much about fixing it other
than increase IO_Servers (which I did to 20) and prefetch size (which
is 20 times the extent size) and turn on IO parrallelism (which is
DB2_PARALLEL_IO=*) and make sure your using a raid array (which were
using an IBM San machine which uses for our Lpar at least 16-20 disks)
So what am I missing?