View Poll Results: SEQDETECT in DB CFG

Voters
2. You may not vote on this poll
  • SEQDETECT to NO

    0 0%
  • SEQDETECT to YES

    2 100.00%
Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    48

    Unanswered: Parameter SEQDETECT in db cfg

    Hi

    In udb version 7.1 in db cfg they have parameter SEQDETECT. When i read the mannual it says that it will be useful for activating sequential prefetching.
    To my knowledge, Sequential prefetch is good if you have all the data in the sequential manner and moreover there should not be any overflow records. But practically for OLTP High Availability systems it is not possible to have reorg in weekly basis. So in that case it will be better to go for list prefetch.

    So can i go ahead and change to db cfg SEQDETECT as No. If i make this to no what are all the consequences i have to face.

    Thanks
    Nagesh

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Sequential prefetch is most often used by DB2 when doing a tablespace scan. DB2 notices that a process is asking for a lot of sequential pages in the same tablespace and starts placing pages in the buffer pool even before they are asked for. There is a similar concept relating to index pages called list prefetch.

    Ideally in an OLTP environment, there will not be any table space scans (except for very small tables). In addition, some SQL statements where DB2 opens a cursor, may only retrieve a few rows because of the data distribution, but DB2 does not know that, and starts prefetch in a situation where it is not needed (and is not wanted). One way to prevent this (besides changing SEQDETECT) is use the OPTIMIZE FOR n ROWS in the SELECT statement. The value you use for n is not "binding" and is only used for access path optimization.

    DB2 also uses prefetch for indexes, which is called list prefetch. If an SQL statement uses an index but does not use the first column of a composite index, an index scan is required. DB2 may sometimes also use an index scans for complex joins. The SEQDETECT applies to both data and index pages.

    DB2 might also want to use prefetch for utilities (reorg, runstats, etc) when a tablespace scan does occur. For these reasons, you may want to be careful about completely shutting off prefetch. If you use OPTIMIZE FOR n ROWS in the SELECT statement you can probably avoid any unwanted prefetch.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Just to clarify my response above:

    DB2 will not automatically use prefetch on every SQL statement just because SEQDETECT is enabled. In order for prefetch to occur, DB2 would detect that a lot of sequential access to the same tablespace or index is actually occurring, or DB2 is fairly certain that it will occur.

    The case where DB2 may be most often fooled in this respect is an SQL statement with a >, <, or BETWEEN predicate, or a predicate with a very low cardinality. In these cases, if you tell DB2 the expected number of rows to be SELECTed or FETCHed with the OPTIMIZE FOR n ROWS, then DB2 is more likely to make the correct decision. But, remember that sometimes DB2 has to read the entire cursor range to return the first row if an order by or other clause forces materialization.

  4. #4
    Join Date
    Mar 2003
    Posts
    343
    What if SEQDETECT is set to YES and DFT_PREFETCH_SZ is set to some value. And db2 decides that it will benefit from prefetching, however, the tablespace in which the table is placed has a prefetchsize explicitly set to 0. Will it then use the dft_prefetch_sz?

  5. #5
    Join Date
    Jul 2002
    Posts
    48
    Thanks a lot for replying to my query...

    Nagesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •