EXTENT and PREFETCH

trinmoy · #1 (**permalink**) 04-06-04, 03:26

Hello gurus' Looking forward to increase my knowledge on the db2 ...

1. what is an EXTENT ?
2. how much EXTENT size is requrd for a table and view to create ?
3. wht is prefetch ?
4. how prefetch plays a important role for improving DB2 preformance ?
5. how to use prefetch ?

hopefully I will get better theoritical and technical knowledge from you all.

best regards

moy

sathyaram_s · #2 (**permalink**) 04-06-04, 03:35

A recent article on High Performance talks about all performance topics ... You can find it at :

http://www-106.ibm.com/developerwork...hur/index.html

'Copy-pasted' from the article

Quote:

Extent size
The Extent Size specifies the number of PAGESIZE pages that will be written to a container before skipping to the next container and is defined at table space creation time (and cannot be easily modified after). Smaller tables are handled more efficiently with smaller extents.

Rule of thumb is based on the average size of a table in the table space:

Less than 25 MB, use an Extent Size of 8
Between 25 and 250 MB, use an Extent Size of 16
Between 250 MB and 2 GB, use an Extent Size of 32
Greater than 2 GB, use an Extent Size of 64
Use larger values for OLAP databases and tables that are mostly scanned (query only) or have a very high growth rate.

If the table space resides on a disk array, set the extent size to the stripe size (that is, data written to one disk of the array).

Prefetch size
Prefetch size can be changed easily using ALTER TABLESPACE. The optimal setting seems to be: Prefetch Size = (# Containers of the table space on different physical disks) * Extent Size

If the table space resides on a disk array, set it as: PREFETCH SIZE = EXTENT SIZE * (# of non-parity disks in array).

DB2 v8 Documentation:

Concepts ==> Administration ==> Database design ==> Physical ==> Table Space Design
Reference ==> SQL ==> SQL Statements ==> CREATE TABLESPACE
Reference ==> SQL ==> SQL Statements ==> ALTER TABLESPACE

HTH

Sathyaram

Internet Services

Web Development

Digital Marketing

Consumer Tech