Interpreting Explain output in Plan_table

vijikesavan · Posted: Thu Oct 12, 2006 3:33 am

Hi,
I have run a explain to optimize a query. Found the value of a column prefetch as 'D' . Not sure wht this means?
Any pointers would help.

Thanks,
Viji

arka_nandi · New User Joined: 15 Dec 2005 Posts: 2

Below are posible values for the prefetch column in the plan table and their explanation -

S = pure sequential prefetch;
L = prefetch through a page list;
D = optimizer expects dynamic prefetch;
blank = unknown at bind time or no prefetch.

Hope this helps.

- Arka.

mkarmegam · New User Joined: 28 May 2006 Posts: 13 Location: USA

More details...
| 5.11.5.2 Dynamic prefetch (PREFETCH=D)

| Dynamic prefetch can reduce paging and improve performance over sequential
| prefetch for some data access that involves data that is not on
| consecutive pages. When DB2 expects that dynamic prefetch will be used,
| DB2 sets PREFETCH=D. At runtime, dynamic prefetch might or might not
| actually be used. However, DB2 expects dynamic prefetch and optimizes for
| that behavior.

| When dynamic prefetch is used: Dynamic prefetch is used in prefetch
| situations when the pages that DB2 will access are distributed in a
| nonconsecutive manner. If the pages are distributed in a sufficiently
| consecutive manner, sequential prefetch is used instead.